更改狀態時獲取記錄之間的總時間

我有一個表格，顯示每個設備在給定時間戳的狀態（健康、損壞）。我需要獲得整個故障的總時間。

所以數據是這樣儲存的：

device_owner   device_id    timestamp          status
owner1         device_1     2001-01-01 09:00   0
owner1         device_2     2001-01-01 09:15   0
owner1         device_1     2001-01-01 09:30   1
owner1         device_2     2001-01-01 09:45   1

等等。

上面的例子顯示

• device_1 從 09:00 下降到 09:30（共 30 分鐘），
• device_2 從 09:15 下降到 09:45（共 30 分鐘）

我需要的

需要計算整個故障的總時間為owner_145 分鐘（從 09:00 到 09:45）而不是 60 分鐘（30 + 30）。

device_owner   total_breakdown_min
owner_1        45

我做了什麼

目前我可以分別為每個設備定義一個故障時間（device_1 = 30mins，device_2 = 30mins）。為此，我使用以下方法轉換了表格LEAD over partition by device_owner, device_id and date part from timestamp：

device_owner  device_id     timestamp        status lead_timestamp   lead_status
owner1        device_1     2001-01-01 09:00   0     2001-01-01 09:30     1
owner1        device_1     2001-01-01 09:30   1     NULL                 NULL
owner1        device_2     2001-01-01 09:15   0     2001-01-01 09:45     1
owner1        device_2     2001-01-01 09:45   1     NULL                 NULL

因此，時間戳和lead_timestamp 之間的日期時間差總和為我提供了設備的總故障時間。

;WITH LeadStatus AS
(
   SELECT
       D.*,
       lead_status = LEAD(D.status) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC),
       lead_timestamp = LEAD(D.datetime) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC)
   FROM
       #DeviceStatus AS D
)
SELECT * FROM LeadStatus

對於任何所有者來說，它都是相同的設備列表。數據快照將在 2 個日期 ( StartDate, EndDate) 之間拍攝。記錄每 n 秒寫入一次 DB，並且在設備狀態發生變化時寫入。

我們可以使用條件聚合和OVER子句來獲取devicestatus.

SELECT device_owner,
      timestamp,
      sum(CASE status
            WHEN 0
              THEN 1
            WHEN 1
              THEN -1
          END) OVER (PARTITION BY device_owner
                     ORDER BY timestamp) broken#
      FROM devicestatus;

我們現在使用lag()來獲取每行的先前損壞設備的數量。

SELECT device_owner,
      timestamp,
      broken#,
      lag(broken#,
          1,
          0) OVER (PARTITION BY device_owner
                   ORDER BY timestamp) previous_broken#
      FROM (SELECT device_owner,
                   timestamp,
                   sum(CASE status
                         WHEN 0
                           THEN 1
                         WHEN 1
                           THEN -1
                       END) OVER (PARTITION BY device_owner
                                  ORDER BY timestamp) broken#
                   FROM devicestatus) x1;

OVER現在我們再次使用條件聚合和子句來獲取每個使用者的至少一個設備停機的時間段的標識符。

SELECT device_owner,
      timestamp,
      sum(CASE
            WHEN previous_broken# = 0
              THEN 1
            ELSE
              0
          END) OVER (PARTITION BY device_owner
                     ORDER BY timestamp) broken_period#
      FROM (SELECT device_owner,
                   timestamp,
                   broken#,
                   lag(broken#,
                       1,
                       0) OVER (PARTITION BY device_owner
                                ORDER BY timestamp) previous_broken#
                   FROM (SELECT device_owner,
                                timestamp,
                                sum(CASE status
                                      WHEN 0
                                        THEN 1
                                      WHEN 1
                                        THEN -1
                                    END) OVER (PARTITION BY device_owner
                                               ORDER BY timestamp) broken#
                                FROM devicestatus) x1) x2;

從這裡開始，我們現在可以使用GROUP BY來獲取使用者的至少一台設備停機的時間段的最小和最大時間戳。

SELECT device_owner,
      max(timestamp) mints,
      min(timestamp) maxts
      FROM (SELECT device_owner,
                   timestamp,
                   sum(CASE
                         WHEN previous_broken# = 0
                           THEN 1
                         ELSE
                           0
                       END) OVER (PARTITION BY device_owner
                                  ORDER BY timestamp) broken_period#
                   FROM (SELECT device_owner,
                                timestamp,
                                broken#,
                                lag(broken#,
                                    1,
                                    0) OVER (PARTITION BY device_owner
                                             ORDER BY timestamp) previous_broken#
                                FROM (SELECT device_owner,
                                             timestamp,
                                             sum(CASE status
                                                   WHEN 0
                                                     THEN 1
                                                   WHEN 1
                                                     THEN -1
                                                 END) OVER (PARTITION BY device_owner
                                                            ORDER BY timestamp) broken#
                                             FROM devicestatus) x1) x2) x3
      GROUP BY device_owner,
               broken_period#;

我們現在可以簡單地使用datediff()和sum()來GROUP BY計算每個設備所有者的總停機時間。

SELECT device_owner,
      sum(datediff(minute,
                   maxts,
                   mints)) total_breakdown_min
      FROM (SELECT device_owner,
                   max(timestamp) mints,
                   min(timestamp) maxts
                   FROM (SELECT device_owner,
                                timestamp,
                                sum(CASE
                                      WHEN previous_broken# = 0
                                        THEN 1
                                      ELSE
                                        0
                                    END) OVER (PARTITION BY device_owner
                                               ORDER BY timestamp) broken_period#
                                FROM (SELECT device_owner,
                                             timestamp,
                                             broken#,
                                             lag(broken#,
                                                 1,
                                                 0) OVER (PARTITION BY device_owner
                                                          ORDER BY timestamp) previous_broken#
                                             FROM (SELECT device_owner,
                                                          timestamp,
                                                          sum(CASE status
                                                                WHEN 0
                                                                  THEN 1
                                                                WHEN 1
                                                                  THEN -1
                                                              END) OVER (PARTITION BY device_owner
                                                                         ORDER BY timestamp) broken#
                                                          FROM devicestatus) x1) x2) x3
                   GROUP BY device_owner,
                            broken_period#) x4
      GROUP BY device_owner;

SQL小提琴

引用自：https://dba.stackexchange.com/questions/211041