如何按指定序列补全年份维度的Left Join数据适配热力图？

问题分析 #

你当前的查询仅能返回有交易记录的年份-字母组合，无法覆盖无交易的情况，且排序逻辑依赖聚合后的字段，导致X轴字母顺序不符合预期。最优方案是先生成所有年份与所有字母的全量笛卡尔积，再左连接交易统计数据，从而补全缺失项并保证排序正确。

实现SQL #

WITH all_year_letter AS (
    -- 生成所有交易年份与所有字母的全量组合
    SELECT 
        y.year,
        tr.letter,
        tr.letter_seq
    FROM (
        -- 提取交易数据中所有唯一年份
        SELECT DISTINCT to_char(transact_dt_tm, 'YYYY') AS year
        FROM temp_data
    ) y
    -- 交叉连接得到年份-字母的全量配对
    CROSS JOIN temp_ref tr
),
transaction_stats AS (
    -- 预聚合各年份-字母的交易次数
    SELECT
        to_char(td.transact_dt_tm, 'YYYY') AS year,
        td.letter,
        COUNT(td.transact_dt_tm) AS trans_count
    FROM temp_data td
    GROUP BY to_char(td.transact_dt_tm, 'YYYY'), td.letter
)
-- 全量组合左连接统计数据，补全缺失值
SELECT
    ayl.year,
    ayl.letter,
    -- 无交易时显示0，若需空值可去掉COALESCE
    COALESCE(ts.trans_count, 0) AS trans_count
FROM all_year_letter ayl
LEFT JOIN transaction_stats ts 
    ON ayl.year = ts.year 
    AND ayl.letter = ts.letter
-- 按年份、字母序列排序，保证X轴顺序正确
ORDER BY ayl.year, ayl.letter_seq;

方案优势 #

• 无需手动编写UNION拼接多年份，适配任意年份数量的场景
• 基于temp_ref的letter_seq排序，完美匹配热力图X轴的顺序要求
• 通过COALESCE统一无交易场景的数值展示（0或空值可按需调整）

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