Архив

Публикации с меткой ‘сложить строки’

Функция LISTAGG (объединение строк)

20 Сентябрь 2011 20 comments

LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, затем соединяет указаный столбец measure_expr.

1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.

2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.

3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.

Пример агрегатной функции

SQL> select deptno, LISTAGG(t.ename, ', ') WITHIN GROUP (order by t.empno) as ename_list
  2  from scott.emp t
  3  group by deptno;

DEPTNO ENAME_LIST
------ --------------------------------------------------------------------------------
    10 CLARK, KING, MILLER
    20 SMITH, JONES, SCOTT, ADAMS, FORD, SMIT2
    30 ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES

Пример аналитической функции

SQL> select deptno,
  2         ename,
  3         job,
  4         hiredate, listagg(t.ename, ', ') 
  6           WITHIN GROUP(order by t.empno) over(partition by deptno) as ename_list
  7  from scott.emp t
  8  where hiredate > to_date('01-01-1982','dd-mm-yyyy');

DEPTNO ENAME      JOB       HIREDATE    ENAME_LIST
------ ---------- --------- ----------- -----------------------
    10 MILLER     CLERK     23.01.1982  MILLER
    20 SCOTT      ANALYST   09.12.1982  SCOTT, ADAMS, SMIT2
    20 ADAMS      CLERK     12.01.1983  SCOTT, ADAMS, SMIT2
    20 SMIT2      CLERK     25.07.2011  SCOTT, ADAMS, SMIT2

Oracle Database SQL Language Reference 11g Release 2 : LISTAGG

Значение строк в одну, через xml функции

with t as (
select 1 as id, 'a' gr from dual
union all
select 2 as id, 'a' gr from dual
union all
select 4 as id, 'b' gr from dual
)
select
 rtrim(to_char(sys_xmlagg(xmlelement(id,id ||',')).extract('/ROWSET/ID/text()').getclobval()),',') TEXT
from t
group by gr;

TEXT
-------
1,2
4

Агрегатная функция stragg — складывает строки

26 Май 2010 4 comments

Скрипт создания функции

create or replace type string_agg_type as object (
 total varchar2(4000),
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
 return number,
member function ODCIAggregateIterate(self IN OUT string_agg_type ,
                                     value IN varchar2 ) return number,
member function ODCIAggregateTerminate(self IN string_agg_type,
                                       returnValue OUT  varchar2,
                                       flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                   ctx2 IN string_agg_type) return number
);
/
create or replace type body string_agg_type is

  static function odciaggregateinitialize(sctx IN OUT string_agg_type)
    return number is
  begin
    sctx := string_agg_type(null);
    return odciconst.success;
  end;
member function odciaggregateiterate(self  IN OUT string_agg_type,
                                     value IN varchar2) return number is
  begin
    self.total := self.total || ',' || value;
    return odciconst.success;
   end;
member function odciaggregateterminate(self        IN string_agg_type,
                                       returnvalue OUT varchar2,
                                       flags       IN number) return number is
  begin
    returnvalue := ltrim(self.total, ',');
    return odciconst.success;
  end;
member function odciaggregatemerge(self IN OUT string_agg_type,
                                   ctx2 IN string_agg_type) return number is
  begin
    self.total := self.total || ctx2.total;
    return odciconst.success;
  end;
end;
/
CREATE or replace FUNCTION stragg(input varchar2) RETURN varchar2
  PARALLEL_ENABLE
  AGGREGATE USING string_agg_type;
/

Читать дальше про “Агрегатная функция stragg — складывает строки” »