题目需求
从订单详情表 (order_detail) 中，求出商品连续售卖的时间区间
结果如下（截取部分）：
| sku_id (商品id) | start_date (起始时间) | end_date (结束时间) |
|---|---|---|
| 1 | 2024-09-27 | 2024-10-01 |
| 1 | 2024-10-03 | 2024-10-08 |
| 9 | 2024-10-02 | 2024-10-03 |
| 9 | 2024-10-05 | 2024-10-08 |
| 10 | 2024-09-30 | 2024-10-08 |
| 13 | 2024-10-02 | 2024-10-06 |
| 13 | 2024-10-08 | 2024-10-08 |
需要用到的表：
订单明细表：order_detail
| order_detail_id (订单明细id) | order_id (订单id) | sku_id (商品id) | create_date (下单日期) | price (商品单价) | sku_num (商品件数) |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2024-09-28 | 3000.00 | 2 |
| 2 | 1 | 3 | 2024-09-28 | 6000.00 | 5 |
| 15 | 10 | 4 | 2023-10-01 | 9000.00 | 1 |
| 19 | 10 | 5 | 2023-10-01 | 600.00 | 24 |
| 28 | 10 | 6 | 2023-10-01 | 3000.00 | 5 |
select sku_id,
date_add(date_diff, min_rk) start_date,
date_add(date_diff, min_rk + cnt - 1) end_date
from (select sku_id,
date_diff,
min(rk) min_rk,
count(*) cnt
from (select sku_id,
create_date,
date_sub(create_date, rk) date_diff,
rk
from (select sku_id,
create_date,
row_number() over (partition by sku_id order by create_date) rk
from order_detail
) t1
) t2
group by sku_id,
date_diff
) t3
856
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
