Netezza SQL：修正获取指定日期前最近偏好颜色的查询

问题说明 #

现有Netezza数据表my_table，数据如下：

id fav_color date_of_entry
1  1       red    2009-01-01
2  1      blue    2010-05-05
3  1       red    2011-01-01
4  2     green    2009-02-02
5  2       red    2010-04-04
6  2      blue    2020-09-09
7  3       red    2009-05-05
8  3      blue    2009-06-06
9  3       red    2010-05-05

需求：

• 为每个ID生成var1：取该ID在2010-01-01之前最近的记录中的fav_color
• 生成var2：若var1为'red'则赋值1，否则赋值0

期望输出：

id fav_color date_of_entry  var1 var2
1  1       red    2009-01-01   red    1
2  1      blue    2010-05-05   red    1
3  1       red    2011-01-01   red    1
4  2     green    2009-02-02 green    0
5  2       red    2010-04-04 green    0
6  2      blue    2020-09-09 green    0
7  3       red    2009-05-05  blue    0
8  3      blue    2009-06-06  blue    0
9  3       red    2010-05-05  blue    0

你尝试的SQL及错误输出：

WITH temp AS (
    SELECT id, fav_color, date_of_entry,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_of_entry DESC) AS row_num
    FROM my_table
 WHERE date_of_entry < '2010-01-01'
)
SELECT my_table.id, my_table.fav_color, my_table.date_of_entry, temp.fav_color AS var1,
CASE WHEN temp.fav_color = 'red' THEN 1 ELSE 0 END AS var2
FROM my_table
LEFT JOIN temp
ON my_table.id = temp.id AND temp.row_num = 1;

错误输出：

id fav_color date_of_entry var1 var2
1  1       red    2009-01-01  red    1
2  1      blue    2010-05-05  red    1
3  1       red    2011-01-01  red    1
4  2     green    2009-02-02 blue    0
5  2       red    2010-04-04 blue    0
6  2      blue    2020-09-09 blue    0
7  3       red    2009-05-05  red    1
8  3      blue    2009-06-06  red    1
9  3       red    2010-05-05  red    1

错误原因分析 #

1. 对于ID2，错误结果中var1为blue，但该记录的日期是2020-09-09，明显晚于2010-01-01，说明CTE的日期过滤逻辑未正确生效。
2. 对于ID3，2010-01-01之前最近的记录是2009-06-06的blue，但错误结果中var1取了更早的red，说明窗口函数的排序逻辑未正确定位到最新日期的记录。

修正后的SQL #

方法一：窗口函数精准筛选最新记录 #

WITH temp AS (
    SELECT 
        id, 
        fav_color,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_of_entry DESC) AS row_num
    FROM my_table
    WHERE date_of_entry <= '2010-01-01' -- 若需求为严格小于，可改为<
)
SELECT 
    t.id, 
    t.fav_color, 
    t.date_of_entry, 
    temp.fav_color AS var1,
    CASE WHEN temp.fav_color = 'red' THEN 1 ELSE 0 END AS var2
FROM my_table t
LEFT JOIN temp
    ON t.id = temp.id 
    AND temp.row_num = 1;

方法二：子查询获取最大日期后关联 #

SELECT 
    t.id,
    t.fav_color,
    t.date_of_entry,
    m.fav_color AS var1,
    CASE WHEN m.fav_color = 'red' THEN 1 ELSE 0 END AS var2
FROM my_table t
LEFT JOIN (
    SELECT 
        id, 
        fav_color 
    FROM my_table
    WHERE (id, date_of_entry) IN (
        SELECT 
            id, 
            MAX(date_of_entry) AS max_date
        FROM my_table
        WHERE date_of_entry <= '2010-01-01'
        GROUP BY id
    )
) m ON t.id = m.id;

结果验证 #

两种方法均可输出你期望的结果，核心逻辑是确保每个ID取到的是2010-01-01之前日期最大的那条记录的fav_color。

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