Устранить дубликаты в ListAgg (Oracle)

До Oracle 11.2 я использовал настраиваемую агрегированную функцию для конкатенации столбца в строку. 11.2. Добавлена ​​функция LISTAGG, поэтому я пытаюсь использовать ее вместо этого. Моя проблема в том, что мне нужно устранить дубликаты результатов и, похоже, не в состоянии это сделать. Вот пример.

CREATE TABLE ListAggTest AS (
  SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual 
     CONNECT BY rownum<=6
  );
SELECT * FROM ListAggTest;

      NUM1 NUM2
---------- ---------------------
         1 2
         2 2                    << Duplicate 2
         3 3
         4 4
         5 5
         6 6

Я хочу видеть следующее:

      NUM1 NUM2S
---------- --------------------
         1 2-3-4-5-6
         2 2-3-4-5-6
         3 2-3-4-5-6
         4 2-3-4-5-6
         5 2-3-4-5-6
         6 2-3-4-5-6

Вот версия listagg, которая близка, но не устраняет дубликаты.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s 
FROM ListAggTest;

У меня есть решение, но это хуже, чем использование пользовательской агрегатной функции.

40 голосов | спросил Leigh Riffel 19 Jam1000000amWed, 19 Jan 2011 01:15:08 +030011 2011, 01:15:08

13 ответов


29

Вы можете использовать регулярные выражения и regexp_replace , чтобы удалить дубликаты после конкатенации с помощью listagg:

SELECT Num1, 
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()), 
           '([^-]*)(-\1)+($|-)', 
           '\1\3'),
         '-') Num2s 
FROM ListAggTest;

Это может быть более аккуратным, если бы выражение регулярного выражения Oracle поддерживало поисковые или неконвертирующие группы, но это не так.

Однако это решение позволяет избежать сканирования источника более одного раза.

DBFiddle здесь

ответил Jack Douglas 28 12011vEurope/Moscow11bEurope/MoscowMon, 28 Nov 2011 19:40:21 +0400 2011, 19:40:21
10

Насколько я понимаю, с имеющейся в настоящее время спецификацией языка это кратчайший для достижения желаемого , если он должен быть выполнен с помощью listagg.

select distinct
       a.Num1, 
       b.num2s
  from listaggtest a cross join (
       select listagg(num2d, '-') within group (order by num2d) num2s 
       from (
         select distinct Num2 num2d from listaggtest
       )
      ) b;

Какое ваше решение было хуже, чем настраиваемое агрегированное решение ?

ответил René Nyffenegger 19 Jam1000000amWed, 19 Jan 2011 01:56:11 +030011 2011, 01:56:11
7

Создайте настраиваемую функцию агрегации для этого.

  

База данных Oracle предоставляет ряд предопределенных совокупных функций   таких как MAX, MIN, SUM для выполнения операций над набором записей.   Эти предопределенные совокупные функции могут использоваться только со скалярными   данные. Однако вы можете создать свои собственные пользовательские реализации этих   функций или определить совершенно новые агрегированные функции для использования с   сложные данные - например, с мультимедийными данными, хранящимися с использованием объекта   типы, непрозрачные типы и LOB.

     

Пользовательские агрегированные функции используются в операторах SQL DML только   как встроенные агрегаты базы данных Oracle. Когда такие функции   зарегистрированная на сервере, база данных просто вызывает   которые вы поставили вместо собственных.

     

Пользовательские агрегаты могут использоваться со скалярными данными. Для   Например, может оказаться целесообразным реализовать специальные агрегированные функции   для работы со сложными статистическими данными, связанными с финансовыми или   научных приложений.

     

Определяемые пользователем агрегаты являются отличительными чертами Рамок расширяемости.   Вы реализуете их, используя подпрограммы интерфейса ODCIAggregate.

ответил Leigh Riffel 28 12011vEurope/Moscow11bEurope/MoscowMon, 28 Nov 2011 18:54:37 +0400 2011, 18:54:37
6

