开发者社区> 技术小胖子> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

行转列的一个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,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
ORACLE性能优化之SQL语句优化
ORACLE性能优化之SQL语句优化
2594 0
Oracle MySQL Or NoSQL?(转载)
转载: 作者:Sky.Jian (简朝阳)链接:http://isky000.com/database/oracle-mysql-or-nosql-2     一些英文缩写的含义: Nosql: not only sql OLTP: 联机事务处理 OLAP:联机分析处理 去IOE:摆脱掉IT部署中原有的IBM小型机、Oracle数据库以及EMC存储的过度依赖     接前面一篇,这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来,也算是前面博文中PPT的一个文字版解读吧。
1365 0
简单对比MySQL和Oracle中的一个sql解析细节
SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析。。一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值也更加明显。
943 0
SQL行转列
SQL行转列是比较经典的问题: 比如有如下数据表,有如下某一款号(表1):    颜色  尺码  库存     红色    S    10    红色    M    80    白色    L    50    白色    S    60 要将上面的表转化为如下格式(表2):  颜...
627 0
ORACLE-SQL优化
Oracle的优化器共有两种的优化方式,即: 基于规则的优化方式(Rule-Based Optimization,RBO) 基于代价的优化方式(Cost-Based Optimization,CBO)      RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。
1151 0
oracle的rollup
rollup   配合   goup   by   命令使用,可以提供信息汇总功能(类似于"小计")     下面是一个简单例子:         SQL>   select   job,de...
539 0
21114
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载