SQL查询之【开窗函数】

最新推荐文章于 2024-08-29 15:20:11 发布

Big_Ambation

最新推荐文章于 2024-08-29 15:20:11 发布

阅读量898

点赞数

文章标签：数据库 sql 后端大数据

本文链接：https://blog.csdn.net/weixin_51389291/article/details/134484845

版权

本文详细介绍了SQL中的开窗函数，包括SUM()OVER,Rank,ROW_NUMBER,Lag,和Lead。讲解了如何利用这些函数进行分组计算、排序和获取相邻行数据，以及它们在实际场景中的应用。

摘要由CSDN通过智能技术生成

SUM() OVER (PARTITION BY 分组字段名)

在 SQL 中，开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

打个比方，可以将开窗函数想象成一种 "透视镜"，它能够将我们聚焦在某个特定的分组，同时还能看到整体的全景。

本节我们先讲第一个开窗函数：sum over。

该函数用法为：

SUM(计算字段名) OVER (PARTITION BY 分组字段名)

示例

假设我们有订单表 orders，表格数据如下：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

现在，我们希望计算每个客户的订单总金额，并显示每个订单的详细信息。

示例 SQL 如下：

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROM
    orders;

查询结果：

order_id	customer_id	order_date	total_amount	customer_total_amount
1	101	2023-01-01	200	320
3	101	2023-01-10	120	320
2	102	2023-01-05	350	350
4	103	2023-01-15	500	500

在上面的示例中，我们使用开窗函数 SUM 来计算每个客户的订单总金额（customer_total_amount），并使用 PARTITION BY 子句按照customer_id 进行分组。从前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单总金额。

sum over order by

之前的教程中，我们讲到了 sum over 开窗函数，并且用它实现了分组统计。

本节教程我们将学习 sum over 函数的另一种用法：sum over order by，可以实现同组内数据的 累加求和 。

示例用法如下：

SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

举一个应用场景：老师在每个班级里依次点名，每点到一个学生，老师都会记录当前已点到的学生们的分数总和。

示例

假设我们有订单表 orders，表格数据如下：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

现在，我们希望计算每个客户的历史订单累计金额，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROM
    orders;

结果将是：

order_id	customer_id	order_date	total_amount	cumulative_total_amount
1	101	2023-01-01	200	200
3	101	2023-01-10	120	320
2	102	2023-01-05	350	350
4	103	2023-01-15	500	500

在上面的示例中，我们使用开窗函数 SUM 来计算每个客户的历史订单累计金额（cumulative_total_amount），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 order_date 进行排序。从结果的前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的历史订单累计金额；相比于只用 sum over，同组内的累加列名称

rank

Rank 开窗函数是 SQL 中一种用于对查询结果集中的行进行排名的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

Rank 开窗函数的常见用法是在查询结果中查找前几名（Top N）或排名最高的行。

Rank 开窗函数的语法如下：

RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column

其中，PARTITION BY 子句可选，用于指定分组列，将结果集按照指定列进行分组；ORDER BY 子句用于指定排序列及排序方式，决定了计算 Rank 时的排序规则。AS rank_column 用于指定生成的 Rank 排名列的别名。

示例

假设我们有订单表 orders，表格数据如下：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

现在，我们希望为每个客户的订单按照订单金额降序排名，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
    orders;

查询结果：

order_id	customer_id	order_date	total_amount	customer_rank
1	101	2023-01-01	200	1
3	101	2023-01-10	120	2
2	102	2023-01-05	350	1
4	103	2023-01-15	500	1

在上面的示例中，我们使用开窗函数 RANK 来为每个客户的订单按照订单金额降序排名（customer_rank），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 total_amount 从大到小进行排序。

可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单金额排名。

row_number

Row_Number 开窗函数是 SQL 中的一种用于为查询结果集中的每一行 分配唯一连续排名 的开窗函数。

它与之前讲到的 Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。

Row_Number 开窗函数的语法如下（几乎和 Rank 函数一模一样）：

