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'