Redshift下合并异日期双表为单一日期列的实现及报错解决

报错原因 #

• Redshift 中generate_series属于仅领导节点可执行的函数，无法直接与普通用户表做JOIN关联，这是触发报错的核心原因。
• 原有逻辑不需要生成全量日期序列，反而会引入不必要的性能开销。

正确实现方案 #

只需要将两张表的记录先统一结构合并，再按日期和客户分组聚合，就能直接得到预期结果，逻辑如下：

1. 提取关单表的所有记录，标识close=1、check=0
2. 提取结账表的所有记录，标识close=0、check=1
3. 合并两类记录后按日期、客户分组，判断当日是否有关单/结账操作即可

可直接运行的SQL代码 #

WITH all_records AS (
    -- 提取所有关单记录
    SELECT 
        time_modified::date AS date,
        customer_name AS account_id_a,
        1 AS close,
        0 AS check
    FROM schema.rz

    UNION ALL

    -- 提取所有结账记录
    SELECT 
        time_modified::date AS date,
        account_id AS account_id_a,
        0 AS close,
        1 AS check
    FROM schema.bo
)
SELECT 
    date,
    account_id_a,
    -- 当日有关单记录则为1，否则为0
    CASE WHEN SUM(close) >= 1 THEN 1 ELSE 0 END AS close,
    -- 当日有结账记录则为1，否则为0
    CASE WHEN SUM(check) >= 1 THEN 1 ELSE 0 END AS check
FROM all_records
GROUP BY date, account_id_a
-- 按照用户、日期排序，和预期输出顺序一致
ORDER BY account_id_a, date;

逻辑适配说明 #

如果存在同一个客户同一天有多条同类型操作的情况，上面用SUM加CASE判断的逻辑也能正确输出1/0标识，不会出现数值大于1的问题，完全匹配需求。

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