笔记：Oracle SQL 高级查询简介 (3) MODEL子句，PIVOT与UNPIVOT子句

来源：互联网发布：泰勒斯威夫特知乎编辑：程序博客网时间：2024/04/30 10:43

一、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 = 987model  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_IDYEARMONTH_IDAMOUNT_SOLD1131998011232.162131998031232.993132008011232.164132008032465.155132008046141998041159.997142008018142008039142008042319.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 = 987model  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 = 987model  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 = 987model  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_IDYEARMONTH_IDAMOUNT_SOLD1131998011232.162131998031232.993132008011232.5754141998041159.995142008011159.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 = 987model  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_IDYEARMONTH_IDAMOUNT_SOLD1131998011232.162131998031232.993132008011232.164141998041159.99514200801

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 = 987model  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 = 987model  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 = 987model  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 = 987model  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 = 987model 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 = 987model    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_IDJANFEBMAR113125575.64122325.2161649.5214239773.24278879.97221083.56315165643.35160732.422307.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_141125575.64122325.2161649.5  239773.24        278879.97221083.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_AMTJAN_13_AVG_AMTFEB_13_TOTL_AMTFEB_13_AVG_AMTMAR_13_TOTL_AMTMAR_13_AVG_AMTJAN_14_TOTL_AMTJAN_14_AVG_AMTFEB_14_TOTL_AMTFEB_14_AVG_AMTMAR_14_TOTL_AMTMAR_14_AVG_AMT1125575.641231.1337254902122325.211235.6081818181861649.51232.99239773.241217.12304568528278879.971212.52160869565221083.561214.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_IDJAN        FEB        MAR113125575.64122325.2161649.5214239773.24278879.97221083.56315165643.35160732.422307.78

列转回行：

select  * from pivot_sales_dataunpivot(   amount_sold   for (month_id) in  (JAN ,FEB, MAR ))

结果：

   PROD_IDMONTH_IDAMOUNT_SOLD113JAN125575.64213FEB122325.21313MAR61649.5414JAN239773.24514FEB278879.97614MAR221083.56715JAN165643.35815FEB160732.4915MAR22307.78

0 0