行转列的一个SQL写法(以Oracle为例)

简介:

在数据分析的过程中,常常会遇到行转列的问题。例如,系统按事务逐条摆放,但是分析时希望以某个线索(例如每一个客户)为中心,查看所有的事项。写个性化程序或者写自定义聚合函数都可以有很好的解决方案,但是计算环境复杂多变,有程序未必能部署上,掌握一个SQL写法快速响应大多数分析场景还是很有必要的。本文以Oracle为例,主要使用了row_number函数(不使用rank函数的原因是不希望出现2条记录排名相同的情况,我们的最终目的是行转列,不是合理排名)

下列sql准备一下测试数据:

 
  1. -- drop table example_row_data;  
  2.  
  3. create table example_row_data (  
  4.        user_school varchar2(2000),  
  5.        user_name varchar2(2000),  
  6.        user_item varchar2(2000),  
  7.        user_value number         
  8. );  
  9.  
  10. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','Linux',20);  
  11. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','BI',2);  
  12. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_1','数据库',20);  
  13. commit;  
  14. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_2','Linux',10);  
  15. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_2','BI',30);  
  16. commit;  
  17. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','Linux',5);  
  18. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','BI',2);  
  19. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','数据库',6);  
  20. insert into example_row_data(user_school,user_name,user_item,user_value) values('博客','iData_3','数据挖掘',20);  
  21. commit

 查看样例数据:

 
  1. SQL> select substr(t.user_school,1,4) 类别,substr(user_name,1,8) 用户 ,substr(t.user_item,1,8) 科目,substr(t.user_value,1,2) 数值 from example_row_data t;  
  2.    
  3. 类别     用户             科目             数值  
  4. -------- ---------------- ---------------- ----  
  5. 博客     iData_1          Linux            20  
  6. 博客     iData_1          BI               2  
  7. 博客     iData_1          数据库           20  
  8. 博客     iData_2          Linux            10  
  9. 博客     iData_2          BI               30  
  10. 博客     iData_3          Linux            5  
  11. 博客     iData_3          BI               2  
  12. 博客     iData_3          数据库           6  
  13. 博客     iData_3          数据挖掘         20  
  14.    
  15. rows selected 

行转列前的准备工作,计算单个用户最大可能的科目数:

 
  1. SQL> SELECT MAX(cnt)  
  2.   2  FROM   (SELECT t.user_school  
  3.   3                ,t.user_name  
  4.   4                ,COUNT(*) cnt  
  5.   5          FROM   example_row_data t  
  6.   6          GROUP  BY t.user_school  
  7.   7                   ,t.user_name) t;  
  8.    
  9.   MAX(CNT)  
  10. ----------  
  11.          4 

行转列(已知最大科目数为4):

 
  1. SELECT substr(t.user_school, 1, 4) 类别  
  2.       ,substr(t.user_name, 1, 8) 用户  
  3.       ,MAX(decode(rk, 1, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) ||  
  4.        MAX(decode(rk, 2, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) ||  
  5.        MAX(decode(rk, 3, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) ||  
  6.        MAX(decode(rk, 4, rpad(t.user_item || ':' || t.user_value, 14, ' '), NULL)) 科目成绩  
  7. FROM   (SELECT t.*  
  8.               ,row_number() over(PARTITION BY t.user_school, t.user_name ORDER BY t.user_item) rk  
  9.         FROM   example_row_data t) t  
  10. GROUP  BY t.user_school  
  11.          ,t.user_name;  
  12.  
  13. 类别     用户             科目成绩  
  14. -------- ---------------- ---------------------------------------------------  
  15. 博客     iData_1          BI:2          Linux:20      数据库:20  
  16. 博客     iData_2          BI:30         Linux:10  
  17. 博客     iData_3          BI:2          Linux:5       数据库:6      数据挖掘:20 



本文转自 hexiaini235 51CTO博客,原文链接:http://blog.51cto.com/idata/1071030 ,如需转载请自行联系原作者

相关文章
|
18小时前
|
Oracle 关系型数据库
Oracle查询优化-行转列
【2月更文挑战第6天】【2月更文挑战第15篇】行转列
12 4
|
23天前
|
SQL Oracle 关系型数据库
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
74 0
|
1月前
|
SQL 存储 Oracle
oracle如何定期备份数据库sql文件
【1月更文挑战第7天】oracle如何定期备份数据库sql文件
47 8
|
1月前
|
SQL Oracle 关系型数据库
Oracle PL/SQL基础知识及应用案例
Oracle PL/SQL基础知识及应用案例
18 0
|
1月前
|
SQL 流计算
Flink SQL提供了行转列的功能,可以通过使用`UNPIVOT`操作来实现
【1月更文挑战第1天】Flink SQL提供了行转列的功能,可以通过使用`UNPIVOT`操作来实现
44 0
|
1月前
|
SQL Oracle 关系型数据库
oracle查询数据库参数sql语句
oracle查询数据库参数sql语句
|
1月前
|
SQL Oracle 关系型数据库
oracle查询数据库状态sql语句
oracle查询数据库状态sql语句
|
1月前
|
SQL Oracle 关系型数据库
Oracle PL/SQL 第五章–复合类型
Oracle PL/SQL 第五章–复合类型
|
9月前
|
Oracle 关系型数据库 MySQL
oracle 行转列函数 wm_concat(列)
oracle 行转列函数 wm_concat(列)
508 0
|
Oracle 关系型数据库
oracle中的行转列函数
转自:https://blog.csdn.net/qq_33157666/article/details/72854801 一、简单介绍 最近项目需要进行行转列,经过上网查资料发现了wmsys.wm_concat和LISTAGG函数,在这分享给大家 wmsys.wm_concat是oracle 10g推出的,用来连接字符串,LISTAGG是oracle 11g推出的,它的作用和wmsys.wm_concat是一样的,但是他不支持LISTAGG。
3688 0

推荐镜像

更多