Oracle：聚合连接分组结果中未分组列的方法

Question

我有一个包含多个聚合函数和几个分组列的查询。我想从组中取出一个分组列，并对它拥有的所有 VARCHAR 值执行某种聚合“连接”。 （最好是在新的回车分隔列表中）。

这是我的查询，我记下我想在哪里执行此操作：

SELECT rownum, F.*
FROM (SELECT 
  c.logical_type "MerchantType",
  c.merchant_id "MerchantID",
  c.m_name "MerchantName",
  m.m_name "TransferredBy", /* <----- Make this aggregate */
  SUM(DECODE(b.ba_price,null,0,DECODE(b.BILL_SRVC_ID,'CREDITCHANGE',0,b.ba_price))) "TotalValue", 
  sum(DECODE(b.ba_price,null,0,DECODE(b.BILL_SRVC_ID,'CREDITCHANGE',b.ba_price,0))) "LimitChange", 
  SUM(DECODE(b.ba_status,'bdone',1,0)) "TxnCount",
  sum(to_number(decode(substr(b.ba_merchant_freetext,1,10),'Commission',substr(b.ba_merchant_freetext, 12,(instr(b.ba_merchant_freetext,';',1,1)-12))))) "Commission"
FROM bill_auth0 b,
  merchant0 m,
  merchant0 c
WHERE 
  b.srvc_prod_id = 'TRANSFER'
    AND b.ba_channel = 'WPSS'
    AND b.ba_status     IN ('bdone')
    AND b.merchant_id    = m.merchant_id
    AND b.customer_id    = c.merchant_id
    AND b.ba_timestamp BETWEEN to_date( '11/01/2009', 'MM/DD/YYYY' ) 
        AND to_date( '11/17/2009', 'MM/DD/YYYY' )+1
GROUP BY 
  c.logical_type,
  c.merchant_id,
  c.m_name,
  m.m_name /* <-- Remove from Grouped By */
ORDER BY c.logical_type, c.merchant_id, m.m_name) F;

所以本质上我希望能够得到一个结果，其中“TransferredBy”看起来像这样： 商户1
商家2
则为 Merchant3 。

如果有 3 个单独的 m.m_name 与该组行匹配，

Answer 1

这是一篇关于不同字符串聚合技术的好文章。

我可以添加另一种方法（基于 XML）：

select rtrim(
         extract(
           sys_xmlagg(
             xmlelement("X",ename||', ')
           ),
           '/ROWSET/X/text()'
         ).getstringval(),
         ', '
       )
  from emp;

在 11g Release 2 中，我们终于内置了 LISTAGG 函数。

Answer 2

虽然我不知道有任何内置函数能够解决您的问题，但似乎 您可以编写自己的聚合函数，可以！因为我很好奇，所以我尝试实现一个自定义聚合函数，该函数将文本与分隔符连接起来：

类型规范：

CREATE OR REPLACE TYPE TextConcatenation AS OBJECT
(

  text VARCHAR2(10000),
  delimiter VARCHAR2(10),
  concatenation_count NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT TextConcatenation) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TextConcatenation, val IN VARCHAR2) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self IN TextConcatenation, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TextConcatenation, ctx2 IN TextConcatenation) RETURN NUMBER

)

类型正文：

CREATE OR REPLACE TYPE BODY TextConcatenation AS

  STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT TextConcatenation) RETURN NUMBER IS
  BEGIN
    IF actx IS NULL THEN
      actx := TextConcatenation('', ', ', 0); #substitute your own delimiter here in the second argument
    ELSE
      actx.text := '';
      actx.delimiter := ', '; # substitute your own delimiter here
      actx.concatenation_count := 0;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT TextConcatenation, val IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF self.concatenation_count > 0 THEN
      self.text := self.text || delimiter;
    END IF;

    self.text := self.text || val;

    self.concatenation_count := self.concatenation_count + 1;

    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN TextConcatenation, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := text;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT TextConcatenation, ctx2 IN TextConcatenation) RETURN NUMBER IS
  BEGIN
    IF ctx2.concatenation_count > 0 THEN
      IF self.concatenation_count > 0 THEN
        self.text := self.text || delimiter || ctx2.text;
      ELSE
        self.text := ctx2.text;
      END IF;
      self.concatenation_count := self.concatenation_count + ctx2.concatenation_count;
    END IF;

    RETURN ODCIConst.Success;
  END;

END;

聚合函数：

CREATE OR REPLACE FUNCTION text_concatenate(text VARCHAR2) RETURN VARCHAR2 AGGREGATE USING TextConcatenation;

毕竟，我能够执行以下查询：

SELECT text_concatenate(name) FROM
(
  SELECT 'Paynter' name FROM DUAL
  UNION ALL
  SELECT 'Adam' name FROM DUAL
)

结果是单行：

Paynter, Adam

Answer 3

使用 listagg http://download.oracle.com/docs /cd/E14072_01/server.112/e10592/functions087.htm
它有充分的记录和支持。

Answer 4

尝试 wm_concat(yourColumn)...它是否可用取决于您的数据库版本。它不是官方记录的函数，但与上面列出的许多其他函数执行相同的操作。

这将生成一个逗号分隔的列表。要获得新行字符，您可以用 replace() 将其包围起来。

另一个技巧是在 wm_concat() 中使用 unique，例如 wm_concat(distinct yourColumn)，这样您就不会不会得到重复项。