窗口函数 边界值注意事项

窗口函数 边界值注意事项

例1:将“截止到之后1行”作为汇总对象。
rows BETWEEN CURRENT ROW AND 1 FOLLOWING

SELECT product_id, product_name, sale_price,
avg (sale_price) over (order by product_id rows BETWEEN CURRENT ROW AND 1 FOLLOWING) as moving_avg
 FROM Product;

例2:将“1 PRECEDING”（之前 1 行）和“1 FOLLOWING”（之后 1 行）的区间作为汇总对象。

SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
 FROM Product;

例3：将“截止到之前1行”作为汇总对象。此时可以直接写成ROWS 1 PRECEDING

SELECT product_id, product_name, sale_price,
avg (sale_price) over (order by product_id rows 1 preceding) as moving_avg
 FROM Product;

https://help.aliyun.com/knowledge_detail/120397.html