SQL之互联网商业实战

1.漏斗分析

漏斗分析是用于分析用户从起始环节到终点环节，过程当中每一步环节的转化（或者流失）情况的分析。

该模型广泛应用于网站用户行为分析和APP用户行为分析的流量监控、产品目标转化等日常数据运营与数据分析工作中。

举例说明：

假如有100人访问某电商网站，有30人点击注册，有10人注册成功。

这个过程共有三步：

第一步到第二步的转化率为30%，流失率为70%；

第二步到第三步转化率为33%，流失率67%；

整个过程的转化率为10%，流失率为90%。 该模型就是经典的漏斗分析模型。

2.拆借需求

根据用户的访问购买路径，拆解出需要的数据如下：

  • UV网页访客数—cart（加入购物车数量）-buy（加购后购买的数量）

  • UV网页访客数—fav（加入收藏数量）-buy（收藏后购买数量）

3.理解需求

可以使用用户行为表shop_behaviors中的数据来计算。

在用户行为表中beh_type标记为行为类型，行为类型有4种。

4.清洗数据

在计算之前需要清洗数据，也就是查看数据中是否有缺失值。

计算方法：通过计数查看全部列是否有缺失值。如果每一列的计算结果返回一样，说明没有缺失值。

使用COUNT()函数对shop_behaviors中的每一列进行计数，并分别取别名为id,user_id,item_id,item_cat,beh_type,date。

代码实现：

1
2
SELECT COUNT(id) AS id,COUNT(user_id) AS user_id,COUNT(item_id) AS item_id,COUNT(item_cat) AS item_cat,COUNT(beh_type) AS beh_type,COUNT(date) AS date
FROM shop_behaviors;

对表中的每一列进行计数，都是100093，没有缺失值。

如果出现缺失值，可以通过删除、Python语言 (pandas库) 中的fillna()函数实现NULL值填充。

5.构建漏斗模型

需要统计：
1.各流程的具体数据
UV访问量—cart（加入购物车数量）-buy（加购后购买的数量）
UV访问量—fav（加入收藏数量）-buy（收藏后购买数量）
2.计算各流程之间的漏斗，并展示

6.计算UV

需要显示的内容：uv访问量

统计用户行为表中的独立访问用户人数。

因为每个用户当天若多次访问只记录一次，所以对用户需要去重处理。

代码实现：

1
2
3
SELECT COUNT(DISTINCT user_id) AS uv
FROM shop_behaviors
WHERE beh_type = 'pv';

7.浏览-加购物车-购买

接下来要计算：加入购物车的用户数，以及加入购物车之后再购买的用户数。

为了得到更准确的数据，需要限定，用户id，商品编号，种类编号是一致的，只有时间先后不一致。

实现方法：

可以查询加入购物车的数据作为表a，左联结，查询购买下单的信息表b

条件是user_id,item_id,item_cat一致，表a时间小于表b，在此基础上查询用户数量。

代码实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- TODO 查询加入购物车的用户数并去除重复值，取别名为 加购物车人数
-- TODO 查询购买的人数并去除重复值，取别名为 之后购买人数
SELECT COUNT(DISTINCT a.user_id) AS 加购物车人数,COUNT(DISTINCT b.user_id) AS 之后购买人数

-- TODO 从shop_behaviors表中查找user_id,item_id,item_cat,date字段，筛选条件为beh_type ='cart'，将查询内容作为表a
FROM (SELECT user_id,item_id,item_cat,date
     FROM shop_behaviors
     WHERE beh_type = 'cart') AS a

-- TODO 左联结 
LEFT JOIN

-- TODO 从shop_behaviors表中查找user_id,item_id,item_cat,date字段，筛选条件为beh_type ='buy'，将查询内容作为表b
(SELECT user_id,item_id,item_cat,date
     FROM shop_behaviors
     WHERE beh_type = 'buy') AS b

-- TODO 设置条件 
ON 
a.user_id = b.user_id
AND a.item_id = b.item_id 
AND a.item_cat = b.item_cat
AND a.date < b.date;

