Oracle兼容-函数-LISTAGG()函数

1. 语法

1. LISTAGG ( [ { ALL | DISTINCT } ] measure_expr [ , 'delimiter' ] )
2. [ WITHIN GROUP ( order_by_clause ) ]
3. [ OVER query_partition_clause ]

5. query_partition_clause := ( PARTITION BY column_name [,...] )

2. 定义和用法

参数说明：

  1. measure_expr 可以是列，常量或基于列的表达式。
  2. delimiter 分隔符，可以是 ‘’、字符串文字、数字、浮点等。
  3. order_by_clause ORDER BY子句决定了列值被拼接的顺序。
  4. 当 ALL 及 DISTINCT 都未指定时，视为 ALL。
  5. OVER query_partition_clause 不存在时，视为 聚合（AGGREGATE） 函数。反之，视为 分析（ANALYTIC） 函数。
  6. 目前不支持聚合类函数嵌套使用，例如：
1. greatsql> SELECT listagg(sum(id))  FROM t1 GROUP BY id;
2. ERROR 1111 (HY000): Invalid use of group function

3. Oracle兼容说明

  1. 在GreatSQL中的 LISTAGG() 函数语法参考 Oracle 19c 版本用法，与 Oracle 11r2 版本的 WITHIN GROUP 用法部分有差异。
  2. 目前尚未支持 LISTAGG OVERFLOW 语法，仍沿用GreatSQL中的 group_concat_max_len 选项限制方式。
  3. 分隔符 delimiter 目前只支持字符常量，不支持使用 session/global 设定或运算式（注意： 会将数字常量转换为字符常量，转换时可能会因浮点精度或格式问题，不一定如预期呈现，因此建议直接使用字符常量）。

4. 示例

说明：GreatSQL中返回结果顺序与Oracle顺序可能会因为使用字符集不同存在排序差异。

1. -- 创建测试表，填充测试数据
2. greatsql> CREATE TABLE plan1 (
3. id int PRIMARY KEY,
4. name VARCHAR(255)
5. );

7. greatsql> CREATE TABLE plandetail(
8. id INT PRIMARY KEY,
9. name VARCHAR(255),
10. state INT,
11. planid INT
12. );

14. greatsql> INSERT INTO plan1 VALUES(1,'计划一'), (2,'计划二'), (3,'计划三');

16. greatsql> INSERT INTO plandetail VALUES(1,'明细一',0,1), (2,'明细2',1,1), (3,'明细3',1,1);

18. greatsql> INSERT INTO plandetail VALUES(4,'明细一',0,2), (5,'明细2',0,2), (6,'明细3',1,2);

20. greatsql> INSERT INTO plandetail VALUES(7,'明细一',1,3), (8,'明细2',1,3), (9,'明细3',1,3);

1. 分别测试 GROUP_CONCAT() 和 LISTAGG()

1. greatsql> SELECT t1.name ,GROUP_CONCAT(t2.name ORDER BY planid ASC SEPARATOR ',') FROM plan1 t1
2. LEFT JOIN plandetail t2
3. ON t1.id = t2.planid
4. GROUP BY t1.name;
5. +-----------+---------------------------------------------------------+
6. | name      | GROUP_CONCAT(t2.name ORDER BY planid ASC SEPARATOR ',') |
7. +-----------+---------------------------------------------------------+
8. | 计划一    | 明细一,明细2,明细3                                      |
9. | 计划三    | 明细一,明细2,明细3                                      |
10. | 计划二    | 明细一,明细2,明细3                                      |
11. +-----------+---------------------------------------------------------+

13. greatsql> SELECT t1.name ,LISTAGG(t2.name,',') WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
14. LEFT JOIN plandetail t2
15. ON t1.id = t2.planid
16. GROUP BY t1.name;
17. +-----------+-------------------------------------------------------------+
18. | name      | LISTAGG(t2.name,',') WITHIN GROUP (ORDER BY t2.planid ASC) |
19. +-----------+-------------------------------------------------------------+
20. | 计划一    | 明细一,明细2,明细3                                          |
21. | 计划三    | 明细一,明细2,明细3                                          |
22. | 计划二    | 明细一,明细2,明细3                                          |
23. +-----------+-------------------------------------------------------------+

25. greatsql> SELECT t1.name ,LISTAGG(t2.name,'') WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
26. LEFT JOIN plandetail t2
27. ON t1.id = t2.planid
28. GROUP BY t1.name;
29. +-----------+------------------------------------------------------------+
30. | name      | LISTAGG(t2.name,'') WITHIN GROUP (ORDER BY t2.planid ASC) |
31. +-----------+------------------------------------------------------------+
32. | 计划一    | 明细一明细2明细3                                           |
33. | 计划三    | 明细一明细2明细3                                           |
34. | 计划二    | 明细一明细2明细3                                           |
35. +-----------+------------------------------------------------------------+

