使用Window Function分组连续数据：修正库存连续天数计算

问题场景 #

原始数据表：

ITEM   STOCK   DAY   
A      5       2024-08-12
B      2       2024-08-12
C      7       2024-08-12
A      3       2024-08-13
B      2       2024-08-13
C      7       2024-08-13
D      1       2024-08-13
A      3       2024-08-14
B      3       2024-08-14
C      7       2024-08-14
A      3       2024-08-15
B      3       2024-08-15
C      9       2024-08-15
A      2       2024-08-16
B      3       2024-08-16
C      7       2024-08-16
A      5       2024-08-17
B      2       2024-08-17
C      7       2024-08-17
D      3       2024-08-17

需求：用窗口函数计算每个物品保持相同库存的连续天数（CDAYS），预期结果：

ITEM   CDAYS    DAY_START   DAY_END   
A      0        2024-08-12   2024-08-12
B      1        2024-08-12   2024-08-13  
C      5        2024-08-12   2024-08-17
A      2        2024-08-13   2024-08-15
D      0        2024-08-13   2024-08-13
B      2        2024-08-14   2024-08-16
A      0        2024-08-16   2024-08-16
A      0        2024-08-17   2024-08-17
B      0        2024-08-17   2024-08-17
D      0        2024-08-17   2024-08-17  

原SQL的问题 #

尝试的SQL语句：

create table mytable (ITEM char, STOCK int,  DATE date);
insert into mytable values
('A',    5,    '2024-08-12'),
('B',    2,    '2024-08-12'),
('C',    7,    '2024-08-12'),
('A',    3,    '2024-08-13'),
('B',    2,    '2024-08-13'),
('C',    7,    '2024-08-13'),
('D',    1,    '2024-08-13'),
('A',    3,    '2024-08-14'),
('B',    3,    '2024-08-14'),
('C',    7,    '2024-08-14'),
('A',    3,    '2024-08-15'),
('B',    3,    '2024-08-15'),
('C',    7,    '2024-08-15'),
('A',    2,    '2024-08-16'),
('B',    3,    '2024-08-16'),
('C',    7,    '2024-08-16'),
('A',    5,    '2024-08-17'),
('B',    2,    '2024-08-17'),
('C',    7,    '2024-08-17'),
('D',    3,    '2024-08-17');


SELECT ITEM, STOCK,
ROW_NUMBER() OVER (PARTITION BY ITEM, STOCK ORDER BY DATE) AS Cdays, DATE
FROM mytable
ORDER BY ITEM, DATE

原SQL仅按ITEM和STOCK分区，会把非连续日期的相同库存归为同一组，比如ITEM A在2024-08-12和2024-08-17的库存5被错误判定为连续数据，导致Cdays计算错误。

修改后的SQL方案 #

核心思路是通过日期连续性和库存变化生成分组标识，将真正连续的相同库存日期归为同一组，再计算每组的连续天数。

WITH grouped_data AS (
    SELECT 
        ITEM,
        STOCK,
        DATE,
        -- 生成分组标识：当前行与前一行日期不连续或库存变化时，创建新分组
        SUM(CASE 
            WHEN LAG(DATE) OVER (PARTITION BY ITEM ORDER BY DATE) + INTERVAL '1 day' = DATE 
                 AND LAG(STOCK) OVER (PARTITION BY ITEM ORDER BY DATE) = STOCK
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY ITEM ORDER BY DATE) AS group_id
    FROM mytable
),
group_summary AS (
    SELECT 
        ITEM,
        MIN(DATE) AS DAY_START,
        MAX(DATE) AS DAY_END,
        -- 连续天数=结束日期与起始日期的天数差
        DATEDIFF(MAX(DATE), MIN(DATE)) AS CDAYS
    FROM grouped_data
    GROUP BY ITEM, STOCK, group_id
)
SELECT ITEM, CDAYS, DAY_START, DAY_END
FROM group_summary
ORDER BY DAY_START, ITEM;

逻辑解释 #

1. grouped_data CTE：
   • 用LAG窗口函数获取每个物品的前一行日期和库存。
   • 判断当前行是否与前一行属于同一连续相同库存段：如果日期连续且库存不变，分组标识不变；否则分组标识+1，生成新分组。
2. group_summary CTE：
   • 对每个分组计算起始日期、结束日期，以及连续天数（用DATEDIFF计算天数差，对应预期结果中的CDAYS）。
3. 最终按起始日期和物品排序，得到符合要求的结果。

执行后，ITEM A的2024-08-12和2024-08-17会被分成两个独立分组，CDAYS均为0，其他物品的连续段也会正确计算。

内容的提问来源于stack exchange，提问作者이정윤