目前我有下表，我想將它聚合到某種狀態更改的每個資產 ID 的日志中，其中包括開始和結束日期。
即，如果這是我的資料：
| Asset_Id | Status_Id | 更新日期 |
|---|---|---|
| 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 |
我想將其匯總為：
| Asset_Id | Status_Id | 開始日期 | 結束日期 |
|---|---|---|---|
| 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-30 12:12:11 |
| 1 | 2 | 2021-06-30 12:12:12 | 2099-12-31 00:00:0 |
我找到了類似的建議解決方案，但沒有一個包含實際的 status_id 并且 end_date 不包括直到下一行開始時間減去一秒或一分鐘的時間，即從日期時間表中獲取包含開始值和結束值的串列
uj5u.com熱心網友回復：
請嘗試以下解決方案。
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 |
---------- ----------- ------------------------- -------------------------
uj5u.com熱心網友回復：
解決此問題的一種選擇可能是：
- 生成一個標識何時遇到新組的標志（當“ Status_Id ”的值已更改時）-
cte1 - 計算標志上的運行總和，這樣您就可以擁有越來越多的組 -
cte2 - 通過對新識別的組進行分組，在 Update_Date 和下一個日期進行聚合 -
query
WITH cte1 AS (
SELECT *,
CASE WHEN Status_Id <> LAG(Status_Id) OVER(PARTITION BY Asset_Id ORDER BY Update_Date)
THEN 1
END AS Changed_Status
FROM tab
), cte2 AS (
SELECT *,
LEAD(Update_Date) OVER(PARTITION BY Asset_Id ORDER BY Update_Date) AS Last_Date,
SUM(Changed_Status) OVER(PARTITION BY Asset_Id ORDER BY Update_Date) AS New_groups
FROM cte1
)
SELECT Asset_Id,
Status_Id,
MIN(Update_Date) AS Start_Date,
MAX(Last_Date) AS End_Date
FROM cte2
GROUP BY Asset_Id,
Status_Id,
New_groups
ORDER BY New_groups
在此處查看演示。
注意：應該進行小修復以專門匹配您的輸出（例如從結束日期洗掉一秒，使用最后一行的最大值），盡管您可以從這個解決方案中獲得核心思想。
uj5u.com熱心網友回復：
我在操作評論上遵循了 Larnu 的建議，并將 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/497468.html