RANGE和ROWS子句的差异及使用技巧

窗口函数是基于一个滑动窗口，也就是与当前行相关的一组数据行计算出一个结果。 本文章通过图文形式介绍了常用的取值窗口函数和排名窗口函数。 取值窗口函数：lag和lead函数不支持动态的窗口大小，而是以整个分区（PARTITION BY）作为分析的窗口。 排名窗口函数不支持动态的窗口大小，而是以整个分区（PARTITION BY）作为分析的窗口。 在SQL中，窗口函数是一种强大的工具，它允许我们在一行数据的基础上计算与该行相关的其他行的数据，而不仅仅是基于整个表或特定分组的数据。窗口函数的使用极大地扩展了数据分析和报表制作的能力，使得复杂的计算变得更为简洁高效。 窗口函数与传统的聚合函数（如SUM, AVG, COUNT等）有所不同。聚合函数会将一组数据根据指定的分组字段汇总成一个单一的结果，而窗口函数则为每一行数据计算出一个结果。在使用窗口函数时，我们可以指定一个窗口（PARTITION BY），排序方式（ORDER BY），甚至窗口大小（frame_clause），这使得窗口函数具有高度的灵活性。 1. **窗口函数的基本结构** 使用窗口函数的语法通常如下： ```sql SELECT col1, col2, window_function OVER(PARTITION BY ... ORDER BY ...) AS alias_col FROM table_name; ``` 或者使用命名窗口： ```sql SELECT col1, col2, window_function OVER window_namae FROM table_name WINDOW window_name AS (PARTITION BY ... ORDER BY ... frame_clause); ``` 2. **窗口函数的类型** - **取值窗口函数**：如`LAG`和`LEAD`，它们可以用来获取当前行之前或之后的值。尽管它们不支持动态窗口大小，但可以以整个分区作为分析的窗口。 - **排名窗口函数**：例如`RANK`, `DENSE_RANK`, 和`ROW_NUMBER`，它们为每行提供一个唯一的排名，同样基于整个分区。 3. **窗口函数的应用场景** - 分区（PARTITION BY）：可以将数据分为多个独立的组，每个组内部进行计算。 - 排序（ORDER BY）：定义在每个分区内如何对数据进行排序，这对于排名函数尤为重要。 - 窗口大小（frame_clause）：定义分析函数考虑的行范围，例如`ROWS BETWEEN`或`RANGE BETWEEN`。 4. **窗口函数与其他SQL子句的执行顺序** 窗口函数在`GROUP BY`和`HAVING`之后，`SELECT`之前执行。在SQL语句的执行顺序中，它位于`FROM/JOIN`、`WHERE`、`GROUP BY`、`聚合函数`、`HAVING`之后，`SELECT`、`DISTINCT`、`UNION`等操作之前。 5. **PARTITION BY** 使用`PARTITION BY`可以将数据分割成不同的分区，每个分区内部的计算是独立的。如果没有指定`PARTITION BY`，那么整个数据集被视为一个分区。 6. **ORDER BY** `ORDER BY`用于定义在每个分区内的数据排序规则。这对`RANK`类函数尤其重要，因为它们依赖于排序来确定行的相对位置。 7. **窗口大小** 窗口大小可以基于当前行设置，如`ROWS BETWEEN`和`RANGE BETWEEN`。`UNBOUNDED PRECEDING`和`UNBOUNDED FOLLOWING`表示从分区开始或结束，而`N PRECEDING`和`N FOLLOWING`则指定具体的行数。`GROUPS`选项在PostgreSQL和SQLite中可用，提供更复杂的窗口定义。 8. **示例** 创建一个交易流水表`transfer_log`，并插入数据后，可以使用窗口函数来计算每个用户的历史交易总额： ```sql SELECT month, city, sold, SUM(sold) OVER (PARTITION BY city) AS sold_sum FROM table_name; ``` 这个查询将返回每个城市的月度销售总额。 SQL中的窗口函数是数据分析的重要工具，它能帮助我们处理复杂的统计问题，如计算移动平均、差异、排名等，从而提升数据处理的效率和精度。理解并熟练运用窗口函数，可以显著提升SQL查询的复杂性和实用性。