当前位置:   article > 正文

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

【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 次及以上才进行统计。

结果示例:

category_nameorder_monthcategory_month_cnt
Books2023-013
Clothing2023-013
Electronics2023-013
Home2023-011
Books2023-022
Clothing2023-021
Electronics2023-023

结果按 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和月份构建联合键,完成过滤后,再去进行统计。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/你好赵伟/article/detail/686140
推荐阅读
相关标签
  

闽ICP备14008679号