SQL练习，求商品价格为最近X天内最低价

移动窗口函数，范围是之前所有天到今天，筛选最低价格min()over()

读了3遍才读懂。不是输出价格，而是输出“为最近X天内的最低价。” 这里面的 X 所以这里面还有一个“状态连续性”，没想到咋写。

SELECT gid,price,date,row_number() OVER(PARTITION BY gid,n1) X from( SELECT *,price-(min(price) over(PARTITION BY gid order by date rows BETWEEN unbounded preceding AND CURRENT ROW)) n1 from goods ORDER by gid,date)a

一共要返回多少天的最低价呢？

有难度，没想出来怎么搞

是不是这个意思 With base as ( Select id, price, date lag(price)over(partition by id order by price) as prcie1, lag(date)over(partition by id order by date) as date1 From a ) Select id,max(case when price>prcie1 then datediff(date,date2) then 0 end) From base Group by id

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