SQL窗口函数的入门与进阶：解析SQL窗口函数的强大功能-SQL-PHP中文网

SQL窗口函数的入门与进阶：解析SQL窗口函数的强大功能

絕刀狂花

发布： 2025-08-07 15:30:03

原创

338人浏览过

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窗口函数的强大功能

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、工资，以及该部门的工资总额、平均工资和该员工工资占部门工资的百分比。

SpeakingPass-打造你的专属雅思口语语料

使用chatGPT帮你快速备考雅思口语，提升分数

查看详情

案例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查询中游刃有余，解决各种复杂的分析需求。