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 -- 不等值关联，过滤大于这个价格的数据，最后计算连续天数，最后再取最近的连续数据。