Oracle Database 11g SQL开发指南学习笔记：高级查询(三)
ame,year,month;
--3.2通过any和is any,表示任何维度的数据
select *
from t
model
partition by (type_name)
dimension by (month,year)
measures (amount sales_amount)
(
--这里不太清楚为什么如果采用标记访问，会导致不会产生新的1,2014的记录
--sales_amount[month =1,year =2014] = sum(sales_amount)[month between 1 and 3,2013]
--必须要用sum
sales_amount[1,2014] = sum(sales_amount)[any,year is any]
)
order by type_name,year,month;
--3.3 for循环
--currentv()函数来访问当前的维度
--is present检查单元格是否存在
--is not null检查是否是null值
select *
from t
model
partition by (type_name)
dimension by (month,year)
measures (amount sales_amount)
(
sales_amount[for month from 10 to 12 increment 1,2014] =
case when sales_amount[currentv(),2013] is present and
sales_amount[currentv(),2013] is not null
then sales_amount[currentv(),2013]
else 0
end
)
order by type_name,year,month;
--3.3 ignore nav和keep nav
select *
from t
model ignore nav --忽略null，自动返回0
partition by (type_name)
dimension by (month,year)
measures (amount sales_amount)
(
sales_amount[for month from 10 to 12 increment 1,2014] =
case when sales_amount[currentv(),2013] is present
then sales_amount[currentv(),2013]
else 0
end
)
order by type_name,year,month;
--3.4 rules update更新已经存在的值，如果不存在，不会创建，也就是只会更新
select *
from t
model ignore nav --忽略null，自动返回0
partition by (type_name)
dimension by (month,year)
measures (amount sales_amount)
rules update --返回结果集中，不会包含2014年的数据
(
sales_amount[for month from 10 to 12 increment 1,2014] =
case when sales_amount[currentv(),2013] is present
then sales_amount[currentv(),2013]
else 0
end
)
order by type_name,year,month;
--4.pivot
--4.1 单列转置
select *
from
(
select type_name,
year,
month,
amount
from t
)
pivot
(
--虽然有12个月的数据，但这里只需要1-4月份的数据
sum(amount) for month in (1 as jan,2 as feb,3 as mar,4 apr)
)
order by type_name,year;
--4.2 多列转置
select *
from
(
select type_name,year,month,amount
from t
)
pivot
(
sum(amount) for (month,type_name) in ( (1,'Book') as jan_book,
(2,'Book') as feb_book,
(3,'Book') as mar_book
)
);
--5. unpivot
SELECT *
FROM
(
select *
from
(
select type_name,
year,
month,
amount
from t
)
pivot
(
--虽然有12个月的数据，但这里只需要1-4月份的数据
sum(amount) for month in (1 as jan,2 as feb,3 as mar,4 apr)
)
)
unpivot
(
amount for month in (JAN as 1,FEB as 2,MAR as 3,APR as 4)
)