hive sql 提取中位数

在hue上做一个当天价格与前四天中位数的pricegap，代码如下：

 select  dt,
        pro_link,
        fnl_price,
        previous_price,
        percentile(previous_price, 0.5) over(partition by pro_link order by dt asc ROWS BETWEEN 4 preceding and 1 preceding)  median,
        fnl_price-percentile(previous_price, 0.5) over(partition by pro_link order by dt asc ROWS BETWEEN 4 preceding and 1 preceding)  pricegap
from (
select dt,
        pro_link,
        fnl_price,
        lead(fnl_price,1,fnl_price) over (partition by pro_link order by dt desc ) previous_price
from dwd_zdm.dwd_zdm_hive_product_pro_info
where dt >='2021-08-01'
and pro_link='item.jd.com/100017605364.html'
and fnl_price is not null and fnl_price >1 and fnl_price <30000
order by pro_link,dt desc) 
order by dt