基于Netezza SQL实现同组同颜色日期行扁平化

原始表数据 #

表名：my_table

id_1 id_2  color start_date   end_date
1  111  222    red 2010-01-01 2010-05-05
2  111  222    red 2010-05-05 2011-01-01
3  111  222    red 2011-01-01 2012-01-01
4  111  666   blue 2012-01-01 2012-05-05
5  111  444  green 2012-05-05 2013-01-01
6  111  444  green 2013-01-01 2013-06-06
7  333  555 yellow 2020-01-01 2020-05-05

需求 #

• 针对id_1和id_2的组合
• 若该组合在连续日期区间内颜色保持不变
• 将这些连续的记录“扁平化”为单条记录，取该区间的最早开始日期和最晚结束日期

期望结果 #

id_1 id_2  color start_date   end_date
1  111  222    red 2010-01-01 2012-01-01
4  111  666   blue 2012-01-01 2012-05-05
5  111  444  green 2012-05-05 2013-06-06
7  333  555 yellow 2020-01-01 2020-05-05

你的两次尝试代码验证 #

尝试1：双ROW_NUMBER分组法 #

这是处理连续相同值分组的经典方案，通过计算两个窗口函数的差值，为连续同颜色的记录分配相同分组ID，在Netezza中可正常运行：

WITH CTE AS (
    SELECT
        id_1,
        id_2,
        color,
        start_date,
        end_date,
        ROW_NUMBER() OVER (PARTITION BY id_1, id_2 ORDER BY start_date) - ROW_NUMBER() OVER (PARTITION BY id_1, id_2, color ORDER BY start_date) AS grp
    FROM my_table
)
SELECT
    id_1,
    id_2,
    color,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM CTE
GROUP BY
    id_1,
    id_2,
    color,
    grp
ORDER BY
    start_date;

尝试2：LAG标记+累加分组法 #

通过LAG函数判断当前行与上一行颜色是否一致，生成分组标记后累加得到分组ID，同样能实现需求，Netezza支持该语法：

WITH CTE AS (
    SELECT
        id_1,
        id_2,
        color,
        start_date,
        end_date,
        CASE
            WHEN LAG(color) OVER (PARTITION BY id_1, id_2 ORDER BY start_date) = color THEN 0
            ELSE 1
        END AS new_grp
    FROM my_table
),
CTE2 AS (
    SELECT
        id_1,
        id_2,
        color,
        start_date,
        end_date,
        SUM(new_grp) OVER (PARTITION BY id_1, id_2 ORDER BY start_date) AS grp
    FROM CTE
)
SELECT
    id_1,
    id_2,
    color,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM CTE2
GROUP BY
    id_1,
    id_2,
    color,
    grp
ORDER BY
    start_date;

说明 #

以上两种方法均能正确实现需求，核心逻辑是为连续同颜色的记录分配唯一分组ID，再通过分组聚合获取每个组的起始、结束日期，Netezza的窗口函数支持度可满足这两种写法的执行。

原始数据定义（补充） #

(
    id_1 = c(111,111,111,111, 111,111,333),
    id_2 = c(222,222,222, 666, 444, 444, 555),
    color = c("red", "red", "red", "blue", "green", "green", "yellow"),
    start_date = c("2010-01-01", "2010-05-05", "2011-01-01", "2012-01-01" , "2012-05-05", "2013-01-01", "2020-01-01"),
    end_date = c("2010-05-05", "2011-01-01", "2012-01-01", "2012-05-05", "2013-01-01", "2013-06-06", "2020-05-05")
)

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