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 |