MySQL 实战案例：电商⽤户、商品、平台价值分析

1
2
3
4
5
6
7
8
9
-- 创建数据表
CREATE TABLE o_retailers_trade_user (
  user_id INT(9),
  item_id INT(9),
  behavior_type INT(1),
  user_geohash VARCHAR(14),
  item_category INT(5),
  time VARCHAR(13) 
);

1
2
-- 查看基础数据
SELECT * FROM o_retailers_trade_user LIMIT 11;

1
2
3
4
5
6
7
8
-- 增加新字段 date_time，字段数据来自基础数据中的 time 字段
-- %H 可以表示 0-23；⽽ %h 表示 0-12
ALTER TABLE o_retailers_trade_user ADD COLUMN date_time DATETIME NULL;
UPDATE o_retailers_trade_user SET date_time = STR_TO_DATE(time, '%Y-%m-%d %H');

-- 增加新字段 dates，字段数据来自上面新增的 date_time 字段
ALTER TABLE o_retailers_trade_user ADD COLUMN dates CHAR(10) NULL;
UPDATE o_retailers_trade_user SET dates = DATE(date_time);

1
2
-- 查看更新后的表结构
DESC o_retailers_trade_user;

1
2
-- 查看更新后的数据
SELECT * FROM o_retailers_trade_user LIMIT 11;

1
2
3
-- 创建新表 temp_trade，并向其中插⼊去重后的数据
CREATE TABLE temp_trade LIKE o_retailers_trade_user;
INSERT INTO temp_trade SELECT DISTINCT * FROM o_retailers_trade_user;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*
pv：统计 behavior_type = 1 的记录数量，需要按日统计（分组）
uv：统计 DISTINCT user_id 的数量，需要按⽇统计（分组）
浏览深度：pv/uv

注意：pv 进⾏ COUNT 的时候，如果 behavior_type = 1 则进⾏计算；否则，不进⾏计算
*/

SELECT dates, 
  COUNT(DISTINCT user_id) AS uv,
  COUNT(IF(behavior_type = 1, user_id, NULL)) AS pv,
  COUNT(IF(behavior_type = 1, user_id, NULL)) / COUNT(DISTINCT user_id) AS 'pv/uv'
FROM temp_trade 
GROUP BY dates;

1
SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates;

1
2
3
4
5
6
SELECT * 
FROM
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS a
  LEFT JOIN
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS b
  ON a.user_id = b.user_id;

1
2
3
4
5
6
7
8
9
10
SELECT 
  a.user_id, 
  a.dates, 
  b.dates AS dates_after 
FROM 
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS a
  LEFT JOIN 
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS b
  ON a.user_id = b.user_id
WHERE b.dates >= a.dates;

1
2
3
4
5
6
7
8
9
10
SELECT 
  a.dates,
  COUNT(DISTINCT a.user_id) AS user_count
FROM
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS a
  LEFT JOIN
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS b
  ON a.user_id = b.user_id
WHERE b.dates >= a.dates
GROUP BY a.dates;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT 
  a.dates,
  COUNT(DISTINCT a.user_id) AS user_count,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 1, b.user_id, NULL)) AS remain_1,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 2, b.user_id, NULL)) AS remain_2,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 3, b.user_id, NULL)) AS remain_3,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 4, b.user_id, NULL)) AS remain_4,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 5, b.user_id, NULL)) AS remain_5,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 6, b.user_id, NULL)) AS remain_6,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 7, b.user_id, NULL)) AS remain_7,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 15, b.user_id, NULL)) AS remain_15,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 30, b.user_id, NULL)) AS remain_30
FROM
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS a
  LEFT JOIN 
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS b
  ON a.user_id = b.user_id
WHERE b.dates >= a.dates
GROUP BY a.dates;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE VIEW user_remain_view AS
SELECT 
  a.dates,
  COUNT(DISTINCT a.user_id) AS user_count,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 1, b.user_id, NULL)) AS remain_1,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 2, b.user_id, NULL)) AS remain_2,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 3, b.user_id, NULL)) AS remain_3,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 4, b.user_id, NULL)) AS remain_4,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 5, b.user_id, NULL)) AS remain_5,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 6, b.user_id, NULL)) AS remain_6,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 7, b.user_id, NULL)) AS remain_7,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 15, b.user_id, NULL)) AS remain_15,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 30, b.user_id, NULL)) AS remain_30
