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