我不能让LISTAGG在甲骨文做它应该做的？

Question

我的数据看起来是：

MEDIA_ID | CHANNEL_NAME
EH/123A     CH-1
EH/123A     CH-4
EH/132A     CH-5
ES/133B     CH-1
ES/133B     CH-2
ES/133B     CH-5

我想要的是：

EH/123A  |  CH-1,CH-4,CH-5
ES/123B  |  CH-1,CH-2,CH-5

我在Oracle中使用这个SQL：

SELECT DISTINCT 
PR.MEDIA_ID
, LISTAGG(PR.CHANNEL_NAME, ', ') WITHIN GROUP (ORDER BY CHANNEL_NAME) AS PREM_CHAN
FROM PREM_REPORT PR
GROUP BY PR.MEDIA_ITEM, PR.CHANNEL_NAME;

我得到的是：

MEDIA_ID | CHANNEL_NAME
EH/123A     CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1
EH/123A     CH-4,CH-4,CH-4,CH-4,CH-4,CH-4,CH-4,CH-4,CH-4,CH-4,CH-4,CH-4
EH/132A     CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5
ES/133B     CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1,CH-1
ES/133B     CH-2,CH-2,CH-2,CH-2,CH-2,CH-2,CH-2,CH-2,CH-2,CH-2,CH-2,CH-2
ES/133B     CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5,CH-5

想法？

谢谢。本

Answer 1

我想你想要的问题是：

SELECT PR.MEDIA_ID,
       LISTAGG(PR.CHANNEL_NAME, ', ') WITHIN GROUP (ORDER BY CHANNEL_NAME) AS PREM_CHAN
FROM PREM_REPORT PR
GROUP BY PR.MEDIA_ITEM;

也就是说，从查询中删除PR.CHANNEL_NAME。我不知道你为什么要通过你提供的查询得到你的结果。也许select distinct和group by之间有一些奇怪的交互。您几乎从不将select distinct与group by结合使用。

编辑：

要在LIST_AGG()中返回不同的值，需要使用子查询。在这种情况下，一种简单的工作方式是：

SELECT PR.MEDIA_ID,
       LISTAGG(PR.CHANNEL_NAME, ', ') WITHIN GROUP (ORDER BY CHANNEL_NAME) AS PREM_CHAN
FROM (SELECT DISTINCT MEDIA_ID, CHANNEL_NAME
      FROM PREM_REPORT PR
     ) PR
GROUP BY PR.MEDIA_ITEM;

Answer 2

我想你搞错小组了。这是对我有用的。

WITH prem_report AS (
  SELECT 'EH/123A' media_id, 'CH-1' channel_name FROM DUAL
  UNION
  SELECT 'EH/123A' media_id, 'CH-4' channel_name FROM DUAL
  UNION
  SELECT 'EH/132A' media_id, 'CH-5' channel_name FROM DUAL
  UNION
  SELECT 'ES/133B' media_id, 'CH-1' channel_name FROM DUAL
  UNION
  SELECT 'ES/133B' media_id, 'CH-2' channel_name FROM DUAL
  UNION
  SELECT 'ES/133B' media_id, 'CH-5' channel_name FROM DUAL
)
SELECT DISTINCT pr.media_id, LISTAGG(pr.channel_name, ', ') WITHIN GROUP     (ORDER BY channel_name) AS prem_chan
FROM prem_report pr
GROUP BY pr.media_id