logo标签列表

需要SQL Server查询帮助，新手

sqlsql-server
5
我有一个表格，其中包含以下列。 
id, 
compid(used to identify a piece of equipment), 
startalarmdate, 
endalarmdate
当一台设备进入报警状态时，我会将compid和startalarmdate（当前时间）插入表格中。当设备退出报警状态时，我会在同一行的endalarmdate列中填写null，并将当前时间填入其中。 因此，我的表格会有以下类似的内容： 
417,6,Sun Oct 30 18:48:17 CDT 2011,Mon Oct 31 09:49:21 CDT 2011
422,6,Mon Oct 31 10:19:19 CDT 2011,Mon Oct 31 12:19:22 CDT 2011
427,6,Mon Oct 31 20:19:56 CDT 2011,Tue Nov 01 09:50:59 CDT 2011
429,6,Tue Nov 01 21:51:41 CDT 2011,Wed Nov 02 09:52:37 CDT 2011
432,6,Wed Nov 02 21:23:23 CDT 2011,Fri Nov 04 16:26:29 CDT 2011

我能够构建一个查询，为每个事件提供总停机时间（以小时为单位），但现在我想构建一个查询，为月份中的每一天提供总停机时间（以小时为单位）。我希望它将compid完全放在左侧，然后将该月的每一天放在同一行上compid的右侧列中。我希望没有停机时间的日期为空。使用此表的设置是否可能实现这一点？

问题：对于给定的一天，我猜开始日期是最重要的，即使分辨率是在午夜之后，是吗？ - fge
3
可以实现。你尝试过什么？你应该先查看DATEDIFF的文档。此外，你需要决定如何处理跨越一天的停机时间，这会让事情变得更复杂，但肯定是可行的。 - paulbailey
3个回答
1

正如 @paulbailey 所提到的，您需要使用 DATEDIFF 函数来获取停机时间的数量。

要提取日期和停机期间（我添加了一些您可能需要的列）...

SELECT compid,
       YEAR(startalarmdate) AS [Year],
       MONTH(startalarmdate) AS [Month],
       DAY(startalarmdate) AS [Day],
       DATEDIFF(ss, startalarmdate, endalarmdate) AS DowntimeInSeconds --You will need to convert thid later to the format you wish to use
FROM YourTable
/* WHERE CLAUSE - Most probably a date range */

现在这将为您提供每天停机时间的秒数，以便于出现停机的每一天。

要获得每天的停机时间很容易，只需按天分组并对停机时间进行求和（再添加您可能需要的更多列）。

SELECT compid, 
       [Year],
       [Month],
       [Day],
       SUM(DowntimeInSeconds) AS TotalDowntimeInSeconds
FROM (SELECT compid,
             YEAR(startalarmdate) AS [Year],
             MONTH(startalarmdate) AS [Month],
             DAY(startalarmdate) AS [Day],
             DATEDIFF(ss, startalarmdate, endalarmdate) AS DowntimeInSeconds --You will need to convert thid later to the format you wish to use
      FROM YourTable
      /* WHERE CLAUSE - Most probably a date range */) AS GetDowntimes
GROUP BY compid,  [Year], [Month], [Day]
ORDER BY [Year], [Month], [Day], compid

我相信这会帮助你达到你想要的目标。

编辑： 为了在结果中包含没有停机时间的日期，您需要首先拥有一个月中所有日期的列表。您可以使用LEFT OUTER JOIN将此列表与上述查询的结果连接（您需要先删除ORDER BY）。

1
Philip Kelley的答案中的case语句不起作用，尽管使用临时表填充日期并进行左连接的主要原则是正确的。对于我的版本，我使用了相同的变量来开始 - 输入日期和要报告的天数。 
DECLARE @StartDate DATETIME, @Days INT
SELECT  @StartDate = GETDATE(),
        @Days = 5

-- REMOVE ANY TIME FROM THE STARTDATE
SET @StartDate = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @StartDate))

-- CREATE THE TEMP TABLE OF DATES USING THE SAME METHODOLOGY
DECLARE @Dates TABLE (AlarmDate SMALLDATETIME NOT NULL PRIMARY KEY)
WHILE (@Days > 0)
BEGIN
    INSERT @Dates VALUES (DATEADD(DAY, @Days, @StartDate))
    SET @Days = @Days - 1
END

-- NOW SELECT THE DATA
SELECT  AlarmDate,
        CompID,
        CONVERT(DECIMAL(10, 2), ISNULL(SUM(DownTime), 0) / 3600.0) [DownTime]
