ORACLE分析函数(6)--使用listagg实现行列转换

最新推荐文章于 2021-04-09 08:47:41 发布

ezbit

最新推荐文章于 2021-04-09 08:47:41 发布

阅读量5.1k

点赞数 1

分类专栏： Oracle Develop Oracle Warehouse 文章标签： oracle listagg 行转列分析函数聚合函数

本文链接：https://blog.csdn.net/yidian815/article/details/12848399

版权

Oracle Develop 同时被 2 个专栏收录

11 篇文章 2 订阅

订阅专栏

Oracle Warehouse

8 篇文章 0 订阅

订阅专栏

listagg函数是在oracle11g中新引入的聚合函数，当然也可以作为分析函数来使用。该函数可以基于分组内特定列的排序，将测量列进行拼接合并。

示例：

在10g中，该函数不被支持

SQL> select prod_id, LISTAGG(cust_first_name||' '||cust_last_name,';') WITHIN GROUP (order by amount_sold) cust_name from sales, customers
  2  where sales.cust_id = customers.cust_id and cust_gender = 'M' 
and cust_credit_limit = 15000 and prod_id between 15 and 18
and channel_id =2 and time_id > to_date('01-06-01','YY-MM-DD')
group by prod_id;  3    4    5  
select prod_id, LISTAGG(cust_first_name||' '||cust_last_name,';') WITHIN GROUP (order by amount_sold) cust_name from sales, customers
                                                                         *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

在11g中，运行结果如下：

SQL> l
  1  select prod_id, LISTAGG(cust_first_name||' '||cust_last_name,';') WITHIN GROUP (order by amount_sold) cust_name from sales, customers
  2  where sales.cust_id = customers.cust_id and cust_gender = 'M'
  3  and cust_credit_limit = 15000 and prod_id between 15 and 18
  4  and channel_id =2 and time_id > to_date('01-06-01','YY-MM-DD')
  5* group by prod_id
SQL> /

   PROD_ID CUST_NAME
---------- ----------------------------------------------------------------------------------------------------
	15 Mason Murray;Helga Nickols;Roxanne Crocker;Glenn Wong;Roxanne Crocker;Franklin Hagan;Franklin Hagan;
	   Ransom Wiser;Reyburn Markman

	16 Forrest Lindsey;Helga Nickols;Helga Nickols;Roxanne Crocker;Garrett Manson;Roxanne Crocker;Franklin
	   Hagan;Prane Oppy;Franklin Hagan;Bud Smyth;Reyburn Markman

	17 Garrett Manson;Roxanne Crocker;Helga Nickols;Roxanne Crocker;Helga Nickols;Mason Murray;Ethan Jeffre
	   ys;Franklin Hagan;Prane Oppy;Royd Ricketts

	18 Erik Ready;Garrett Manson;Forrest Lindsey;Franklin Hagan;Franklin Hagan;Reyburn Markman

当然，listagg也可以基于over()的分区。

示例：

SQL> l
  1  select time_id,prod_id,min(amount_sold),listagg(min(amount_sold),';') within group (order by prod_id) over (partition by time_id) cust_list
  2  from sales where time_id > to_date('20-DEC-01','DD-MON-YY','NLS_DATE_LANGUAGE = American') and prod_id between 120 and 125
  3* group by prod_id,time_id
SQL> /

TIME_ID 	       PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-21 00:00:00	   120		  51.36 51.36;10.81
2001-12-21 00:00:00	   121		  10.81 51.36;10.81
2001-12-22 00:00:00	   120		  51.36 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00	   121		  10.81 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00	   122		  20.23 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00	   123		  56.12 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00	   124		  17.79 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00	   125		  15.67 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-23 00:00:00	   120		  51.36 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00	   121		  10.49 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00	   122		  20.23 51.36;10.49;20.23;57.86;17.79;15.67

TIME_ID 	       PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-23 00:00:00	   123		  57.86 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00	   124		  17.79 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00	   125		  15.67 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-24 00:00:00	   120		  51.36 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00	   121		  10.49 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00	   122		  20.23 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00	   123		  56.12 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00	   124		  17.79 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00	   125		  15.67 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-25 00:00:00	   120		  51.36 51.36;10.81
2001-12-25 00:00:00	   121		  10.81 51.36;10.81

TIME_ID 	       PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-26 00:00:00	   123		  57.86 57.86
2001-12-27 00:00:00	   120		  51.36 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00	   121		  10.49 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00	   122		  20.23 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00	   123		  56.12 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00	   124		  17.79 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00	   125		  15.67 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-28 00:00:00	   120		  51.36 51.36;10.49;56.12;17.79;15.67
2001-12-28 00:00:00	   121		  10.49 51.36;10.49;56.12;17.79;15.67
2001-12-28 00:00:00	   123		  56.12 51.36;10.49;56.12;17.79;15.67
2001-12-28 00:00:00	   124		  17.79 51.36;10.49;56.12;17.79;15.67

TIME_ID 	       PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-28 00:00:00	   125		  15.67 51.36;10.49;56.12;17.79;15.67
2001-12-29 00:00:00	   122		  20.23 20.23;56.12;17.79;15.67
2001-12-29 00:00:00	   123		  56.12 20.23;56.12;17.79;15.67
2001-12-29 00:00:00	   124		  17.79 20.23;56.12;17.79;15.67
2001-12-29 00:00:00	   125		  15.67 20.23;56.12;17.79;15.67
2001-12-30 00:00:00	   120		  51.36 51.36;10.49
2001-12-30 00:00:00	   121		  10.49 51.36;10.49
2001-12-31 00:00:00	   121		  10.81 10.81

已选择41行。