privot子句
1、pivot子句是Oracle database 11g的新增特性,
2、可以在查询输出中将行旋转为列,同时对数据使用聚合函数。
3、同时新增了unpivot子句,他可以在查询输出中将列旋转为行;
例如:
我们sql会写为1、
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;
但是这种显示结果也许并不清晰,想想如果把月份横着排列是不是更清晰,若用一般方法我们可以这样写:
方式2:
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函数的可以用一下方法
方式3:
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 APR
from all_sales
where 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(不一定非得叫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;
值得注意的是系统会自动将两个别名合为一个;
具体拆解例子:
ps:
SELECT * FROM (
SELECT APPLE_MIN, APPLE_TOTAL
FROM t_demo_unpivot
) ①
PIVOT (
SUM(APPLE_MIN)苹果最少
FOR
APPLE_TOTAL IN (7000,2000,3000,4000))
②
解释
1、①是一个子句得到的是一行数据,且select出来的字段必须包含要行转列的字段
例如:
将①中指定范围内的APPLE_TOTAL 汇总然后pivot
APPLE_MIN(要汇总的数据字段),
APPLE_TOTAL(要行转列的数据范围)
2、需要注意pivot(必须有聚合函数字段等,
3、for后面是数据范围
4、pviot中的字段必须在①中出现
… …
unpivot子句
unpivot子句的作用是将列转换为行。
现有表格数据如下:
select * from pivot_sales_data
unpivot(
amount for month in (jan, feb, mar, apr)
)
order by prd_type_id;
具体拆解例子:
ps:
select *,type from t_demo_unpivot unpivot(aa for TYPE in (APPLE_TOTAL,APPLE_MIN))
解释
1、select 的type是将要列转行得到的新字段并对应for后面的type,
2、in(xx,xx,xx),是将in()中的字段列转行并赋值给type字段,注意in中字段必须真实存在,且in中有多少字段,最后select就会得到多少列;
3、aa,type,TYPE这三个字段的自定义命名,但是建议见名知意
4、*代表了所有字段,且此处不能select * ,type,只能保留一个字段,若是select *,则结果的时候,只有列转行的字段值不一样,其他字段无论多少列均为一模一样的,需要注意