SQL商品连续售卖的时间区间

订单明细表： order_detail
字段：order_id sku_id create_date price sku_num

需求：求商品连续售卖的时间区间

期望结果：
sku_id start_date end_date

 1 select sku_id,start_date,end_date from (
 2     select 
 3      sku_id
 4      ,afdate
 5      ,min(create_date)  start_date
 6      ,max(create_date)  end_date
 7      
 8      
 9      from (
10         select 
11               sku_id
12              ,create_date
13              ,date_sub(create_date,rn) afdate
14          
15          from (
16             select 
17              sku_id
18              ,create_date
19              ,row_number() over(partition by  sku_id order by create_date) rn 
20              
21              from (
22                 select distinct sku_id,create_date from order_detail
23             ) a 
24         ) b 
25     
26     )  c group by sku_id,afdate
27 
28 ) d

posted on 2025-06-21 18:25 北京的小乔阅读(5) 评论(0) 收藏举报