SQL窗口函数的高级用法：ROW与RANGE的灵活使用

在 SQL 中，窗口函数（Window Functions）允许用户对与当前行相关的数据子集执行计算，而不需要使用复杂的子查询或临时表。窗口函数通常与 OVER() 子句一起使用，以定义窗口的边界和如何对行进行分组。在 OVER() 子句中，ROWS 和 RANGE 是两个重要的子句，用于指定窗口帧的边界。

1. ROWS 子句

ROWS 子句基于物理行号来定义窗口的边界。以下是几种常见的 ROWS 子句的用法：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 窗口从当前分区的第一行开始，直到当前行。
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 窗口包括当前行、前一行和后一行。
• ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING: 窗口从当前行的前两行开始，直到分区的最后一行。

示例：

	SELECT 

	id, 

	value, 

	AVG(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as avg_value 

	FROM 

	my_table;

在这个示例中，我们计算了每个 id 对应的 value 字段的平均值，包括当前行、前一行和后一行的值。

2. RANGE 子句

与 ROWS 子句不同，RANGE 子句基于窗口函数排序依据的列的值来定义窗口的边界。这在处理日期、时间或其他可以自然排序的数据类型时特别有用。

以下是几种常见的 RANGE 子句的用法：

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 窗口从当前分区的最小值开始，直到当前行的值。
• RANGE BETWEEN INTERVAL '1 day' PRECEDING AND INTERVAL '1 day' FOLLOWING: 窗口包括当前行值前后各一天的所有行（假设排序依据的列是日期类型）。

示例（假设有一个包含日期的列 date_column）：

SELECT 
date_column, 
value, 
AVG(value) OVER (ORDER BY date_column RANGE BETWEEN INTERVAL '1 day' PRECEDING AND INTERVAL '1 day' FOLLOWING) as avg_value 
FROM 
my_table;

在这个示例中，我们计算了每个日期对应的 value 字段的平均值，包括当前日期前后各一天的所有行的值。

注意事项：

• 不是所有的数据库系统都支持 RANGE 子句，特别是较旧的或特定的数据库系统。在使用之前，请查阅您所使用的数据库系统的文档。
• 当使用 RANGE 子句时，确保排序依据的列具有明确的顺序（例如，日期或数字），否则结果可能是不确定的。
• 在某些情况下，使用 ROWS 可能比使用 RANGE 更有效，因为基于物理行号的计算通常比基于值的计算更快。但是，这取决于您的具体需求和数据集。