原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。
今天，我们来探讨 GBase 8s 数据库中的窗口函数（Window Functions），着重说说 Window Frame 子句，看看它是如何帮助我们更高效地处理和分析数据的。
窗口函数是 SQL 标准中引入的一种的工具，它允许我们在查询中对一组行（称为窗口）进行计算，而不会像传统聚合函数那样丢失行的细节。通过窗口函数，我们可以在保持数据行完整性的同时，进行复杂的计算，如累计和、移动平均、排名等。而 Window Frame 子句则是窗口函数中的一个重要组成部分，它定义了窗口的范围，即哪些行将被包含在计算中。通过灵活地定义窗口范围，我们可以实现各种复杂的数据分析需求。
概述
Window Frame子句返回每一window分区中的行的子集，frame子句指定由特定数目的行或值的范围来指定如何定义这个子集。
框架是相对于当前行确定的，这使得框架可以根据当前行在分区中的位置在分区中移动。
例如：
(1) 通过将一个框架定义为从分区开始到当前行的所有行，可以计算截至当前行的累积和；
(2) 通过将框架定义为在当前行的任意一侧扩展N行，可以计算滚动平均。
语法
关键字介绍
在 Window Frame 子句中，有以下几个关键字，它们共同定义了窗口的范围：
| 元素 | 描述 |
|---|---|
| OFFSET | 从当前行位置的偏移量的无符号整数，不可为负的,如果为零，则指定当前的行 |
| RANGE | 基于值的frame子句 |
| ROWS | 基于行的frame子句 |
| UNBOUNDED | 从当前行至该 window 分区的限度的所有行 |
| UNBOUNDED PRECEDING | 起始边界为该分区中的第一行 |
| UNBOUNDED FOLLOWING | 终止边界为该分区中的最后一行 |
| PRECEDING | 指定从当前行的负偏移量 |
| FOLLOWING | 指定从当前行的正偏移量 |
| CURRENT ROW | 当前行 |
基于行(ROWS)的window frame
ROWS 关键字创建基于行的 window frame，框架由开始行和结束行的位置来定义， 该偏移量表示要返回的行的数目。
示例1：返回当前行以及之前的6行。
avg(price) over (order by year,day
ROWS BETWEEN 6 PRECEDING AND CURRENT NOW)
示例2：需求：按照员工部门分区按照薪资排序返回员工，每一分区内，对该员工与之前的员工薪资求平均值。
--建表
CREATE TABLE employees (
id serial PRIMARY KEY,
emp_id VARCHAR(20) NOT NULL,
name VARCHAR(20) NOT NULL,
age int NOT NULL,
salary decimal(10,2) NOT NULL);
--插入数据
INSERT INTO employees (emp_id, name, age, salary) VALUES
('E00A', 'carry', 18, 5000),
('E00A', 'lili', 28, 5500),
('E00B', 'tom', 22, 6000),
('E00B', 'amy', 25, 6500),
('E00B', 'chun', 26, 7000),
('E00C', 'zouzou', 25, 5200),
('E00D', 'candy', 23, 5800),
('E00D', 'mumu', 29, 6200);
--按照员工部门分区按照薪资排序返回员工，每一分区内，对该员工与之前的员工薪资求平均值
SELECT
emp_id,name,salary,
AVG(salary) OVER (
PARTITION BY emp_id
ORDER BY salary
ROWS BETWEEN 1 PRECEDING AND current row
) AS avg_salary FROM employees;
--返回结果
>
SELECT
emp_id,name,salary,
AVG(salary) OVER (
PARTITION BY emp_id
ORDER BY salary
ROWS BETWEEN 1 PRECEDING AND current row
> ) AS avg_salary FROM employees;
EMP_ID NAME SALARY AVG_SALARY
E00A carry 5000.000000000 5000.000000000
E00A lili 5500.000000000 5250.000000000
E00B tom 6000.000000000 6000.000000000
E00B amy 6500.000000000 6250.000000000
E00B chun 7000.000000000 6750.000000000
E00C zouzou 5200.000000000 5200.000000000
E00D candy 5800.000000000 5800.000000000
E00D mumu 6200.000000000 6000.000000000
8 row(s) retrieved.
基于值(RANGE)的window frame
RANGE 关键字创建基于值的 frame 子句，框架由某个值范围内的行定义，偏移量是行值与当前行值之间的差异。由当前行与满足标准的行组成，通过 ORDER 子句中的排序键设置该标准并符合指定的偏移量。偏移量表示排序键的数据类型的单位数目。排序键必须为数值的、DATE 或 DATETIME 数据类型。例如，如果排序键为 DATE 数据类型，则偏移量表示特定的天数。
示例1：返回日期在在当前行的两天之内的 行以及当前行的总数。
COUNT(*) OVER (ORDER BY ship_date
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)
示例2：需求查询按照部门分区并按照年龄排序，返回员工信息。在每一个分区，对每一个运动员以及年龄相差5岁的员工薪资求平均值。
SELECT
emp_id,name,age,salary,
AVG(salary) OVER (
PARTITION BY emp_id
ORDER BY age
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING
) AS avg_salary FROM employees;
--返回结果
SELECT
emp_id,age,salary,
AVG(salary) OVER (
PARTITION BY emp_id
ORDER BY age
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING
> ) AS avg_salary FROM employees;
emp_id NAME age salary avg_salary
E00A carry 18 5000.00 5000.00000000000
E00A lili 28 5500.00 5500.00000000000
E00B tom 22 6000.00 6500.00000000000
E00B amy 25 6500.00 6500.00000000000
E00B chun 26 7000.00 6500.00000000000
E00C zouzou 25 5200.00 5200.00000000000
E00D candy 23 5800.00 5800.00000000000
E00D mumu 29 6200.00 6200.00000000000
*E00A分区中，两位员工年龄相差大于5岁，因此平均薪资就就是自己的薪资，E00B分区中，员工年龄相差均在5岁之内，因此平均薪资包含对方的薪资。
注：以上所用示例使用的数据库版本为:GBase8sV8.8_TL_3.6.1_x86_64
通过以上介绍，我们可以看到，GBase 8s 中的窗口函数和 Window Frame 子句为我们提供的数据分析能力，无论是基于行的精确控制，还是基于值的灵活处理，窗口函数都能帮助我们快速实现复杂的计算需求。在实际应用中，我们可以根据具体需求选择合适的窗口类型和范围，从而更高效地处理和分析数据。
希望本文的介绍能帮助你更好地理解和应用窗口函数，解锁数据分析的新视角。如果你对窗口函数或 GBase 8s 有任何疑问或建议，欢迎在评论区留言，让我们共同学习，共同进步！
原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。
