HiveSQL 面试题 - 统计每个商品销售首年的年份，首销年销售数量和销售总额_al利用hivesql计算每一种商品的订单总金额-CSDN博客

本文链接：https://blog.csdn.net/SUNK2014/article/details/130657050

文章讲述了如何使用SQL查询订单明细表，通过窗口函数rank()找出每个商品的销售首年，并统计首年的销售数量和销售总额。首先按商品ID和年份排序并排名，然后筛选出排名为1的首销年记录，最后进行分组汇总。

摘要由CSDN通过智能技术生成

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)	year (销售首年年份)	order_num (首年销量)	order_amount <decimal(16,2)> (首年销售金额)
1	2020	2	4000.00
2	2020	26	260.00
3	2020	1	5000.00
4	2021	53	318000.00
5	2021	242	121000.00
6	2020	6	12000.00
7	2020	35	3500.00
8	2020	59	35400.00
9	2021	194	194000.00
10	2020	94	9400.00
11	2020	95	4750.00
12	2020	83	1660.00

2 解答

思路

这一题与 每日新增用户 相比较，都有 “第一” 这个要求，不同的是新增用户的 “第一” 指的是首次登录，有且仅有一次，但是商品在首销的 “第一年” 内可能会有多次售出记录，所以在进行排名的时候，需要将第一年所有的记录都保存下里，所以需要使用 rank() 或 dense_rank() 进行排名。

排名完成之后，在外层 SQL 中限定排名为 1 的值，并且根据 sku_id 和首销年份进行分组，统计出销售数量和销售总额即可。

实现

1 开窗，使用 rank() 函数针对每一个 sku_id 按照 substr(create_date,1,4) 进行排名

SELECT 
	sku_id,
    substr(create_date,1,4) year,
    price,
    sku_num,
    rank() over(PARTITION BY sku_id ORDER BY substr(create_date,1,4)) rk
FROM order_detail

sku_id	year	price	sku_num	rk
3	2020	5000.00	1	1
3	2021	5000.00	1	2
3	2021	5000.00	1	2
4	2021	6000.00	1	1
4	2021	6000.00	1	1
6	2021	2000.00	1	2
6	2021	2000.00	1	2
4	2021	6000.00	10	1
8	2021	600.00	11	3

2 根据上述中间结果，限定 rk = 1 筛选出所需要的首销年的记录，再按照 sku_id、year 进行分组，统计每个商品的首销年的销售数量和销售总额

SELECT sku_id, year, sum(sku_num) order_num, sum(price * sku_num) order_amount FROM (
    SELECT 
        sku_id,
        substr(create_date,1,4) year,
        price,
        sku_num,
        rank() over(PARTITION BY sku_id ORDER BY substr(create_date,1,4)) rk
    FROM order_detail
) t1 WHERE rk = 1 GROUP BY sku_id, year order by cast(sku_id as int);

sku_id	year	order_num	order_amount
1	2020	2	4000.00
2	2020	26	260.00
3	2020	1	5000.00
4	2021	53	318000.00
5	2021	242	121000.00
6	2020	6	12000.00
7	2020	35	3500.00
8	2020	59	35400.00
9	2021	194	194000.00
10	2020	94	9400.00
11	2020	95	4750.00
12	2020	83	1660.00