找出连续三天销量增长50%的日期

连续三天销量增长50%

建表

create table date_sales
(
date_id date,
sales int
)

随机输入一些可以满足条件的数据

with first_data as (
select date_id,sales,row_number()over(order by date_id) as id_num
from date_sales
where date_id > ‘2018-01-01’ # select min(date_id) from date_sales
)
,
two_data as (
select date_id,sales,row_number()over(order by date_id) as id_num
from date_sales
)
,
three_data as (
select fd.date_id,fd.sales, 1.0*(fd.sales - td.sales)/ td.sales as sales_rate
from two_data td
join first_data fd on td.id_num = fd.id_num
)
,
four_date as (
select date_id,sales,case when sales_rate >= 0.5 then 1 else 0 end as is_meet

from three_data
)
,
five_data as (
select date_id,sales, dateadd(day,- row_number()over(order by date_id asc),date_id) as id_num
from four_date
where is_meet = 1
)
select id_num,min(date_id) as start_time,max(date_id) as end_time
from five_data
group by id_num
having count(id_num) >= 3

没有验证数据的运行速度如何，只是给出了结果