使用SQL/Spark-SQL：基于聚合记录数>1更新列值的方法

业务规则 #

同一household_id且同一promo_action_date的客户组中，只要有任意客户的promo_action为DELETE，则该组所有客户的promo_action统一更新为DELETE。

一、传统SQL更新方案（适用于MySQL、PostgreSQL等） #

通过CTE定位需要批量更新的家庭-日期组合，再关联原表完成更新：

WITH delete_groups AS (
    SELECT household_id, promo_action_date
    FROM promo_action_household
    WHERE promo_action = 'DELETE'
    GROUP BY household_id, promo_action_date
)
UPDATE promo_action_household pah
SET promo_action = 'DELETE'
WHERE EXISTS (
    SELECT 1
    FROM delete_groups dg
    WHERE pah.household_id = dg.household_id
      AND pah.promo_action_date = dg.promo_action_date
);

如果数据库不支持CTE，用子查询实现：

UPDATE promo_action_household pah
SET promo_action = 'DELETE'
WHERE (household_id, promo_action_date) IN (
    SELECT household_id, promo_action_date
    FROM promo_action_household
    WHERE promo_action = 'DELETE'
    GROUP BY household_id, promo_action_date
);

二、Spark-SQL更新方案 #

Spark-SQL常用两种方式实现，根据表格式和场景选择：

方式1：生成新表覆盖（通用兼容） #

计算更新后的全量数据，直接覆盖原表：

CREATE OR REPLACE TABLE promo_action_household AS
SELECT
    customer_id,
    household_id,
    CASE
        WHEN dg.household_id IS NOT NULL THEN 'DELETE'
        ELSE pah.promo_action
    END AS promo_action,
    promo_action_date
FROM promo_action_household pah
LEFT JOIN (
    SELECT household_id, promo_action_date
    FROM promo_action_household
    WHERE promo_action = 'DELETE'
    GROUP BY household_id, promo_action_date
) dg ON pah.household_id = dg.household_id
    AND pah.promo_action_date = dg.promo_action_date;

方式2：MERGE INTO（适用于Delta Lake等ACID表） #

针对支持MERGE的表格式，做增量更新：

MERGE INTO promo_action_household pah
USING (
    SELECT household_id, promo_action_date
    FROM promo_action_household
    WHERE promo_action = 'DELETE'
    GROUP BY household_id, promo_action_date
) dg
ON pah.household_id = dg.household_id
   AND pah.promo_action_date = dg.promo_action_date
WHEN MATCHED AND pah.promo_action != 'DELETE' THEN
    UPDATE SET promo_action = 'DELETE';

效果验证 #

更新前数据：

执行方案后更新后数据：

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