基于Netezza SQL计算特定时段符合条件用户的颜色变更次数

错误原因分析 #

你原来的CTE逻辑错误在于：它是取每个ID截止到2015-01-01的最新记录，而非2005-01-01时的最新偏好颜色，这就导致像ID=333这样的用户，被错误返回了2009年的blue颜色，而非2005年1月1日时的red颜色。

正确查询实现 #

以下是分步满足需求的完整Netezza SQL查询：

WITH cte_2005_color AS (
    -- 步骤1：获取每个ID在2005-01-01时的最新偏好颜色，并筛选出颜色为Red的ID
    SELECT id, color
    FROM (
        SELECT 
            id,
            color,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
        FROM my_table
        WHERE date <= '2005-01-01'
    ) t
    WHERE rn = 1 AND color = 'red'
),
cte_all_records AS (
    -- 合并2005-01-01的初始颜色记录，以及2005-01-01至2015-01-01的后续变更记录
    SELECT id, color, '2005-01-01'::DATE AS date
    FROM cte_2005_color
    UNION ALL
    SELECT id, color, date
    FROM my_table
    WHERE date > '2005-01-01' AND date <= '2015-01-01'
),
cte_change_count AS (
    -- 统计每个ID的颜色变更次数（仅当前后颜色不同时计数）
    SELECT 
        id,
        SUM(CASE WHEN prev_color != color THEN 1 ELSE 0 END) AS color_changes
    FROM (
        SELECT 
            id,
            color,
            LAG(color) OVER (PARTITION BY id ORDER BY date) AS prev_color
        FROM cte_all_records
    ) t
    GROUP BY id
)
-- 关联结果，确保无变更记录的ID显示0次变更
SELECT 
    c.id,
    COALESCE(cc.color_changes, 0) AS color_changes
FROM cte_2005_color c
LEFT JOIN cte_change_count cc ON c.id = cc.id
ORDER BY c.id;

查询逻辑说明 #

1. cte_2005_color：通过窗口函数ROW_NUMBER()按ID分区，取截止到2005-01-01的最新记录，筛选出颜色为Red的ID。
2. cte_all_records：将2005年1月1日的初始颜色记录与后续（2005-01-01至2015-01-01）的变更记录合并，为后续统计变更次数提供完整的颜色序列。
3. cte_change_count：使用LAG()函数获取每个记录的前一次颜色，当前后颜色不同时计数为1，最终按ID求和得到总变更次数。
4. 最后通过左连接关联，确保像ID=666这种无后续变更记录的用户，变更次数显示为0。

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