SQL-根据时间戳记录查找结束日期和开始日期

Question

目前，我有下表，我想将其汇总到每个资产_ID状态更改的日志中，其中包括开始和结束日期。

即，如果这是我的数据：

asset_id	status_id	update_date
1	1	2021-06-29 01:00：00
1	1	2021-06-29 04:00：00
1	3	2021-06-29 056-29 05:00:00
1	3	2021 05:00:31
1	1	2021-06-29 05:01:00
1	1	2021-06-29 05:08:00
1	2	2021-06-06-06-06-06-06-30 12:12:12
12	-06-29	2021-06 -30 12：15：12
1	2	2021-07-30 04:12:12

我想将其汇总到：

asset_id	status_id state_id	start_date	end_date end_date
1	1	2021-06-29 01:00：00	2021-06-29 04： 59:59
1	3	2021-06-29 05:00:00	2021-06-29 05:00:59
1	1	2021-06-29 05:01:00	2021-06-06-06-30 12:12:11
1	2	2021 -06-30 12:12：12	2099-12-31 00：00：0

我找到了类似的建议解决方案，但是它们都不包括实际的状态_ID，而end_date直到下一个行开始时间才包括时间减去第二或分钟，即获取列表从数据表的开始和结束值

Answer 1

请尝试以下解决方案。

sql

DECLARE @tbl TABLE (Asset_Id INT, Status_Id INT, Update_Date DATETIME);
INSERT @tbl (Asset_Id, Status_Id, Update_Date) VALUES
(1, 1, '2021-06-29 01:00:00'),
(1, 1, '2021-06-29 04:00:00'),
(1, 3, '2021-06-29 05:00:00'),
(1, 3, '2021-06-29 05:00:31'),
(1, 1, '2021-06-29 05:01:00'),
(1, 1, '2021-06-29 05:08:00'),
(1, 2, '2021-06-30 12:12:12'),
(1, 2, '2021-06-30 12:15:12'),
(1, 2, '2021-07-30 04:12:12');

;WITH rs AS
(
    SELECT * 
        , series = status_id + ROW_NUMBER() OVER (ORDER BY Update_Date ASC) - 
            ROW_NUMBER() OVER (PARTITION BY Asset_ID, Status_Id ORDER BY Update_Date ASC)
    FROM @tbl
)
SELECT Asset_Id, Status_Id, MIN(Update_Date) AS Start_date, MAX(rs.Update_Date) AS End_Date
FROM rs
GROUP BY Asset_Id, Status_Id, series
ORDER BY MIN(Update_Date) ASC;

sql＃2

满足以下内容

end_date直到下一行开始时间才包括时间
减去第二个

;WITH rs AS
(
    SELECT * 
        , series = status_id + ROW_NUMBER() OVER (ORDER BY Update_Date ASC) - 
            ROW_NUMBER() OVER (PARTITION BY Asset_Id, Status_Id ORDER BY Update_Date ASC)
        , DATEADD(SECOND,-1, LEAD(Update_Date,1) OVER (ORDER BY Update_Date ASC)) AS end_date
    FROM @tbl
)
SELECT Asset_Id, Status_Id, MIN(rs.Update_Date) AS Start_date, MAX(COALESCE(rs.end_date,'2099-12-31')) AS End_Date
FROM rs
GROUP BY Asset_Id, Status_Id, series
ORDER BY MIN(rs.Update_Date) ASC;

输出

+----------+-----------+-------------------------+-------------------------+
| Asset_Id | Status_Id |       Start_date        |        End_Date         |
+----------+-----------+-------------------------+-------------------------+
|        1 |         1 | 2021-06-29 01:00:00.000 | 2021-06-29 04:00:00.000 |
|        1 |         3 | 2021-06-29 05:00:00.000 | 2021-06-29 05:00:31.000 |
|        1 |         1 | 2021-06-29 05:01:00.000 | 2021-06-29 05:08:00.000 |
|        1 |         2 | 2021-06-30 12:12:12.000 | 2021-07-30 04:12:12.000 |
+----------+-----------+-------------------------+-------------------------+

输出＃2

