窗口函数的SQL语法

1、窗口函数和聚合函数的区别

窗口函数包含了一个over子句，聚合函数会针对每一组数据返回一个结果，窗口函数会对每一行数据都会返回一个结果。

最简单的例句如下：

select emp_name ,sum(salary) over()  from  tab1;

关键字over 表明这是一个窗口函数，括号内为空，表示将所有数据作为一个分组进行汇总。

一个完整的窗口函数语法： 

sum(salary) over (

partition by ...

order by ...

frame_clause   --这里是限定窗口尺寸，对应的语句是： { rows | range} between frame_start  and  frame_end

）

其中frame_clause  部分的内容可以是：

frame_start 选项用于定义窗口的起始位置，可以指定以下：

unbounded preceding  --表示窗口从分区的第一行开始

n  preceding --表示窗口从当前行之前的第n行开始

current row --表示窗口从当前行开始

frame_end 选项用于定义窗口的结束位置，可以指定以下：

current row --表示窗口到当前行结束

n  following --表示窗口到当前行之后的第m行结束

unbounded following --表示窗口到分区的最后一行结束

举例：sum(amount) over (

partition by  product

order by ym

rows  between 2 preceding and 1 following    --窗口尺寸

）

案例分析1：移动平均值

select product , ym, amount, 

avg(amount) over(partition by product  order by ym rows between 2 preceding and current row) as “最近3月平均销售额”

from tab1 order by product ,ym;

案例分析2：查询最近5天累计转账超过100万的账户

select log_ts,from_user,total_amount 

from(

select log_ts,from_user,sum(amount) over(partition by from_user order by log_ts range interval '5' day preceding) as total_amount 

from  transfer_log where type='转账'

)t

where total_amount>=1000000;

排名窗口函数

row_number() 给每一行数据生成一个行号；

rank()  生成排名序号，有重复的话后续排名会产生跳跃；

dense_rank()  生成排名序号，有重复的话后续排名不会产生跳跃；

percent_rank() 函数按照百分比指定名次，如果有重复后续排名会产生跳跃；

cume_dist() 返回当前行在分区内的累计分布，也就是排名在当前行之前（包含当前行）所有数据所占的比率；例如：

select emp_name, salary, cume_dist() over(order by salary) as '累计占比'  from  employee;

ntile() 是将分区内的数据分成n等份，并返回当前行所在的分片位置；

select emp_name,hire_date, ntile(5) over(order by hire_date) as '分组位置' from employee; --分组位置为1的是最早入职的20%员工，分组位置为5的是最晚入职的20%员工。

取值窗口函数

lag 函数可以返回窗口内当前行之前的第N行数据；

lead 函数可以返回窗口内当前行之后的第N行数据；

first_value 返回窗口内第一行数据；

last_value 返回窗口内最后一行数据；

nth_value 返回窗口内第N行数据。

其中lag 和lead 函数不支持动态窗口大小，它们以整个分区作为分析的窗口。

案例分析：环比、同比分析

环比增长是本期数据与上期数据相比的增长，例如2022年12月的销售额与2022年11月的销售额相比的增长。以下语句统计了各种产品每个月的环比增长率：

select product, ym, amount, 

((amount - lag(amount,1) over (partition by product order by ym)) / lag(amount,1) over (partition by product order by ym) ) * 100  as '环比增长率'

from sales_monthly   order by  product, ym;

同比增长是指本期数据与上一年度或历史同期相比的增长，例如2022年12月的销售额与2021年12月的销售额相比的增长：

select product, ym, amount, 

((amount - lag(amount,12) over (partition by product order by ym)) / lag(amount,12) over (partition by product order by ym) ) * 100  as '同比增长率'

from sales_monthly   order by  product, ym;

案例分析：复合增长率

with s(product, ym, amount, first_amount, num)  as (

select product, ym, amount, 

first_value(amount) over(partition by product order by ym), row_number() over (partiton by product order by ym)

from sales_monthly

)

select product, ym, amount,

(power( 1.0*amount/first_amount, 1.0/nullif(num-1, 0)) - 1) * 100 as '月度符合增长率(%)'

from s   order by product,ym;

其中 power用于执行开方运算，其中1.0是为了避免由整数除法所导致的精度丢失问题，其中 1.0/nullif(num-1, 0) 代表开 nullif(num-1, 0) 次方；

以下语句统计了不同产品最高销售额、最低销售额以及第三高销售额所在的月份

select product, ym, amount, 

first_value(ym) over (partition by product order by amount desc   rows between unbounded preceding and unbounded following) as '最高销售额月份'，

last_value(ym) over (partition by product order by amount desc   rows between unbounded preceding and unbounded following) as '最低销售额月份',

nth_value(ym) over (partition by product order by amount desc   rows between unbounded preceding and unbounded following) as '销售额第三高月份'

from sales_monthly order by product,ym;

窗口函数能提供复杂报表功能，这些通过聚合函数和分组操作很难实现，而且窗口函数在各种数据库中实现几乎是一样的，具有很好的移植性。