37. greatsql> SELECT t1.name ,LISTAGG(t2.name,':') WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
38. LEFT JOIN plandetail t2
39. ON t1.id = t2.planid
40. GROUP BY t1.name;
41. +-----------+-------------------------------------------------------------+
42. | name      | LISTAGG(t2.name,':') WITHIN GROUP (ORDER BY t2.planid ASC) |
43. +-----------+-------------------------------------------------------------+
44. | 计划一    | 明细一:明细2:明细3                                          |
45. | 计划三    | 明细一:明细2:明细3                                          |
46. | 计划二    | 明细一:明细2:明细3                                          |
47. +-----------+-------------------------------------------------------------+

49. greatsql> SELECT t1.name ,LISTAGG(t2.name,'\'') WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
50. LEFT JOIN plandetail t2
51. ON t1.id = t2.planid
52. GROUP BY t1.name;
53. +-----------+--------------------------------------------------------------+
54. | name      | LISTAGG(t2.name,'\'') WITHIN GROUP (ORDER BY t2.planid ASC) |
55. +-----------+--------------------------------------------------------------+
56. | 计划一    | 明细一'明细2'明细3                                           |
57. | 计划三    | 明细一'明细2'明细3                                           |
58. | 计划二    | 明细一'明细2'明细3                                           |
59. +-----------+--------------------------------------------------------------+

61. greatsql> SELECT t1.name ,LISTAGG(t2.name,1) WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
62. LEFT JOIN plandetail t2
63. ON t1.id = t2.planid
64. GROUP BY t1.name;
65. +-----------+-----------------------------------------------------------+
66. | name      | LISTAGG(t2.name,1) WITHIN GROUP (ORDER BY t2.planid ASC) |
67. +-----------+-----------------------------------------------------------+
68. | 计划一    | 明细一1明细21明细3                                        |
69. | 计划三    | 明细一1明细21明细3                                        |
70. | 计划二    | 明细一1明细21明细3                                        |
71. +-----------+-----------------------------------------------------------+

73. greatsql> SELECT t1.name ,LISTAGG(t2.name,-1) WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
74. LEFT JOIN plandetail t2
75. ON t1.id = t2.planid
76. GROUP BY t1.name;
77. +-----------+------------------------------------------------------------+
78. | name      | LISTAGG(t2.name,-1) WITHIN GROUP (ORDER BY t2.planid ASC) |
79. +-----------+------------------------------------------------------------+
80. | 计划一    | 明细一-1明细2-1明细3                                       |
81. | 计划三    | 明细一-1明细2-1明细3                                       |
82. | 计划二    | 明细一-1明细2-1明细3                                       |
83. +-----------+------------------------------------------------------------+

85. greatsql> SELECT t1.name ,LISTAGG(t2.name,1.0000000001) WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
86. LEFT JOIN plandetail t2
87. ON t1.id = t2.planid
88. GROUP BY t1.name;
89. +-----------+----------------------------------------------------------------------+
90. | name      | LISTAGG(t2.name,1.0000000001) WITHIN GROUP (ORDER BY t2.planid ASC) |
91. +-----------+----------------------------------------------------------------------+
92. | 计划一    | 明细一1.0000000001明细21.0000000001明细3                             |
93. | 计划三    | 明细一1.0000000001明细21.0000000001明细3                             |
94. | 计划二    | 明细一1.0000000001明细21.0000000001明细3                             |
95. +-----------+----------------------------------------------------------------------+

97. greatsql> SELECT t1.name ,LISTAGG(t2.name,-1.0000000001) WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
98. LEFT JOIN plandetail t2
99. ON t1.id = t2.planid
100. GROUP BY t1.name;
101. +-----------+-----------------------------------------------------------------------+
102. | name      | LISTAGG(t2.name,-1.0000000001) WITHIN GROUP (ORDER BY t2.planid ASC) |
103. +-----------+-----------------------------------------------------------------------+
104. | 计划一    | 明细一-1.0000000001明细2-1.0000000001明细3                            |
105. | 计划三    | 明细一-1.0000000001明细2-1.0000000001明细3                            |
106. | 计划二    | 明细一-1.0000000001明细2-1.0000000001明细3                            |
107. +-----------+-----------------------------------------------------------------------+

109. greatsql> SELECT t1.name ,LISTAGG(t2.name,'|分割|') WITHIN GROUP (ORDER BY t2.planid ASC) FROM plan1 t1
110. LEFT JOIN plandetail t2
111. ON t1.id = t2.planid
112. GROUP BY t1.name;
113. +-----------+--------------------------------------------------------------------+
114. | name      | LISTAGG(t2.name,'|分割|') WITHIN GROUP (ORDER BY t2.planid ASC)   |
115. +-----------+--------------------------------------------------------------------+
116. | 计划一    | 明细一|分割|明细2|分割|明细3                                       |
117. | 计划三    | 明细一|分割|明细2|分割|明细3                                       |
118. | 计划二    | 明细一|分割|明细2|分割|明细3                                       |
119. +-----------+--------------------------------------------------------------------+

