oracle wm_concat listagg oracle分析函数：四 listagg和wmsys.wm_concat

/**************************************

**函数：listagg

**作用：列连接

**时间：-7-8

**内容：语法和使用案例

**************************************/

–1 语法

listagg(measure_expr,’delimiter’) within group (order_by_clause) over(query_partition_clause)

–注意：order_by_clause不在over()里面

–注：语法解读

The arguments to the function are subject to the following rules:

•The measure_expr can be any expression. Null values in the measure column are ignored.

•The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL.

定界符

•The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.

The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.

–2 目的

For a specified measure,LISTAGG orders data within each group specified in the ORDER BY clause

and then concatenates the values of the measure column.

对一个制定的列，listagg根据order_by_clause排序每个组的数据；

然后对于这个特定列，进行值的连接

•As a group-set aggregate,the function operates on and returns an output row for each group defined by the GROUP BY clause

•As an analytic function,LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause

–3 例子

SELECT unit_id,

unit_code,

tjrq_q,

out_row,

end_time,

LISTAGG(out_row,‘\’) WITHIN GROUP(ORDER BY end_time,out_row) OVER(PARTITION BY unit_id,tjrq_q) as “out_row_list”

FROM etl_exdw_log

ORDER BY unit_id,unit_code,tjrq_q;

–4 相似函数 SELECT unit_id,tjrq_q,out_row,end_time,wmsys.wm_concat(out_row) OVER(PARTITION BY unit_id,tjrq_q) as “Emp_list” FROM etl_exdw_log ORDER BY unit_id,out_row;