ROW_NUMBER() OVER (
  PARTITION BY column1, column2, ... -- 可选，用于指定分组列
  ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS row_number_column

其中，PARTITION BY子句可选，用于指定分组列，将结果集按照指定列进行分组。ORDER BY 子句用于指定排序列及排序方式，决定了计算 Row_Number 时的排序规则。AS row_number_column 用于指定生成的行号列的别名。

示例

假设我们有订单表 orders，表格数据如下：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

现在，我们希望为每个客户的订单按照订单金额降序排列，并且分配一个 row_number 编号，示例 SQL 语句如下：

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS row_number
FROM
    orders;

结果将是：

order_id	customer_id	order_date	total_amount	row_number
4	103	2023-01-15	500	1
2	102	2023-01-05	350	1
1	101	2023-01-01	200	1
3	101	2023-01-10	120	2

在上面的示例中，我们使用开窗函数 ROW_NUMBER 为每个客户的订单按照订单金额降序排列，并为每个订单分配了一个编号（row_number），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 total_amount 进行排序。

ROW_NUMBER和rank函数的区别

ROW_NUMBER和RANK都是窗口函数，用于在查询结果中为每一行分配一个序号。它们的区别主要在于对于相同值的处理方式：

ROW_NUMBER函数：ROW_NUMBER函数为每一行分配一个唯一的、连续递增的整数值。当有多行具有相同的排序值时，它们将被赋予不同的行号。因此，最终结果可能会产生空位。
RANK函数：RANK函数为每一行分配一个排名，如果有多行具有相同的排序值，则它们将获得相同的排名，并且下一个排名将会跳过相应的位置。这意味着，如果有两个行具有相同的排序值并且都获得了第一名，那么下一个行将会获得第三名。

示例

假设我们有以下订单表（orders）：

order_id  customer_id  order_total
1         A            100
2         B            200
3         C            150
4         D            200
5         E            150

如果我们使用ROW_NUMBER函数对订单按照订单总额进行排序，查询结果如下：

SELECT order_id, customer_id, order_total, ROW_NUMBER() OVER (ORDER BY order_total) AS row_number
FROM orders;

order_id  customer_id  order_total  row_number
1         A            100          1
3         C            150          2
5         E            150          3
2         B            200          4
4         D            200          5

可以看到，ROW_NUMBER按照订单总额的升序为每一行分配了唯一的行号。

如果我们改用RANK函数进行相同的排序，查询结果如下：

SELECT order_id, customer_id, order_total, RANK() OVER (ORDER BY order_total) AS rank
FROM orders;

order_id  customer_id  order_total  rank
1         A            100          1
3         C            150          2
5         E            150          2
2         B            200          4
4         D            200          4

在这种情况下，RANK函数将相同的订单总额的行赋予了相同的排名，并跳过了下一个位置。

总结

ROW_NUMBER函数为每一行分配唯一的行号，而RANK函数为具有相同排序值的行分配相同的排名，并跳过相应位置。根据你的具体需求，选择合适的函数来满足你的要求。

lag / lead

开窗函数 Lag 和 Lead 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。

1）Lag 函数

Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。

Lag 函数的语法如下：

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

column_name：要获取值的列名。
offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
default_value：可选参数，用于指定当没有前一行时的默认值。
PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

2）Lead 函数

Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。

Lead 函数的语法如下：

LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

column_name：要获取值的列名。
offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推。
default_value：可选参数，用于指定当没有后一行时的默认值。
PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

示例

以下是一个示例，假设我们有一个学生成绩表scores，其中包含学生的成绩和考试日期：

student_id	exam_date	score
101	2023-01-01	85
101	2023-01-05	78
101	2023-01-10	92
101	2023-01-15	80

现在我们想要查询每个学生的考试日期和上一次考试的成绩，以及下一次考试的成绩，示例 SQL 如下：

SELECT 
    student_id,
    exam_date,
    score,
    LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
    LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
    scores;

结果将是：