LISTAGG как оконная функция с нарастающим итогом

Question

Нужен аналог SUM, но вместо суммы - конкатенация. Например следующий код является рабочим:

SELECT sum(num) OVER (ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
     , num
  FROM (SELECT level AS num FROM dual CONNECT BY level <= 4) t;

Ну или просто:

SELECT sum(num) OVER (ORDER BY num) AS running_total
     , num
  FROM (SELECT level AS num FROM dual CONNECT BY level <= 4) t;

Вернется результат:

RUNNING_TOTAL	NUM
1	1
3	2
6	3

А следующий код является не рабочим (хотя среда Intellij Idea ошибки не видит):

SELECT listagg(num, ',') WITHIN GROUP (ORDER BY num)
               OVER (ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
     , num
  FROM (SELECT level AS num FROM dual CONNECT BY level <= 10) t;

Вернется непонятная ошибка: "[99999][30487] ORA-30487: ORDER BY not allowed here" (хотя в целом её можно использовать как оконную, но только с PARTITION и без ORDER BY.

Я же хочу получить результат:

RUNNING_TOTAL	NUM
1	1
1,2	2
1,2,3	3

Как вариант на ум приходит использование функции как агрегирующей. Что-то вроде:

  WITH
    cte(num, lvl) AS (SELECT level AS num
                           , 1 AS lvl
                        FROM dual
                     CONNECT BY level <= 3
                       UNION ALL
                      SELECT c.num - 1 AS num
                           , lvl + 1 AS lvl
                        FROM cte c
                       WHERE num - 1 >= 1)
SELECT listagg(cte.num, ',') WITHIN GROUP (ORDER BY num) AS running_total
     , max(num) AS num
  FROM cte
 GROUP BY lvl
 ORDER BY num;

Но вопрос в том, можно ли сделать это проще.

Answer 1

У listagg есть order-by-clause, просто order-by пишется внутри within group, а не внутри over.
У неё нет windowing-clause (где задаётся range/rows between).

SELECT 
    n, val,
    regexp_substr(
        listagg(val, ',') within group (order by n) over (), 
        '(,?[^,]+){'||n||'}'
    ) as list
FROM
    (SELECT level AS n, 'V'||level as val FROM dual CONNECT BY level <= 10) t
order by n;

почему разрабы не включили возможность использования накопления как в случае с суммой

Потому что из общей суммы частичную сумму извлечь невозможно. А из общей конкатенации частичную конкатенацию - легко! Вот поэтому и не сделали, чтобы не усложнять реализацию listagg ненужными возможностями.

БЛОГ НА HUSL

LISTAGG как оконная функция с нарастающим итогом

Ответы (1 шт):