+----------+-----------+-------------------------+-------------------------+
| Asset_Id | Status_Id |       Start_date        |        End_Date         |
+----------+-----------+-------------------------+-------------------------+
|        1 |         1 | 2021-06-29 01:00:00.000 | 2021-06-29 04:59:59.000 |
|        1 |         3 | 2021-06-29 05:00:00.000 | 2021-06-29 05:00:59.000 |
|        1 |         1 | 2021-06-29 05:01:00.000 | 2021-06-30 12:12:11.000 |
|        1 |         2 | 2021-06-30 12:12:12.000 | 2099-12-31 00:00:00.000 |
+----------+-----------+-------------------------+-------------------------+

Answer 2

我遵循了Larnu在OP评论中的建议，并将SQL脚本调整到下面，这是我所需要的

WITH EndsMarked
AS
    (SELECT F.Asset_Id
          , F.Status_Id
          , F.Creation_Date
          , CASE
                 WHEN LAG(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                ORDER BY F.Asset_Id
                                                       , F.Creation_Date
                                               ) IS NULL
                      AND ROW_NUMBER() OVER (PARTITION BY F.Asset_Id
                                             ORDER BY F.Creation_Date
                                            ) = 1 THEN 1
                 WHEN LAG(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                ORDER BY F.Asset_Id
                                                       , F.Creation_Date
                                               ) <> LAG(F.Status_Id, 0) OVER (PARTITION BY F.Asset_Id
                                                                              ORDER BY F.Asset_Id
                                                                                     , F.Creation_Date
                                                                             ) THEN 1
                 ELSE 0
            END AS IS_START
          , CASE
                 WHEN LEAD(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                 ORDER BY F.Asset_Id
                                                        , F.Creation_Date
                                                ) IS NULL
                      AND ROW_NUMBER() OVER (PARTITION BY F.Asset_Id
                                             ORDER BY F.Creation_Date DESC
                                            ) = 1 THEN 1
                 WHEN LEAD(F.Status_Id, 0) OVER (PARTITION BY F.Asset_Id
                                                 ORDER BY F.Asset_Id
                                                        , F.Creation_Date
                                                ) <> LEAD(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                                                ORDER BY F.Asset_Id
                                                                                       , F.Creation_Date
                                                                               ) THEN 1
                 ELSE 0
            END AS IS_END
     FROM
            (
            SELECT mrsabda.Assets_AssetId        AS Asset_Id
                 , mrsabda.CreationDate          AS Creation_Date
                 , mrsabda.Assets_Asset_StatusId AS Status_Id
            --,[Aantal Facturen]
            FROM   MRR.MRR_Round_Status_Audit_Buildup_Dim_Asset AS mrsabda
            ) AS F )
   , GroupsNumbered
AS
    (SELECT EndsMarked.Asset_Id
          , EndsMarked.Status_Id
          , EndsMarked.Creation_Date
          , EndsMarked.IS_START
          , EndsMarked.IS_END
          , COUNT(   CASE
                          WHEN EndsMarked.IS_START = 1 THEN 1
                     END
                 ) OVER (ORDER BY EndsMarked.Asset_Id
                                , EndsMarked.Creation_Date
                        ) AS GroupNum
     FROM   EndsMarked
     WHERE  EndsMarked.IS_START = 1
            OR EndsMarked.IS_END = 1)
SELECT   a.Asset_Id
       , a.Status_Id
       , a.GROUP_START                                                                                   AS Start_Date
       , DATEADD(SECOND, -1, LEAD(a.GROUP_START, 1, '2099-12-31 00:00:01') OVER (ORDER BY a.GROUP_START)) AS End_Date
FROM
         (
         SELECT   GroupsNumbered.Asset_Id
                , GroupsNumbered.Status_Id
                , MIN(GroupsNumbered.Creation_Date) AS GROUP_START
                , MAX(GroupsNumbered.Creation_Date) AS GROUP_END
         FROM     GroupsNumbered
         GROUP BY GroupsNumbered.Asset_Id
                , GroupsNumbered.Status_Id
                , GroupsNumbered.GroupNum
         ) AS a
GROUP BY a.Asset_Id
       , a.Status_Id
       , a.GROUP_START