18、PostgreSQL高级查询与窗口函数应用

### PostgreSQL 窗口函数使用指南 PostgreSQL 是一个功能强大的开源关系数据库管理系统，广泛应用于数据分析、Web 应用和企业级解决方案中。窗口函数是 PostgreSQL 中的一项重要特性，它允许在不减少行数的前提下，对一组相关行执行计算，从而实现复杂的数据分析任务 [^1]。 #### 窗口函数的基本语法结构如下： ```sql function_name() OVER ( [PARTITION BY <分组列>] [ORDER BY <排序列>] [windowing_clause] ) ``` 窗口函数与传统聚合函数不同之处在于，它不会将多行合并为一行输出，而是在每一行上保留原始数据，并附加基于窗口定义的计算值 。 #### 常见窗口函数分类及用途： - **排名函数**： - `ROW_NUMBER()`：为每一行分配唯一的序号。 - `RANK()`：相同值并列，后续排名跳跃。 - `DENSE_RANK()`：相同值并列，但排名连续 [^2]。 - **聚合窗口函数**： - `SUM()`, `AVG()`, `MIN()`, `MAX()`：在窗口范围内进行聚合计算。 - 常用于累计总和、区间平均值等场景 。 - **偏移函数**： - `LAG(column, offset, default)`：访问当前行之前的某一行数据。 - `LEAD(column, offset, default)`：访问当前行之后的某一行数据 [^1]。 - **分布函数**： - `CUME_DIST()`：计算当前行在窗口中的累积分布。 - `PERCENT_RANK()`：计算当前行的百分比排名 。 #### 示例：使用 ROW_NUMBER() 进行分组排名 ```sql SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; ``` 该查询将按部门分组，对员工工资进行降序排列，并为每位员工分配一个部门内的排名 [^2]。 #### 示例：使用 LAG 函数比较前后行数据 ```sql SELECT employee_id, salary, LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS prev_salary FROM employees; ``` 该查询将显示每位员工的工资及其前一位员工的工资，便于进行数据比较 [^1]。 #### 示例：使用 SUM 窗口函数进行累计计算 ```sql SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders; ``` 该查询将按订单日期顺序累计订单金额，生成运行总和 。 #### 窗口函数与 GROUP BY 的区别： - `GROUP BY` 会将数据合并为一组，每组只保留一行结果。 - 窗口函数则保留原始行数，同时添加基于窗口定义的计算值，适用于需要保留明细数据并附加聚合或排名信息的场景 [^1]。 #### 窗口函数的应用场景： - **排名分析**：如销售排名、成绩排名。 - **趋势分析**：如计算移动平均、增长率。 - **数据比较**：如比较当前行与前一行的数据。 - **窗口聚合**：如计算累计总和、区间平均值 。 PostgreSQL 的窗口函数功能非常强大，结合地理空间分析工具（如 PostGIS），可以实现更高级的数据分析任务。例如，以下查询展示了如何结合 `ST_Distance` 和 `RANK()` 进行地理距离排名 [^3]： ```sql SELECT location, timestamp, ST_Distance(location, 'POINT(0 0)') AS distance, RANK() OVER (ORDER BY distance) AS rank FROM gps_logs; ``` 该查询计算了每个 GPS 点与原点之间的距离，并对这些距离进行排名。 ---