1. 窗口函数初探：数据库计算新境界

各位SQL老司机都知道，常规的聚合函数只能计算整张表的统计值。但当我们需要为每一行记录附加独立的计算结果时（比如为每个学生单独生成班级内的排名），传统SQL就像只会做"全班平均分"的数学老师，而窗口函数就是能精准给出每个学生排名位置的智能助教。

举个真实的生活场景：超市收银系统需要分析每个收银员每日的销售业绩排名。如果用传统GROUP BY写法，代码会变得复杂低效。而窗口函数只需一句SQL就能生成带排名的完整记录集。

技术栈说明： 本文将全程使用SQLite 3.38.5版本，所有示例均基于该版本的语法特性验证通过。

2. 核心函数深度解析

2.1 ROW_NUMBER：你的数据身份证生成器

典型应用场景：

  • 生成数据分页的唯一标识
  • 消除完全重复记录
  • 动态流水号生成
-- 商品限时抢购活动记录分页查询
SELECT 
    ROW_NUMBER() OVER (ORDER BY purchase_time DESC) AS row_id,
    user_id,
    product_id,
    purchase_time
FROM flash_sale
WHERE activity_id = 202305
ORDER BY purchase_time DESC
LIMIT 10 OFFSET 20;

/* 执行效果：
为第21-30条抢购记录生成连续编号
确保翻页时即使时间相同也不会出现重复序号 */

2.2 RANK：真实世界的竞赛排名

典型应用场景：

  • 销售业绩排名（允许并列）
  • 体育比赛成绩排名
  • 学生考试成绩排名
-- 销售团队季度奖金排名
SELECT 
    salesperson_id,
    total_sales,
    RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM q2_sales
WHERE department = '华东大区';

/* 示例输出：
销售冠军 | 500万 | 1
销售亚军 | 480万 | 2  
并列第三 | 450万 | 3
并列第三 | 450万 | 3
第五名   | 440万 | 5 */

2.3 DENSE_RANK：无间隔的紧凑排名

典型应用场景：

  • 职称等级评定
  • 产品评级划分
  • 会员等级体系
-- 手机跑分性能等级划分
SELECT 
    model_name,
    antutu_score,
    DENSE_RANK() OVER (ORDER BY antutu_score DESC) AS performance_tier
FROM mobile_phones
WHERE release_year = 2023;

/* 示例输出：
旗舰机A | 150万 | 1
旗舰机B | 148万 | 2  
次旗舰C | 135万 | 3
次旗舰D | 135万 | 3
中端机E | 120万 | 4 */

3. 高阶应用技巧

3.1 分区控制艺术（PARTITION BY）

当我们需要在分组内进行计算时，PARTITION BY子句就像智能分组器：

-- 各科成绩班级排名（窗口函数与CTE结合使用）
WITH score_data AS (
    SELECT 
        student_id,
        subject,
        score,
        RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS subject_rank
    FROM exam_results
    WHERE semester = '2023春季'
)
SELECT * 
FROM score_data
WHERE subject_rank <= 3;

3.2 框架子句的魔法

通过ROWS BETWEEN实现移动平均计算：

-- 股票7日移动平均线计算
SELECT 
    trade_date,
    close_price,
    AVG(close_price) OVER (
        ORDER BY trade_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma7
FROM stock_daily
WHERE stock_code = '600519';

4. 技术选型深度对比

4.1 性能特点分析

优势特性：

  • 在单次扫描中完成复杂计算
  • 避免多层嵌套查询
  • 支持渐进式计算（适合流式处理）
  • 减少客户端计算压力

潜在瓶颈：

  • 大数据集内存消耗问题
  • 复杂排序的性能损耗
  • 索引优化策略的特殊性

4.2 使用禁忌清单

  • 百万级数据量慎用复杂窗口函数
  • 避免在频繁更新的表上使用
  • 移动设备应用要注意内存限制
  • 慎用嵌套窗口函数

5. 最佳实践方案

5.1 索引优化策略

针对窗口函数中的ORDER BY子句创建覆盖索引：

-- 为销售排名查询优化
CREATE INDEX idx_sales_ranking ON q2_sales (department, total_sales DESC);

5.2 参数调优指南

修改SQLite的临时存储设置：

PRAGMA temp_store = MEMORY;  -- 将临时数据存储在内存中
PRAGMA cache_size = -8000;   -- 设置8MB的内存缓存

6. 经典问题解决方案

6.1 分组TopN难题破解

-- 找出每个部门工资前三名的员工
WITH ranked_salaries AS (
    SELECT 
        emp_id,
        department,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT *
FROM ranked_salaries
WHERE rank <= 3;

6.2 数据清洗高效方案

-- 删除完全重复记录（保留最新记录）
DELETE FROM sensor_data
WHERE rowid NOT IN (
    SELECT rowid 
    FROM (
        SELECT 
            rowid,
            ROW_NUMBER() OVER (
                PARTITION BY device_id, sensor_value, timestamp
                ORDER BY timestamp DESC
            ) AS rn
        FROM sensor_data
    ) 
    WHERE rn = 1
);

7. 未来演进方向

SQLite窗口函数的最新发展动态：

  • 3.39版本新增的GROUPS模式
  • 滚动窗口的时间区间支持
  • 与JSON功能的结合应用