sql窗口函数是一种在不减少行数的情况下对“窗口”内数据进行计算的sql功能，与group by不同，group by会将数据分组并折叠为单行，而窗口函数保留每行原始信息的同时进行聚合或排名等计算。1. 窗口函数使用over()定义窗口范围，可结合partition by分组、order by排序；2. 常用函数包括sum()、avg()、rank()、dense_rank()、row_number()、lag()、lead()等；3. 案例中可计算部门工资占比、用户订单排名、相邻订单时间间隔；4. 性能优化需避免不必要的排序、合理使用分区、创建索引、简化表达式并选择合适函数；5. 主流数据库如postgresql、mysql 8.0+、sql server、oracle均支持窗口函数，但功能和语法略有差异。掌握窗口函数能显著提升复杂分析查询的效率和可读性。
SQL窗口函数，简单来说，就是在SQL查询中，让你能针对一个“窗口”（也就是一组相关的行）进行计算，而不用像GROUP BY那样把行折叠起来。它既能进行聚合计算，又能保留原始行的详细信息，非常强大。
SQL窗口函数能让你在查询结果中进行复杂的分析和计算，比如计算累计和、排名、移动平均值等等，而无需使用子查询或临时表。
什么是SQL窗口函数？和GROUP BY有什么区别？
SQL窗口函数，与其说是函数，不如说是一种SQL的扩展功能。它允许你对查询结果集中的“窗口”（一组相关的行）执行计算。这个“窗口”是相对于当前行的，你可以定义窗口的大小和内容。
那么，它和GROUP BY有什么区别呢？这是个关键问题。GROUP BY会将结果集按照指定的列进行分组，然后对每个组进行聚合计算，最终每个组只返回一行。而窗口函数，则是在不改变原始结果集行数的情况下，为每一行计算一个值。
举个例子，假设你有一个订单表，包含订单ID、客户ID和订单金额。
- 如果你想知道每个客户的总订单金额，你会使用GROUP BY。
- 如果你想知道每个订单的金额占该客户总订单金额的百分比，你就会使用窗口函数。
使用GROUP BY，你会得到每个客户的总订单金额，但失去了每个订单的详细信息。而使用窗口函数，你既能得到每个订单的金额，又能得到该客户的总订单金额，并计算出百分比。
简单来说，GROUP BY是聚合，会减少行数；窗口函数是计算，不会减少行数。
窗口函数的基本语法和常用关键字
窗口函数的基本语法如下：
函数名(参数) OVER (PARTITION BY 列名 ORDER BY 列名 ASC/DESC)
- 函数名(参数)：这是你要执行的聚合或排名函数，比如SUM, AVG, RANK, ROW_NUMBER等等。
- OVER()：这是窗口函数的关键，它定义了窗口的范围。
- PARTITION BY 列名：将结果集按照指定的列进行分区，每个分区就是一个窗口。如果没有PARTITION BY，则整个结果集就是一个窗口。
- ORDER BY 列名 ASC/DESC：在每个窗口内，按照指定的列进行排序。
一些常用的窗口函数关键字：
- SUM(列名) OVER(…): 计算窗口内指定列的总和。
- AVG(列名) OVER(…): 计算窗口内指定列的平均值。
- RANK() OVER(…): 计算窗口内每一行的排名，排名相同会跳跃。
- DENSE_RANK() OVER(…): 计算窗口内每一行的排名，排名相同不会跳跃。
- ROW_NUMBER() OVER(…): 为窗口内的每一行分配一个唯一的行号。
- LAG(列名, offset, default) OVER(…): 访问窗口内当前行之前的第offset行的值，如果不存在则返回default。
- LEAD(列名, offset, default) OVER(…): 访问窗口内当前行之后的第offset行的值，如果不存在则返回default。
窗口函数实战：案例分析与代码示例
让我们通过几个实际的案例来深入理解窗口函数。
案例1：计算每个部门的工资总额和平均工资，并显示每个员工的工资和部门工资占比。
假设我们有一个员工表
employees
，包含
employee_id
,
department_id
, 和
salary
字段。
SELECT employee_id, department_id, salary, SUM(salary) OVER (PARTITION BY department_id) AS department_total_salary, AVG(salary) OVER (PARTITION BY department_id) AS department_average_salary, salary / SUM(salary) OVER (PARTITION BY department_id) AS salary_percentage FROM employees;
这个查询会返回每个员工的ID、部门ID、工资，以及该部门的工资总额、平均工资和该员工工资占部门工资的百分比。
案例2：计算每个用户的订单总数，并按照订单总数进行排名。
假设我们有一个订单表
orders
，包含
order_id
和
user_id
字段。
SELECT user_id, COUNT(order_id) AS total_orders, RANK() OVER (ORDER BY COUNT(order_id) DESC) AS order_rank FROM orders GROUP BY user_id;
这个查询会返回每个用户的ID、订单总数，以及按照订单总数的排名。注意这里使用了GROUP BY，因为我们需要先计算每个用户的订单总数，然后再进行排名。
案例3：计算每个用户相邻两次订单的时间间隔。
假设我们有一个订单表
orders
，包含
order_id
,
user_id
, 和
order_date
字段。
SELECT order_id, user_id, order_date, LAG(order_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY order_date) AS previous_order_date, order_date - LAG(order_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY order_date) AS time_difference FROM orders;
这个查询会返回每个订单的ID、用户ID、订单日期，以及上一次订单的日期和两次订单的时间间隔。这里使用了LAG函数来访问窗口内上一行的值。
这些案例只是窗口函数强大功能的冰山一角。熟练掌握窗口函数，能让你在SQL查询中游刃有余，解决各种复杂的分析需求。
窗口函数的性能优化技巧
窗口函数虽然强大，但如果使用不当，也可能导致性能问题。以下是一些性能优化技巧：
- 避免不必要的排序: ORDER BY 子句会影响性能，如果不需要排序，就不要使用。
- 合理使用PARTITION BY: PARTITION BY 子句会将数据分成多个分区，如果分区过多，也会影响性能。
- 索引优化: 如果窗口函数中使用了ORDER BY 子句，可以考虑在ORDER BY 列上创建索引。
- 避免在窗口函数中使用复杂的表达式: 复杂的表达式会增加计算量，影响性能。
- 合理选择窗口函数: 不同的窗口函数性能不同，选择合适的窗口函数可以提高性能。
例如，在计算排名时，如果不需要考虑排名相同的情况，可以使用ROW_NUMBER()函数，而不是RANK()或DENSE_RANK()函数，因为ROW_NUMBER()函数的性能通常更好。
不同数据库系统对窗口函数的支持情况
虽然SQL标准定义了窗口函数，但不同的数据库系统对窗口函数的支持程度有所不同。
- PostgreSQL: 对窗口函数的支持非常完善，几乎支持所有的SQL标准窗口函数，并且性能也很好。
- mysql 8.0+: MySQL 8.0开始支持窗口函数，但支持的函数数量和功能不如PostgreSQL。
- SQL Server: 对窗口函数的支持也比较好，支持大部分SQL标准窗口函数。
- oracle: 对窗口函数的支持也比较完善。
在使用窗口函数时，需要注意不同数据库系统的语法差异和支持程度。建议查阅相应数据库系统的官方文档，了解详细的支持情况。
总的来说，SQL窗口函数是SQL查询中一个非常强大的工具，能够让你在查询结果中进行复杂的分析和计算。掌握窗口函数，能让你写出更简洁、更高效的SQL查询语句。