使用Pyecharts绘制漏斗图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 使用from...import从pyecharts.charts中导入Funnel
from pyecharts.charts import Funnel
# 使用from...import从pyecharts导入options，简写为opts
from pyecharts import options as opts

# 使用Funnel()函数创建对象赋值给funnel
funnel = Funnel()
# 将series_name设为 漏斗图
# 设置gap值为10
# 将参数添加到add()函数中
funnel.add(series_name="漏斗图", data_pair = [['浏览',524],['加入购物车',300],['购买',89]], gap=10)

# 使用LegendOpts()，传入参数is_show=False，赋值给legend_opts 
# 使用TitleOpts()，设置标题为"加够漏斗"，赋值给title_opts
# 调用set_global_opts()
funnel.set_global_opts(
    legend_opts=opts.LegendOpts(is_show=False),
    title_opts=opts.TitleOpts(title="加购漏斗"))

# 使用render()生成漏斗图，存到路径/Users/pm.html
funnel.render("/Users/pm.html")

8.浏览-收藏-购买

用同样方法，可以计算加入收藏的用户数，以及加入收藏之后再购买的用户数。

代码实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- TODO 查询加入收藏的用户数并去除重复值，取别名为 加收藏人数
-- TODO 查询购买的人数并去除重复值，取别名为 之后购买人数
SELECT COUNT(DISTINCT a.user_id) AS 加收藏人数,COUNT(DISTINCT b.user_id) AS 之后购买人数

-- TODO 从shop_behaviors表中查找user_id,item_id,item_cat,date字段，筛选条件为beh_type ='fav'，将查询内容作为表a
FROM (SELECT user_id,item_id,item_cat,date
     FROM shop_behaviors
     WHERE beh_type = 'fav') AS a
-- TODO 左联结
LEFT JOIN
-- TODO 从shop_behaviors表中查找user_id,item_id,item_cat,date字段，筛选条件为beh_type ='buy'，将查询内容作为表b
(SELECT user_id,item_id,item_cat,date
     FROM shop_behaviors
     WHERE beh_type = 'buy') AS b
-- TODO 设置条件 
ON
-- TODO user_id,item_id,item_cat相同，a.date < b.date
a.user_id = b.user_id
AND
a.item_id = b.item_id
AND
a.item_cat = b.item_cat
AND
a.date < b.date;

绘制漏斗图：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 使用from...import从pyecharts.charts中导入Funnel
from pyecharts.charts import Funnel
# 使用from...import从pyecharts导入options，简写为opts
from pyecharts import options as opts

# 使用Funnel()函数创建对象赋值给funnel
funnel = Funnel()

# 将series_name设为 漏斗图
# 设置gap值为10
# 将参数添加到add()函数中
funnel.add(series_name="漏斗图", data_pair = [['浏览',524],['加入收藏',231],['购买',22]], gap=10)

# 使用LegendOpts()，传入参数is_show=False，赋值给legend_opts 
# 使用TitleOpts()，设置标题为"收藏漏斗"，赋值给title_opts
# 调用set_global_opts()
funnel.set_global_opts(
    legend_opts=opts.LegendOpts(is_show=False),
    title_opts=opts.TitleOpts(title="收藏漏斗"))

# 使用render()生成漏斗图，存到路径/Users/pm1.html
funnel.render("/Users/pm1.html")

9.计算加入购物车，并在三天内购买的用户数量，取别名为number。

代码实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT COUNT(DISTINCT b.user_id) AS number
FROM (SELECT user_id,item_id,item_cat,date
      FROM shop_behaviors
      WHERE beh_type='cart') AS a
LEFT JOIN (SELECT user_id,item_id,item_cat,date
           FROM shop_behaviors
           WHERE beh_type='buy') AS b
ON
a.user_id = b.user_id
AND
a.item_id = b.item_id
AND
a.item_cat = b.item_cat
AND
a.date < b.date
WHERE
TIMESTAMPDIFF(DAY,a.date,b.date) < 3;