FROM 
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS a
  LEFT JOIN 
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS b
  ON a.user_id = b.user_id
WHERE b.dates >= a.dates
GROUP BY a.dates;

SELECT * FROM user_remain_view;

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
  dates, 
  user_count,
  CONCAT(ROUND(remain_1 / user_count * 100, 2), '%') AS day_1,
  CONCAT(ROUND(remain_2 / user_count * 100, 2), '%') AS day_2,
  CONCAT(ROUND(remain_3 / user_count * 100, 2), '%') AS day_3,
  CONCAT(ROUND(remain_4 / user_count * 100, 2), '%') AS day_4,
  CONCAT(ROUND(remain_5 / user_count * 100, 2), '%') AS day_5,
  CONCAT(ROUND(remain_6 / user_count * 100, 2), '%') AS day_6,
  CONCAT(ROUND(remain_7 / user_count * 100, 2), '%') AS day_7,
  CONCAT(ROUND(remain_15 / user_count * 100, 2), '%') AS day_15,
  CONCAT(ROUND(remain_30 / user_count * 100, 2), '%') AS day_30
FROM user_remain_view;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
WITH temp_table_trades AS
(SELECT 
  a.dates,
  COUNT(DISTINCT a.user_id) AS user_count,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 1, b.user_id, NULL)) AS remain_1,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 2, b.user_id, NULL)) AS remain_2,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 3, b.user_id, NULL)) AS remain_3,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 4, b.user_id, NULL)) AS remain_4,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 5, b.user_id, NULL)) AS remain_5,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 6, b.user_id, NULL)) AS remain_6,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 7, b.user_id, NULL)) AS remain_7,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 15, b.user_id, NULL)) AS remain_15,
  COUNT(DISTINCT IF(DATEDIFF(b.dates, a.dates) = 30, b.user_id, NULL)) AS remain_30
FROM 
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS a
  LEFT JOIN 
  (SELECT user_id, dates FROM temp_trade GROUP BY user_id, dates) AS b
  ON a.user_id = b.user_id
WHERE b.dates >= a.dates
GROUP BY a.dates)
SELECT
  dates, 
  user_count,
  CONCAT(ROUND(remain_1 / user_count * 100, 2), '%') AS day_1,
  CONCAT(ROUND(remain_2 / user_count * 100, 2), '%') AS day_2,
  CONCAT(ROUND(remain_3 / user_count * 100, 2), '%') AS day_3,
  CONCAT(ROUND(remain_4 / user_count * 100, 2), '%') AS day_4,
  CONCAT(ROUND(remain_5 / user_count * 100, 2), '%') AS day_5,
  CONCAT(ROUND(remain_6 / user_count * 100, 2), '%') AS day_6,
  CONCAT(ROUND(remain_7 / user_count * 100, 2), '%') AS day_7,
  CONCAT(ROUND(remain_15 / user_count * 100, 2), '%') AS day_15,
  CONCAT(ROUND(remain_30 / user_count * 100, 2), '%') AS day_30
FROM temp_table_trades;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- R 指标分析：根据每个用户最近一次购买时间，给出相应的分数

-- 1. 建立 R 视图：统计每个用户的最近购买时间
DROP VIEW IF EXISTS user_recency_view;
CREATE VIEW user_recency_view AS
SELECT
  user_id, 
  MAX(dates) AS recent_buy_time
FROM  temp_trade
WHERE behavior_type = 2
GROUP BY user_id;

-- 2. 建立 R 评分视图：计算每个用户最近购买时间距离参照日期 '2019-12-18' 的天数，
-- 根据距离天数进行打分：<=2 5分；<=4 4分；<=6 3分；<=8 2分；其他 1分
DROP VIEW IF EXISTS r_score_view;
CREATE VIEW r_score_view AS
SELECT 
  user_id,
  recent_buy_time,
  DATEDIFF('2019-12-18', recent_buy_time) AS date_distance,
  CASE
    WHEN DATEDIFF('2019-12-18', recent_buy_time) <= 2 THEN 5
    WHEN DATEDIFF('2019-12-18', recent_buy_time) <= 4 THEN 4
    WHEN DATEDIFF('2019-12-18', recent_buy_time) <= 6 THEN 3
    WHEN DATEDIFF('2019-12-18', recent_buy_time) <= 8 THEN 2
    ELSE 1 
  END AS r_score
