数据库的窗口函数与高级分析功能

一、描述
窗口函数（Window Function）是数据库中对一组相关行（称为"窗口"）进行计算的函数，与聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行返回一个计算结果。它常用于排名、移动平均、累计求和等场景，是复杂数据分析的核心工具。

二、关键概念

1. 窗口函数与聚合函数的区别

   • 聚合函数（如SUM、AVG）将多行合并为一行结果。
   • 窗口函数保留所有行，并为每一行附加计算结果。

2. 核心语法结构

   FUNCTION_NAME(参数) OVER (  
       [PARTITION BY 分组字段]  
       [ORDER BY 排序字段]  
       [ROWS/RANGE 窗口范围]  
   )  
   

   • PARTITION BY：将数据划分为多个窗口，类似GROUP BY但不聚合。
   • ORDER BY：确定窗口内行的顺序。
   • ROWS/RANGE：定义计算范围（如"当前行及前两行"）。

三、常见窗口函数分类

1. 排名函数

   • ROW_NUMBER()：连续编号（相同值不同号）。
   • RANK()：相同值排名相同，但跳过后续序号（如1,1,3）。
   • DENSE_RANK()：相同值排名相同，不跳号（如1,1,2）。

2. 分布函数

   • PERCENT_RANK()：计算行的百分比排名。
   • CUME_DIST()：计算累计分布。

3. 前后值函数

   • LAG(列, 偏移量)：访问当前行之前的某行数据。
   • LEAD(列, 偏移量)：访问当前行之后的某行数据。

4. 聚合类窗口函数

   • SUM(列) OVER()：对指定窗口求和。
   • AVG(列) OVER()：计算窗口平均值。

四、具体示例与步骤
场景：销售表sales包含日期、销售人员、销售额，需计算每个销售人员的累计销售额和移动平均。

1. 按人员分组计算累计销售额

   SELECT  
       日期,  
       销售人员,  
       销售额,  
       SUM(销售额) OVER (  
           PARTITION BY 销售人员  
           ORDER BY 日期  
           ROWS UNBOUNDED PRECEDING  
       ) AS 累计销售额  
   FROM sales;  
   

   • 步骤解析：
     • PARTITION BY 销售人员：每个销售人员单独计算窗口。
     • ORDER BY 日期：按日期排序确定累计顺序。
     • ROWS UNBOUNDED PRECEDING：窗口范围从第一行到当前行。

2. 计算3日移动平均

   SELECT  
       日期,  
       销售人员,  
       销售额,  
       AVG(销售额) OVER (  
           PARTITION BY 销售人员  
           ORDER BY 日期  
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  
       ) AS 移动平均  
   FROM sales;  
   

   • 窗口范围解释：
     • 2 PRECEDING：当前行及前两行。
     • 若数据不足3行，仅计算已有行的平均值。

3. 排名应用示例

   SELECT  
       销售人员,  
       销售额,  
       RANK() OVER (ORDER BY 销售额 DESC) AS 排名  
   FROM sales;  
   

   • 按销售额降序排名，相同销售额的行共享排名并跳过后续序号。

五、性能优化注意事项

1. 索引策略：对PARTITION BY和ORDER BY的字段建立索引，避免全表扫描。
2. 窗口范围选择：
   • ROWS按物理行数划分，效率高。
   • RANGE按逻辑值划分（如"最近7天"），可能需排序。
3. 避免嵌套窗口函数：部分数据库不支持嵌套，可改用CTE（公共表表达式）分步计算。

六、适用场景

• 趋势分析（如累计值、移动平均）。
• 排名与分组对比（如部门内薪资排名）。
• 数据间隔处理（如计算同比/环比）。

通过灵活组合PARTITION BY、ORDER BY和窗口范围，可高效解决复杂分析需求，同时保持数据的原始粒度。