2. 测试分析性查询

1. -- 创建测试表，填充测试数据
2. greatsql> CREATE TABLE p (population INT, nation CHAR(20), city CHAR(20));
3. greatsql> INSERT INTO p VALUES (500, 'China','Guangzhou'),
4. (1500, 'China','Shanghai'),
5. (500, 'China','Beijing'),
6. (1000, 'USA','New York'),
7. (500, 'USA','Bostom'),
8. (500, 'Japan','Tokyo');

10. -- 测试LISTAGG over partition
11. greatsql> SELECT
12. nation, LISTAGG(p.city, ',') OVER (PARTITION BY nation ) la_a_city
13. FROM p;
14. +--------+----------------------------+
15. | nation | la_a_city                  |
16. +--------+----------------------------+
17. | China  | Guangzhou,Shanghai,Beijing |
18. | China  | Guangzhou,Shanghai,Beijing |
19. | China  | Guangzhou,Shanghai,Beijing |
20. | Japan  | Tokyo                      |
21. | USA    | New York,Bostom            |
22. | USA    | New York,Bostom            |
23. +--------+----------------------------+

25. greatsql> SELECT
26. nation, city, LISTAGG(p.city, ',') OVER (PARTITION BY nation ) la_a_city
27. FROM p;
28. +--------+-----------+----------------------------+
29. | nation | city      | la_a_city                  |
30. +--------+-----------+----------------------------+
31. | China  | Guangzhou | Guangzhou,Shanghai,Beijing |
32. | China  | Shanghai  | Guangzhou,Shanghai,Beijing |
33. | China  | Beijing   | Guangzhou,Shanghai,Beijing |
34. | Japan  | Tokyo     | Tokyo                      |
35. | USA    | New York  | New York,Bostom            |
36. | USA    | Bostom    | New York,Bostom            |
37. +--------+-----------+----------------------------+

39. greatsql> SELECT
40. nation, city, LISTAGG(p.city, ',') WITHIN GROUP (ORDER BY city ASC)
41. OVER (PARTITION BY nation ) la_a_city
42. FROM p;
43. +--------+-----------+----------------------------+
44. | nation | city      | la_a_city                  |
45. +--------+-----------+----------------------------+
46. | China  | Beijing   | Beijing,Guangzhou,Shanghai |
47. | China  | Guangzhou | Beijing,Guangzhou,Shanghai |
48. | China  | Shanghai  | Beijing,Guangzhou,Shanghai |
49. | Japan  | Tokyo     | Tokyo                      |
50. | USA    | Bostom    | Bostom,New York            |
51. | USA    | New York  | Bostom,New York            |
52. +--------+-----------+----------------------------+

54. -- 测试其他分析型函数
55. greatsql> SELECT
56. SUM(population) OVER (PARTITION BY nation) population,
57. LISTAGG(p.city, ',') OVER (PARTITION BY nation) la_a_city
58. FROM p;
59. +------------+----------------------------+
60. | population | la_a_city                  |
61. +------------+----------------------------+
62. |       2500 | Guangzhou,Shanghai,Beijing |
63. |       2500 | Guangzhou,Shanghai,Beijing |
64. |       2500 | Guangzhou,Shanghai,Beijing |
65. |        500 | Tokyo                      |
66. |       1500 | New York,Bostom            |
67. |       1500 | New York,Bostom            |
68. +------------+----------------------------+

70. greatsql> SELECT
71. SUM(population) OVER (PARTITION BY nation) population,
72. LISTAGG(p.city, ',') WITHIN GROUP (ORDER BY city ASC)
73. OVER (PARTITION BY nation ) la_a_city
74. FROM p;
75. +------------+----------------------------+
76. | population | la_a_city                  |
77. +------------+----------------------------+
78. |       2500 | Beijing,Guangzhou,Shanghai |
79. |       2500 | Beijing,Guangzhou,Shanghai |
80. |       2500 | Beijing,Guangzhou,Shanghai |
81. |        500 | Tokyo                      |
82. |       1500 | Bostom,New York            |
83. |       1500 | Bostom,New York            |
84. +------------+----------------------------+

86. greatsql> SELECT
87. SUM(population) OVER (PARTITION BY nation ORDER BY city ASC) population,
88. LISTAGG(p.city, ',') OVER (PARTITION BY nation ) la_a_city
89. FROM p;
90. +------------+----------------------------+
91. | population | la_a_city                  |
92. +------------+----------------------------+
93. |        500 | Beijing,Guangzhou,Shanghai |
94. |       1000 | Beijing,Guangzhou,Shanghai |
95. |       2500 | Beijing,Guangzhou,Shanghai |
96. |        500 | Tokyo                      |
97. |        500 | Bostom,New York            |
98. |       1500 | Bostom,New York            |
99. +------------+----------------------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx