解锁SQL的秘密花园：窗口函数的全面解析

最新推荐文章于 2025-12-02 09:49:43 发布

原创最新推荐文章于 2025-12-02 09:49:43 发布 · 377 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #java

sql题目专栏收录该内容

5 篇文章

订阅专栏

解锁SQL的秘密花园：窗口函数的全面解析

在SQL的广阔天地中，窗口函数犹如一座神秘的花园，隐藏着无尽的宝藏。它们能够以惊人的效率处理复杂的数据计算，让数据分析变得更加优雅和高效。今天，让我们一起推开这扇大门，深入探索窗口函数的奥秘。

前置知识

在踏入这片神秘花园之前，你需要掌握一些基础知识：

SQL基础：熟悉基本的SQL查询语法，如SELECT、FROM、WHERE等。
聚合函数：了解常见的聚合函数，如SUM()、AVG()、COUNT()等。

窗口函数的概念

窗口函数（Window Functions）是一种能够在查询结果集中执行复杂计算的强大工具。与普通聚合函数不同，窗口函数不会将多行数据合并成一行，而是为每一行生成一个结果。这使得窗口函数在处理排名、累计和移动平均等问题时尤为强大。

窗口函数的结构

窗口函数的基本结构如下：

function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [frame_clause]
)

function_name：窗口函数的名称，如ROW_NUMBER()、RANK()、SUM()等。
expression：要计算的表达式。
PARTITION BY：可选，用于将结果集分区。
ORDER BY：可选，用于指定分区内的排序顺序。
frame_clause：可选，用于定义窗口框架。

窗口函数的实战应用

让我们通过几个实际例子，深入理解窗口函数的强大之处。

示例数据

假设我们有一个名为Sales的表，结构如下：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| salesperson | varchar |
| amount      | decimal |
+-------------+---------+

数据如下：

+----+------------+--------+
| id | salesperson| amount |
+----+------------+--------+
| 1  | Alice      | 100    |
| 2  | Bob        | 200    |
| 3  | Alice      | 150    |
| 4  | Bob        | 250    |
| 5  | Charlie    | 300    |
+----+------------+--------+

1. 累计求和

我们希望计算每个销售人员的累计销售额。

SELECT 
    id, 
    salesperson, 
    amount,
    SUM(amount) OVER (PARTITION BY salesperson ORDER BY id) AS cumulative_amount
FROM 
    Sales;

结果

+----+------------+--------+-----------------+
| id | salesperson| amount | cumulative_amount |
+----+------------+--------+-----------------+
| 1  | Alice      | 100    | 100             |
| 3  | Alice      | 150    | 250             |
| 2  | Bob        | 200    | 200             |
| 4  | Bob        | 250    | 450             |
| 5  | Charlie    | 300    | 300             |
+----+------------+--------+-----------------+

2. 排名计算

我们希望计算每个销售人员的销售额排名。

SELECT 
    id, 
    salesperson, 
    amount,
    RANK() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS rank
FROM 
    Sales;

结果

+----+------------+--------+------+
| id | salesperson| amount | rank |
+----+------------+--------+------+
| 3  | Alice      | 150    | 1    |
| 1  | Alice      | 100    | 2    |
| 4  | Bob        | 250    | 1    |
| 2  | Bob        | 200    | 2    |
| 5  | Charlie    | 300    | 1    |
+----+------------+--------+------+

3. 移动平均

我们希望计算每个销售人员的最近两笔交易的平均销售额。

SELECT 
    id, 
    salesperson, 
    amount,
    AVG(amount) OVER (PARTITION BY salesperson ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    Sales;

结果

+----+------------+--------+------------+
| id | salesperson| amount | moving_avg |
+----+------------+--------+------------+
| 1  | Alice      | 100    | 100        |
| 3  | Alice      | 150    | 125        |
| 2  | Bob        | 200    | 200        |
| 4  | Bob        | 250    | 225        |
| 5  | Charlie    | 300    | 300        |
+----+------------+--------+------------+