10.SQL中的多条件筛选

例如：

1
2
3
4
5
6
7
8
9
SELECT sku_name,
     (CASE 
      WHEN sku_price < 100 THEN '普通'
      WHEN sku_price >= 100 AND 
           sku_price <= 500  THEN '中等'
      WHEN sku_price > 500 THEN '高级' 
      ELSE '异常' 
      END) AS MARK
FROM commodity;

这部分代码的意思是将商品表中，价格在100以下，100到500元区间，500以上的商品分别筛选出来打上普通，中等，高级的标签。

第一行，查找商品名称

第三行，查找价格低于100的商品并标注普通

第四行, 查找价格大于等于100且小于等于500的商品并标注中等

第六行，查找价格高于500的商品并标注高级，其他情况标注为异常

将上述筛选条件取别名为MARK。

第九行，查找表格为commodity

应用：分别统计没有使用优惠券，和使用3种优惠券的数量。

其中：

  • coupon_id = -1 时展示的值为4；
    coupon_id = 1111 时展示的值为1 ；
    coupon_id = 2222 时展示的值为2；
    coupon_id = 3333 时展示的值为3；
    除此之外，展示的值为0
    分类后取别名为coupon

  • 统计订单数量取别名为 订单数

  • 按照coupon分组

表coupon_list：

order_id coupon_id payment coupon_value
100001 -1 10 0
100002 -1 22 0
100003 -1 106 0
100004 -1 77 0
100005 -1 61 0
100006 -1 175 0
100007 -1 135 0
100008 1111 199 20
100009 3333 701 50
100010 -1 40 0

代码实现：

1
2
3
4
5
6
7
8
9
SELECT (CASE
     WHEN coupon_id = -1 THEN 4
     WHEN coupon_id = 1111 THEN 1
     WHEN coupon_id = 2222 THEN 2
     WHEN coupon_id = 3333 THEN 3
     ELSE 0
     END) AS coupon,COUNT(order_id) AS 订单数
FROM coupon_list
GROUP BY coupon;

11.CASE WHEN与聚合函数的联合使用

上例中，可以使用聚合函数里的COUNT()函数和CASE WHEN对每一种优惠券使用数量进行统计并取别名，取得同样的计算结果。

例如：

1
2
3
4
5
6
SELECT COUNT(DISTINCT order_id) AS 订单数,
       COUNT(DISTINCT CASE WHEN coupon_id = -1 THEN order_id END) AS 未使用优惠券订单数,
       COUNT(DISTINCT CASE WHEN coupon_id = 1111 THEN order_id END) AS coupon_1,
       COUNT(DISTINCT CASE WHEN coupon_id = 2222 THEN order_id END) AS coupon_2,
       COUNT(DISTINCT CASE WHEN coupon_id = 3333 THEN order_id END) AS coupon_3
FROM coupon_list;

根据coupon_id，也就是优惠券id的不同，分别统计不同优惠券的使用情况，并取别名进行区分。

聚合函数与CASE WHEN搭配进行统计的方法在实际工作中应用较多，是一种很好的行转列的方法。

注意：THEN后显示的值可以是表格里本身的列值，例如order_id。

也可以是重命名的一个新值，例如前面例子里的“普通”、“中等”、“高级”。

绘制使用优惠券情况的柱状图：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 从pyecharts.charts中 导入 Bar模块
from pyecharts.charts import Bar
from pyecharts import options as opts
# 创建一个柱状图Bar对象并赋值给变量bar
bar = Bar(init_opts=opts.InitOpts(theme="dark"))
coupon_list = ['未使用优惠券','20元优惠券','30元优惠券','50元优惠券']
count_list = [52,15,12,10]

# 使用add_xaxis函数，传入pointList作为x轴数据
bar.add_xaxis(xaxis_data=coupon_list)

