数据库SQL Server窗口函数、聚合函数

已于 2025-06-06 12:49:57 修改
阅读量758 收藏 12
点赞数 10
分类专栏： sql语言 sql server + mysql 文章标签： 数据库 大数据 人工智能 flask oracle python
于 2024-07-07 01:02:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_46530492/article/details/140238551
版权

文章目录

窗口函数

SQL Server中的窗口函数（也称为分析函数）是一组非常强大的SQL功能，**它们允许你在结果集的行上执行计算，而不需要将结果集分组为多个输出行。**窗口函数可以对一组行执行计算，这组行与当前行相关，被称为窗口。窗口函数可以执行排名、聚合、行号分配等操作，而无需改变查询结果中的行数。

<窗口函数> OVER (  
    PARTITION BY <列名> -- 可选，用于将结果集划分为多个分区  
    ORDER BY <列名> -- 可选，用于在每个分区内对行进行排序  
    ROWS|RANGE BETWEEN <开始范围> AND <结束范围> -- 可选，定义窗口帧  
)
窗口函数分类
  • 排名函数：如ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE(n)等，用于对行进行排名或分组。
  • 聚合函数：如SUM()、AVG()、MIN()、MAX()、COUNT()等，但在这里它们不是对整个结果集进行聚合，而是对每个窗口进行聚合。
  • 分析函数：如LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()等，用于访问窗口中的其他行数据。
窗口函数示例

假设我们有一个名为Employees的表，包含DepartmentID和Salary列，我们可以使用ROW_NUMBER()窗口函数来为每个部门内的员工按薪水排序并分配一个唯一的行号：

SELECT   
    DepartmentID,  
    Salary,  
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank  
FROM   
    Employees;

这个查询将结果集按DepartmentID分区，并在每个分区内按Salary降序排列，为每个分区内的行分配一个唯一的行号（SalaryRank）。

聚合函数示例

如果我们想计算每个部门内员工的平均薪水，但不想改变结果集中的行数（即仍然为每位员工显示数据），我们可以使用AVG()窗口函数：

SELECT   
    DepartmentID,  
    Salary,  
    AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgDepartmentSalary  
FROM   
    Employees;

这个查询将结果集按DepartmentID分区，并计算每个分区内薪水的平均值（AvgDepartmentSalary），但结果集中仍包含每位员工的记录。

LAG和LEAD

LAG和LEAD是在处理窗口函数时会用到的两个函数，它们能够让你访问表中多行数据，无需使用自连接。下面来详细了解一下这两个函数。

LAG函数

LAG函数的作用是获取当前行之前的行的数据。其语法格式如下：

LAG(column_name, offset, default_value) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
  • column_name：这是你想要获取值的列。
  • offset：表示要往前偏移的行数，默认值为1。
  • default_value：当偏移超出表的范围时返回的值，默认是NULL。

下面通过一个例子来更好地理解。假设有一个名为sales的表，其数据如下：

monthsales
January100
February150
March200

现在，我们使用LAG函数来获取前一个月的销售额：

SELECT 
    month,
    sales,
    LAG(sales, 1) OVER (ORDER BY month) AS previous_month_sales
FROM sales;

得到的结果如下：

monthsalesprevious_month_sales
January100NULL
February150100
March200150
LEAD函数

LEAD函数与LAG函数相反，它用于获取当前行之后的行的数据。其语法格式为：

LEAD(column_name, offset, default_value) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

参数的含义和LAG函数相同。

还是以sales表为例，我们使用LEAD函数来获取下一个月的销售额：

SELECT 
    month,
    sales,
    LEAD(sales, 1) OVER (ORDER BY month) AS next_month_sales
FROM sales;

结果如下：

monthsalesnext_month_sales
January100150
February150200
March200NULL
主要区别
  • LAG：是向后看，用于访问之前行的数据。
  • LEAD：是向前看，用于访问后续行的数据。

这两个函数在进行时间序列分析、计算增长率或者比较相邻行数据时非常实用。

注意事项
  • 窗口函数不会减少结果集中的行数，它们只是为每行添加了额外的计算列。
  • 窗口函数可以与GROUP BY子句结合使用，但通常它们用于那些不需要完全分组聚合的查询。
  • PARTITION BY子句是可选的，如果不使用，则整个结果集被视为一个单一的分区。
  • ORDER BY子句在窗口函数中通常是必需的，尤其是在使用排名函数或需要定义窗口帧时。然而，它仅用于定义窗口内的排序顺序，并不改变结果集的最终排序。

是的，在使用LAG函数计算变化率的基础上，可以通过子查询或临时表来简化计算逻辑，使查询更清晰。此外，要呈现百分号，可以使用字符串格式化函数将变化率转换为百分比形式。

使用子查询计算变化率并显示百分比

下面是一个使用子查询的示例，计算每月销售额的变化率并以百分比形式显示：

SELECT 
    month,
    sales,
    previous_month_sales,
    CONCAT(ROUND(((sales - previous_month_sales) / previous_month_sales) * 100, 2), '%') AS sales_change_percentage
FROM (
    SELECT 
        month,
        sales,
        LAG(sales, 1) OVER (ORDER BY month) AS previous_month_sales
    FROM sales
) AS subquery
WHERE previous_month_sales IS NOT NULL; -- 过滤掉第一个月，因为没有前一个月的数据
使用CTE（公共表表达式）替代子查询

CTE可以使查询更具可读性，尤其在处理复杂计算时：

