我有一個包含 3 列的表：personID、weeknumber 和 event。如果那個人在那周沒有事件，則事件為 0，如果有，則為 1。
我需要創建一個新的列 weeksincelastevent ，它在 event=1 的那一周為 0，然后在之后的幾周為 1,2,3,4 等。如果有后面的事件，則它再次從 0 開始。例如
| 人名 | 周數 | 事件 | 自上次事件起數周 |
|---|---|---|---|
| 1 | 1 | 0 | 空值 |
| 1 | 2 | 0 | 空值 |
| 1 | 3 | 1 | 0 |
| 1 | 4 | 0 | 1 |
| 1 | 5 | 0 | 2 |
| 1 | 6 | 0 | 3 |
| 2 | 1 | 0 | 空值 |
| 2 | 2 | 1 | 0 |
| 2 | 3 | 0 | 1 |
| 2 | 4 | 1 | 0 |
| 2 | 5 | 0 | 1 |
該列在第一個事件之前應該為 NULL，并且所有值都為 NULL，其中 personID 從未有事件。
我想不出如何用 SQL 寫這個。
該表有大約 600m 行（60m 個 personID，每個 personID 有 100 個周數，盡管有些 personID 沒有所有的周數）。
非常感謝您的任何見解。
uj5u.com熱心網友回復：
這里有點缺口和孤島問題。第一部分，在 CTE 中，將資料放入“組”中。每次有一個新組的事件。它還計算自上一周以來過去的周數（0對于承載事件的行設定為）。然后在外部查詢中我們SUM計算每組過去的周數，給出已經過去的周數：
WITH Groups AS(
SELECT PersonID,
WeekNumber,
Event,
COUNT(CASE Event WHEN 1 THEN 1 END) OVER (PARTITION BY PersonID ORDER BY WeekNumber ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Events,
CASE Event WHEN 0 THEN WeekNumber - LAG(WeekNumber) OVER (PARTITION BY PersonID ORDER BY WeekNumber ASC) ELSE 0 END AS WeeksPassed
FROM dbo.YourTable)
SELECT PersonID,
WeekNumber,
Event,
CASE WHEN Events = 0 THEN NULL
ELSE SUM(WeeksPassed) OVER (PARTITION BY PersonID, Events ORDER BY WeekNumber ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
END AS WeekSinceLastEvent
FROM Groups;
資料庫<>小提琴
uj5u.com熱心網友回復：
您可以使用視窗函式中的條件聚合來執行此操作：
SELECT t.PersonID,
t.WeekNumber,
t.Event,
WeeksSinceLastEvent = t.WeekNumber - MAX(CASE WHEN t.Event = 1 THEN t.WeekNumber END)
OVER(PARTITION BY t.PersonID ORDER BY t.WeekNumber)
FROM dbo.T AS t;
關鍵部分是：
CASE WHEN t.Event = 1 THEN t.WeekNumber END僅考慮有效事件的周數。由于MAX忽略空值，這將只考慮相關行OVER (PARTITION BY t.PersonID ORDER BY t.WeekNumber)- 僅考慮當前人員的行，其中周數低于當前行。
DB<>Fiddle 示例
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/341842.html
標籤：sql-server