Netezza SQL：DSL_CARD_TYPE分组及Next_COLLECTION_DATE生成需求

问题背景 #

我们有一张记录用户线路DSL卡类型每日快照的表，样本数据如下（仅单LINE_ID）：

需求是：将连续相同的DSL_CARD_TYPE归为一组，为每个组新增Next_COLLECTION_DATE字段，存储下一个分组的首个COLLECTION_DATE，最终期望输出如下：

现有代码的问题 #

当前给出的SQL只能获取每个连续分组的起止日期，无法直接得到下一个分组的首个日期：

select line_id, dsl_card_type, min(collection_date), max(collection_date)
from (select v.*,
             row_number() over (partition by line_id order by collection_date) as seqnum,
             row_number() over (partition by line_id, dsl_card_type order by collection_date) as seqnum_2
      from ANALYTICS.tmp.V_PORTS_LINE_CARD_DATA_ALL v
      where collection_date >= '2020-07-27 00:00:00'
     ) v
group by line_id, dsl_card_type, (seqnum - seqnum_2);

解决方案 #

我们可以基于“连续相同分组”的逻辑，先给每个连续组标记ID，然后获取每个组的首个日期，最后用LEAD()窗口函数获取下一组的首个日期：

完整SQL代码 #

WITH grouped_data AS (
    -- 第一步：标记连续相同DSL_CARD_TYPE的分组ID
    SELECT 
        v.*,
        -- 当当前行的DSL_CARD_TYPE和上一行不同时，分组ID+1，否则保持不变
        SUM(CASE WHEN prev_type = dsl_card_type THEN 0 ELSE 1 END) 
            OVER (PARTITION BY line_id ORDER BY collection_date) AS group_id
    FROM (
        SELECT 
            *,
            LAG(dsl_card_type) OVER (PARTITION BY line_id ORDER BY collection_date) AS prev_type
        FROM ANALYTICS.tmp.V_PORTS_LINE_CARD_DATA_ALL v
        -- 注意：原WHERE条件日期是2020-07-27，但样本数据是3-4月，这里可根据实际需求调整
        WHERE collection_date BETWEEN '2020-03-25' AND '2020-04-07'
    ) v
),
group_first_dates AS (
    -- 第二步：获取每个分组的首个COLLECTION_DATE
    SELECT 
        line_id,
        dsl_card_type,
        group_id,
        MIN(collection_date) AS collection_date
    FROM grouped_data
    GROUP BY line_id, dsl_card_type, group_id
)
-- 第三步：用LEAD()获取下一个分组的首个日期
SELECT 
    line_id,
    collection_date,
    LEAD(collection_date) OVER (PARTITION BY line_id ORDER BY collection_date) AS Next_COLLECTION_DATE,
    dsl_card_type
FROM group_first_dates
ORDER BY collection_date;

代码逻辑解释 #

1. 标记连续分组：使用LAG()函数获取上一行的DSL_CARD_TYPE，然后通过累加判断当前行是否属于新分组，生成group_id，这样连续相同的类型会被分到同一个group_id中。
2. 提取分组首个日期：对每个group_id取最小的COLLECTION_DATE，也就是该分组的起始日期。
3. 获取下一组日期：使用LEAD()窗口函数，按collection_date排序后，获取当前行的下一行的collection_date，也就是下一个分组的起始日期；最后一组没有下一个分组，所以返回NULL。

这样就能完美匹配你需要的输出格式啦！

内容的提问来源于stack exchange，提问作者Ahmed Mohammed Abdel Kader