本文介绍如何通过调整 group by 子句与聚合函数，将来自多个表（historical、visualizations、other）的同 nid 记录合并为单行，统一取最早 start_date 和最晚 end_date，并合理处理非聚合字段（如 hid、type、url）。
本文介绍如何通过调整 group by 子句与聚合函数，将来自多个表（historical、visualizations、other）的同 nid 记录合并为单行，统一取最早 start_date 和最晚 end_date，并合理处理非聚合字段（如 hid、type、url）。
原始查询的问题在于：GROUP BY NID, TYPE, HID, URL 导致同一 NID 因不同 TYPE 或 HID 被拆分为多行，无法实现“按 NID 全局归并”。而需求本质是：以 NID 为唯一分组键，跨数据源统一计算时间边界，并对其他字段做语义合理的聚合表达。
✅ 正确解法：精简分组 + 语义化聚合
核心修改有两点：
- GROUP BY 仅保留 NID —— 确保所有同 ID 记录被归入一行；
- 对非分组字段使用聚合函数 —— 避免 ORA-00979 错误，同时体现业务意图。
以下是优化后的完整 SQL（Oracle 12c+）：
SELECT
NID,
MIN(START_DATE) AS START_DATE_,
MAX(END_DATE) AS END_DATE_,
-- 语义化处理：HID 取首个非空值（按优先级：HISTORICAL > 其他），可用 FIRST_VALUE 或自定义逻辑
MAX(CASE WHEN TYPE = 'HISTORICAL' THEN HID END) AS HID,
-- TYPE 统一标记为混合类型，或取出现频次最高者；此处简化为字符串拼接示意（生产环境建议用子查询统计）
LISTAGG(DISTINCT TYPE, ', ') WITHIN GROUP (ORDER BY TYPE) AS TYPE,
-- URL 仅保留 HISTORICAL 来源（因 VISUALIZATIONS/OTHER 中为 NULL），否则用 MAX(URL) 取非空值
MAX(URL) AS URL
FROM (
SELECT NID, DATESTART AS START_DATE, DATEEND AS END_DATE,
RID AS HID, 'HISTORICAL' AS TYPE, URL
FROM HISTORICAL
WHERE READER = :userId AND DISPLAY = 1
UNION ALL -- 推荐用 UNION ALL 替代 UNION（无重复去重开销，逻辑已保证不重叠）
SELECT NID, DATE AS START_DATE, DATE AS END_DATE,
NULL AS HID, 'VISUALIZATIONS' AS TYPE, NULL AS URL
FROM VISUALIZATIONS
WHERE READER = :userId AND SHOW = 1
UNION ALL
SELECT NID, DATE AS START_DATE, DATE AS END_DATE,
NULL AS HID, 'OTHER' AS TYPE, NULL AS URL
FROM OTHER
WHERE READER = :userId AND SHOW = 1 AND TYPEREMOTE != 'OR'
)
GROUP BY NID
ORDER BY MAX(END_DATE) DESC
OFFSET :offset ROWS FETCH NEXT :size ROWS ONLY;⚠️ 关键注意事项
- 慎用 UNION → 改为 UNION ALL：三个子查询来源互斥（TYPE 字面值不同），无需去重，UNION ALL 显著提升性能；
-
HID/URL 的聚合需符合业务逻辑：
- 若 HID 仅 HISTORICAL 表有效，用 MAX(CASE WHEN TYPE='HISTORICAL' THEN HID END) 安全提取；
- 若需“任意一个非空值”，可用 MAX(HID)（因 NULL < 非空值）；
- URL 同理，MAX(URL) 自动忽略 NULL 并返回首个非空值（Oracle 字符串最大值规则）；
-
TYPE 字段的聚合策略：
LISTAGG 适用于调试与展示；生产中若需单一标识（如 'MIXED'），可改为：
CASE COUNT(DISTINCT TYPE) WHEN 1 THEN MAX(TYPE) ELSE 'MIXED' END AS TYPE； - 索引建议：确保 HISTORICAL(READER, DISPLAY, NID, DATESTART, DATEEND, RID)、VISUALIZATIONS(READER, SHOW, NID, DATE) 等关键列上有复合索引，加速过滤与排序。
✅ 最终效果验证
输入（多行同 NID）：
NID | START_DATE | END_DATE | HID | TYPE 5 | 2021-04-30 | 2021-05-10 | 60 | HISTORICAL 5 | 2021-04-23 | 2021-05-09 | 70 | HISTORICAL 5 | 2021-01-01 | 2021-02-01 | 80 | HISTORICAL 5 | 2021-05-05 | 2021-05-05 | NULL| VISUALIZATIONS
输出（单行聚合）：
NID | START_DATE_ | END_DATE_ | HID | TYPE 5 | 2021-01-01 | 2021-05-10 | 60 | HISTORICAL, VISUALIZATIONS
此方案彻底解决重复问题，在保持 SQL 简洁性的同时，精准满足“NID 维度时间范围归并 + 辅助字段语义聚合”的核心诉求。
