Мне не так чтобы часто, но регулярно требуется с помощью SQL сделать конкатенацию всех строк в выборке где-то в таком духе:
|
1 2 3 4 5 6 7 |
SELECT field1, CONCAT(field2) FROM tbl GROUP BY field1 |
Проблема в том, что ни в стандартном SQL, ни в Oracle (до самого недавнего времени) не было функции вроде CONCAT. Что можно сделать?
Пользовательская функция
Можно написать свою собственную агрегатную функцию на PL/SQL. Не то чтобы это было совсем не фан, но коду нужно прилично настрочить.
Главная проблема такого подхода — привязка SQL к вашей базе данных. Если вы кому-то даете свой SQL, вы должны дать ему и функцию.
WM_CONCAT
Оказывается, в Oracle есть функция под названием WM_CONCAT, которая делает ровно то, что нужно:
|
1 2 3 4 5 6 7 8 |
SELECT field1, CAST(wmsys.wm_concat(field2) AS VARCHAR2(4000)), CAST(wmsys.wm_concat(DISTINCT field2) AS VARCHAR2(4000)) FROM tbl GROUP BY field1 |
Она даже поддерживает DISTINCT. Все бы хорошо, но эта функция не поддерживается Oracle официально, и СТРАШНО ГЛЮЧИТ. Она может втихую вернуть NULL. Может вдруг (именно что вдруг, вчера все работало, а сегодня план поменялся) начать выдавать ошибки, например ORA-22922. Так что не используйте эту функцию!
LISTAGG
В Oracle 11g Release 2 наконец-то добавили стандартную встроенную агрегатную функцию для конкатенации: LISTAGG. Она всем хороша: явно указывается порядок сортировки значений, можно задать произвольный разделитель, работает с DISTINCT, ее можно использовать как аналитическую функцию.
|
1 2 3 4 5 6 7 |
SELECT field1, LISTAGG(field2, '; ') WITHIN GROUP (ORDER BY field3) FROM tbl GROUP BY field1 |
Недостатков у функции всего два. Во-первых, нет способа вернуть CLOB, а не VARCHAR2; значит, длина результата ограничена. WM_CONCAT, к примеру, возвращает CLOB. Но этот недостаток может даже преимущество скорее, поскольку такие длинные строки обычно не нужны, а касты лишний раз делать — некрасиво. Во-вторых… ну, это временный недостаток, но пока актуальный: еще не все перешли на последний Oracle.
Upd от 03.09.2015
У LISTAGG есть еще один, регулярно крайне досаждающий недостаток: не поддерживается DISTINCT. Что мешало прикрутить фичу есть загадка выше моего понимания. Раз уж сделана сортировка значений, то какие технические проблемы в том, чтобы дубликаты по пути убрать?
Что делать, приходится изгаляться. Один из вариантов — это описанный ниже вариант со вложенным запросом, в котором DISTINCT уже сделан. Громоздко и не всегда возможно. Народ придумал другой способ: убрать дубликаты регулярными выражениями. Вот пример из моей практики:
|
1 2 3 4 5 6 7 8 9 |
SELECT regexp_replace( LISTAGG(accountno, ',') WITHIN GROUP (ORDER BY accountno), '([^,]+)(,\1)+', '\1' ) AS accounts_list FROM some_table_with_accountno_field WHERE ... |
Для тех, кто не очень знаком с регулярными выражениями, поясню суть происходящего: каждая найденная последовательность одинаковых значений, разделенных (в данном случае) запятой, заменяется первым значением из найденной последовательности. Естественно, чтобы это работало, сами значения не должны содержать разделителя. В моем случае все ОК, номера счетов в моей таблице запятых содержать не могут по определению.
Регулярные выражения — это явно не самый эффективный способ убирать дубликаты, но зачастую это не проблема.
XML
Так что те, кто сидит на более старой версии, чем 11g-2 (а также мазохисты, которым все же нужен CLOB), используют расширения Oracle для работы с XML:
|
1 2 3 4 5 6 7 |
SELECT field1, CAST(RTRIM(Sys_xmlagg(XMLELEMENT(col, field2||', ')).extract('/ROWSET/COL/text()').getclobval(), ', ') AS VARCHAR2(4000)) FROM tbl GROUP BY field1 |
Выглядит это жутко, но зато работает, работает надежно, и на всех версиях. Из минусов — нет DISTINCT и красивого определения порядка значений в результате. Минусы при желании обходятся, так же длинно и неуклюже. Например, DISTINCT можно заменить SELECT’ом из SELECT’а:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT field1, CAST(RTRIM(Sys_xmlagg(XMLELEMENT(col, field2||', ')).extract('/ROWSET/COL/text()').getclobval(), ', ') AS VARCHAR2(4000)) FROM ( SELECT DISTINCT field1, field2 FROM tbl ) t GROUP BY field1 |
Итого
Если вас не интересует обратная совместимость и вы уже на 11g-2 или выше, используйте LISTAGG. В противном случае извращайтесь с XML, ну или совсем уж извращайтесь и пишите свою собственную агрегатную функцию.
И с каких это пор listagg «работает с DISTINCT»?
Ты прав, я ошибся. LISTAGG с DISTINCT не работает.
Странно, почему так. Лавры монополиста позволяют забивать на очевидные вещи?