无需UDF和存储过程，在Snowflake SQL实现日期数组聚合

实现思路 #

通过窗口函数识别分组逻辑，再基于分组聚合目标日期数组，核心逻辑如下：

• 用SUM() OVER()生成分组ID：当DATE_15_DAYS_BEFORE >= PREV_DATE，或上一行的DATE小于当前行的DATE_15_DAYS_BEFORE时，开启新分组。
• 针对不同分组场景生成数组：
  • 若DATE_15_DAYS_BEFORE >= PREV_DATE，直接返回空数组。
  • 若为新分组且上一行日期小于当前DATE_15_DAYS_BEFORE，则将DATE_15_DAYS_BEFORE加入数组头部，再聚合组内所有DATE。
  • 其他情况直接聚合组内所有DATE。

完整SQL代码 #

WITH input_data AS (
    SELECT DATE('2023-09-13') AS DATE, DATE('2023-08-29') AS DATE_15_DAYS_BEFORE, DATE('2023-08-21') AS PREV_DATE
    UNION ALL
    SELECT DATE('2023-09-19'), DATE('2023-09-04'), DATE('2023-09-13')
    UNION ALL
    SELECT DATE('2023-09-27'), DATE('2023-09-12'), DATE('2023-09-19')
    UNION ALL
    SELECT DATE('2023-10-05'), DATE('2023-09-20'), DATE('2023-09-27')
),
grouped_data AS (
    SELECT 
        *,
        SUM(CASE 
            WHEN DATE_15_DAYS_BEFORE >= PREV_DATE THEN 1
            WHEN LAG(DATE) OVER (ORDER BY DATE) < DATE_15_DAYS_BEFORE THEN 1
            ELSE 0
        END) OVER (ORDER BY DATE) AS group_id
    FROM input_data
)
SELECT 
    DATE,
    DATE_15_DAYS_BEFORE,
    PREV_DATE,
    CASE 
        WHEN DATE_15_DAYS_BEFORE >= PREV_DATE THEN ARRAY_CONSTRUCT()
        ELSE 
            ARRAY_CAT(
                CASE 
                    WHEN LAG(DATE) OVER (PARTITION BY group_id ORDER BY DATE) IS NULL 
                         AND LAG(DATE) OVER (ORDER BY DATE) < DATE_15_DAYS_BEFORE 
                    THEN ARRAY_CONSTRUCT(DATE_15_DAYS_BEFORE) 
                    ELSE ARRAY_CONSTRUCT() 
                END,
                ARRAY_AGG(DATE) OVER (PARTITION BY group_id ORDER BY DATE)
            )
    END AS ARRAY_AGG
FROM grouped_data
ORDER BY DATE;

执行结果 #

运行上述SQL后，将得到与需求完全匹配的输出，数组元素为字符串格式的日期值。

内容的提问来源于stack exchange，提问作者Vipendra Singh