SQL总结大厂真题-订单量前3，且存在某个商品购买了2个或以上的用户

数据仓库晨曦

发布于 2024-01-08 15:40:09

1472

发布于 2024-01-08 15:40:09

1.题目

现有一张订单表 t_order 有订单ID、用户ID、商品ID、购买商品数量、购买时间，请查出订单量前3，且存在某个商品购买了2个或以上的用户。样例数据如下：

| order_id | user_id | product_id | quantity | purchase_time       |
|----------|---------|------------|----------|---------------------|
| 1        | 1       | 1001       | 1        | 2023-03-13 08:30:00 |
| 2        | 1       | 1002       | 1        | 2023-03-13 10:45:00 |
| 3        | 1       | 1001       | 1        | 2023-03-13 10:45:01 |
| 4        | 2       | 1001       | 3        | 2023-03-13 14:20:00 |
| 5        | 3       | 1003       | 1        | 2023-03-13 16:15:00 |
| 6        | 3       | 1002       | 1        | 2023-03-13 12:10:00 |
| 7        | 3       | 1001       | 1        | 2023-03-13 12:10:01 |
| 8        | 4       | 1002       | 2        | 2023-03-13 09:00:00 |
| 9        | 4       | 1003       | 1        | 2023-03-13 11:30:00 |
| 10       | 4       | 1004       | 3        | 2023-03-13 13:40:00 |
| 11       | 4       | 1001       | 1        | 2023-03-13 17:25:00 |
| 12       | 4       | 1002       | 2        | 2023-03-13 15:05:00 |
| 13       | 4       | 1004       | 1        | 2023-03-13 11:55:00 |

2.题目分析：

需要查询出在某个商品上购买了2个或两个以上的用户，用户可能是一个订单直接购买了2个或者两个以上，也可能是分多次购买；（举例：用户1在1001商品上分两次，每次购买了1个商品，共计2个，用户1符合条件；用户3，分别在1001,1002,1003三个商品上购买了一单，所以不符合条件；）
计算每个用户的订单量；
查询出符合条件，订单量前3的用户

3.SQL

step1:查询符合购买2个或者以上商品的用户

select
    user_id
from
    (
        select
            user_id,
            product_id,
            sum(quantity) as product_u_nums
        from t_order
        group by user_id,product_id
        having sum(quantity) >=2
    ) t
group by user_id
;

查询结果

step2:计算每个用户的订单量

select user_id,count(order_id) as order_num
from t_order t1
group by user_id

查询结果

step3:关联step1和step2的结果，得出最后结果

select
    t1.user_id
from
(
    select
    user_id,
    count(order_id) as order_num
    from t_order t1
    group by user_id
) t1
join
(select
    user_id
from
    (
        select
            user_id,
            product_id,
            sum(quantity) as product_u_nums
        from t_order
        group by user_id,product_id
        having sum(quantity) >=2
    ) t
group by user_id
) t2
on t1.user_id = t2.user_id
order by t1.order_num desc
limit 3

查询结果

4.数据准备

1.建表语句

CREATE TABLE t_order (
    order_id INT,
    user_id INT,
    product_id INT,
    quantity INT,
    purchase_time TIMESTAMP
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

2.数据插入语句

INSERT INTO t_order VALUES
(1, 1, 1001, 1, '2023-03-13 08:30:00'),
(2, 1, 1002, 1, '2023-03-13 10:45:00'),
(3, 1, 1001, 1, '2023-03-13 10:45:01'),
(4, 2, 1001, 3, '2023-03-13 14:20:00'),
(5, 3, 1003, 1, '2023-03-13 16:15:00'),
(6, 3, 1002, 1, '2023-03-13 12:10:00'),
(7, 3, 1001, 1, '2023-03-13 12:10:01'),
(8, 4, 1002, 2, '2023-03-13 09:00:00'),
(9, 4, 1003, 1, '2023-03-13 11:30:00'),
(10, 4, 1004, 3, '2023-03-13 13:40:00'),
(11, 4, 1001, 1, '2023-03-13 17:25:00'),
(12, 4, 1002, 2, '2023-03-13 15:05:00'),
(13, 4, 1004, 1, '2023-03-13 11:55:00');

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2023-03-14，如有侵权请联系 cloudcommunity@tencent.com 删除

sql