根據14天間隔創建組

我知道這是一個常見問題，但我找不到與我的情況相匹配的東西。我有這個資料：

 --------- --------- 
| user_id | view_dt |
 --------- --------- 
| A       |     1/1 |
 --------- --------- 
| A       |    1/10 |
 --------- --------- 
| A       |    1/14 |
 --------- --------- 
| A       |    1/22 |
 --------- --------- 
| A       |    1/23 |
 --------- --------- 
| A       |    1/30 |
 --------- --------- 

我希望根據 14 天的時間間隔對這些資料進行分組。也就是說，這些組將是：

第 1 組：1/1、1/10、1/14

第二組： 1/22, 1/23, 1/30

請注意，我的 1/30 日期應該屬于第 2 組，因為 1/30 應該與第 2 組的第一個日期 (1/22) 而不是 (1/1) 進行比較。

我遇到的問題是我自己的查詢顯示 1/30 屬于第 3 組。

CREATE TABLE T (
    user_id VARCHAR(20),
    view_dt DATETIME
);

INSERT INTO t VALUES ('A', '2022-01-01');
INSERT INTO t VALUES ('A', '2022-01-10');
INSERT INTO t VALUES ('A', '2022-01-14');
INSERT INTO t VALUES ('A', '2022-01-22');
INSERT INTO t VALUES ('A', '2022-01-23');
INSERT INTO t VALUES ('A', '2022-01-30');

SELECT user_id,
    view_dt,
    DENSE_RANK() OVER(ORDER BY gr) grp
FROM (
SELECT 
    user_id,
    view_dt,
    CAST (view_dt - MIN (view_dt) OVER (PARTITION BY user_id ORDER BY view_dt) AS INT )/14   1 AS gr
    FROM T
) x
ORDER BY user_id

理想輸出

 --------- --------- ------- 
| user_id | view_dt | group |
 --------- --------- ------- 
| A       |     1/1 |     1 |
 --------- --------- ------- 
| A       |    1/10 |     1 |
 --------- --------- ------- 
| A       |    1/14 |     1 |
 --------- --------- ------- 
| A       |    1/22 |     2 |
 --------- --------- ------- 
| A       |    1/23 |     2 |
 --------- --------- ------- 
| A       |    1/30 | 2     |
 --------- --------- ------- 

我之前查詢的輸出：

 --------- --------- ------- 
| user_id | view_dt | group |
 --------- --------- ------- 
| A       |     1/1 |     1 |
 --------- --------- ------- 
| A       |    1/10 |     1 |
 --------- --------- ------- 
| A       |    1/14 |     1 |
 --------- --------- ------- 
| A       |    1/22 |     2 |
 --------- --------- ------- 
| A       |    1/23 |     2 |
 --------- --------- ------- 
| A       |    1/30 | 3**   |
 --------- --------- ------- 

uj5u.com熱心網友回復：

一種選擇是使用遞回 CTE

-- Recursive CTE solution
with cte as
(
    -- CTE for adding a row_number
    select rn = row_number() over (partition by user_id order by view_dt),
           user_id, view_dt
    from   T
),
rcte as
(
    -- RCTE - anchor member
    -- first_dt is the first date of the group
    select rn, user_id, view_dt, grp = 1, first_dt = view_dt
    from   cte
    where  rn = 1
    
    union all
    
    -- RCTE - recursive member
    -- if date is more than 14 days from first_dt, grp   1, update first_dt
    select c.rn, c.user_id, c.view_dt,
           grp = case when datediff(day, r.first_dt, c.view_dt) > 14
                      then r.grp   1
                      else r.grp
                      end,
           first_dt = case when datediff(day, r.first_dt, c.view_dt) > 14
                      then c.view_dt
                      else r.first_dt
                      end
    from   cte c
           inner join rcte r on  c.user_id = r.user_id
                             and c.rn      = r.rn   1
)
select *
from   rcte

注意：請避免在日期上使用算術運算子。view_dt - MIN (view_dt). 應該使用datediff() See Bad Habits to Kick : Using shorthand with date/time operations

標籤：sql sql服务器 sql-server-2019

上一篇：獲取SQL中不同值的計數

下一篇：如何為SQL函式設定條件？