mysql窗口函数

mysql官方说明

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

最后编辑于：2020.06.23 15:30:44