如何将事实表、聚合表中的多行数据合并为更少行？

场景与需求 #

现有表#1包含6行冗余数据：

需要合并为仅2行的表#2：

解决方案 #

核心是通过分组聚合提取有效维度的非0值，以下是通用SQL实现：

优化后的SQL（兼容多数关系型数据库） #

SELECT
    cntrc_key,
    MAX(re_key) AS re_key,
    aup_key,
    -- 提取全局唯一的非0 insr_key
    (SELECT insr_key FROM 表#1 WHERE insr_key != 0 LIMIT 1) AS insr_key,
    drvr_key,
    -- 提取分组内非0的othr_key
    MAX(othr_key) AS othr_key,
    MAX(lkp_key) AS lkp_key
FROM 表#1
-- 筛选核心维度非0的有效行，排除全0冗余数据
WHERE aup_key != 0 AND drvr_key != 0
-- 按核心维度分组，每个组合对应一行结果
GROUP BY cntrc_key, aup_key, drvr_key;

逻辑解析 #

1. 确定分组维度：从预期结果可判断，aup_key和drvr_key是核心分组依据，每个有效组合对应最终一行数据。
2. 提取非0值：
   • insr_key在全表仅存在一个非0值，通过子查询直接提取。
   • othr_key在aup_key=45803863的分组内有非0值，用MAX()聚合自动忽略0值，保留有效数据。
3. 过滤冗余行：提前筛选aup_key和drvr_key非0的行，减少聚合计算量。

如果使用PostgreSQL，子查询逻辑不变；SQL Server则将LIMIT 1替换为TOP 1，核心逻辑一致。

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