WITH sales_with_previous AS (
    SELECT 
        month,
        sales,
        LAG(sales, 1) OVER (ORDER BY month) AS previous_month_sales
    FROM sales
)
SELECT 
    month,
    sales,
    previous_month_sales,
    CONCAT(ROUND(((sales - previous_month_sales) / previous_month_sales) * 100, 2), '%') AS sales_change_percentage
FROM sales_with_previous
WHERE previous_month_sales IS NOT NULL;
关键说明：

  1. 子查询/CTE的作用：

    • 先计算出previous_month_sales，将复杂的窗口函数逻辑封装在内部。
    • 外层查询只需专注于变化率的计算，避免了嵌套窗口函数的复杂性。

  2. 百分比格式化：

    • 使用CONCAT函数将计算结果与%符号拼接。
    • ROUND(..., 2)将结果保留两位小数。
    • 计算逻辑：((当前月销售额 - 上月销售额) / 上月销售额) * 100

  3. 处理NULL值：

    • 第一个月的previous_month_sales为NULL，通过WHERE子句过滤掉这些行。
示例结果：

假设sales表数据如下：

monthsales
January100
February150
March200

查询结果将显示：

monthsalesprevious_month_salessales_change_percentage
February15010050.00%
March20015033.33%
替代方案：临时表

如果需要多次使用计算结果，可以创建临时表：

-- 创建临时表存储带前月销售额的数据
CREATE TEMP TABLE sales_with_prev AS
SELECT 
    month,
    sales,
    LAG(sales, 1) OVER (ORDER BY month) AS previous_month_sales
FROM sales;

-- 查询临时表并计算变化率
SELECT 
    month,
    sales,
    previous_month_sales,
    CONCAT(ROUND(((sales - previous_month_sales) / previous_month_sales) * 100, 2), '%') AS sales_change_percentage
FROM sales_with_prev
WHERE previous_month_sales IS NOT NULL;

这种方法在处理大型数据集或需要多次计算时效率更高。

FIRST_VALUE和LAST_VALUE

FIRST_VALUE和LAST_VALUE属于窗口函数，它们能让你从窗口内的第一行或最后一行获取值。下面为你详细介绍这两个函数的功能、语法和使用案例。

FIRST_VALUE函数

FIRST_VALUE函数可返回窗口帧内的第一行值。其语法结构如下：

FIRST_VALUE(expression) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [frame_clause]
)
  • expression：指的是你想要获取值的列或者表达式。
  • PARTITION BY：用于将结果集按照指定的列进行分组。
  • ORDER BY：确定了窗口内的排序方式，这会影响到第一行和最后一行的定义。
  • frame_clause：对窗口帧的范围进行定义，默认情况下是从窗口的起始行到当前行。

下面通过一个例子来理解。假设有一个名为employees的表，记录了不同部门员工的工资情况：

departmentemployee_namesalary
ITAlice80000
ITBob75000
HRCharlie60000
HRDave65000

现在，我们使用FIRST_VALUE函数来找出每个部门工资最高的员工：

SELECT 
    department,
    employee_name,
    salary,
    FIRST_VALUE(employee_name) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS highest_paid_employee
FROM employees;

得到的结果如下：

departmentemployee_namesalaryhighest_paid_employee
ITAlice80000Alice
ITBob75000Alice
HRDave65000Dave
HRCharlie60000Dave
LAST_VALUE函数

LAST_VALUE函数与FIRST_VALUE函数相反，它返回窗口帧内的最后一行值。其语法结构为：

LAST_VALUE(expression) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [frame_clause]
)

参数的含义和FIRST_VALUE函数相同。不过需要注意的是，LAST_VALUE函数默认的窗口帧范围是从窗口的起始行到当前行，这意味着如果不明确指定窗口帧，它可能无法返回你预期的最后一行值。

还是以employees表为例，我们使用LAST_VALUE函数来找出每个部门工资最低的员工：

SELECT 
    department,
    employee_name,
    salary,
    LAST_VALUE(employee_name) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid_employee
FROM employees;

结果如下：

departmentemployee_namesalarylowest_paid_employee
ITAlice80000Bob
ITBob75000Bob
HRDave65000Charlie
HRCharlie60000Charlie
关键区别和注意事项

  1. 窗口帧的影响：

    • FIRST_VALUE函数默认会返回窗口内的第一行，一般能得到预期结果。
    • 而LAST_VALUE函数默认的窗口帧范围是到当前行，所以需要使用RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING来明确指定窗口帧范围，使其包含整个分区。

  2. 处理NULL值：

    • 如果第一行或最后一行的值是NULL，这两个函数会直接返回NULL。你可以结合COALESCE函数来处理这种情况。

  3. 替代方案：

    • 除了使用LAST_VALUE函数，你还可以通过反向排序然后使用FIRST_VALUE函数来实现相同的效果：
      FIRST_VALUE(employee_name) OVER (
    PARTITION BY department 
    ORDER BY salary ASC
) AS lowest_paid_employee

这两个函数在进行排名分析、比较极端值等场景中非常有用。

流水表提取最新状态

通过窗口函数ROW_NUMBER()对每家店铺进行分组，只提取更新时间最新的一条记录，之后再过滤valid_flag=1的店铺作为需要预警的店铺。

WITH RankedStores AS (
    SELECT  *,
        ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY update_time DESC) AS rn
    FROM table1
    WHERE type='通用' and platform = 'tmall')

   SELECT  STRING_AGG(store_id,',')
   FROM  RankedStores
WHERE rn = 1 and valid_flag = 1
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Cachel wood

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值