基于月度求和的Redshift SQL查询：填充c_category字段

表结构 #

CREATE TABLE cat (
    c_id int,
    c_date DATE,
    c_sit VARCHAR(3),
    c_value int,
    c_category VARCHAR(1)
);

现有数据 #

当前c_category字段值为空，需实现以下规则：

• 当c_sit='25'时，若该行所属月度的c_value总和大于7，则该月所有对应行的c_category赋值为'a'，否则赋值为'b'；
• c_sit不为25的行，c_category保持为空。

期望结果 #

(c_id, c_date, c_sit, c_value, c_category)
(147121,'2022-06-06','25',4,'a'),
(147122,'2022-06-06','23',3,''),
(147123,'2022-06-07','25',5,'a'),
(147124,'2022-11-23','25',1,'b'),
(147125,'2022-11-16','25',3,'b'),
(147126,'2023-10-08','25',2,'b'),
(147127,'2023-10-09','25',5,'b'),
(147128,'2022-04-14','25',4,'a'),
(147129,'2022-04-15','25',6,'a'),
(147130,'2022-04-16','25',1,'a');

解决方案 #

查询结果生成 #

通过窗口函数按月度+c_sit汇总数值，再根据条件判断赋值：

SELECT 
    c_id,
    c_date,
    c_sit,
    c_value,
    CASE 
        WHEN c_sit != '25' THEN ''
        WHEN SUM(c_value) OVER (PARTITION BY DATE_TRUNC('month', c_date), c_sit) > 7 THEN 'a'
        ELSE 'b'
    END AS c_category
FROM cat;

更新表字段 #

如果需要直接更新表中的c_category字段，使用关联子查询的UPDATE语句：

UPDATE cat
SET c_category = sub.category
FROM (
    SELECT 
        c_id,
        CASE 
            WHEN c_sit != '25' THEN ''
            WHEN SUM(c_value) OVER (PARTITION BY DATE_TRUNC('month', c_date), c_sit) > 7 THEN 'a'
            ELSE 'b'
        END AS category
    FROM cat
) sub
WHERE cat.c_id = sub.c_id;

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