# 使用add_yaxis函数，设置图例名称参数series_name为"扣分"，传入scoreList作为y轴数据
bar.add_yaxis(
    series_name="优惠券使用情况", 
    y_axis=count_list
    )

# 绘制出这条柱状图
bar.render("/Users/bar.html")

从图表中可以很明显的看出，有60%的用户没有使用优惠券。

通过访谈和调研发现大部分客单价都没有达到活动优惠券设定的门槛。

据此决定将优惠券金额调低，设置100-10元的优惠券进行测试。

测试结果：增加100-10的优惠券，实际下单数量提升。

证明确实是优惠券设置影响了顾客下单的意愿。

12.回溯过程

  1. 通过漏斗分析：定位用户流失的关键环节。
  2. 通过内外部调研：聚焦关键问题。
  3. 通过数据拆解：定位关键结点。
  4. 通过迭代：切实带来业务的提升。

例题：查找表格为订单表。使用SUM()函数统计当payment_method为Wechat时的total_price，并取别名为微信支付。使用SUM()函数统计当payment_method为Alipay时的total_price，并取别名为支付宝支付。使用SUM()函数统计当payment_method为other时的total_price，并取别名为其它。

代码实现：

1
2
3
4
5
SELECT 
       SUM(CASE WHEN payment_method = 'Wechat' THEN total_price END) AS 微信支付,
       SUM(CASE WHEN payment_method = 'Alipay' THEN total_price END) AS 支付宝支付,
       SUM(CASE WHEN payment_method = 'other' THEN total_price END) AS 其它
FROM orders;

例题：在表格views中包含某个用户在某天阅读某篇文章的记录。编写SQL语句帮助翔翔从表views中找到在同一天内，阅读了大于1篇文章的用户编号 (viewer_id)，并按照viewer_id正序排列

表views

article_id author_id viewer_id view_date
1 3 5 2021-07-01
3 4 5 2021-07-01
1 3 6 2021-07-02
2 7 7 2021-07-01
2 7 6 2021-07-02
4 7 1 2021-07-13
3 4 3 2021-07-11
3 4 3 2021-07-11

代码实现：

1
2
3
4
5
SELECT DISTINCT viewer_id
FROM views
GROUP BY viewer_id,view_date
HAVING COUNT(DISTINCT article_id)>1
ORDER BY viewer_id;

例题：有一个通过题目个数记录的表(passing_number)。每一行记录了用户编号 (id) 和对应的刷题数量 (number)。编写SQL语句帮助明明从表中输出通过的题目的排名rank；通过题目个数相同的，排名rank相同，此时按照id升序排列。

表passing_number

id number
1 26
2 100
3 98
4 98
5 66
6 14

代码实现：

1
2
3
4
5
SELECT a.id,a.number,(SELECT COUNT(DISTINCT b.number) 
                      FROM passing_number AS b
                      WHERE b.number >= a.number) AS rank
FROM passing_number AS a
ORDER BY rank,a.id;

例题：表request_accepted存储了所有好友申请通过的数据记录，其中， requester_id 和 accepter_id 都是用户的编号。
且好友申请只会被接受一次，因此不会有requester_id和accepter_id相同的重复记录。编写一个SQL语句，找出拥有最多的好友，和其拥有的好友数目

表request_accepted

requester_id accepter_id accept_date
1 2 2021-06-04
1 3 2021-06-06
2 3 2021-06-06
3 4 2021-06-08

代码实现：

1
2
3
4
5
6
7
8
9
SELECT id, SUM(num) AS num
FROM
SELECT requester_id AS id, COUNT(*) AS num FROM request_accepted GROUP BY requester_id
UNION ALL
SELECT accepter_id AS id, COUNT(*) AS num FROM request_accepted GROUP BY accepter_id
)AS t
GROUP BY id
ORDER BY num DESC
LIMIT 1;

例题：写一个SQL代码从商品表(bread_shop)中给面包分类，类别(total_group)是：正常售卖，待甩卖，下架。其中：过期日期(expiration_date)在5月24日前(不包含5月24日当天)的面包视作坏掉；过期日期是5月24日的是临近过期的面包用于甩卖。