Хотя это старый пост с принятым ответом, я думаю, что аналитическая функция LAG () хорошо работает в этом случае и заслуживает внимания:

  • LAG () удаляет повторяющиеся значения в столбце num2 с минимальными затратами
  • Нет необходимости в нетривиальном регулярном выражении для фильтрации результатов
  • Только одно полное сканирование таблицы (стоимость = 4 в таблице простых примеров)

Вот предлагаемый код:

with nums as (
SELECT 
    num1, 
    num2, 
    decode( lag(num2) over (partition by null order by num2), --get last num2, if any
            --if last num2 is same as this num2, then make it null
            num2, null, 
            num2) newnum2
  FROM ListAggTest
) 
select 
  num1, 
  --listagg ignores NULL values, so duplicates are ignored
  listagg( newnum2,'-') WITHIN GROUP (ORDER BY Num2) OVER () num2s
  from nums;

Ниже приведены результаты, которые желают OP:

NUM1  NUM2S       
1   2-3-4-5-6
2   2-3-4-5-6
3   2-3-4-5-6
4   2-3-4-5-6
5   2-3-4-5-6
6   2-3-4-5-6 
ответил RJLyders 8 TueEurope/Moscow2015-12-08T00:49:43+03:00Europe/Moscow12bEurope/MoscowTue, 08 Dec 2015 00:49:43 +0300 2015, 00:49:43
5

Вот мое решение проблемы, которое, на мой взгляд, не так хорошо, как использование нашей пользовательской агрегатной функции, которая уже существует.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s FROM (
  SELECT Num1, DECODE(ROW_NUMBER() OVER (PARTITION BY Num2 ORDER BY NULL),
     1,Num2,NULL) Num2 FROM ListAggTest
);
ответил Leigh Riffel 19 Jpm1000000pmWed, 19 Jan 2011 16:52:55 +030011 2011, 16:52:55
4

Вместо этого используйте WMSYS.WM_Concat.

SELECT Num1, Replace(Wm_Concat(DISTINCT Num2) OVER (), ',', '-')
FROM ListAggTest;

Примечание. Эта функция недокументирована и не поддерживается. См. https://forums.oracle.com/forums/message.jspa? MESSAGEID = 4372641 # 4372641 .

ответил Karlos 25 52011vEurope/Moscow11bEurope/MoscowFri, 25 Nov 2011 17:56:29 +0400 2011, 17:56:29
4

Вы также можете использовать инструкцию collect, а затем написать пользовательскую функцию pl /sql, которая преобразует коллекцию в строку.

CREATE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
CREATE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

select cast(collect(distinct num2 order by num2) as varchar2_ntt) 
from listaggtest

Вы можете использовать distinct и order by в collect, но если в сочетании distinct не будет работа с 11.2.0.2: (

Обходной путь может быть подзапросом:

select collect(num2 order by num2) 
from 
( 
    select distinct num2 
    from listaggtest
)
ответил Nico 16 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowFri, 16 Sep 2011 12:55:22 +0400 2011, 12:55:22
0

Я знаю, что это было когда-то после первоначальной публикации, но это было первое место, которое я нашел после того, как Гуглинг получил ответ на ту же проблему и подумал, что кто-то, кто приземлился здесь, может быть рад найти краткий ответ, который не полагается на слишком сложные запросы или регулярные выражения.

Это даст вам желаемый результат:

with nums as (
  select distinct num2 distinct_nums
  from listaggtest
  order by num2
) select num1,
         (select listagg(distinct_nums, '-') within group (order by 1) from nums) nums2list 
         from listaggtest;
ответил geekmuse 13 MarpmFri, 13 Mar 2015 20:07:59 +03002015-03-13T20:07:59+03:0008 2015, 20:07:59
0

Попробуйте следующее:

select num1,listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) Num2s 
from (
select distinct num1
    ,b.num2
from listaggtest a
    ,(
        select num2
        from listaggtest
    ) b
    order by 1,2
    )
