with base_data as(
SELECT
'1' as gid, 4 as price, '2023-08-01' as dt
UNION all
SELECT
'1' as gid , 3 as price, '2023-08-02' as dt
UNION all
SELECT
'1' as gid , 2 as price, '2023-08-03' as dt
UNION all
SELECT
'1' as gid , 3 as price, '2023-08-04' as dt
)
select
gid,price,dt,days as day -- 最近day天最小值
from (
select
gid,price,dt,b_price,dt2,rn,
COUNT() over(partition by gid,price,dt,res) as days
from (
select
gid,price,dt,b_price,dt2,rn,date_add('day',-rn,CAST(dt2 AS date)) as res
from (
SELECT
a.gid,a.price,a.dt,b.price as b_price,b.dt as dt2,
row_number() over(partition by a.gid,a.dt order by b.dt) as rn
from base_data as a
left join base_data as b
on a.dt >= b.dt
where a.price <= b.price
) as t1
) as t2
) as t3
where dt = dt2
-- 不等值关联，过滤大于这个价格的数据，最后计算连续天数，最后再取最近的连续数据。
