Netezza SQL：查询各ID最接近2010年的有效偏好颜色

我正在使用Netezza SQL开发，现有表my_table，每个ID（用户）在不同date_provided（记录日期）记录了favorite_color（偏好颜色）：同一日期可记录不同颜色，不同日期也可记录相同颜色，表数据如下：

id favorite_color date_provided
1  111            red    2000-01-01
2  111            red    2002-01-01
3  111           blue    2003-01-01
4  222          green    2005-01-01
5  222         yellow    2006-01-01
6  222         yellow    2010-01-01
7  222         yellow    2010-05-05
8  222           pink    2010-12-31
9  333          black    2008-01-01
10 333          black    2012-01-01
11 333          black    2015-01-01
12 444         orange    2020-01-01
13 555          white    2010-01-01
14 555          white    2010-01-01
15 555          white    2010-01-01
16 666           grey    2009-01-01
17 666           grey    2009-01-05
18 666         purple    2009-01-10

需求：为每个ID找出最接近2010年且date_provided不晚于2010年的偏好颜色，即推断该用户2010年的偏好颜色，期望输出如下：

id favorite_color date_provided valid_color_2010
1  111            red    2000-01-01               no
2  111            red    2002-01-01               no
3  111           blue    2003-01-01              yes
4  222          green    2005-01-01               no
5  222         yellow    2006-01-01               no
6  222         yellow    2010-01-01              yes
7  222         yellow    2010-05-05              yes
8  222           pink    2010-12-31              yes
9  333          black    2008-01-01              yes
10 333          black    2012-01-01               no
11 333          black    2015-01-01               no
12 444         orange    2020-01-01               no
13 555          white    2010-01-01              yes
14 555          white    2010-01-01              yes
15 555          white    2010-01-01              yes
16 666           grey    2009-01-01               no
17 666           grey    2009-01-05               no
18 666         purple    2009-01-10              yes

我尝试编写了如下SQL实现：

WITH closest_date_provided AS (
    SELECT id, MAX(date_provided) AS date_provided
    FROM my_table
    WHERE date_provided <= 2010
    GROUP BY id
)
SELECT my_table.id, my_table.favorite_color, my_table.date_provided,
       CASE WHEN my_table.date_provided = closest_date_provided.date_provided THEN 'yes' ELSE 'no' END AS valid_color_2010
FROM my_table
LEFT JOIN closest_date_provided
ON my_table.id = closest_date_provided.id
ORDER BY my_table.id, my_table.date_provided;

请问该实现是否正确？是否有更简便的实现方式？

补充说明 #

1. 我同时尝试扩展实现了查询2003年有效颜色的场景，SQL如下：

WITH closest_date_provided_2010 AS (
    SELECT id, MAX(date_provided) AS date_provided
    FROM my_table
    WHERE date_provided <= 2010
    GROUP BY id
),
closest_date_provided_2003 AS (
    SELECT id, MAX(date_provided) AS date_provided
    FROM my_table
    WHERE date_provided <= 2003
    GROUP BY id
)
SELECT my_table.id, my_table.favorite_color, my_table.date_provided,
       CASE WHEN my_table.date_provided = closest_date_provided_2010.date_provided THEN 'yes' ELSE 'no' END AS valid_color_2010,
       CASE WHEN my_table.date_provided = closest_date_provided_2003.date_provided THEN 'yes' ELSE 'no' END AS valid_color_2003
FROM my_table
LEFT JOIN closest_date_provided_2010
ON my_table.id = closest_date_provided_2010.id
LEFT JOIN closest_date_provided_2003
ON my_table.id = closest_date_provided_2003.id
ORDER BY my_table.id, my_table.date_provided;

2. 创建原表的数据源如下：

(
    id = c(111, 111,111,222, 222, 222,222, 222, 333,333,333, 444, 555, 555, 555, 666, 666, 666),
    favorite_color = c("red", "red", "blue", "green", "yellow", "yellow", "yellow", "pink" ,"black" , "black", "black", "orange", "white", "white", "white", "grey", "grey", "purple"),
    date_provided = c("2000-01-01", "2002-01-01", "2003-01-01", "2005-01-01", "2006-01-01", "2010-01-01", "2010-05-05", "2010-12-31", "2008-01-01","2012-01-01", "2015-01-01", "2020-01-01", "2010-01-01", "2010-01-01", "2010-01-01", "2009-01-01", "2009-01-05", "2009-01-10")

)

原SQL实现是否正确？ #

你的原SQL实现是正确的，完全符合需求：

• 通过CTEclosest_date_provided按ID分组，筛选出每个用户不晚于2010年的最新记录日期；
• 左连接原表后，通过CASE语句标记出所有匹配该最新日期的记录为yes，其余为no；
• 最终输出和期望结果完全一致，包括处理同一ID在2010年有多个不同日期记录（如ID222）、同一日期多条重复记录（如ID555）的场景。

更简便的实现方式 #

可以使用Netezza支持的窗口函数MAX() OVER()来简化SQL，无需单独的CTE和JOIN操作，代码更紧凑：

SELECT 
    id, 
    favorite_color, 
    date_provided,
    CASE 
        WHEN date_provided = MAX(CASE WHEN date_provided <= '2010-12-31' THEN date_provided END) OVER (PARTITION BY id) 
        THEN 'yes' 
        ELSE 'no' 
    END AS valid_color_2010
FROM my_table
ORDER BY id, date_provided;

逻辑说明： #

1. 使用MAX(CASE WHEN date_provided <= '2010-12-31' THEN date_provided END) OVER (PARTITION BY id)，按ID分组计算每个用户不晚于2010年的最新日期；
2. 直接在原表行中对比当前日期是否等于该分组最新日期，标记结果；
3. 避免了JOIN操作，执行效率和原SQL相当，但代码更简洁。

扩展场景的优化（多年份查询） #

针对你扩展的2010和2003年双场景查询，同样可以用窗口函数简化，无需多个CTE和JOIN：

SELECT 
    id, 
    favorite_color, 
    date_provided,
    -- 2010年有效标记
    CASE 
        WHEN date_provided = MAX(CASE WHEN date_provided <= '2010-12-31' THEN date_provided END) OVER (PARTITION BY id) 
        THEN 'yes' 
        ELSE 'no' 
    END AS valid_color_2010,
    -- 2003年有效标记
    CASE 
        WHEN date_provided = MAX(CASE WHEN date_provided <= '2003-12-31' THEN date_provided END) OVER (PARTITION BY id) 
        THEN 'yes' 
        ELSE 'no' 
    END AS valid_color_2003
FROM my_table
ORDER BY id, date_provided;

这种方式只需要扫描一次原表，相比多次CTE+JOIN的方式，在数据量较大时性能更优，代码也更易维护。

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