Window Frame

Window Frame 是在使用窗口函数时定义的一个子集，用于限制窗口函数的计算范围，只对窗口框架中的数据进行计算。通过窗口框架，可以更加精确地控制窗口函数的计算方式和结果。以下是窗口框架的详细说明和使用示例。

Window Frame语法

在使用窗口函数时，可以通过

OVER

OVER

子句定义窗口框架，语法如下：

<window_function> OVER ( [PARTITION BY <partition_expression>] [ORDER BY <order_expression>] [frame_clause>] )

其中，

<window_function>

<window_function>

表示要使用的窗口函数名称，例如

ROW_NUMBER()

ROW_NUMBER()

、

RANK()

RANK()

、

SUM()

SUM()

等。

PARTITION BY

PARTITION BY

子句用于将数据分组，每个分组称为一个分区（partition）。

ORDER BY

ORDER BY

子句用于在每个分区中对数据进行排序，这将影响某些窗口函数的结果，如

ROW_NUMBER()

ROW_NUMBER()

、

RANK()

RANK()

等。

frame_clause

frame_clause

用于定义窗口框架的范围，有两种形式：

ROWS frame

ROWS frame

和

RANGE frame

RANGE frame

。

ROWS frame

ROWS frame

ROWS frame

是基于行的窗口框架，用于指定窗口框架中包含的行数或位置。其语法如下：

ROWS BETWEEN <start_boundary> AND <end_boundary>

<start_boundary>

<start_boundary>

和

<end_boundary>

<end_boundary>

用于定义窗口框架的起始和结束边界，可以是以下值之一：

```
UNBOUNDED PRECEDING
```
UNBOUNDED PRECEDING
：从分区的第一行开始。
```
UNBOUNDED FOLLOWING
```
UNBOUNDED FOLLOWING
：到分区的最后一行结束。
```
CURRENT ROW
```
CURRENT ROW
：当前行。
```
<offset> PRECEDING
```
<offset> PRECEDING
：当前行之前的第
```
<offset>
```
<offset>
行，
```
<offset>
```
<offset>
为非负整数。
```
<offset> FOLLOWING
```
<offset> FOLLOWING
：当前行之后的第
```
<offset>
```
<offset>
行，
```
<offset>
```
<offset>
为非负整数。

EXCLUDE 子句

EXCLUDE

EXCLUDE

子句用于从窗口帧中排除特定行，在

ROWS BETWEEN ... AND ...

ROWS BETWEEN ... AND ...

之后指定：

ROWS BETWEEN <start> AND <end> EXCLUDE { CURRENT ROW | TIES | GROUP | NO OTHERS }

选项	说明
`EXCLUDE CURRENT ROW` EXCLUDE CURRENT ROW	排除当前行
`EXCLUDE TIES` EXCLUDE TIES	排除与当前行 ORDER BY 值相同的其他行（保留当前行）
`EXCLUDE GROUP` EXCLUDE GROUP	排除当前行及所有与其 ORDER BY 值相同的行
`EXCLUDE NO OTHERS` EXCLUDE NO OTHERS	不排除任何行（默认行为）

-- 计算前后各1行的总和，但排除当前行自身 SELECT id, v, SUM(v) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS sum_excl_self FROM doc_sel2; -- id=1: 20（只有id=2） -- id=2: 40（id=1 + id=3） -- id=3: 60（id=2 + id=4）

，用于指定窗口框架中包含的行的值的范围。其语法如下：

RANGE BETWEEN <start_boundary> AND <end_boundary>

<start_boundary>

<start_boundary>

和

<end_boundary>

<end_boundary>

用于定义窗口框架的起始和结束边界，可以是以下值之一：

```
UNBOUNDED PRECEDING
```
UNBOUNDED PRECEDING
：从分区中最小值开始。
```
UNBOUNDED FOLLOWING
```
UNBOUNDED FOLLOWING
：到分区中最大值结束。
```
CURRENT ROW
```
CURRENT ROW
：当前行的值。
```
<value> PRECEDING
```
<value> PRECEDING
：当前行的值减去
```
<value>
```
<value>
，
```
<value>
```
<value>
为非负数。
```
<value> FOLLOWING
```
<value> FOLLOWING
：当前行的值加上
```
<value>
```
<value>
，
```
<value>
```
<value>
为非负数。

Window Frame的使用示例

以下是一些使用 Window Frame 的 SQL 查询示例，以及它们的输出结果。假设我们有一个名为 sales 的表，它包含了每个月的销售额和利润率的数据，如下所示：

month	sales	profit
1	100	0.1
2	120	0.15
3	80	0.05
4	150	0.2
5	90	0.1
6	110	0.12

ROWS frame

查询：使用
```
SUM()
```
SUM()
函数和
```
ROWS frame
```
ROWS frame
计算每个月的销售额与前两个月和后两个月的销售额的总和。

SELECT month, sales, SUM(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sum_sales FROM sales; +-------+-------+-----------+ | month | sales | sum_sales | +-------+-------+-----------+ | 1 | 100 | 300 | | 2 | 120 | 450 | | 3 | 80 | 540 | | 4 | 150 | 550 | | 5 | 90 | 430 | | 6 | 110 | 350 | +-------+-------+-----------+

查询：使用
```
AVG()
```
AVG()
函数和
```
ROWS frame
```
ROWS frame
计算每个月的利润率与前一个月和后一个月的利润率的平均值。

SELECT month, profit, AVG(profit) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_profit FROM sales; +-------+--------+---------------------+ | month | profit | avg_profit | +-------+--------+---------------------+ | 1 | 0.1 | 0.125 | | 2 | 0.15 | 0.09999999999999999 | | 3 | 0.05 | 0.13333333333333333 | | 4 | 0.2 | 0.11666666666666665 | | 5 | 0.1 | 0.14 | | 6 | 0.12 | 0.11 | +-------+--------+---------------------+

RANGE frame

查询：使用
```
COUNT()
```
COUNT()
函数和
```
RANGE frame
```
RANGE frame
计算每个月的销售额在全年销售额中的分布，即有多少个月的销售额与当前月的销售额相差不超过 10。

SELECT month, sales, COUNT(*) OVER (ORDER BY sales RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING) AS count_sales FROM sales; +-------+-------+-------------+ | month | sales | count_sales | +-------+-------+-------------+ | 3 | 80 | 2 | | 5 | 90 | 3 | | 1 | 100 | 3 | | 6 | 110 | 3 | | 2 | 120 | 2 | | 4 | 150 | 1 | +-------+-------+-------------+

查询：使用
```
MAX()
```
MAX()
函数和
```
RANGE frame
```
RANGE frame
计算每个月的利润率与前一个月和后一个月的利润率的最大值。

SELECT month, profit, MAX(profit) OVER (ORDER BY month RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS max_profit FROM sales; +-------+--------+------------+ | month | profit | max_profit | +-------+--------+------------+ | 1 | 0.1 | 0.15 | | 2 | 0.15 | 0.15 | | 3 | 0.05 | 0.2 | | 4 | 0.2 | 0.2 | | 5 | 0.1 | 0.2 | | 6 | 0.12 | 0.12 | +-------+--------+------------+

联系我们