SQLite 的窗口函数（Window Functions）如何使用？

SQLite 的窗口函数（Window Functions）提供了强大的数据分析能力：

1. 窗口函数的概念

   • 窗口函数对一组行执行计算，但不会将多行合并为一行
   • 类似于聚合函数，但保留了原始行的详细信息
   • SQLite 3.25.0+ 支持窗口函数

2. 窗口函数语法

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

3. 常用窗口函数

   • 聚合函数：SUM()、AVG()、COUNT()、MAX()、MIN()
   • 排名函数：ROW_NUMBER()、RANK()、DENSE_RANK()
   • 偏移函数：LAG()、LEAD()
   • 分析函数：FIRST_VALUE()、LAST_VALUE()

4. 排名函数示例

   sql
   -- ROW_NUMBER：连续排名
   SELECT 
       name,
       score,
       ROW_NUMBER() OVER (ORDER BY score DESC) as rank_num
   FROM students;
   
   -- RANK：相同分数相同排名，跳过后续排名
   SELECT 
       name,
       score,
       RANK() OVER (ORDER BY score DESC) as rank_num
   FROM students;
   
   -- DENSE_RANK：相同分数相同排名，不跳过后续排名
   SELECT 
       name,
       score,
       DENSE_RANK() OVER (ORDER BY score DESC) as rank_num
   FROM students;

5. 偏移函数示例

   sql
   -- LAG：获取前一行的值
   SELECT 
       date,
       sales,
       LAG(sales) OVER (ORDER BY date) as prev_sales,
       sales - LAG(sales) OVER (ORDER BY date) as growth
   FROM sales_data;
   
   -- LEAD：获取后一行的值
   SELECT 
       date,
       sales,
       LEAD(sales) OVER (ORDER BY date) as next_sales
   FROM sales_data;

6. 分区示例

   sql
   -- 按部门分区，计算每个部门内的排名
   SELECT 
       department,
       name,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
   FROM employees;
   
   -- 计算每个部门的平均工资
   SELECT 
       department,
       name,
       salary,
       AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
   FROM employees;

7. 窗口帧（Frame）示例

   sql
   -- 计算移动平均（3天）
   SELECT 
       date,
       sales,
       AVG(sales) OVER (
           ORDER BY date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) as moving_avg
   FROM sales_data;
   
   -- 计算累计总和
   SELECT 
       date,
       sales,
       SUM(sales) OVER (
           ORDER BY date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) as running_total
   FROM sales_data;

8. 实际应用场景

   sql
   -- 计算销售额排名
   SELECT 
       product_name,
       sales_amount,
       RANK() OVER (ORDER BY sales_amount DESC) as sales_rank
   FROM sales;
   
   -- 计算同比增长
   SELECT 
       year,
       month,
       revenue,
       LAG(revenue) OVER (ORDER BY year, month) as prev_revenue,
       (revenue - LAG(revenue) OVER (ORDER BY year, month)) / 
           LAG(revenue) OVER (ORDER BY year, month) * 100 as growth_rate
   FROM monthly_revenue;
   
   -- 找出每个类别的前3名产品
   WITH ranked_products AS (
       SELECT 
           category,
           product_name,
           sales,
           RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_num
       FROM products
   )
   SELECT * FROM ranked_products WHERE rank_num <= 3;

9. 窗口帧类型

   • ROWS：基于物理行数
   • RANGE：基于值范围
   • GROUPS：基于分组

10. 性能优化

    • 为窗口函数的 ORDER BY 和 PARTITION BY 列创建索引
    • 避免在窗口函数中使用复杂表达式
    • 考虑使用子查询或 CTE 优化复杂窗口函数

窗口函数是 SQLite 进行复杂数据分析的重要工具。