oracle分析函数:四、listagg和wmsys.wm_concat
来源:互联网 发布:windows系统版本大全 编辑:程序博客网 时间:2024/05/21 09:41
/**************************************
**函数:listagg
**作用:列连接
**时间:2016-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,
unit_code,
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, unit_code, tjrq_q, end_time, out_row;
- oracle分析函数:四、listagg和wmsys.wm_concat
- Oracle行转列函数WMSYS.WM_CONCAT() 和 Listagg()
- Oracle行专列函数Listagg()和WMSYS.WM_CONCAT()
- oracle连接字符串函数,wmsys.wm_concat和LISTAGG
- 使用Listagg分析函数优化wmsys.wm_concat
- Oracle ListAgg 和 wm_concat函数
- oracle wm_concat 和 listagg函数
- oracle listagg和wm_concat函数
- listagg和wmsys.wm_concat, f_concatenate_str---无内容
- oracle wmsys.wm_concat函数
- 破解Oracle函数"wmsys.wm_concat()"
- oracle 内部函数 wmsys.wm_concat
- oracle中的wmsys.wm_concat()函数
- LISTAGG(替代wmsys.wm_concat)
- oracle 的wmsys.wm_concat函数用法
- oracle 的wmsys.wm_concat函数用法
- oracle内置函数 wmsys.wm_concat使用
- Oracle函数wmsys.wm_concat的使用
- flume学习(四):Flume Interceptors的使用
- word两个不同表格合并,防止自动调整
- 关于开发过程中,遇到的拓展问题
- 编程日记
- 百宝云注册码系统
- oracle分析函数:四、listagg和wmsys.wm_concat
- 13、new date()
- 关于NSString stringWithFormat输出“%”问题
- Web开发
- AndroidVolley
- Python获取网页指定内容(BeautifulSoup工具的使用方法)
- [译] 玩转ptrace (一) 拦截系统调用
- RabbitMQ(三):任务分发机制
- IIC_Recv如何在IIC静默模式下读取IIC总线数据