信阳市住房建设局网站,网站如何做实名验证码,上海网站开发公司排名,php论坛网站建设教程系列文章目录
【SQL开发实战技巧】系列#xff08;一#xff09;:关于SQL不得不说的那些事 【SQL开发实战技巧】系列#xff08;二#xff09;#xff1a;简单单表查询 【SQL开发实战技巧】系列#xff08;三#xff09;#xff1a;SQL排序的那些事 【SQL开发实战技巧…系列文章目录
【SQL开发实战技巧】系列一:关于SQL不得不说的那些事 【SQL开发实战技巧】系列二简单单表查询 【SQL开发实战技巧】系列三SQL排序的那些事 【SQL开发实战技巧】系列四从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列五从执行计划看IN、EXISTS 和 INNER JOIN效率我们要分场景不要死记网上结论 【SQL开发实战技巧】系列六从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率记住内外关联条件不要乱放 【SQL开发实战技巧】系列七从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起 【SQL开发实战技巧】系列八聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表 【SQL开发实战技巧】系列九一个update误把其他列数据更新成空了Merge改写update给你五种删除重复数据的写法 【SQL开发实战技巧】系列十从拆分字符串、替换字符串以及统计字符串出现次数说起 【SQL开发实战技巧】系列十一拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数 【SQL开发实战技巧】系列十二三问(如何对字符串字母去重后按字母顺序排列字符串如何识别哪些字符串中包含数字如何将分隔数据转换为多值IN列表?) 【SQL开发实战技巧】系列十三讨论一下常用聚集函数通过执行计划看sum()over()对员工工资进行累加 【SQL开发实战技巧】系列十四计算消费后的余额计算银行流水累计和计算各部门工资排名前三位的员工 【SQL开发实战技巧】系列十五查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report 【SQL开发实战技巧】系列十六数据仓库中时间类型操作初级日、月、年、时、分、秒之差及时间间隔计算 【SQL开发实战技巧】系列十七数据仓库中时间类型操作初级确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数 【SQL开发实战技巧】系列十八数据仓库中时间类型操作进阶INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算 【SQL开发实战技巧】系列十九数据仓库中时间类型操作进阶如何一个SQL打印当月或一年的日历如何确定某月内第一个和最后—个周内某天的日期 【SQL开发实战技巧】系列二十数据仓库中时间类型操作进阶获取季度开始结束时间以及如何统计非连续性时间的数据 【SQL开发实战技巧】系列二十一数据仓库中时间类型操作进阶识别重叠的日期范围按指定10分钟时间间隔汇总数据 【SQL开发实战技巧】系列二十二数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式 【SQL开发实战技巧】系列二十三数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高 【SQL开发实战技巧】系列二十四数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的 文章目录系列文章目录前言一、行转列二、列转行三、将结果集反向转置为一列总结前言
本篇文章讲解的主要内容是目前Oracle支持的行列互换有两种方式case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”“列转行”的需求并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。 【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作毕竟SQL开发在数据分析场景非常重要且基础面试也会经常问SQL开发和调优经验相信当我写完这一系列文章也能再有所收获未来面对SQL面试也能游刃有余~。 一、行转列
行转列的这个写法在做报表或语句改写时要经常用到,是一个非常重要的语句。 在Oracle中,有CASE WHEN END和Oracle11g新增的pivot函数两种方式。其中
CASE WHEN END编写和维护较麻烦,但适合的场景较多。PIVOT编写和维护简单,但有较大限制。
下面简单介绍这两种方法。 现在我有这么一个需求 对emp表按job分组汇总,每个部门显示为一列。首先看一下最常用的CASE WHEN END的方法。 根据不同的条件来取值,就可以把数据分为几列。
SQL select a.job,2 case when a.deptno 10 then sal end as d10,3 case when a.deptno 20 then sal end as d20,4 case when a.deptno 30 then sal end as d305 from emp a6 order by 1;JOB D10 D20 D30
--------- ---------- ---------- ----------
ANALYST 3000
CLERK 800
CLERK 950
CLERK 1100
MANAGER 2450
MANAGER 2975
MANAGER 2850
MGR
PRESIDENT 5000
SALESMAN 1250
SALESMAN 1600
SALESMAN 1250
SALESMAN 1500
sdf 1300 17 rows selected只是这样的数据看上去杂乱无章,需要再按job分组汇总,所以一般行转列语句里都会有聚集函数,就是为了把同类数据转为一行显示。 另外,要注意最后一列,我们增加了合计工资的显示,这在后面介绍的PIVOT函数中是做不到的,PIVOT 函数只能按同一个规则分类各数据,各列之间的数据不能交叉重复。
SQL select a.job,2 sum(case when a.deptno 10 then sal end) as d10,3 sum(case when a.deptno 20 then sal end) as d20,4 sum(case when a.deptno 30 then sal end) as d30,5 sum(sal)as sm6 from emp a7 group by job8 order by 1;JOB D10 D20 D30 SM
--------- ---------- ---------- ---------- ----------
ANALYST 3000 3000
CLERK 1900 950 2850
MANAGER 2450 2975 2850 8275
MGR
PRESIDENT 5000 5000
SALESMAN 5600 5600
sdf 1300 13008 rows selected下面看一下Oracle11g新增的行转列函数PIVOT,对简单的PIVOT环境提供了简单的实现方法。
SQL
SQL select * from (2 --先查询出来要进行操作的数据3 select job,sal,deptno from emp4 )pivot(5 sum(sal) as sm /*这里写聚集函数以及起别名SUM、MAX等聚集函数列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/6 for deptno in(7 10 as d10, /*这里等价于前面的查询sum(case when deptno 10 then sal end) as d10,列名称为d10_sm这个sm是前面聚集函数的别名*/8 20 as d20,/*这里等价于前面的查询sum(case when deptno 20 then sal end) as d20*/9 30 as d30/*这里等价于前面的查询sum(case when deptno 30 then sal end) as d30*/10 )11 )12 order by 1;JOB D10_SM D20_SM D30_SM
--------- ---------- ---------- ----------
ANALYST 3000
CLERK 1900 950
MANAGER 2450 2975 2850
MGR
PRESIDENT 5000
SALESMAN 5600
sdf 1300 8 rows selected大家可以看一下两种方式的对比,如果还要增加提成的返回,用PIVOT则只需要增加一个设定即可。
SQL select * from (2 --先查询出来要进行操作的数据3 select job,sal,comm,deptno from emp4 )pivot(5 sum(sal) as sm,/*这里写聚集函数以及起别名SUM、MAX等聚集函数列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/6 sum(comm)as sc /*这里写聚集函数以及起别名SUM、MAX等聚集函数列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/7 for deptno in(8 10 as d10, /*这里等价于前面的查询sum(case when deptno 10 then sal end) as d10,sum(case when deptno 10 then comm end) as d10,列名称为d10_sc这个sm是前面聚集函数的别名*/9 20 as d20,/*这里等价于前面的查询sum(case when deptno 20 then sal end) as d20*/10 30 as d30/*这里等价于前面的查询sum(case when deptno 30 then sal end) as d30*/11 )12 )13 order by 1;JOB D10_SM D10_SC D20_SM D20_SC D30_SM D30_SC
--------- ---------- ---------- ---------- ---------- ---------- ----------
ANALYST 3000
CLERK 1900 950
MANAGER 2450 2975 2850
MGR
PRESIDENT 5000
SALESMAN 5600 2200
sdf 1300 8 rows selected如果用CASE WHEN要增加三行语句。
SQL select a.job,2 sum(case when a.deptno 10 then sal end) as d10_sm,3 sum(case when a.deptno 10 then sal end) as d10_sc,4 sum(case when a.deptno 20 then sal end) as d20_sm,5 sum(case when a.deptno 20 then sal end) as d20_sc,6 sum(case when a.deptno 30 then sal end) as d30_sm,7 sum(case when a.deptno 30 then sal end) as d30_sc8 from emp a9 group by job10 order by 1;JOB D10_SM D10_SC D20_SM D20_SC D30_SM D30_SC
--------- ---------- ---------- ---------- ---------- ---------- ----------
ANALYST 3000 3000
CLERK 1900 1900 950 950
MANAGER 2450 2450 2975 2975 2850 2850
MGR
PRESIDENT 5000 5000
SALESMAN 5600 5600
sdf 1300 1300 8 rows selectedPIVOT一次只能按一个条件来完成行转列,如果同时把工作与部门都转为列,并汇总为一行时,PIVOT就无能为力了,这时只能用CASE WHEN。
SQL select2 case when deptno10 then ename end as d10,3 case when deptno20 then ename end as d20,4 case when deptno30 then ename end as d30,5 case when jobANALYST then ename end as ANALYST,6 case when jobCLERK then ename end as CLERK,7 case when jobMANAGER then ename end as MANAGER,8 case when jobMGR then ename end as MGR,9 case when jobPRESIDENT then ename end as PRESIDENT,10 case when jobSALESMAN then ename end as SALESMAN11 from emp;D10 D20 D30 ANALYST CLERK MANAGER MGR PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------zhaoyd zhaoyd er er ALLEN ALLENWARD WARDJONES JONES MARTIN MARTINBLAKE BLAKE
CLARK CLARK
KING KING TURNER TURNERADAMS ADAMS JAMES JAMES FORD FORD
sdf 17 rows selectedSQL 最后分析一下PIVOT的PLAN(用dbms_xplan.display_cursor看)
select/*zydtest*/ * from (
--先查询出来要进行操作的数据
select job,sal,comm,deptno from emp
)pivot(
sum(sal) as sm /*这里写聚集函数以及起别名SUM、MAX等聚集函数列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/
for deptno in(10 as d10, /*这里等价于前面的查询sum(case when deptno 10 then sal end) as d10,sum(case when deptno 10 then comm end) as d10,列名称为d10_sc这个sm是前面聚集函数的别名*/20 as d20,/*这里等价于前面的查询sum(case when deptno 20 then sal end) as d20*/30 as d30 /*这里等价于前面的查询sum(case when deptno 30 then sal end) as d30*/)
)
order by 1;
select * from v$sql aa where aa.SQL_TEXT like %zydtest%;SQL select * from table(dbms_xplan.display_cursor(5ss3y129x7p0a,0,advanced)); PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5ss3y129x7p0a, child number 0
-------------------------------------
select/*zydtest*/ * from ( -- select
job,sal,comm,deptno from emp )pivot( sum(sal) as sm
/*SUMMAX,,
in,*/ for deptno
in( 10 as d10, /*sum(case when deptno 10 then
sal end) as d10,sum(case when deptno 10 then comm end) as
d10,d10_scsm*/ 20 as
d20,/*sum(case when deptno 20 then sal end) as
d20*/ 30 as d30 /*
Plan hash value: 1018027214
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT GROUP BY PIVOT| | 19 | 285 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 19 | 285 | 3 (0)| 00:00:01 |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1 - SEL$117FC0EF2 - SEL$117FC0EF / EMPSEL$2
Outline Data
-------------/*BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE(11.2.0.4)DB_VERSION(11.2.0.4)ALL_ROWSOUTLINE_LEAF(SEL$117FC0EF)MERGE(SEL$F5BB74E1)OUTLINE(SEL$3)OUTLINE(SEL$F5BB74E1)PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------MERGE(SEL$2)OUTLINE(SEL$1)OUTLINE(SEL$2)FULL(SEL$117FC0EF EMPSEL$2)END_OUTLINE_DATA*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------1 - (#keys2) JOB[VARCHAR2,9], COMM[NUMBER,22], SUM(CASE WHEN(DEPTNO10) THEN SAL END )[22], SUM(CASE WHEN (DEPTNO20) THENSAL END )[22], SUM(CASE WHEN (DEPTNO30) THEN SAL END )[22]2 - JOB[VARCHAR2,9], SAL[NUMBER,22], COMM[NUMBER,22],DEPTNO[NUMBER,22]57 rows selected
通过上面PLAN可以看到,PIVOT被转换成了如下语句
SUM(CASE WHEN(DEPTNO10) THEN SAL END )[22], SUM(CASE WHEN (DEPTNO20) THENSAL END )[22], SUM(CASE WHEN (DEPTNO30) THEN SAL END )[22]也就是说,PIVOT只是写法简单了一些,实际上仍用的是CASE WHEN语句。
二、列转行
测试数据如下
drop table test purge;
create table test as
select *from (select deptno, sal from emp)
pivot(count(*) as ct, sum(sal) as sfor deptno in(10 as d10, 20 as d20, 30 as d30));
SQL select * from test;D10_CT D10_S D20_CT D20_S D30_CT D30_S
---------- ---------- ---------- ---------- ---------- ----------3 8750 5 7875 6 9400SQL 要求把三个部门的人次转为一列显示。以前这种需求一直用UNION ALL来写
SQL SELECT10AS 部门编码,d10_ct AS 人次 FROM test UNION ALL2 SELECT20AS 部门编码,d20_ct AS 人次 FROM test UNION ALL3 SELECT30AS 部门编码,d30_ct AS 人次 FROM test4 ;部门编码 人次
---------- ----------
10 3
20 5
30 6这时PLAN如下 Plan Hash Value : 2176849128 ---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 9 | 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL | TEST | 1 | 13 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST | 1 | 13 | 3 | 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST | 1 | 13 | 3 | 00:00:01 |
---------------------------------------------------------------------Note
-----
- dynamic sampling used for this statement需要扫描test三次,而且如果列数较多,这种查询编写与维护都比较麻烦,而用UNPIVOT就不一样了。
SQL
SQL select * from test unpivot(2 ct for deptno in(d10_ct,d20_ct,d30_ct)3 );D10_S D20_S D30_S DEPTNO CT
---------- ---------- ---------- ------ ----------8750 7875 9400 D10_CT 38750 7875 9400 D20_CT 58750 7875 9400 D30_CT 6SQL UNPIYOT函数生成两个新列“deptno与ct”。
而in()中的D10_CT、D20_CT和D30_CT三列,其列名成为行deptno的值原来D10_CT等列中的值分别转为ct列中的三行 那怎么恢复成原来deptno号的样子?只要将语句整理如下
SQL select deptno AS lm,substr(deptno,-5, 2)AS deptno,ct2 from test unpivot(3 ct for deptno in(d10_ct,d20_ct,d30_ct)4 );LM DEPTNO CT
------ -------- ----------
D10_CT 10 3
D20_CT 20 5
D30_CT 30 6这时PLAN如下Plan Hash Value : 734873962 -----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 63 | 9 | 00:00:01 |
| * 1 | VIEW | | 3 | 63 | 9 | 00:00:01 |
| 2 | UNPIVOT | | | | | |
| 3 | TABLE ACCESS FULL | TEST | 1 | 39 | 3 | 00:00:01 |
-----------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(unpivot_view_006.CT IS NOT NULL)Note
-----
- dynamic sampling used for this statement可以看到,与PIVOT不一样,UNPIVOT不仅语句简略,而且只需要扫描test一次。我们可以很容易地在后面的unpivot列表里维护要转换的列。当然,UNPIVOT同样有限制。 如果现在有这么一个需求还是用前面的表test
SQL select * from test;D10_CT D10_S D20_CT D20_S D30_CT D30_S
---------- ---------- ---------- ---------- ---------- ----------3 8750 5 7875 6 9400我们要求将人次和sal聚合的结果恢复成按照部门统计的行列表也就是下面这样的 DEPTNO RC SM-------- ---------- ----------10 3 875020 5 787530 6 9400那我们该怎么实现 要记得如果同时有人次与工资合计要转换,unpivot就不能一次性完成,只有分别转换后再用JOIN连接。
select *from (select substr(deptno, 1, 3) as lm,substr(deptno, -5, 2) AS deptno,rcfrom (select *from test unpivot include nulls(rc for deptno in(d10_ct, d20_ct, d30_ct)))) ainner join (select substr(deptno, 1, 3) as lm,substr(deptno, -4, 2) AS deptno,smfrom (select *from test unpivot include nulls(sm for deptno in(d10_s,d20_s,d30_s)))) bon a.lm b.lmLM DEPTNO RC LM DEPTNO SM
------------ -------- ---------- ------------ -------- ----------
D10 10 3 D10 10 8750
D20 20 5 D20 20 7875
D30 30 6 D30 30 9400上面的结果只要去掉几个列名就实现了前面的要求。这里为了让两个结果集一致,使用了参数include nulls这样即使数据为空,也显示一行。 是否有办法只用UNPIVOT,而不用JOIN呢看下面的示例
SQL select *2 from test3 unpivot include nulls(rc for deptno in(d10_ct as 10, d20_ct as 20, d30_ct as 30))4 unpivot include nulls(sm for deptno2 in(d10_s as 10, d20_s as 20, d30_s as 30));DEPTNO RC DEPTNO2 SM
---------- ---------- ---------- ----------10 3 10 875010 3 20 787510 3 30 940020 5 10 875020 5 20 787520 5 30 940030 6 10 875030 6 20 787530 6 30 94009 rows selected可以看到,当有两个UNPJVOT时,生成的结果是一个笛卡儿积。 上面的语句实际上就是一个嵌套语句,前一个UNPIVOT结果出来后,再执行另一个
SQL with t as (2 select *3 from test4 unpivot include nulls(rc for deptno in(d10_ct as 10, d20_ct as 20, d30_ct as 30))5 )6 select * from t unpivot include nulls(sm for deptno2 in(d10_s as 10, d20_s as 20, d30_s as 30));DEPTNO RC DEPTNO2 SM
---------- ---------- ---------- ----------10 3 10 875010 3 20 787510 3 30 940020 5 10 875020 5 20 787520 5 30 940030 6 10 875030 6 20 787530 6 30 94009 rows selected回来继续说那上面这样的数据就不能用了吗必须用join吗 不不不既然他笛卡尔积了其实针对需要的数据,在上面的查询上加一个过滤即可。
SQL select *2 from test3 unpivot include nulls(rc for deptno in(d10_ct as 10, d20_ct as 20, d30_ct as 30))4 unpivot include nulls(sm for deptno2 in(d10_s as 10, d20_s as 20, d30_s as 30))5 where deptnodeptno2;DEPTNO RC DEPTNO2 SM
---------- ---------- ---------- ----------10 3 10 875020 5 20 787530 6 30 9400SQL 三、将结果集反向转置为一列
有时会要求数据竖向显示,如CLARK的数据显示如下各行之间用空格隔开
CLARK
MANAGER
2450我们使用刚学到的UNPIVOT,再加一点小技巧就可以。
select emps from (
select ename, job, to_char(sal) as sal, null as t_colfrom empwhere deptno 10)unpivot include nulls(emps for aa in(ename,job,sal,t_col));
EMPS
------
CLARK
MANAGER
2450KING
PRESIDENT
5000sdf
sdf
1300这里要注意以下两点。
与UNION ALL一样,要合并的几列数据类型必须相同,如果sal不用to_char转换,就会报错
SQL
SQL select emps from (2 select ename, job, sal as sal, null as t_col3 from emp4 where deptno 10)5 unpivot include nulls(emps for aa in(ename,job,sal,t_col));
select emps from (
select ename, job, sal as sal, null as t_colfrom empwhere deptno 10)unpivot include nulls(emps for aa in(ename,job,sal,t_col))ORA-01790: expression must have same datatype as corresponding expression如果不加include nu11s,将不会显示空行
select emps from (
select ename, job, to_Char(sal) as sal, null as t_colfrom empwhere deptno 10)unpivot (emps for aa in(ename,job,sal,t_col));
EMPS
------
CLARK
MANAGER
2450
KING
PRESIDENT
5000
sdf
sdf
1300总结
本篇文章讲解的主要内容是目前Oracle支持的行列互换有两种方式case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”“列转行”的需求并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。
