1.窗口函数的基本用法如下:
window_name() over ([partition_defintion]|[order_definition]|[frame_definition])
其中,over是关键字,用来指定函数执行的窗口范围,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下四种语法来设置窗口:
(1)window_function_name:给窗口指定一个别名,如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读。
(2)partition子句:窗口按照那些字段进行分组,窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上,按照order by的顺序分别生成从1开始的顺序编号。
(3)order by子句:按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用,也可以单独使用。上例中二者同时使用,如果没有partition子句,则会按照所有用户的订单金额排序来生成序号。
(4)frame子句:frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额,则可以设置如下frame子句来创建滑动窗口
摘自http://blog.sina.com.cn/s/blog_141b32d280102ym7h.html
2.窗口函数可以和聚类函数一起使用
可以解决许多分组求和的问题,并且可以带出来一些其他字段。
mysql> SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;
year
country
product
profit
total_profit
country_profit
2000
Finland
Computer
1500
7535
1610
2000
Finland
Phone
100
7535
1610
2001
Finland
Phone
10
7535
1610
2000
India
Calculator
75
7535
1350
2000
India
Calculator
75
7535
1350
2000
India
Computer
1200
7535
1350
2000
USA
Calculator
75
7535
4575
2000
USA
Computer
1500
7535
4575
2001
USA
Calculator
50
7535
4575
2001
USA
Computer
1200
7535
4575
2001
USA
Computer
1500
7535
4575
2001
USA
TV
100
7535
4575
2001
USA
TV
150
7535
4575
3.mysql专门提供了窗口函数
CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()
mysql> SELECT
year, country, product, profit,
ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
FROM sales;
year
country
product
profit
row_num1
row_num2
2000
Finland
Computer
1500
2
1
2000
Finland
Phone
100
1
2
2001
Finland
Phone
10
3
3
2000
India
Calculator
75
2
1
2000
India
Calculator
75
3
2
2000
India
Computer
1200
1
3
2000
USA
Calculator
75
5
1
2000
USA
Computer
1500
4
2
2001
USA
Calculator
50
2
3
2001
USA
Computer
1500
3
4
2001
USA
Computer
1200
7
5
2001
USA
TV
150
1
6
2001
USA
TV
100
6
7