Oracle Database 11g SQL开发指南学习笔记：高级查询(六)
select 300 as v from dual
)
--3.窗口函数
--下面用的是sum，但avg、count、max、min等函数都适用
select year,
month,
sum(amount) as amount,
--计算累积和
sum(sum(amount)) over(order by month
rows between unbounded preceding and current row) as cumulative_amount,
--计算移动累积和,本月与前3个月销量和
sum(sum(amount)) over(order by month
rows between 3 preceding and current row) as moving_amout,
--计算中心累积和，也就是本月、前一个月、后一个月的销量总和
sum(sum(amount)) over(order by month
rows between 1 preceding and 1 following) as moving_center_amount,
--获取窗口的第一条记录
first_value(sum(amount)) over(order by month
rows between 1 preceding and 1 following) as first_value_amount,
--获取窗口的最后一条记录
last_value(sum(amount)) over(order by month
rows between 1 preceding and 1 following) as last_value_amount,
--当前记录的向前第1条记录，如果前面没有记录，那么返回null
lag(sum(amount),1) over(order by month) as lag_amount,
--当前记录的下一条记录，如果后面没有记录，返回null
lead(sum(amount),1) over(order by month) as lead_amount,
--对null值的不同处理,默认是respect nulls，表示把null正常处理
lag(sum(amount),1) respect nulls over(order by month) as respect_nulls,
--ignore nulls表示忽略null,比如要找前一条记录，如果前一条记录是null,那么会跳过这条记录再往前找一条
lag(sum(amount),1) ignore nulls over(order by month) as ignore_nulls
from t
group by year,month
order by year,month;
--4.报表函数
--下面用到了sum，也适合avg,max,min,count
select month,
type_name,
sum(amount) as amout,
--对group by之后的结果，再次对结果进行group by month求sum
sum(sum(amount)) over(partition by month) as month_amount,
--对group by之后的结果，再次对结果进行group b
y type_name求sum
sum(sum(amount)) over(partition by type_name) as type_name_amount,
--计算某个月某个品类的amount/某个月不分品类的amount
ratio_to_report(sum(amount)) over(partition by month) as ratio_to_report
from t
group by month,type_name
order by month,type_name;
--5.first函数、last函数
--适用于min、max、count、sum、avg
select
--按照amount的和排序，求dense_rank，取排第1的，然后求最小的month
min(month) keep (dense_rank first order by sum(amount)) as first_amount,
max(month) keep (dense_rank last order by sum(amount)) as last_amount
from t
group by month
order by month;
6、model子句、povit与unpovit
[sql]
--1.位置标记访问
select *
from t
model
partition by (type_name)
dimension by (month,year)
measures (amount sales_amount)
(
sales_amount[1,2014] = sales_amount[1,2013],
sales_amount[2,2014] = sales_amount[2,2013] + sales_amount[3,2013],
sales_amount[3,2014] = round(sales_amount[3,2013] * 1.5, 2)
)
order by type_name,year,month;
--2.符号标记访问
--注意位置必须要对齐，
select type_name,year,month,sales_amount
from t
model
partition by (type_name)
dimension by (month,year)
measures (amount sales_amount)
(
sales_amount[month = 1,year = 2014] = sales_amount[month = 1,year = 2014],
sales_amount[month = 1,year = 2014] = sales_amount[month = 1,year = 2014],
sales_amount[month = 1,year = 2014] = round(sales_amount[month = 1,year = 2014] * 1.5,2)
)
order by type_name,year,month;
--3.1 between and
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)[month between 1 and 3,2013]
)
order by type_n