窗口函数sql使用总结

一、开窗

基础知识：窗口分析函数
（1）LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）；

（2）LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）；

（3）FIRST_VALUE(col） 取分组内排序后，截止到当前行，第一个值；

（4）LAST_VALUE(col） 取分组内排序后，截止到当前行，最后一个值；

1、查询上一行数据的值。

select * ,lag(product_num,1,null) over(order by save_date asc) as pre_product_num from test_product 

 2、开窗函数，每行数据记录最大值、最小值、sum 而不是用group by。  

用窗口函数，可以查字段所有信息，group by 的select只能是分组字段信息，有些场景很有用

按产品类目分组，查询平均值、最大值、最小值等等

select *,  avg(price) over ( PARTITION BY category_name ) AS avg_price,sum(amount) over(PARTITION BY category_name)，lag(product_num,1,null) over(order by save_date asc) as pre_product_num

from product

二、rows between的基本使用

基础知识

 rows between

• preceding：往前
• following：往后
• current row：当前行
• unbounded：起点
• unbounded preceding：表示从前面的起点
• unbounded following：表示到后面的终点

1、查询前n条数据平局值

    select *,avg(product_num) over(order by save_date desc rows BETWEEN unbounded preceding and current row) as avg_n  from test_product

2、查询当前行数据前2条到当前行数的和

        select *,sum(product_num) over(order by save_date desc rows BETWEEN 2 preceding and current row) as sum_n from test_product

3、查询当前行数据之后的所有行数据的和

select *,sum(product_num) over(order by save_date desc rows BETWEEN current row and unbounded following) as sum_n from test_product

4、查询当前行前1行后1行当前行，3行数据的和

        select *,sum(product_num) over(order by save_date desc rows BETWEEN  1 preceding and 1 following) as sum_n from test_product