笔记：Oracle SQL 高级查询简介 (3) MODEL子句，PIVOT与UNPIVOT子句
一、MODEL 子句
1、实例
model 子句可进行行间计算。
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold['01','2008'] = amount_sold['01','1998'],
amount_sold['03','2008'] = amount_sold['01','1998'] + amount_sold['03','1998'],
amount_sold['04','2008'] = amount_sold['04','1998'] *2
)
order by prod_id, year, month_id;
结果：
PROD_ID YEAR MONTH_ID AMOUNT_SOLD 1 13 1998 01 1232.16 2 13 1998 03 1232.99 3 13 2008 01 1232.16 4 13 2008 03 2465.15 5 13 2008 04 6 14 1998 04 1159.99 7 14 2008 01 8 14 2008 03 9 14 2008 04 2319.98
2、位置标记和符号标记访问数据单元
在以上的SQL中保留位置和符号标记
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold[month_id='01',year='2008'] = amount_sold[month_id='01',year='1998'],
amount_sold[month_id='03',year='2008'] = amount_sold[month_id='01',year='1998'] + amount_sold[month_id='03',year='1998'],
amount_sold[month_id='04',year='2008'] = amount_sold[month_id='04',year='1998'] *2
)
order by prod_id, year, month_id;
结果与实例中一样
3、between and
用在measure中的聚合计算中。
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold[month_id='01',year='2008'] =
avg(amount_sold)[month_id between 1 and 3,'1998']
)
order by prod_id, year, month_id;
4、any、is any
any 与位置标记合用，is any 与符号标记合用。
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold['01','2008'] =
avg(amount_sold)[any,year is any]
)
order by prod_id, year, month_id;
结果：
PROD_ID YEAR MONTH_ID AMOUNT_SOLD 1 13 1998 01 1232.16 2 13 1998 03 1232.99 3 13 2008 01 1232.575 4 14 1998 04 1159.99 5 14 2008 01 1159.99
5、currentv() 获取某个维度的当前值
譬如，将2008年第一个月设为1998年同月销售的2倍：
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold['01','2008'] = amount_sold[currentv(),'1998']
)
order by prod_id, year, month_id;
结果：
PROD_ID YEAR MONTH_ID AMOUNT_SOLD 1 13 1998 01 1232.16 2 13 1998 03 1232.99 3 13 2008 01 1232.16 4 14 1998 04 1159.99 5 14 2008 01
6、for 循环
语法如下：for month_id from 1 to 3 increment 1
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold[for month_id from 1 to 3 increment 1,'2008']
= amount_sold[currentv(),'1998']
)
order by prod_id, year, month_id;
7、处理空值、缺失值
用 is present， 与case when 连用：
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold[for month_id from 1 to 3 increment 1,'2008']
= case when amount_sold[currentv(),'1998'] is present then
amount_sold[currentv(),'1998']*2
else
0
end
)
order by prod_id, year, month_id;
用presentv，相当于is present + case when
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold[for month_id from 1 to 3 increment 1,'2008']
= presentv( amount_sold[currentv(),'1998'], amount_sold[currentv(),'1998']*2, 0 )
)
order by prod_id, year, month_id;
用presentnnv， 存在且不为空。
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold[for month_id from 1 to 3 increment 1,'2008']
= presentnnv( amount_sold[currentv(),'1998'], amount_sold[currentv(),'1998']*2, 0 )
)
order by prod_id, year, month_id;
用ignore nav (忽略空) 、 keep nav (保留空)
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model ignore nav
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)(
amount_sold[for month_id from 1 to 3 increment 1,'2008']
= amount_sold[currentv(),'1998']*2
)
order by prod_id, year, month_id;
8、更新已有单元
在前面的例子中，单元不存在则创建，存在更新
使用 result update，指定只更新不创建
select prod_id, year, month_id, amount_sold
from all_sales s where prod_id in (13,14) and s.cust_id = 987
model
partition by (prod_id)
dimension by (month_id, year)
measures ( amount_sold)
rules update (
amount_sold[for month_id from 1 to 3 increment 1,'2008']
= amount_sold[currentv(),'1998']*2
)
order by prod_id, year, month_id;
二、PIVOT与UNPIVOT子句
1、PIVOT 行转列
select *
from (
select s.month_id , s.prod_id, s.amount_sold
from all_sales s where s.year=1998 and s.prod_id in (13,14,15)
)
pivot (
sum(amount_sold) for month_id in ('01' as JAN, '02' as FEB, '03' as MAR)
)
order by prod_id;
结果：
PROD_ID JAN FEB MAR 1 13 125575.64 122325.21 61649.5 2 14 239773.24 278879.97 221083.56 3 15 165643.35 160732.4 22307.78
2、PIVOT FOR 转换多列
select *
from (
select s.month_id , s.prod_id, s.amount_sold
from all_sales s where s.year=1998 and s.prod_id in (13,14,15)
)
pivot (
sum(amount_sold) for (month_id,prod_id) in
(('01',13) as JAN_13,
('02',13) as FEB_13,
('03',13) as MAR_13,
('01',14) as JAN_14,
('02',14) as FEB_14,
('03',14) as MAR_14)
)
结果：
JAN_13 FEB_13 MAR_13 JAN_14 FEB_14 MAR_14 1 125575.64 122325.21 61649.5 239773.24 278879.97 221083.56
3、转换中使用多个聚合函数
select *
from (
select s.month_id , s.prod_id, s.amount_sold
from all_sales s where s.year=1998 and s.prod_id in (13,14,15)
)
pivot (
sum(amount_sold) as totl_amt,
avg(amount_sold) as avg_amt
for (month_id,prod_id) in
(('01',13) as JAN_13,
('02',13) as FEB_13,
('03',13) as MAR_13,
('01',14) as JAN_14,
('02',14) as FEB_14,
('03',14) as MAR_14)
)
结果：
JAN_13_TOTL_AMT JAN_13_AVG_AMT FEB_13_TOTL_AMT FEB_13_AVG_AMT MAR_13_TOTL_AMT MAR_13_AVG_AMT JAN_14_TOTL_AMT JAN_14_AVG_AMT FEB_14_TOTL_AMT FEB_14_AVG_AMT MAR_14_TOTL_AMT MAR_14_AVG_AMT 1 125575.64 1231.1337254902 122325.21 1235.60818181818 61649.5 1232.99 239773.24 1217.12304568528 278879.97 1212.52160869565 221083.56 1214.74483516483
4、UNPIVOT 列转行
建立一个行转列的表
drop table pivot_sales_data;
create table pivot_sales_data as select *
from (
select s.month_id , s.prod_id, s.amount_sold
from all_sales s where s.year=1998 and s.prod_id in (13,14,15)
)
pivot (
sum(amount_sold)
for (month_id) in
(('01') as JAN,
('02') as FEB,
('03') as MAR)
)
结果：
PROD_ID JAN FEB MAR 1 13 125575.64 122325.21 61649.5 2 14 239773.24 278879.97 221083.56 3 15 165643.35 160732.4 22307.78
列转回行：
select * from pivot_sales_data unpivot( amount_sold for (month_id) in (JAN ,FEB, MAR ) )结果：
PROD_ID MONTH_ID AMOUNT_SOLD 1 13 JAN 125575.64 2 13 FEB 122325.21 3 13 MAR 61649.5 4 14 JAN 239773.24 5 14 FEB 278879.97 6 14 MAR 221083.56 7 15 JAN 165643.35 8 15 FEB 160732.4 9 15 MAR 22307.78
