over()函数不能单独使用,要和分析函数或聚集函数:rank(),dense_rank(),row_number(),sum()等一起使用。
语法格式:over(partition by columnname1 order by columnname2)
意思是:按columnname1字段进行分组,columnname2字段进行排序
现通过sum() over()实现累加功能:
先看一普通的分组统计,执行sql语句:select to_char(t.gettime,'YYYY-MM-DD') as gettime,count(*) as cnum,sum(t.prize) as prize from h_record t where 1=1 group by to_char(t.gettime,'YYYY-MM-DD') order by 1
执行后结果如下图所示(统计每天的抽奖人数与中奖金额):
通过sum() over()函数实现对上述结果的累加,即2014年9月9日的cnum为2014年9月9日以前所有cnum的累加,prize同理
执行上sql语句:select m.gettime,sum(m.cnum) over(order by m.gettime) cnum,sum(m.prize) over(order by m.gettime) prize from (
select to_char(t.gettime,'YYYY-MM-DD') as gettime,count(*) as cnum,sum(t.prize) as prize from h_record t
where 1=1 group by to_char(t.gettime,'YYYY-MM-DD') order by 1
)m order by m.gettime
执行后结果如下图所示:
可以看到每行的cnum和prize两列都为gettime列值小于等于本列gettime列值的所有值的累加。