电商运营是如何用 SQL 实现数据分析的？

时间：2023-03-16 00:44:01 | 来源：电子商务

时间：2023-03-16 00:44:01 来源：电子商务

此时你是一个电商公司的数据分析师，或者是运营，此时在你面前有个疑问：核心粉丝，一般粉丝，路人，不同核心层的用户他们的访问次数分别是多少？

你得到了你们公司的拼多多店铺的基本数据

此时的你，会如何⽤数据分析⼯具SQL写出分析结果。

逻辑树分析⽅法

遇到复杂问题，怎么办？可以⽤11⼤数据分析⽅法⾥的“逻辑树分析⽅法”，把复杂问题拆解为简答

问题

我们可以⽤把这个复杂的问题拆解为3个⼦问题：

1）找出访问次数前20%的⽤⼾

2）剔除访问次数前20%的⽤⼾

3）每类⽤⼾的平均访问次数

只要分别解决了每个⼦问题，那么就解决了该业务问题。

1.访问次数前20%的⽤⼾

先按“访问次数”排名，然后就可以找到”前20%”的数据。排名问题可以⽤SQL的⾼级功能窗⼝函

数来解决。

⾸先对所有⽤⼾的访问量按从低到⾼的顺序⽤窗⼝函数排名：

select *, row_number() over(order by 访问量 desc) as 排名

from ⽤⼾访问次数表;

排名后，如何找出前20%的数据呢？

排名<=最⼤的排名值 * 20%,就是前20%的数据。

把前⾯的排名结果表当作临时表a，加上筛选条件（where）对应的sql语句如下：

select * from a where 排名<= 最⼤的排名 * 0.2;

最⼤的排名值如何得到呢？可以⽤下⾯的sql语句：

select max(排名) from a;

把前⾯的sql语句组合到⼀起就得到了筛选出排名前20%的数据了：

select * from a where 排名<= (select max(排名) from a) * 0.2;

2.剔除访问次数前20%的⽤⼾

题⽬要求是“剔除访问次数前20%的⽤⼾”，也就是把上⾯sql语句⾥的where条件中的 <= 变成 >就获

取到相反的数据了。

select * from a where 排名 > (select max(排名) from a) * 0.2;

把前⾯得到的临时表a的sql语句带⼊后就是：

select * from

(select *, row_number() over(order by 访问量 desc) as 排名 from ⽤⼾访问次数表) as a

where 排名 > (select max(排名) from (select *, row_number() over(order by 访问量 desc) as 排名

from ⽤⼾访问次数表) as a) * 0.2;

3.每类⽤⼾的平均访问次数

当“每个”出现的时候，就要想这时候就是要分组汇总了。

按“⽤⼾类型”分组(group by)，然后汇总求平均访问次数avg(访问次数）。

select ⽤⼾类型,avg(访问量) from b group by ⽤⼾类型;

这⾥的表b就是前⾯第2步得到的临时表，带⼊sql⾥就是：

select ⽤⼾类型,avg(访问量)

from

(select * from

(select *, row_number() over(order by 访问量 desc) as 排名 from ⽤⼾访问次数表) as a

where 排名 > (select max(排名) from (select row_number() over(order by 访问量 desc) as 排名

from ⽤⼾访问次数表) as a) * 0.2) as b

group by ⽤⼾类型;

【案例复盘】

1.⾯对复杂问题，要会使⽤逻辑树分析⽅法将复杂问题拆解成简单问题

2.排名问题，要想到使⽤SQL⾼级功能窗⼝函数来实现。

3. 当有“每个”出现的时候，要想到⽤分组汇总，下图是常⽤的汇总函数

Version:0.9 StartHTML:0000000105 EndHTML:0000003245 StartFragment:0000000141 EndFragment:0000003205

3.通过这个案例，我们可以总结出万能模板：

选出前百分之N的问题如何解决？下⾯是这类问题的万能模板

1）先使⽤窗⼝函数对数据排名得到临时表a

select *, row_number() over(order by 排名的列 desc) as 排名 from 表名;

2）然后⽤表a筛选出前百分之N的数据

select * from a where 排名 <= (select max(排名) from a) * 百分之N;

3）如果是剔除前前百分之N的数据，也就是选出后(1-百分之N)的数据。例如选出后80%的数据，就把

上⾯的where⼦句⾥的 <= 修改成 >

select * from a where 排名 > (select max(排名) from a) * 百分之N;

最后，如果你想更系统地学习数据分析，依然推荐本站官方开设的「数据分析实战训练营」，这个课为期3天，直播讲解+学习社群的形式，对小白友好。

可以短时间掌握职场上常用的工具操作、分析技巧方法、和数据思维都能讲明白，连我这种老鸟都觉得很受用，而且只要 1 毛钱，感兴趣的朋友可以试试哦，链接我放这啦。