FROM user_recency_view;

SELECT * FROM r_score_view;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- F 指标计算：统计每个用户的消费次数，给出相应的分数

-- 1. 建立 F 视图：统计每个用户的消费次数
DROP VIEW IF EXISTS user_frequency_view;
CREATE VIEW user_frequency_view AS
SELECT
  user_id,
  COUNT(user_id) AS buy_frequency
FROM temp_trade
WHERE behavior_type = 2
GROUP BY user_id;

-- 2. 建立 F 评分视图：基于购买次数对用户进行打分
-- 按照购买次数评分：<=2 1分；<=4 2分；<=6 3分；<=8 4分；其他 5分
DROP VIEW IF EXISTS f_score_view;
CREATE VIEW f_score_view AS
SELECT
  user_id,
  buy_frequency,
  CASE
    WHEN buy_frequency <= 2 THEN 1
    WHEN buy_frequency <= 4 THEN 2
    WHEN buy_frequency <= 6 THEN 3
    WHEN buy_frequency <= 8 THEN 4
    ELSE 5
  END AS f_score
FROM user_frequency_view;

SELECT * FROM f_score_view;

1
2
3
4
-- 计算 R 得分和 F 得分的均值
SELECT
  (SELECT AVG(r_score) FROM r_score_view) AS r_avg,
  (SELECT AVG(f_score) FROM f_score_view) AS f_avg;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 拿到每个用户的 R 得分和 F 得分（两表关联 r_score_view 和 f_score_view），然后与均值对比，得到各用户类型
DROP VIEW IF EXISTS rfm_inall_view;
CREATE VIEW rfm_inall_view AS
SELECT
  r.user_id,
  r.r_score,
  f.f_score,
  CASE
    WHEN r.r_score > 2.7939 AND f.f_score > 2.2606 THEN '重要高价值客户'
    WHEN r.r_score < 2.7939 AND f.f_score > 2.2606 THEN '重要唤回客户'
    WHEN r.r_score > 2.7939 AND f.f_score < 2.2606 THEN '重要深耕客户'
    WHEN r.r_score < 2.7939 AND f.f_score < 2.2606 THEN '重要挽留客户'
  END AS user_class
FROM r_score_view AS r INNER JOIN f_score_view AS f ON r.user_id = f.user_id;

SELECT * FROM rfm_inall_view;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 统计各用户的用户类型、最近一次购买时间间隔、购买频率
DROP VIEW IF EXISTS user_class_view;
CREATE VIEW user_class_view AS 
WITH temp_rf AS (
SELECT
  r.user_id, 
  date_distance, 
  buy_frequency
FROM r_score_view r INNER JOIN f_score_view f ON r.user_id = f.user_id) 
SELECT 
  rfm.user_id,
  user_class, 
  date_distance, 
  buy_frequency
FROM rfm_inall_view AS rfm LEFT JOIN temp_rf AS trf ON rfm.user_id = trf.user_id;

SELECT * FROM user_class_view;

1
2
3
4
5
6
7
8
-- 统计各个用户类型下的用户数量、最近一次购买的平均时间间隔、平均消费频率
SELECT
  user_class,
  COUNT(user_id) AS user_count,
  AVG(date_distance) AS avg_date_distance,
  AVG(buy_frequency) AS avg_buy_frequency
FROM user_class_view 
GROUP BY user_class;

1
2
3
4
5
6
7
8
9
10
11
12
-- 统计各商品的点击量、收藏量、加购量、购买次数、购买转化率
SELECT
  item_id,
  SUM(IF(behavior_type = 1, 1, 0)) AS pv,
  SUM(IF(behavior_type = 4, 1, 0)) AS favorite,
  SUM(IF(behavior_type = 3, 1, 0)) AS cart,
  SUM(IF(behavior_type = 2, 1, 0)) AS purchase,
  CONCAT(ROUND(COUNT(DISTINCT IF(behavior_type = 2, user_id, NULL)) / 
               COUNT(DISTINCT user_id) * 100, 2), '%') AS purchase_ratio
