Oracle LISTAGG() 聚合查询用法 GROUP BY 和 PARTITION BY 的使用和比较

基本语法:LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)。
可以配合 GROUP BY 进行聚合查询,也可以配合 PARTITION BY 进行聚合查询。
直接上例子。
TEST_USER 表记录如下:
TEST_USER表记录.png
TEST_RECORD 表记录如下:
TEST_RECORD表记录.png
期望结果:

IDNAMEAGG_VALUES
111aaa1,3
222bbb1,2
333ccc1,3

配合 GROUP BY 查询语句:

SELECT u.ID, u.NAME, LISTAGG(r.VALUE, ',') WITHIN GROUP (ORDER BY r.VALUE) AS AGG_VALUES
FROM TEST_USER u LEFT OUTER JOIN TEST_RECORD r ON u.ID = r.ID
WHERE r.TAG IN ('start', 'end')
GROUP BY u.ID, u.NAME;

查询结果:
配合GROUP BY查询.png
配合 PARTITION BY 查询语句:

SELECT DISTINCT u.ID, u.NAME, LISTAGG(r.VALUE, ',') WITHIN GROUP (ORDER BY r.VALUE) 
OVER (PARTITION BY U.ID) AS AGG_VALUES
FROM TEST_USER u LEFT OUTER JOIN TEST_RECORD r ON u.ID = r.ID
WHERE r.TAG IN ('start', 'end');

查询结果:
配合PARTITION BY查询.png
看来二者都能达成同样的目标。
二者相似之处:

  • 都能实现聚合查询同样的结果
  • 都需要 ORDER BY 对 LISTAGG 的对象进行排序。WITHIN GROUP 可以对聚合后的单元内 (如上例中的 1,3) 元素进行排序, ORDER BY 的字段任意,并不仅限于要进行聚合操作的字段

二者不同:

  • GROUP BY 查询结果列必须出现在 GROUP BY 后面,不然会有 ORA-00979: not a GROUP BY expression 错误
  • PARTITION BY 查出的结果集是重复的 (重复数视符合条件记录数而定),需要使用 DISTINCT 进行显式去重

对于查询结果非常复杂的业务场景,个人感觉使用 PARTITION BY 会更灵活一些;GROUP BY 由于其查询结果列必须出现在 GROUP BY 条件里边而显得有些臃肿,不够优雅。

参考资料

Use LISTAGG() on multiple joined table in Oracle

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 设计师:Defonds 返回首页