【gp数据库】统计常用窗口函数详解

1.按照网别计算近三月累计/平均缴费人数

select net_type 网别
,pay_date_first_zy 缴费时间
,count(1) 人数 
,sum(count(1)) over(PARTITION BY net_type ORDER BY pay_date_first_zy 
										ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 近三月累计缴费人数
,round(avg(count(1)) over(PARTITION BY net_type ORDER BY pay_date_first_zy   
										ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),1) 近三月平均缴费人数
FROM anrpt.rpt_label_info
GROUP BY net_type,pay_date_first_zy
ORDER BY net_type,pay_date_first_zy

结果如下：
可见当观察账期为201910时，201910没有上月值，所以用201910人数+201911人数=累计缴费人数,3+1 = 4。
当观察账期为201911时，用201910+201911+201911人数=累计缴费人数，3+1+3 = 7。

2.流量溢出情况人数占比

select gprs_more_type 流量溢出频率
,row_number() over(order by count(1) DESC) 人数排名 
,count(1) 人数
,sum(count(1)) over(ORDER BY count(1) ROWS BETWEEN unbounded preceding AND current row) sum1 -- 指第一行至当前行的汇总
,sum(count(1)) over(ORDER BY count(1) ROWS BETWEEN current row AND unbounded following) sum2 -- 指当前行到最后一行的汇总
,sum(count(1)) over(ORDER BY count(1) ROWS BETWEEN 1 preceding AND current row) sum3 -- 指当前行的上一行(rownum-1)到当前行的汇总
,sum(count(1)) over(ORDER BY count(1) ROWS BETWEEN 1 preceding AND 1 following) sum4 -- 指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
,sum(count(1)) over(order by count(1) rows between unbounded preceding and unbounded following) sum5 -- 每行对应的数据窗口是从第一行到最后一行
FROM anrpt.rpt_label_info
GROUP BY gprs_more_type 
ORDER BY 人数排名 desc

结果如下：
求人数占比直接用人数/sum5就可以了。
这里列出了多种分组求和方式，当然如果维度多个可以在over里面加PARTITION BY，除了求和也可以用max、min等其他聚合函数。

3. 标准差

select STDDEV_POP(value_updown) -- 总体标准差
,STDDEV_SAMP(value_updown) -- 样本标准差
from st_tactics_pathway_score_total a
where acct_month = '202306'

上一篇：【gp数据库】你可能不知道却超级实用的函数