我正在研究原始資料采用以下格式的要求之一
要求- 開始日期應該是狀態更改為 1 的日期，結束日期應該是記錄狀態從 1 更改為任何其他數字后的第一個日期。
| 顧客 | 狀態 | 日期 |
|---|---|---|
| A123 | 0 | 2021 年 7 月 2 日 |
| A123 | 0 | 2021 年 7 月 15 日 |
| A123 | 0 | 2021 年 7 月 22 日 |
| A123 | 1 | 2021 年 8 月 18 日 |
| A123 | 1 | 2021 年 9 月 8 日 |
| A123 | 0 | 2021 年 12 月 1 日 |
| A123 | 0 | 2022 年 1 月 21 日 |
| A123 | 1 | 2022 年 3 月 6 日 |
| A123 | 1 | 2022 年 3 月 7 日 |
| A123 | 0 | 2022 年 3 月 15 日 |
| B123 | 1 | 2022 年 1 月 1 日 |
| B123 | 0 | 2022 年 1 月 6 日 |
| C123 | 1 | 2022 年 1 月 2 日 |
| C123 | 2 | 2022 年 1 月 8 日 |
| C123 | 0 | 2022 年 1 月 9 日 |
預期產出
| 顧客 | 開始日期 | 結束日期 |
|---|---|---|
| A123 | 2021 年 8 月 18 日 | 2021 年 12 月 1 日 |
| A123 | 2021 年 9 月 8 日 | 2021 年 12 月 1 日 |
| A123 | 2022 年 3 月 6 日 | 2022 年 3 月 15 日 |
| A123 | 2022 年 3 月 7 日 | 2022 年 3 月 15 日 |
| B123 | 2022 年 1 月 1 日 | 2022 年 1 月 6 日 |
| C123 | 2022 年 1 月 2 日 | 2022 年 1 月 8 日 |
查詢我試圖得到的輸出如下，我得到的是客戶 B123 和 C123 的輸出，但不是 A123 的輸出。
查詢說明- 在查詢的第一部分中，我將獲取狀態 = 1 的所有記錄，在下一部分中僅獲取狀態不等于 1 的那些記錄，并根據生成的客戶和行號加入這兩個資料集。
SELECT A.[Customer],A.StartDate,B.EndDate
from
(
SELECT [Customer],MIN(Date) AS STARTDATE,[Status],RANK() OVER (PARTITION BY [STATUS] ORDER BY Date ASC) AS ROWNUM
FROM table1
WHERE [STATUS] = 1
GROUP BY Customer,Date,[Status]
) A
LEFT JOIN
(
SELECT [Customer],MIN(Date) AS ENDDATE,[Status],RANK() OVER (PARTITION BY [STATUS] ORDER BY Date ASC) AS ROWNUM
FROM table1
WHERE [STATUS] != 1
AND Date>(
SELECT MIN(Date) AS STARTDATE
FROM table1
WHERE [STATUS] = 1
)
GROUP BY Customer,Date,[Status]
) B
ON
(
A.[Customer] = B.[Customer]
AND A.RowNum = B.RowNum
)
ORDER BY A.Startdate
uj5u.com熱心網友回復：
這是一種更有效的方法，無需自聯接。
WITH cte01only AS
( SELECT *, CASE Status WHEN 1 THEN 1 ELSE 0 END AS Status1 FROM table1 ),
cteDifference AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Date, Status1)
- ROW_NUMBER() OVER (PARTITION BY Customer, Status1 ORDER BY Date) AS StatusGroup
FROM cte01only
),
cteGroup AS
(
SELECT Customer, StatusGroup, Status1, MIN(Date) As StartDate
FROM cteDifference
GROUP BY Customer, StatusGroup, Status1
),
cteNextDate AS
(
SELECT Customer, StatusGroup, Status1, StartDate,
LEAD(StartDate, 1, NULL) OVER (PARTITION BY Customer ORDER BY StatusGroup) AS EndDate
FROM cteGroup
)
SELECT Customer, StartDate, EndDate
FROM cteNextDate
WHERE Status1 = 1
ORDER BY Customer, StateDate
這里的關鍵技巧是第二個 CTE，它使用兩個ROW_NUMBER()函式的差異通過連續運行狀態為 1 或非 1 的記錄將客戶記錄（與StatusGroup列）標記到單獨的磁區中。之后可以根據標簽獲取開始日期，然后使用該LEAD()函式獲取以下組StartDate作為當前分組EndDate。
（可能有更緊湊的方式來表達這一點，但我喜歡將每個階段布局為單獨的 CTE。）
uj5u.com熱心網友回復：
首先列出其中的行，Status = 1然后用于CROSS APPLY獲取狀態不等于 1 的相應最小日期
select s.[Customer],
StartDate = s.[Date],
EndDate = e.[Date]
from Table1 s
cross apply
(
select [Date] = min(e.[Date])
from Table1 e
where e.[Customer] = s.[Customer]
and e.[Date] > s.[Date]
and e.[Status] <> 1
) e
where s.[Status] = 1
order by s.[Customer], s.[Date]
uj5u.com熱心網友回復：
您可以嘗試使用CROSS APPLYwithTOP 1來獲取大于子查詢的值。
SELECT t1.*,t2.Date
FROM (
SELECT Customer,Date
FROM table1
WHERE Status = 1
) t1 CROSS APPLY (
SELECT TOP 1 Date
FROM table1 tt
WHERE tt.Status <> 1
AND tt.Customer = t1.Customer
AND tt.Date > t1.Date
ORDER BY tt.Date
) t2
sqlfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/453567.html
上一篇：如何在導航視圖中隱藏滾動條
下一篇：兩次加入同一表中的同一列