FROM temp_trade
GROUP BY item_id
ORDER BY purchase DESC, purchase_ratio DESC;

1
2
3
4
5
6
7
8
9
10
11
12
-- 统计各品类的点击量、收藏量、加购量、购买次数、购买转化率
SELECT
  item_category,
  SUM(IF(behavior_type = 1, 1, 0)) AS pv,
  SUM(IF(behavior_type = 4, 1, 0)) AS favorite,
  SUM(IF(behavior_type = 3, 1, 0)) AS cart,
  SUM(IF(behavior_type = 2, 1, 0)) AS purchase,
  CONCAT(ROUND(COUNT(DISTINCT IF(behavior_type = 2, user_id, NULL)) / 
               COUNT(DISTINCT user_id) * 100, 2), '%') AS purchase_ratio
FROM temp_trade
GROUP BY item_category
ORDER BY purchase DESC, purchase_ratio DESC;

1
2
3
4
5
6
7
8
9
10
11
12
-- 统计平台每日的点击量、收藏量、加购量、购买次数、购买转化率
SELECT
  dates,
  SUM(IF(behavior_type = 1, 1, 0)) AS pv,
  SUM(IF(behavior_type = 4, 1, 0)) AS favorite,
  SUM(IF(behavior_type = 3, 1, 0)) AS cart,
  SUM(IF(behavior_type = 2, 1, 0)) AS purchase,
  CONCAT(ROUND(COUNT(DISTINCT IF(behavior_type = 2, user_id, NULL)) / 
               COUNT(DISTINCT user_id) * 100, 2), '%') AS purchase_ratio
FROM temp_trade
GROUP BY dates
ORDER BY dates DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/*
用户行为拼接准备

使用偏移分析函数 LAG() 或者 LEAD() 为各行为分别创建一个字段，这里涉及到分组和排序：
1. 将 PARTITION BY 后的字段指定为 user_id 和 item_id
2. 将 ORDER BY 后的字段指定为 date_time

注意，这里我们排序字段指定的是 date_time 而不是 dates，因为同一天内可能存在多个行为，需要考虑它们之间的顺序。
另外，由于要统计的是最近一次购买行为对应的路径，可以使用排序函数 RANK() 按照 date_time 字段进行倒排序，然后取第一条记录。
*/
DROP VIEW IF EXISTS path_base_view;
CREATE VIEW path_base_view AS
SELECT a.* FROM 
  (SELECT
    user_id,
    item_id,
    LAG(behavior_type, 4) OVER (PARTITION BY user_id, item_id ORDER BY date_time) AS lag_4,
    LAG(behavior_type, 3) OVER (PARTITION BY user_id, item_id ORDER BY date_time) AS lag_3,
    LAG(behavior_type, 2) OVER (PARTITION BY user_id, item_id ORDER BY date_time) AS lag_2,
    LAG(behavior_type, 1) OVER (PARTITION BY user_id, item_id ORDER BY date_time) AS lag_1,
    behavior_type,
    RANK() OVER (PARTITION BY user_id, item_id ORDER BY date_time DESC) AS rank_number
  FROM temp_trade) AS a
WHERE a.rank_number = 1 AND a.behavior_type = 2;

SELECT * FROM path_base_view;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 拼接行为路径，并统计各行为路径下的用户数量 
-- 注意：lag_4 到 lag_1 可能存在 NULL，因此我们用字符串 '空' 来对其进行标记，防止 CONCAT 函数输出 NULL 值。 
SELECT 
  CONCAT(IFNULL(lag_4, '空'), '-', 
         IFNULL(lag_3, '空'), '-', 
         IFNULL(lag_2, '空'), '-', 
         IFNULL(lag_1, '空'), '-', 
         behavior_type) AS behavior_path,
  COUNT(DISTINCT user_id) AS user_count
FROM path_base_view
GROUP BY
  CONCAT(IFNULL(lag_4, '空'), '-', 
         IFNULL(lag_3, '空'), '-', 
         IFNULL(lag_2, '空'), '-',
         IFNULL(lag_1, '空'), '-', 
         behavior_type)
ORDER BY user_count DESC;