HiveSQL 面试题 - 统计每种商品销售件数最多的日期以及当日销量

1 需求

从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。

• 订单明细表 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

• 期望结果

sku_id （商品id）	create_date （销量最高的日期）	sum_num （销量）
1	2021-09-30	9
2	2021-10-02	5800
3	2021-10-05	9
4	2021-10-07	10
5	2021-10-03	47
6	2021-10-03	8
7	2021-10-05	58
8	2020-10-08	59
9	2021-10-01	45
10	2020-10-08	94
11	2020-10-08	95
12	2021-10-03	20400

2 解答

• 思路

由于需要统计每一件商品的销售件数最多的日期，针对每一个 sku_id 进行统计，所以需要使用窗口函数对数据按照指定的队则进行排名，需要注意的时排名时需要进行倒序，并且相同的排名需要使用二级字段来确定先后。

• 实现

1 按照商品 + 日期进行分组，统计每个商品在每一天的总销量

SELECT sku_id,
       create_date,
       sum(sku_num) sum_num
from order_detail
group by sku_id, create_date;

sku_id	create_date	sum_num
1	2020-10-08	2
1	2021-09-27	2
1	2021-10-01	8
1	2021-10-02	9
1	2021-10-03	4
1	2021-10-04	5
1	2021-10-05	5
1	2021-10-06	8
1	2021-10-07	8
10	2020-10-08	94
10	2021-10-02	48
10	2021-10-03	69
10	2021-10-05	27
10	2021-10-06	37
10	2021-10-07	24

2 在步骤 1 的基础上，开窗进行排名，注意排序函数中的 order by sum_num desc,create_date asc

SELECT sku_id,
       create_date,
       sum_num,
       row_number() over (partition by sku_id order by sum_num desc,create_date asc) rn
from (SELECT sku_id,
      		  create_date,
             sum(sku_num) sum_num
      from order_detail
      group by sku_id, create_date) t1;

sku_id	create_date	sum_num	rn
1	2021-10-02	9	1
1	2021-10-01	8	2
1	2021-10-06	8	3
1	2021-10-07	8	4
1	2021-10-04	5	5
1	2021-10-05	5	6
1	2021-10-03	4	7
1	2020-10-08	2	8
1	2021-09-27	2	9
10	2020-10-08	94	1
10	2021-10-03	69	2
10	2021-10-02	48	3
10	2021-10-06	37	4
10	2021-10-05	27	5
10	2021-10-07	24	6
11	2020-10-08	95	1
11	2021-10-03	61	2
11	2021-10-06	46	3

3 生成排名之后，限制排名为 1 的记录即可得到结果

SELECT sku_id, create_date, sum_num
from (
         SELECT sku_id,
                create_date,
                sum_num,
                row_number() over (partition by sku_id order by sum_num desc,create_date asc) rn
         from (SELECT sku_id,
                      create_date,
                      sum(sku_num) sum_num
               from order_detail
               group by sku_id, create_date) t1
         ) t2
where rn = 1
order by sku_id, create_date, sum_num;

sku_id	sum_num	create_date
1	9	2021-10-02
2	5800	2021-10-02
3	9	2021-10-05
4	10	2021-10-07
5	47	2021-10-03
6	8	2021-10-03
7	58	2021-10-05
8	59	2020-10-08
9	45	2021-10-01
10	94	2020-10-08
11	95	2020-10-08
12	20400	2021-10-03