时间戳

首页 > 解决方案 > Pivot 不适用于聚合 clob 列

sql - Pivot 不适用于聚合 clob 列

问题描述

我有如下数据

ID   COUNT  COL1    COL_CLOB
12345   5      a     (HUGE_CLOB)
12345   8      b     (HUGE_CLOB)

我想要像下面这样的输出

 ID   a_count   a_COL_CLOB  b_COUNT b_COL_CLOB
 12345   5      (HUGE_CLOB)     8    (HUGE_CLOB)

我在查询中使用 xmlagg，因为 COL_CLOB 大小超过 4000 个字符，而 listagg 不支持，pivot 不适用于聚合 clob 列。好心提醒

标签： sqloracleplsqlpivot

解决方案

您可以定义自己的函数来聚合CLOB值：

CREATE OR REPLACE TYPE CLOBAggregation AS OBJECT(
  value CLOB,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT CLOBAggregation
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT CLOBAggregation,
    value       IN     CLOB
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT CLOBAggregation,
    returnValue    OUT CLOB,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT CLOBAggregation,
    ctx         IN OUT CLOBAggregation
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY CLOBAggregation
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT CLOBAggregation
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := CLOBAggregation( NULL );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT CLOBAggregation,
    value       IN     CLOB
  ) RETURN NUMBER
  IS
  BEGIN
    IF value IS NULL THEN
      NULL;
    ELSIF self.value IS NULL THEN
      self.value := value;
    ELSE
      self.value := self.value || ',' || value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT CLOBAggregation,
    returnValue    OUT CLOB,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.value;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT CLOBAggregation,
    ctx         IN OUT CLOBAggregation
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.value IS NULL THEN
      self.value := ctx.value;
    ELSIF ctx.value IS NULL THEN
      NULL;
    ELSE
      self.value := self.value || ',' || ctx.value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

CREATE FUNCTION CLOB_AGG( value CLOB )
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING CLOBAggregation;
/

然后，对于样本数据：

CREATE TABLE table_name ( id NUMBER, "COUNT" NUMBER, col1 VARCHAR2(5), col_clob CLOB );

DECLARE
  p_clob CLOB;
BEGIN
  p_clob := EMPTY_CLOB() || LPAD( 'a', 4000, 'a' ) || LPAD( 'a', 10, 'a' );
  INSERT INTO table_name VALUES ( 12345, 5, 'a', p_clob );
  p_clob := EMPTY_CLOB() || LPAD( 'b', 4000, 'b' ) || LPAD( 'b', 10, 'b' );
  INSERT INTO table_name VALUES ( 12345, 8, 'b', p_clob );
END;
/

然后，您可以使用条件聚合：

SELECT id,
       MAX( CASE col1 WHEN 'a' THEN "COUNT"  END ) AS a_count,
       CLOB_AGG( CASE col1 WHEN 'a' THEN col_clob END ) AS a_col_clob,
       MAX( CASE col1 WHEN 'b' THEN "COUNT"  END ) AS b_count,
       CLOB_AGG( CASE col1 WHEN 'b' THEN col_clob END ) AS b_col_clob
FROM   table_name
GROUP BY id

哪个输出：

身份证 | A_COUNT | A_COL_CLOB | B_COUNT | B_COL_CLOB
----: | ------: | :-------------------- | ------: | :--------------------
12345 | 5 | aaaaa<4000 更多>aaaaa | 8 | bbbb<4000 更多>bbbb

db<>在这里摆弄

推荐阅读