PostgreSQL 高级查询：窗口函数实战

什么是窗口函数？

窗口函数在相关行集上执行计算，同时保留原始行的详细信息。与 GROUP BY 不同，窗口函数不会折叠行。

基础语法

SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

常用窗口函数

1. 排名函数

ROW_NUMBER() OVER (ORDER BY salary DESC) -- 唯一行号
RANK() OVER (ORDER BY salary DESC)       -- 跳跃排名
DENSE_RANK() OVER (ORDER BY salary DESC) -- 连续排名
NTILE(4) OVER (ORDER BY salary)          -- 分桶

2. 聚合函数

SUM(salary) OVER (PARTITION BY dept)          -- 分组求和
AVG(salary) OVER (ORDER BY hire_date)         -- 累计平均
COUNT(*) OVER ()                              -- 总数

3. 值函数

LAG(salary, 1) OVER (ORDER BY hire_date)      -- 前一行
LEAD(salary, 1) OVER (ORDER BY hire_date)     -- 后一行
FIRST_VALUE(salary) OVER (PARTITION BY dept)  -- 组内第一
LAST_VALUE(salary) OVER (...)                 -- 组内最后

实战案例

计算部门内薪资排名

SELECT 
    name,
    dept,
    salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank
FROM employees;

移动平均

SELECT 
    date,
    sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg
FROM daily_sales;

累计求和

SELECT 
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) as cumulative_revenue
FROM monthly_revenue;

性能优化

1. 合理使用 PARTITION BY
2. 避免不必要的 ORDER BY
3. 在窗口列上创建索引
4. 使用 FILTER 子句过滤数据

总结

窗口函数是 SQL 的强力工具，能解决复杂的分析查询问题。掌握它将极大提升你的数据分析能力。