窗口函数

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），但结果集中仍包含每位员工的记录。

注意事项

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

流水表提取最新状态

通过窗口函数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