原题链接:http://practice.atguigu.cn/#/question/29/desc?qType=SQL
题目需求
从订单详情表(order_detail)中,求出商品连续售卖的时间区间
期望结果如下(截取部分):
sku_id(商品id) | start_date(起始时间) | end_date(结束时间) |
---|---|---|
1 | 2021-09-27 | 2021-09-27 |
1 | 2021-09-30 | 2021-10-01 |
1 | 2021-10-03 | 2021-10-08 |
10 | 2021-10-02 | 2021-10-03 |
10 | 2021-10-05 | 2021-10-08 |
11 | 2021-10-02 | 2021-10-08 |
12 | 2021-09-30 | 2021-09-30 |
12 | 2021-10-02 | 2021-10-06 |
12 | 2021-10-08 | 2021-10-08 |
需要用到的表:
订单明细表: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 |
解题思路
本题与第15题类似,首先通过row_number()
获取某日期在商品所有售卖日期中的排序值rn
,随后通过date_sub(create_date,rn)
进行分组(若为连续日期,则date_sub(create_date,rn)
相同),分组内日期则为连续登录日期
SELECT sku_id,
MIN(create_date) AS start_date,
MAX(create_date) AS end_date
FROM
(
SELECT sku_id,
create_date,
ROW_NUMBER() OVER (PARTITION BY sku_id ORDER BY create_date) AS rn
FROM
(
SELECT sku_id,
create_date
FROM order_detail
GROUP BY sku_id,
create_date
) t1
) t2
GROUP BY sku_id,
date_sub(create_date,rn)