group by num1

Проблема с другими возможными решениями заключается в отсутствии корреляции между результатами для столбца 1 и столбца 2. Чтобы обойти это, внутренний запрос создает эту корреляцию, а затем удаляет дубликаты из этого набора результатов. Когда вы выполните listagg, набор результатов уже будет чистым. проблема связана скорее с получением данных в удобном для использования формате.

ответил Kevin 28 +03002015-10-28T23:45:22+03:00312015bEurope/MoscowWed, 28 Oct 2015 23:45:22 +0300 2015, 23:45:22
0

Моя идея - реализовать хранимую функцию, подобную этой:

CREATE TYPE LISTAGG_DISTINCT_PARAMS AS OBJECT (ELEMENTO VARCHAR2(2000), SEPARATORE VARCHAR2(10));

CREATE TYPE T_LISTA_ELEMENTI AS TABLE OF VARCHAR2(2000);

CREATE TYPE T_LISTAGG_DISTINCT AS OBJECT (

    LISTA_ELEMENTI T_LISTA_ELEMENTI,
        SEPARATORE VARCHAR2(10),

    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX  IN OUT            T_LISTAGG_DISTINCT) 
                    RETURN NUMBER,

    MEMBER FUNCTION ODCIAGGREGATEITERATE   (SELF  IN OUT            T_LISTAGG_DISTINCT, 
                                            VALUE IN                    LISTAGG_DISTINCT_PARAMS ) 
                    RETURN NUMBER,

    MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF         IN     T_LISTAGG_DISTINCT,
                                            RETURN_VALUE OUT    VARCHAR2, 
                                            FLAGS        IN     NUMBER      )
                    RETURN NUMBER,

    MEMBER FUNCTION ODCIAGGREGATEMERGE       (SELF               IN OUT T_LISTAGG_DISTINCT,
                                                                                        CTX2                 IN         T_LISTAGG_DISTINCT    )
                    RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY T_LISTAGG_DISTINCT IS 

    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT) RETURN NUMBER IS 
    BEGIN
                SCTX := T_LISTAGG_DISTINCT(T_LISTA_ELEMENTI() , ',');
        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LISTAGG_DISTINCT, VALUE IN LISTAGG_DISTINCT_PARAMS) RETURN NUMBER IS
    BEGIN

                IF VALUE.ELEMENTO IS NOT NULL THEN
                        SELF.LISTA_ELEMENTI.EXTEND;
                        SELF.LISTA_ELEMENTI(SELF.LISTA_ELEMENTI.LAST) := TO_CHAR(VALUE.ELEMENTO);
                        SELF.LISTA_ELEMENTI:= SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;
                        SELF.SEPARATORE := VALUE.SEPARATORE;
                END IF;
        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LISTAGG_DISTINCT, RETURN_VALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
      STRINGA_OUTPUT            CLOB:='';
            LISTA_OUTPUT                T_LISTA_ELEMENTI;
            TERMINATORE                 VARCHAR2(3):='...';
            LUNGHEZZA_MAX           NUMBER:=4000;
    BEGIN

                IF SELF.LISTA_ELEMENTI.EXISTS(1) THEN -- se esiste almeno un elemento nella lista

                        -- inizializza una nuova lista di appoggio
                        LISTA_OUTPUT := T_LISTA_ELEMENTI();

                        -- riversamento dei soli elementi in DISTINCT
                        LISTA_OUTPUT := SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;

                        -- ordinamento degli elementi
                        SELECT CAST(MULTISET(SELECT * FROM TABLE(LISTA_OUTPUT) ORDER BY 1 ) AS T_LISTA_ELEMENTI ) INTO LISTA_OUTPUT FROM DUAL;

                        -- concatenazione in una stringa                        
                        FOR I IN LISTA_OUTPUT.FIRST .. LISTA_OUTPUT.LAST - 1
                        LOOP
                            STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(I) || SELF.SEPARATORE;
                        END LOOP;
                        STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(LISTA_OUTPUT.LAST);

                        -- se la stringa supera la dimensione massima impostata, tronca e termina con un terminatore
                        IF LENGTH(STRINGA_OUTPUT) > LUNGHEZZA_MAX THEN
                                    RETURN_VALUE := SUBSTR(STRINGA_OUTPUT, 0, LUNGHEZZA_MAX - LENGTH(TERMINATORE)) || TERMINATORE;
                        ELSE
                                    RETURN_VALUE:=STRINGA_OUTPUT;
                        END IF;

                ELSE -- se non esiste nessun elemento, restituisci NULL

                        RETURN_VALUE := NULL;

                END IF;

        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LISTAGG_DISTINCT, CTX2 IN T_LISTAGG_DISTINCT) RETURN NUMBER IS
    BEGIN
        RETURN ODCICONST.SUCCESS;
    END;