FROM    @Dates
        LEFT JOIN
        (   SELECT  DATEADD(DAY, 0, DATEDIFF(DAY, 0, StartAlarmDate)) [StartAlarmDate],
                    CompID,
                    DATEDIFF(SECOND, StartAlarmDate, CASE WHEN EndAlarmDate >= DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate)) THEN DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate)) ELSE EndAlarmDate END) [DownTime]
            FROM    [yourTable]
            UNION ALL
            SELECT  DATEADD(DAY, 0, DATEDIFF(DAY, 0, EndAlarmDate)) [Date],
                    CompID,
                    DATEDIFF(SECOND, DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate)), EndAlarmDate) [DownTime]
            FROM    [yourTable]
            WHERE   EndAlarmDate >= DATEADD(DAY, 1, DATEDIFF(DAY, 0, StartAlarmDate))
        ) data
            ON StartAlarmDate = AlarmDate
GROUP BY AlarmDate, CompID

我已经使用秒来计算日期差，并在将秒数相加后除以3600.0，因为每个间隔一分钟的60行将在使用小时计算日期差时总和为0。

1
步骤1：设置一个临时表，其中包含您想要总计的所需“时间块”。这些块可以是任何时间范围；在您的示例中，它将为该月中每天（24小时）的每个条目设置一个条目。 
CREATE TABLE #TimeRanges
 (
   RangeStart  datetime  not null
  ,RangeEnd    datetime  not null
 )

在您的数据上进行左外连接此表可以确保每个时间块（天）至少获得一行，即使当天没有发生任何警报：

SELECT
   tr.RangeStart  --  Use start of each time block to identify the block
  ,md.CompId      --  With left outer join, will be 0 or more rows for each block
  ,sum(datediff(hh
                ,case
                   when tr.RangeStart > md.StartAlarmDate then tr.RangeStart
                   else md.StartAlarmDate
                 end
                ,case
                   when tr.RangeEnd > md.EndAlarmDate then tr.RangeEnd
                   else md.EndAlarmDate
                 end))  HoursInRange
 from #TimeRanges tr
  left outer join MyData md
   on md.StartAlarmDate < tr.RangeEnd
    and md.EndAlarmDate > tr.From
 group by
   tr.RangeStart
 ,md.CompId

我无法测试这段代码，可能需要进行一些调试--但是概念是可靠的。我会让你自己考虑如何四舍五入部分小时，以及是否要使用 > 和 <，还是 >= 和 <=（如果警报开始和/或结束时间恰好与块边界的某一点相同，则可能会变得棘手）。

编辑/补遗

以下是一种相当基本的方法来设置例程中使用的临时表（我测试过这段代码）：

--  Set up and initialize some variables
DECLARE
  @FirstDay      datetime
 ,@NumberOfDays  int

SET @FirstDay = 'Oct 1, 2011'  --  Without time, this makes it "midnight the morning of" that day
SET @NumberOfDays = 91  --  Through Dec 31


--  Creates a temporary table that will persist until it is dropped or the connection is closed
CREATE TABLE #TimeRanges
  (
    RangeStart  datetime  not null
   ,RangeEnd    datetime  not null
  ) 

--  The order in which you add rows to the table is irrelevant. By adding from last to first, I
--  only have to fuss around with one variable, instead of two (the counter and the end-point)

WHILE @NumberOfDays >= 0
 BEGIN
    INSERT #TimeRanges (RangeStart, RangeEnd)
     values ( dateadd(dd, @NumberOfDays, @FirstDay)       --  Start of day
             ,dateadd(dd, @NumberOfDays + 1, @FirstDay))  --  Start of the next day


    SET @NumberOfDays = @NumberOfDays - 1
 END


--  Review results
SELECT *
 from #TimeRanges
 order by RangeStart


--  Not necessary, but doesn't hurt, especially when testing code
DROP TABLE #TimeRanges
请注意，通过将RangeEnd设置为第二天的开始，您必须小心处理大于和小于符号。细节可能会变得非常微妙和繁琐，您需要进行大量边缘情况测试（例如，如果警报恰好在2011年12月16日00:00.000开始或结束）。我会选择这种方式，因为总体上它比像“2011年12月16日23:59.997”这样的垃圾代码更简单。
2
顺便说一句，我解决这类问题的方法是用纸和笔。为所有可能重叠和不重叠的时间段绘制线段，并相应地处理逻辑。 - Philip Kelley
我以前从未使用过临时表！我如何告诉它要使用什么范围？感谢您在此方面的帮助。我可以做一些基本的SQL操作，但这种高级操作超出了我的能力范围！ - Albert Lambert
添加了关于临时表的部分。 - Philip Kelley
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，