MySQL分组统计及占比分析的方法实现
MySQL分组统计及占比分析的方法实现
·
分组统计及占比分析
题目背景:
统计各性别消费情况(字段包含性别、⼈数、⼈数占⽐、⼈均消费、消费⾦额、消费占⽐) 并以消费
占⽐降序。
这个题目咋一看,觉得非常简单,不就是一个分组就OK了吗,分组是没问题的,但分组之后呢,你能正常在分组的情况下,再获取整个数据集的信息吗,显然在一次查询中是没法完成整个任务的,只有真正在做的时候,才发现不一般,如果以前没接触过的,可能半天也找不出方法来解决。
如下提供两个方法来解决这个问题。
1)方法一:采用我们前面讲述的笛卡尔集。
我们最终的目标是要在查询中获取男生的人数以及整体的人数。显然在分组的时候,是没法再查询到整体数据集的,故无论如何都要套一层子查询
先不讲这个题目的源码,我们先讲一个简单的例子来由浅入深的描述这个问题。假设现在又两个表,分别为
create table t1(c1 varchar(10));
insert into t1 values('1');
insert into t1 values('2');
insert into t1 values('3');
create table t2(c2 varchar(10));
insert into t2 values('10');
好,我们先看看数据集
笛卡尔集1的构建方式:
SELECT t1.c1, t2.c2,t1.c1/t2.c2 as rate
FROM t1,t2
笛卡尔集2的构建方式join,这也是我前期在查询网络的时候,一直在描述on 1=1 的情况,看的我也是很懵。我们运行后,看看结果是什么样。
SELECT t1.c1, t2.c2,t1.c1/t2.c2 as rate
FROM t1 join t2 on 1=1 ;
懂了吧,我们笛卡尔集一个表,这个表仅含一个数据,使用笛卡尔集。
好了,我们在根据这个思想进行整理。
SELECT c.gender,c.id_num,c.GMV,c.avg_sale,c.id_num/b.total_id
from
((SELECT gender,
count(DISTINCT user_id) as id_num ,
sum(purchase) GMV,
sum(purchase)/count(DISTINCT user_id ) as avg_sale
from lagou_lxy.model2_datas GROUP BY gender) c join (SELECT count(DISTINCT user_id) as total_id from model2_datas ) b on 1=1)
2)方法二,使用开窗的窗口函数sum()over ( ),这个方法是极力推荐的一种方式。
--第二层使用sum的开窗函数,一并计算出人数的和以及销售额的和后,在计算占比
select
sub.gender,id_num/sum(sub.id_num)over() AS id_rate,s
GMV,GMV/sum(GMV)OVER() as GMV_rate,avg_sale
from
--第一层先计算出性别、人数、销售额、平均销售额
(SELECT gender,
count(DISTINCT user_id) as id_num ,
sum(purchase) GMV,
sum(purchase)/count(DISTINCT user_id ) as avg_sale
from lagou_lxy.model2_datas GROUP BY gender) sub
order by
GMV_rate desc ;
还有跟多的SQL问题,烦请关注数据分析专栏(专栏包含了MySQL的理论讲解,项目实操落地,SQL中重点难点题目分享知识外,还包含数据分析中可视化,数据挖掘等)
更多推荐
已为社区贡献1条内容
所有评论(0)