PostgreSQL ，OVER 子句妙用

在 PostgreSQL 中，OVER 子句是窗口函数定义的一部分，用于指定窗口函数的窗口范围。窗口函数允许你对一组行执行计算，这些行构成了一个结果集中的窗口。OVER 子句定义了这个窗口的开始和结束行。

以下是 OVER 子句的一些关键点：

1. 默认窗口：如果不指定 OVER 子句，窗口函数将使用当前行作为窗口。

2. 窗口定义：OVER 子句可以定义为一个窗口，其中包含当前行和它前面的行，或者从当前行开始到结果集中的某一行。

3. 排序顺序：使用 ORDER BY 子句在 OVER 中指定窗口的排序顺序。如果没有 ORDER BY，则窗口函数将按照表中行的自然顺序处理。

4. 窗口框架：OVER 子句还可以与 ROWS 或 RANGE 子句一起使用，以定义更具体的窗口框架。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示窗口从分区的第一行开始到当前行。

5. 分区：使用 PARTITION BY 子句在 OVER 中指定如何对数据进行分区。每个分区内部独立应用窗口函数。

以下是一些使用 OVER 子句的示例：

示例 1：累积求和

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum FROM sales;

示例 2：移动平均

SELECT sale_date, amount, AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average FROM sales;

示例 3：行号

SELECT sale_id, sale_date, amount, ROW_NUMBER() OVER (ORDER BY sale_date) AS row_num FROM sales;

示例 4：分区和累积求和

SELECT product_id, sale_date, amount, SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS product_cumulative_sum FROM sales;

在这些示例中，OVER 子句定义了窗口函数的作用域和排序方式，使得你可以对数据进行复杂的分析和计算。