获取日期周期

Question

有必要得到日期周期。输入数据：

     login    date_start    date_end    code
    'user1', '2022-02-09', '2022-02-09'  DO
    'user1', '2022-02-10', '2022-02-10'  DO
    'user1', '2022-02-11', '2022-02-11'  DO
    'user1', '2022-03-28', '2022-03-28'  OT
    'user1', '2022-03-29', '2022-03-29'  OT
    'user1', '2022-03-30', '2022-03-30'  OT
    'user1', '2022-03-31', '2022-03-31'  OT
    'user1', '2022-04-01', '2022-04-01'  OT
    'user1', '2022-04-04', '2022-04-04'  DO

预期结果：

login  date_start  date_end   
user1  2022-02-09  2022-02-11 
user1  2022-03-28  2022-04-01
user1  2022-04-04  2022-04-04

Answer 1

这是一个缺口和岛屿问题。这里我们可以使用的一个技巧是创建一个伪组，跟踪每个记录所属的连续日期岛。

WITH cte AS (
    SELECT *, CASE WHEN LAG(date_start, 1, date_start) OVER
                        (PARTITION BY login ORDER BY date_start) =
                        DATEADD(day, -1, date_start)
                   THEN 0 ELSE 1 END AS cnt
    FROM yourTable
),
cte2 AS (
    SELECT *, SUM(cnt) OVER (PARTITION BY login ORDER BY date_start) AS grp
    FROM cte
)

SELECT login, MIN(date_start) AS date_start, MAX(date_end) AS date_end
FROM cte2
GROUP BY login, grp
ORDER BY MIN(date_start);

​

Demo

​

在上面的第一个CTE中，当上一个日期不是比当前日期早一天时，别名cnt被赋值为1。然后，我们可以对这一列进行求和，并获得形成连续范围的一组日期。

Answer 2

像这样

select max(date_end) date_end , min(date_start) date_start, login from table group by login, code