窗口函数 边界值注意事项
例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