HiveSQL 面试题 - 从订单表中查找符合指定购买条件的用户

1 需求

从订单明细表(order_detail)中查询出所有购买过商品 1 和商品 2，但是没有购买过商品 3 的用户。

• 订单信息表 order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

• 订单明细表 order_detail

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
22	10	4	2020-10-02	6000.00	1
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

• 期望结果如下

user_id
103
105

2 实现

• 思路分析

由于订单信息分为了订单信息表和订单明细表，所以需要先将两张表的数据关联起来，取出需要的 user_id 和 sku_id 信息。

购买过商品 1 和商品 2，但是没有购买过商品 3 的用户，可以将每个用户所有购买的商品聚合成集合，再判断集合中是否包含指定的商品就可以找出符合条件的用户。

• 实现

1 关联数据表，查出每个用户购买过的商品

SELECT
  user_id,
  collect_set (sku_id) sku_set
from
  order_detail od
  inner join order_info oi on oi.order_id = od.order_id
group by
  user_id

user_id	sku_set
101	[“1”,“3”,“4”,“5”,“7”,“8”,“9”,“12”]
1010	[“1”,“2”,“3”,“6”,“7”,“8”,“10”,“11”,“12”]
102	[“1”,“2”,“3”,“4”,“6”,“7”,“8”,“9”,“10”,“11”,“12”]
103	[“1”,“2”,“4”,“5”,“6”,“8”,“10”,“11”,“12”]
104	[“1”,“3”,“4”,“5”,“6”,“7”,“10”,“11”,“12”]
105	[“1”,“2”,“4”,“5”,“6”,“7”,“8”,“9”,“11”,“12”]
106	[“1”,“2”,“3”,“4”,“5”,“7”,“8”,“9”,“10”,“11”,“12”]
107	[“1”,“2”,“3”,“4”,“5”,“6”,“7”,“8”,“9”,“10”,“11”,“12”]
108	[“1”,“2”,“3”,“4”,“5”,“6”,“8”,“9”,“10”,“11”]
109	[“1”,“2”,“3”,“4”,“5”,“6”,“8”,“10”,“11”,“12”]

2 使用商品集合筛选符合条件的用户

select user_id
from (SELECT user_id,
             collect_set(sku_id) sku_set
      from order_detail od
           inner join order_info oi on oi.order_id = od.order_id
      group by user_id) t1
where array_contains(sku_set, '1')
  and array_contains(sku_set, '2')
  and NOT array_contains(sku_set, '3');

user_id
103
105