SQL 窗口函数简介
窗口函数对一组行进行操作,并为基础查询中的每一行返回一个值。 术语窗口描述了函数在其上进行操作的行集。 窗口函数使用窗口中各行的值来计算返回的值。
在查询中使用窗口函数时,请使用OVER()子句定义窗口。 OVER()子句(窗口定义)将窗口函数与其他分析和报告函数区分开。 一个查询可以包含具有相同或不同窗口定义的多个窗口函数。
OVER()子句具有以下功能:
- 定义窗口分区以形成行组。 (PARTITION BY子句)
- 对分区内的行进行排序。 (ORDER BY子句)
例如,以下查询使用AVG()窗口函数来计算第一季度员工的平均销售额:
select emp_name, dealer_id, sales, avg(sales) over() as avgsales from q1_sales;
+-----------------+------------+--------+-----------+
| emp_name | dealer_id | sales | avgsales |
+-----------------+------------+--------+-----------+
| Beverly Lang | 2 | 16233 | 13631 |
| Kameko French | 2 | 16233 | 13631 |
| Ursa George | 3 | 15427 | 13631 |
| Ferris Brown | 1 | 19745 | 13631 |
| Noel Meyer | 1 | 19745 | 13631 |
| Abel Kim | 3 | 12369 | 13631 |
| Raphael Hull | 1 | 8227 | 13631 |
| Jack Salazar | 1 | 9710 | 13631 |
| May Stout | 3 | 9308 | 13631 |
| Haviva Montoya | 2 | 9308 | 13631 |
+-----------------+------------+--------+-----------+
10 rows selected (0.213 seconds)
AVG()窗口函数对窗口中定义的行进行操作,并为每行返回一个值。 为了进行比较,您可以使用AVG()函数作为标准集合函数来运行查询:
select avg(sales) as avgsales from q1_sales;
+-----------+
| avgsales |
+-----------+
| 13630.5 |
+-----------+
1 row selected (0.131 seconds)
查询将返回指定列中所有值的平均值的一行,而不是返回每一行的值。
您还可以在查询中包括可选的PARTITION BY和ORDER BY子句。 PARTITION BY子句将窗口细分为多个分区。 ORDER BY子句定义结果集每个分区内的行的逻辑顺序。 窗口函数将应用于每个分区内的行,并根据顺序规范进行排序。
以下查询将AVG()窗口函数与PARTITION BY子句一起使用,以确定第一季度每个经销商的平均汽车销售量:
select emp_name, dealer_id, sales, avg(sales) over (partition by dealer_id) as avgsales from q1_sales;
+-----------------+------------+--------+-----------+
| emp_name | dealer_id | sales | avgsales |
+-----------------+------------+--------+-----------+
| Ferris Brown | 1 | 19745 | 14357 |
| Noel Meyer | 1 | 19745 | 14357 |
| Raphael Hull | 1 | 8227 | 14357 |
| Jack Salazar | 1 | 9710 | 14357 |
| Beverly Lang | 2 | 16233 | 13925 |
| Kameko French | 2