END; -- fine corpo

CREATE
FUNCTION LISTAGG_DISTINCT (INPUT LISTAGG_DISTINCT_PARAMS) RETURN VARCHAR2
    PARALLEL_ENABLE AGGREGATE USING T_LISTAGG_DISTINCT;

// Example
SELECT LISTAGG_DISTINCT(LISTAGG_DISTINCT_PARAMS(OWNER, ', ')) AS LISTA_OWNER
FROM SYS.ALL_OBJECTS;

Извините, но в некоторых случаях (для очень большого набора) Oracle может вернуть эту ошибку:

Object or Collection value was too large. The size of the value
might have exceeded 30k in a SORT context, or the size might be
too big for available memory.

, но я думаю, что это хорошая точка начала;)

ответил Massimiliano Palese 27 FebruaryEurope/MoscowbTue, 27 Feb 2018 17:31:13 +0300000000pmTue, 27 Feb 2018 17:31:13 +030018 2018, 17:31:13
-2

SQL был разработан как простой язык, очень близко к английскому. Так почему бы вам не написать его, как на английском?

  1. устранить дубликаты на num2 & использовать listagg как агрегированную функцию - не аналитическую, для вычисления concat в строке
  2. присоединиться к оригиналу, так как вы хотите, чтобы одна строка результата для одного входа

select num1, num2s
  from (select num2,
               listagg(num2, '-') within group(order by num2) over() num2s
          from listaggtest
         group by num2
       )
  join listaggtest using (num2);

ответил Štefan Oravec 10 FebruaryEurope/MoscowbThu, 10 Feb 2011 01:28:38 +0300000000amThu, 10 Feb 2011 01:28:38 +030011 2011, 01:28:38
-2
SELECT Num1, listagg(Num2,'-') WITHIN GROUP
(ORDER BY num1) OVER () Num2s FROM 
(select distinct num1 from listAggTest) a,
(select distinct num2 from ListAggTest) b
where num1=num2(+);
ответил Phil 9 PMpTue, 09 Apr 2013 12:13:12 +040013Tuesday 2013, 12:13:12
-2

Наиболее эффективным решением является внутренний SELECT с GROUP BY, потому что DISTINCT и регулярные выражения медленны, как черт.

SELECT num1, LISTAGG(num2, '-') WITHIN GROUP (ORDER BY num2) AS num2s
    FROM (SELECT num1, num2
              FROM ListAggTest
              GROUP BY num1, num2)
    GROUP BY num1;

Это решение довольно просто - сначала вы получаете все уникальные комбинации num1 и num2 (внутренний SELECT), а затем вы получаете строку всех num2, сгруппированных по num1.

ответил Ondřej Baše 6 FriEurope/Moscow2013-12-06T14:39:04+04:00Europe/Moscow12bEurope/MoscowFri, 06 Dec 2013 14:39:04 +0400 2013, 14:39:04

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132