sql分组求和、求平均
1·
·
在sql里求就不用去excel里求了,在excel还需要透视或者使用公式就比较麻烦。
直接在sql写组内平均、求和也是通过窗口函数实现,over里面的partition by实现分组条件,外面用聚合函数得出需要的值。注意:order by cnt rows between unbounded preceding and unbounded following的含义是:对分组(根据省份分组)后的数据cnt进行升序,并取所有行。
比如我们要求湖北、江西、湖南三个省的0401-04-0403三日均、三日和、每日占比等数据:
select
dt,
pro_name,
cnt,
---分组求平均
avg(cnt) over(partition by pro_name) as avg_cnt,
---分组求和
sum(cnt) over(partition by pro_name) as sum_cnt,
---组内求比例
round(cnt/sum(cnt) over partition by pro_name,4) as rate
from
(
select
dt,
pro_name,
count(distinct uid) as cnt
from
table_1
where
dt >= '2021-04-01'
and dt <= '2021-04-03'
and pro_name in ('湖北省', '江西省', '湖南省')
group by
dt,
pro_name
) t1
order by
pro_name,
dt;
select
dt,
pro_name,
cnt,
---分组求平均
avg(cnt) over(
partition by pro_name
order by
cnt rows between unbounded preceding
and unbounded following
) as avg_cnt,
---分组求和
sum(cnt) over(
partition by pro_name
order by
cnt rows between unbounded preceding
and unbounded following
) as sum_cnt,
---组内求比例
round(
cnt / sum(cnt) over(
partition by pro_name
order by
cnt rows between unbounded preceding
and unbounded following
),
4
) as rate
from
(
select
dt,
pro_name,
count(distinct uid) as cnt
from
table_1
where
dt >= '2021-04-01'
and dt <= '2021-04-03'
and pro_name in ('湖北省', '江西省', '湖南省')
group by
dt,
pro_name
) t1
order by
pro_name,
dt;
结果:396+427+295=1118; (396+427+295)/3=372.67
更多推荐
已为社区贡献1条内容
所有评论(0)