数据挖据——如何利用SQL语句实现购物篮分析？

提到购物篮分析，就不得不说到一个无数次被提及的故事——啤酒与尿不湿的故事，这个经典案例常被拿到各种会场、餐桌和文章中，以至于听到耳朵都要磨成茧。购物篮分析，就是分析一段时间内客户购物篮中商品的规律，通过数据分析和数据挖掘，发现用户的消费习惯，尤其是哪些商品经常会一起购买，从而更好用于商品的排列、采购、推广和营销，以此来满足消费者的需要，提高公司收益。
在做购物篮分析时，往往是以订单、产品为最小颗粒度的数据，来分析用户在购买A产品以后，对B产品带来的影响有多大。

1.课前知识预习

购物篮分析的实现，是基于条件概率，也就是贝叶斯公式。在实际应用时，主要会牵扯到3个指标：置信度、支持度和提升度。
将信息展示如下，N代表数量:

2.实现步骤

为方便演示，构建product_order作为测试表，表数据截图如下：

构建数据的代码如下：

--构建测试数据
DROP TABLE IF EXISTS "product_order";
CREATE TABLE "product_order" (
  "order_id" varchar(100) COLLATE "pg_catalog"."default",
  "product" varchar(50) COLLATE "pg_catalog"."default",
  "qty" int8
)
;