表bread_shop

id name production_date expiration_date
1 吐司 2022-05-20 2022-05-23
2 法式长棍面包 2022-05-20 2022-05-23
3 牛角包 2022-05-20 2022-05-23
4 甜甜圈 2022-05-20 2022-05-23
5 雪媚娘 2022-05-22 2022-05-24
6 糖浆唱片 2022-05-22 2022-05-24
7 酥脆红豆面包 2022-05-22 2022-05-24
8 巧克力软包 2022-05-22 2022-05-24
9 乳酪蛋糕 2022-05-22 2022-05-24
10 芝士玉米饼 2022-05-22 2022-05-24

代码实现：

1
2
3
4
5
6
SELECT name,(CASE 
             WHEN expiration_date < '2022-05-24' THEN '下架'
             WHEN expiration_date = '2022-05-24' THEn '待甩卖'
             ELSE '正常售卖'
             END) AS total_group
FROM bread_shop;

例题：当天发货的订单称为即刻订单，而其他的是预定订单。表格delivery中，customer_pref_date表示顾客希望发货日期。而order_date为下单时间。编写SQL语句找到即刻订单占表格中所有订单的比率，并重命名为immediate_percentage
计算公式为：immediate_percentage = 即刻订单数 / 总订单数

表delivery

delivery_id customer_id order_date customer_pref_date
1 1 2021-07-11 2021-07-12
2 3 2021-07-12 2021-07-12
3 5 2021-07-24 2021-07-26
4 1 2021-07-22 2021-07-22
5 4 2021-07-17 2021-07-17
6 2 2021-07-16 2021-07-17

代码实现：

1
2
SELECT (COUNT(CASE WHEN order_date = customer_pref_date THEN delivery_id END) / COUNT(*) ) AS immediate_percentage
FROM delivery;

例题：编写一个SQL语句从表 (student) 中，输出表包含MALE_COUNT, FEMALE_COUNT, MALE_PASS, FEMALE_PASS四个字段名。用于统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格。

表student

stu_id stu_name stu_sex score
1 小红 女 72
2 小明 女 88
3 小君 男 52
4 小希 女 46
5 小翔 男 80
6 小钱 男 66
7 小孙 男 90
8 小丽 女 92
9 小妮 女 75

代码实现：

1
2
3
4
5
6
SELECT
SUM(CASE WHEN stu_sex = "男" THEN 1 ELSE 0 END) AS MALE_COUNT,
SUM(CASE WHEN stu_sex = "女" THEN 1 ELSE 0 END) AS FEMALE_COUNT,
SUM(CASE WHEN score >= 60 AND stu_sex = "男" THEN 1 ELSE 0 END) AS MALE_PASS,
SUM(CASE WHEN score >= 60 AND stu_sex = "女" THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM student;

例题：可以根据每个公司的平均员工工资，估算一下其对应的税收等级：
如果这个公司员工平均工资不到 1000 ，则税收等级为low；
如果这个公司员工平均工资在 1000 到 10000 之间 ，则税收等级medium；如果这个公司员工平均工资大于 10000 ，则税收等级为high。

编写一个SQL代码从表(salaries)中，按照每个公司的税率情况给公司分类，输出表包括company_id，和其对应的税收等级（’low’，’medium’，’high’)。

表salaries

company_id employee_id employee_name salary
1 3 小红 2000
1 13 小绿 10900
1 15 小蓝 21200
2 6 小黄 300
2 17 小白 700
2 18 小黑 450
3 5 小橙 200
3 9 小紫 2500
3 12 小赤 1880
3 16 小青 3200

代码实现：

1
2
3
4
5
SELECT company_id,(CASE WHEN AVG(salary) < 1000 THEN 'low'
                   WHEN AVG(salary) BETWEEN 1000 AND 10000 THEN 'medium'
                   ELSE 'high' END) AS 税收等级
FROM salaries
GROUP BY company_id;