sql - Listagg 分区而不是 Group By

Question

哪个更有效，特别是在将 Listagg 与 varchar 类型数据一起使用的情况下？

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) AS "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

或者

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) 
       OVER (PARTITION BY department_id) AS "Employees"
  FROM employees
  ORDER BY department_id;

对我来说，除非我只选择 1/2 列，否则我将始终使用分区，因为我不必在“GROUP BY”子句中包含每一列。

Answer 1

这两个查询给出不同的输出。使用 GROUP BY将在使用 OVER ( PARTITION BY .. ) 时每组返回一行将返回所有行并复制 LISTAGG分区中每一行的结果。

使用更适合您所需输出的解决方案 - 但它们并不等效。

SQL Fiddle

Oracle 11g R2 架构设置 :

CREATE TABLE employees ( department_id, last_name, hire_date ) AS
SELECT 1, 'AAA', DATE '2018-01-01' FROM DUAL UNION ALL
SELECT 1, 'BBB', DATE '2018-01-02' FROM DUAL UNION ALL
SELECT 1, 'CCC', DATE '2018-01-03' FROM DUAL UNION ALL
SELECT 2, 'DDD', DATE '2018-01-01' FROM DUAL UNION ALL
SELECT 2, 'EEE', DATE '2018-01-02' FROM DUAL;

查询 1 :

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) AS "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id

Results :

| Dept. |     Employees |
|-------|---------------|
|     1 | AAA; BBB; CCC |
|     2 |      DDD; EEE |

查询 2 :

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) 
       OVER (PARTITION BY department_id) AS "Employees"
  FROM employees
  ORDER BY department_id

Results :

| Dept. |     Employees |
|-------|---------------|
|     1 | AAA; BBB; CCC |
|     1 | AAA; BBB; CCC |
|     1 | AAA; BBB; CCC |
|     2 |      DDD; EEE |
|     2 |      DDD; EEE |