2023-08-21 22:30 字节跳动_大数据开发工程师
关注
SQL练习，求商品价格为最近X天内最低价
#SQL面试#大佬们来帮帮孩子吧，写不出来浑身难受
商品表goods有三行数值：gid,price,date。表示商品每天的价格，求商品id在date日期时的价格price为最近X天内的最低价。
例：
gid,price,date
1, 4, 2023-08-01
1, 3, 2023-08-02
1, 2, 2023-08-03
1, 3, 2023-08-04
输出：
gid,price,date,X
1, 4, 2023-08-01,1（近一天最低价）
1, 3, 2023-08-02,2（近两天最低价）
1, 2, 2023-08-03,3（近三天最低价）
1, 3, 2023-08-04,1（近一天最低价）
商品表goods有三行数值：gid,price,date。表示商品每天的价格，求商品id在date日期时的价格price为最近X天内的最低价。
例：
gid,price,date
1, 4, 2023-08-01
1, 3, 2023-08-02
1, 2, 2023-08-03
1, 3, 2023-08-04
输出：
gid,price,date,X
1, 4, 2023-08-01,1（近一天最低价）
1, 3, 2023-08-02,2（近两天最低价）
1, 2, 2023-08-03,3（近三天最低价）
1, 3, 2023-08-04,1（近一天最低价）
全部评论
心态不重要
门头沟学院 数据分析师
移动窗口函数，范围是之前所有天到今天，筛选最低价格min()over()
程序员小陶
青岛科技大学 Java
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
-- 不等值关联，过滤大于这个价格的数据，最后计算连续天数，最后再取最近的连续数据。
叫啥你别问
哈尔滨理工大学 Java
是不是这个意思
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
wolfiefff
门头沟学院 全栈开发
有难度，没想出来怎么搞
牛客437550935号
CentraleSupélec 数据分析师
一共要返回多少天的最低价呢？
Janet_hsh
对外经济贸易大学 数据分析师
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
相关推荐
