【Hive SQL 每日一题】统计每月用户购买商品的种类分布

测试数据

-- 创建 orders 表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id INT,
    user_id INT,
    product_id INT,
    order_date STRING
);

-- 插入 orders 数据
INSERT INTO orders VALUES
(101, 1, 1001, '2023-01-01'),
(102, 1, 1002, '2023-01-02'),
(103, 2, 1001, '2023-01-03'),
(104, 3, 1001, '2023-01-04'),
(105, 3, 1003, '2023-01-05'),
(106, 4, 1002, '2023-01-06'),
(107, 5, 1001, '2023-01-07'),
(108, 5, 1002, '2023-01-08'),
(109, 6, 1001, '2023-01-09'),
(110, 6, 1003, '2023-01-10'),
(111, 7, 1001, '2023-01-11'),
(112, 7, 1002, '2023-01-12'),
(113, 7, 1003, '2023-01-13'),
(114, 8, 1001, '2023-01-14'),
(115, 8, 1002, '2023-01-15'),
(116, 8, 1003, '2023-01-16'),
(117, 8, 1004, '2023-01-17'),
(118, 9, 1001, '2023-01-18'),
(119, 9, 1002, '2023-01-19'),
(120, 9, 1003, '2023-01-20'),
(121, 10, 1004, '2023-01-21'),
(122, 10, 1005, '2023-01-22'),
(123, 2, 1001, '2023-02-03'),
(124, 3, 1001, '2023-02-04'),
(125, 3, 1003, '2023-02-05'),
(126, 4, 1002, '2023-02-06'),
(127, 5, 1001, '2023-02-07'),
(128, 5, 1002, '2023-02-08'),
(129, 6, 1001, '2023-02-09'),
(130, 6, 1003, '2023-02-10'),
(131, 6, 1002, '2023-02-11'),
(132, 8, 1002, '2023-02-14'),
(133, 8, 1003, '2023-02-17'),
(134, 9, 1002, '2023-02-18'),
(135, 9, 1001, '2023-02-19'),
(136, 9, 1001, '2023-02-20');

-- 创建 categories 表
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
    category_id INT,
    category_name STRING
);

-- 插入 categories 数据
INSERT INTO categories VALUES
(1, 'Electronics'),
(2, 'Books'),
(3, 'Clothing'),
(4, 'Home'),
(5, 'Beauty');

-- 创建 products 表
DROP TABLE IF EXISTS products;
CREATE TABLE products (
    product_id INT,
    tag STRING,
    category_id INT
);

-- 插入 products 数据
INSERT INTO products VALUES
(1001, 'Electronics', 1),
(1002, 'Books', 2),
(1003, 'Clothing', 3),
(1004, 'Home', 4),
(1005, 'Beauty', 5);
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

需求说明

统计每月用户购买商品的种类分布，每个用户当月的下单次数至少达到 3 次及以上才进行统计。

结果示例：

结果按 order_month、category_name 升序排列。

其中：

• category_name 表示商品种类名称；
• order_month 表示统计的年月；
• category_month_cnt 表示该种类商品每月的销售数量。

需求实现

select
    category_name,
    date_format(order_date,"yyyy-MM") order_month,
    count(1) category_month_cnt
from
    orders o
join
    products p
on
    o.product_id = p.product_id
join
    categories c
on
    p.category_id = c.category_id
where
    concat(o.user_id,date_format(order_date,"yyyy-MM")) in
        (select
             concat(user_id,date_format(order_date,"yyyy-MM"))
         from
             orders
         group by
             user_id,date_format(order_date,"yyyy-MM")
         having
                 count(order_id) >= 3)
group by
    c.category_id,c.category_name,date_format(order_date,"yyyy-MM")
order by
    order_month,category_name;
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

输出结果如下：

本题的要点在于，如何筛选出我们想要的数据。

需求说明中，要求我们统计每月各个商品种类的销售分布数据，前提是，只有当用户在当月的下单次数 >=3 时，才被作为有效数据进行统计。

所以，我们需要先过滤出每个月份符合这个条件的用户ID，由用户ID和月份构建联合键，完成过滤后，再去进行统计。