Отдельный список Oracle с количеством

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

every_description     needs_count
---------------------------------
Bred                          yes
From Vendor                   yes
Grouped                        no
Removed                       yes
Separated                      no
Weaned                        yes

Таким образом, через день описание может быть чем-то вроде Bred, Grouped, Weaned, и сейчас у меня это работает, используя LISTAGG и удаляя дубликаты с помощью упомянутого решения здесь, но мне нужно добавить счетчики для некоторых описаний, таких как 5 Bred, Grouped, 2 Weaned.

Вот мой текущий запрос, в котором я застрял:

WITH cages AS (
        SELECT 1234 AS id FROM DUAL
  UNION SELECT 5678 AS id FROM DUAL
  UNION SELECT 9012 AS id FROM DUAL
  UNION SELECT 3456 AS id FROM DUAL
), cage_comments AS (
        SELECT 1234 AS cage_id, 'Bred' AS description, TO_DATE('11/14/2017', 'MM/DD/YYYY') AS event_date FROM DUAL
  UNION SELECT 5678 AS cage_id, 'Grouped' AS description, TO_DATE('11/14/2017', 'MM/DD/YYYY') AS event_date FROM DUAL
  UNION SELECT 9012 AS cage_id, 'Weaned' AS description, TO_DATE('11/14/2017', 'MM/DD/YYYY') AS event_date FROM DUAL
  UNION SELECT 3456 AS cage_id, 'Weaned' AS description, TO_DATE('11/14/2017', 'MM/DD/YYYY') AS event_date FROM DUAL
  UNION SELECT 3456 AS cage_id, 'Bred' AS description, TO_DATE('11/02/2017', 'MM/DD/YYYY') AS event_date FROM DUAL
  UNION SELECT 3456 AS cage_id, 'Grouped' AS description, TO_DATE('11/14/2017', 'MM/DD/YYYY') AS event_date FROM DUAL
), calendar AS (
  SELECT dt
  FROM (
    SELECT TRUNC(LAST_DAY(TO_DATE(&month || '/01/' || &year, 'MM/DD/YYYY')) - ROWNUM + 1) dt
    FROM DUAL CONNECT BY ROWNUM <= 31
  )
  WHERE dt >= TRUNC(TO_DATE(&month || '/01/' || &year, 'MM/DD/YYYY'), 'MM')
  ORDER BY dt ASC
)

SELECT
  cal.dt,
  (
    SELECT
      CASE
        WHEN COUNT(cc.cage_id) > 0 THEN RTRIM(
          REGEXP_REPLACE(
            (LISTAGG(cc.description, ',') WITHIN GROUP (ORDER BY cc.description)),
            '([^,]*)(,\1)+($|,)',
            '\1\3'
          ),
          ','
        )
        ELSE NULL
      END
    FROM cages c
    LEFT JOIN cage_comments cc ON cc.cage_id = c.id
    WHERE cc.event_date = cal.dt
  ) AS description
FROM calendar cal
ORDER BY cal.dt

Короче говоря, у меня просто возникли трудности с добавлением COUNT к некоторым описаниям этого дня. В приведенном выше случае я хотел бы сказать 1 Bred вместо November 2, 2017 и 1 Bred, Grouped, 2 Weaned вместо November 14, 2017.


sql oracle count distinct listagg
person Patrick Gregorio    schedule 14.11.2017    source источник
comment
Пожалуйста, опубликуйте CREATE TABLE и INSERT INTO, чтобы воссоздать ваш случай.   -  person Lukasz Szozda    schedule 14.11.2017
comment
@ lad2025 Я добавил тестовый пример. Должно быть plug and play, просто выполните 11 и 2017 для параметров календаря.   -  person Patrick Gregorio    schedule 14.11.2017

Ответы (2)


arrow_upward
1
arrow_downward

В настоящее время вы агрегируете все описания (без DISTINCT), а затем удаляете повторяющиеся описания с заменой регулярного выражения. Это очень неэффективно — было бы лучше выбрать отдельные, а затем применить LISTAGG.

Это становится еще более важным, если вам нужно добавить счет. Возьмите результат вашего соединения и описание GROUP BY. (В частности, это позаботится о DISTINCT). В SELECT для этого статистического шага включите количество. Затем присоедините результат к дополнительной таблице в верхней части вашего вопроса и перепишите аргумент в LISTAGG, чтобы включить выражение CASE, равное количеству (и пробелу), когда значение needs_count равно 'yes'.

person mathguy    schedule 14.11.2017
comment
Прекрасный. Спасибо за это решение! - person Patrick Gregorio; 15.11.2017

arrow_upward
1
arrow_downward

Вы можете использовать:

SELECT
  cal.dt,
  ( 
      SELECT LISTAGG(CASE WHEN COUNT(*)=1 THEN '' 
            ELSE CAST(COUNT(*) AS VARCHAR2(10)) || ' ' END  || description, ', ')
            WITHIN GROUP (ORDER BY description) 
      FROM cages c
      LEFT JOIN cage_comments cc ON cc.cage_id = c.id
      WHERE cc.event_date = cal.dt
      GROUP BY cc.event_date, cc.description
  ) AS description
FROM calendar cal
ORDER BY cal.dt;

демонстрация DBFiddle

person Lukasz Szozda    schedule 14.11.2017
comment
Это близко - просто пришлось немного изменить его (DBFiddle). - person Patrick Gregorio; 15.11.2017