Oracle 11g学习笔记--privot和unpivot子句
来源:互联网 发布:mysql 时间转时间戳 编辑:程序博客网 时间:2024/05/18 01:02
Oracle 11g学习笔记–privot和unpivot子句
pivot子句是Oracle database 11g的新增特性,可以在查询输出中将行旋转为列,同时对数据使用聚合函数。同时新增了unpivot子句,他可以在查询输出中将列旋转为行;
引入
现在有以下表数据:
(未显示完全。。。)
现在我们想观察(1, 2, 3)每种类型id在前四月的销售情况;
你可能会这么写sql:
select prd_type_id, month, sum(amount) from all_sales where month <= 4 and prd_type_id in(1, 2, 3) group by month,prd_type_id order by prd_type_id;
但是这种显示结果也许并不清晰,想想如果把月份横着排列是不是更清晰,若用一般方法我们可以这样写:
select prd_type_id, sum(decode(month, 1, amount, 0)) as JAN, sum(decode(month, 2, amount, 0)) as FEB, sum(decode(month, 3, amount, 0)) as MAR, sum(decode(month, 4, amount, 0)) as APR from all_sales where prd_type_id in(1, 2, 3) group by prd_type_id;--不熟悉decode函数的可以用一下方法select prd_type_id, sum(case when month = 1 then amount else 0 end) as JAN, sum(case when month = 2 then amount else 0 end) as FEB, sum(case when month = 3 then amount else 0 end) as MAR, sum(case when month = 4 then amount else 0 end) as APRfrom all_saleswhere prd_type_id in (1, 2, 3)group by prd_type_id;
这样显示是不是更好呢?但是现在oracle 11g为我们提供了更好的方法, 那就是pivot,他可以将行变成列:
select *from (-- ① select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1, 2, 3))pivot(-- ② sum(amount) for month in (1 as JAN, 2 as FEB, 3 as MAR, 4 as APR))order by prd_type_id;
执行结果同上图;
接下来,我将讲讲这些代码的含义(只是个人理解):
对于①我的理解是,这部分限定了数据的范围,这个范围怎么确定呢,首先你得知道你想显示什么。在本文中,你需要显示prd_type_id, month, amount三种数据,所以就可以确定了;
对于②我的理解是,这部分规定了如何将行变成列;对于sum(amount)的含义,我们先放放,先说说这个month,他就规定了把什么变成从行变成列了,那么in后面的部分,则规定了,哪些month需要变成列,而as规定了别名,可以省略。那么对于sum(amount)怎么理解呢,首先他必须是聚合操作,他就是要在每一个month列下要显示的数据,拿一月来说,他下面的数据肯定不止一条,这也是为什么要使用聚合函数的原因了, 他到底对得是哪些amount的求和?我们知道在本题中,还有些列并未转成行,那么这些列同上面的月份便共同规定了对哪些amount求和了,如一月与id=1对应的那个格子,就是所有满足月份为一月,而类型id为1的数据了;
转换多个列
我们都知道for后面规定了哪些行将被转换为列,而in里面规定了要将哪些特定的行转换,有了这些,我们便不能理解下面的sql:
select *from ( select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1,2,3))pivot ( sum(amount) for(month, prd_type_id) in( (1, 1) as jan_prd_type_1, (2, 2) as feb_prd_type_2, (3, 3) as mar_prd_type_3, (4, 2) as apr_prd_type_2 ));
在转换中使用多个聚合函数
select * from ( select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1, 2, 3) ) pivot ( sum(amount) as sum_amount, avg(amount) as avg_amount for(month) in( 1 as JAN, 2 as FEB ) )order by prd_type_id;
值得注意的是系统会自动将两个别名合为一个;
unpivot子句
unpivot子句的作用是将列转换为行。
现有表格数据如下:
select * from pivot_sales_data unpivot( amount for month in (jan, feb, mar, apr))order by prd_type_id;
- Oracle 11g学习笔记--privot和unpivot子句
- Oracle 11g学习笔记--model子句
- oracle 11g Pivot 和 Unpivot
- Oracle 11g新特性之Pivot 和 Unpivot简介
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g行列互换pivot和unpivot说明
- Oracle 11g行列互换pivot和unpivot说明
- 【转】Oracle 11g行列互换pivot和unpivot
- [面试] Oracle 11g 行列互换 pivot 和 unpivot
- oracle 11g 新特性Pivot 和 Unpivot
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- python(第二版)第三章答案
- gdb 调试
- 时间复杂度的相关证明
- 数值的整数次方
- HDU-1114-Piggy-Bank(变态完全背包)
- Oracle 11g学习笔记--privot和unpivot子句
- view.setTextColor的小坑
- ubuntu 16.04 显示
- mysql ifnull函数使用详解
- GD绘图之设置文本背景色
- GDI之透明画刷(不适用背景)
- Python-模拟鼠标键盘动作
- SQL子查询的一些例子
- C++求pi