INSERT INTO "product_order" VALUES ('1', 'A', 1);
INSERT INTO "product_order" VALUES ('1', 'B', 2);
INSERT INTO "product_order" VALUES ('1', 'C', 3);
INSERT INTO "product_order" VALUES ('1', 'D', 5);
INSERT INTO "product_order" VALUES ('2', 'A', 4);
INSERT INTO "product_order" VALUES ('2', 'C', 2);
INSERT INTO "product_order" VALUES ('3', 'D', 3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.1 步骤1：构建两两组合表

先生成两两组合表，效果图如下：

实现的SQL代码如下：

--构建两两组合表
SELECT A.order_id,
	A.product product_a,
	b.product product_b 
FROM
	product_order
	A LEFT JOIN product_order b ON A.order_id = b.order_id;
1
2
3
4
5
6
7

2.2 步骤2：求两两组合的订单数量

计算出两两组合的订单数量，计算结果如下图所示：

实现代码如下：

-- 求两两组合的订单数量
SELECT
	product_a,
	product_b,
	COUNT ( DISTINCT order_id ) AS buy_together 
FROM
	(
	SELECT A
		.order_id,
		A.product product_a,
		b.product product_b 
	FROM
		product_order
		A LEFT JOIN product_order b ON A.order_id = b.order_id 
	) T 
GROUP BY
	product_a,
	product_b;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.3 步骤3：求每个商品的订单数量

汇总计算每个产品的订单数，计算效果如下图：

实现代码如下：

-- 求每个商品的订单数量
SELECT
	product,
	COUNT ( DISTINCT order_id ) product_cnt 
FROM
	product_order 
GROUP BY
	product;
1
2
3
4
5
6
7
8

2.4 步骤4：求所有订单数量

汇总计算所有的订单数量，截图如下：

实现代码如下图：

--求所有订单数量
SELECT COUNT
	( DISTINCT order_id ) total_order_cnt 
FROM
	product_order;

1
2
3
4
5
6

2.5 步骤5：求拼接所有基础数据

对所有需要的基础数据进行拼接，数据如下：

实现代码如下：

--求拼接所有基础数据
SELECT
	t1.product_a,
	t1.product_b,
	t1.buy_together,
	t2.product_a_cnt,
	t3.product_b_cnt,
	t4.total_order_cnt 
FROM
	(
	SELECT
		product_a,
		product_b,
		COUNT ( DISTINCT order_id ) AS buy_together 
	FROM
		(
		SELECT A
			.order_id,
			A.product product_a,
			b.product product_b 
		FROM
			product_order
			A LEFT JOIN product_order b ON A.order_id = b.order_id 
		) T 
	GROUP BY
		product_a,
		product_b 
	) t1
	LEFT JOIN ( SELECT product, COUNT ( DISTINCT order_id ) product_a_cnt FROM product_order GROUP BY product ) t2 ON t1.product_a = t2.product
	LEFT JOIN ( SELECT product, COUNT ( DISTINCT order_id ) product_b_cnt FROM product_order GROUP BY product ) t3 ON t1.product_b = t3.product
	LEFT JOIN ( SELECT COUNT ( DISTINCT order_id ) total_order_cnt FROM product_order ) t4 ON 1 = 1;
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

2.6 步骤6：排除两两如何中产品相同的数据

对A产品和B产品相同的数据进行排除，数据如下：

实现代码如下：

-- 排除两两如何中产品相同的数据
SELECT
	t1.product_a,
	t1.product_b,
	t1.buy_together,
	t2.product_a_cnt,
	t3.product_b_cnt,
	t4.total_order_cnt 
FROM
	(
	SELECT
		product_a,
		product_b,
		COUNT ( DISTINCT order_id ) AS buy_together 
	FROM
		(
		SELECT A
			.order_id,
			A.product product_a,
			b.product product_b 
		FROM
			product_order
			A LEFT JOIN product_order b ON A.order_id = b.order_id 
		) T 
	GROUP BY
		product_a,
		product_b 
	) t1
	LEFT JOIN ( SELECT product, COUNT ( DISTINCT order_id ) product_a_cnt FROM product_order GROUP BY product ) t2 ON t1.product_a = t2.product
	LEFT JOIN ( SELECT product, COUNT ( DISTINCT order_id ) product_b_cnt FROM product_order GROUP BY product ) t3 ON t1.product_b = t3.product
	LEFT JOIN ( SELECT COUNT ( DISTINCT order_id ) total_order_cnt FROM product_order ) t4 ON 1 = 1 
--增加筛选条件
WHERE
	t1.product_a <> t1.product_b;
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
34

2.7 步骤7：构建衍生数据

利用得到的基础数据，增加对应的衍生指标。

3.完整SQL

按照上述步骤，得出完整计算结果：

--完整SQL，进行购物篮分析
SELECT
	*,
	round(buy_together * 1.0 / total_order_cnt,4) AS support,
	round(buy_together * 1.0 / product_a_cnt,4) AS confidence,
	round(( buy_together * 1.0 / product_a_cnt ) / ( product_b_cnt *1.0/ total_order_cnt ),4) AS promote
-- round(buy_together*total_order_cnt*1.0/product_a_cnt/product_b_cnt,4)	promote_b
FROM
	(
	SELECT
	t1.product_a,
	t1.product_b,
	t1.buy_together,
	t2.product_a_cnt,
	t3.product_b_cnt,
	t4.total_order_cnt 
FROM
	(
	SELECT
		product_a,
		product_b,
		COUNT ( DISTINCT order_id ) AS buy_together 
	FROM
		(
		SELECT A
			.order_id,
			A.product product_a,
			b.product product_b 
		FROM
			product_order
			A LEFT JOIN product_order b ON A.order_id = b.order_id 
		) T 
	GROUP BY
		product_a,
		product_b 
	) t1
	LEFT JOIN ( SELECT product, COUNT ( DISTINCT order_id ) product_a_cnt FROM product_order GROUP BY product ) t2 ON t1.product_a = t2.product
	LEFT JOIN ( SELECT product, COUNT ( DISTINCT order_id ) product_b_cnt FROM product_order GROUP BY product ) t3 ON t1.product_b = t3.product
	LEFT JOIN ( SELECT COUNT ( DISTINCT order_id ) total_order_cnt FROM product_order ) t4 ON 1 = 1 
WHERE
	t1.product_a <> t1.product_b
	) main 
ORDER BY
	promote 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44