如何在Snowflake中按客户生成激活至终止日期的日期数组？

需求说明 #

现有客户数据包含activation_date和termination_date（两者间隔20天），需要为每个客户生成包含从activation_date到termination_date所有日期的数组。

原SQL问题分析 #

你提供的SQL存在以下几个问题：

• SEQ4()是全局递增序列，并非按每个客户单独生成，导致部分客户的起始偏移量过大，直接超出日期区间，所以每个客户仅能得到少数结果，甚至日期不从first_day开始。
• GROUP BY子句使用了subscription_id，但SELECT中是customer_id，字段不匹配导致分组逻辑错误，结果随机分布。
• 外层查询存在语法错误，多了一个闭合括号。

修正后的SQL #

SELECT
    customer_id,
    ARRAY_AGG(date_val ORDER BY date_val) AS date_array
FROM (
    SELECT
        customer_id,
        activation_day AS first_day,
        termination_date_clean_formatted AS last_day,
        DATEADD(day, rn - 1, first_day) AS date_val
    FROM (
        SELECT
            customer_id,
            activation_day,
            termination_date_clean_formatted,
            ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY NULL) AS rn
        FROM v
        JOIN TABLE(GENERATOR(ROWCOUNT => 20)) -- 因间隔固定20天，生成20条数据足够
            ON TRUE
    )
    WHERE date_val <= last_day
)
GROUP BY customer_id
ORDER BY customer_id;

关键修正点 #

• 用ROW_NUMBER() OVER(PARTITION BY customer_id)为每个客户单独生成从1开始的序列，减去1后作为日期偏移量，确保每个客户从first_day开始生成日期。
• 针对固定20天的间隔，将GENERATOR的ROWCOUNT设为20，避免生成无效数据。
• GROUP BY与SELECT字段保持一致，仅按customer_id分组，确保每个客户对应唯一的日期数组。

预期结果 #

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