开窗函数支持表达式
功能描述
Vastbase在Oracle兼容模式下支持查询语句中的开窗语句RANGE BETWEEN AND语法内使用表达式，其中表达式的结果可以是0、正数和时间区间。
注意事项
该功能仅在数据库兼容模式为Oracle时支持（即数据库初始化时指定DBCOMPATIBILITY='A'）。
本语法不支持与KEEP一起使用。
不支持order by后接number类型字段。
RANGE BETWEEN AND中必须有且仅包含有一个ORDER BY。
暂不支持order by列类型与range between offset preceding的offset类型不相同（例如
order by b range between 1.1 following and 1.09 following（其中b是int）)。暂不支持range between小数类型和number类型。
frame_start缺省为UNBOUNDED PRECEDING，frame_end缺省为CURRENT ROW。
frame_end的取值在上面列出的顺序中必须晚于frame_start的取值
value不可为负数或空，可以为0
如下语法是不合理的，所以被禁止：
between unbounded following and xxx between xxx and unbounded preceding between xx following and xx preceding between current row and xx preceding between xx following and current row
语法格式
更多SELECT语法及WINDOW子句信息用户可参考SELECT。
RANGE BETWEEN frame_start and frame_end
其中frame_start和 frame_end可以是以下五种：
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
expr PRECEDING
expr FOLLOWING
参数说明
UNBOUNDED PRECEDING
指定窗口在分区中的第一行开始，即窗口的边界是分区中的第一行。
UNBOUNDED FOLLOWING
指定窗口在分区的最后一行结束，即窗口的边界是分区中的最后一行。
CURRENT ROW
指定窗口在当前行开始或结束，即窗口的边界是当前行或者当前行的值。
expr PRECEDING
指定要置于当前行之前的行或值的数目，即窗口的边界是当前行向前滑动expr的行或当前值-expr的值。
expr FOLLOWING
指定窗口在分区的最后一行结束，即窗口的边界是当前行向后滑动 expr的行或当前值+expr的值
示例
示例1 聚合函数和开窗函数结合，且开窗语句为RANGE BETWEEN UNBOUNDED PRECEDING AND expr preceding表达式。
1、创建测试表并插入数据。
CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount int
);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (1, date '2023-07-01', 100);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (2, date '2023-07-02', 150);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (3, date '2023-07-03', 200);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (4, date '2023-07-04', 250);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (5, date '2023-07-05', 300);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (6, date '2023-07-06', 350);
INSERT INTO sales (sale_id, sale_date, amount) VALUES (7, date '2023-07-07', 400);
2、查看当前表数据。
select * from sales;
返回结果为如下：
sale_id | sale_date | amount
---------+---------------------+--------
1 | 2023-07-01 00:00:00 | 100
2 | 2023-07-02 00:00:00 | 150
3 | 2023-07-03 00:00:00 | 200
4 | 2023-07-04 00:00:00 | 250
5 | 2023-07-05 00:00:00 | 300
6 | 2023-07-06 00:00:00 | 350
7 | 2023-07-07 00:00:00 | 400
(7 rows)
3、执行如下查询，其中sum_amount列的结果为将sale_date列进行排序，当前行进行-3计算后所得行及之前所有行的amount列值累加。
SELECT
sale_id,
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) AS sum_amount
FROM
sales;
查询结果返回为如下，如sale_id为5时，sum_amount为5-3计算得到的第二行及之前的所有行将amount列值累加：
sale_id | sale_date | amount | sum_amount
---------+---------------------+--------+------------
1 | 2023-07-01 00:00:00 | 100 |
2 | 2023-07-02 00:00:00 | 150 |
3 | 2023-07-03 00:00:00 | 200 |
4 | 2023-07-04 00:00:00 | 250 | 100
5 | 2023-07-05 00:00:00 | 300 | 250
6 | 2023-07-06 00:00:00 | 350 | 450
7 | 2023-07-07 00:00:00 | 400 | 700
(7 rows)
示例2： 开窗语句为RANGE BETWEEN expr PRECEDING AND expr FOLLOWING，其中的expr为算数表达式。
1、创建测试表并插入数据。
CREATE TABLE emp (
empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal int CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
2、查询测试表中数据。
select * from emp;
返回结果为如下：
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | | 10
(14 rows)
3、执行如下查询语句。
SELECT ENAME, SAL, SAL*.9 LOW, SAL*1.1 HIGH,COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN SAL*2 PRECEDING AND SAL*2 FOLLOWING) COUNT FROM EMP;
返回结果为如下：
ename | sal | low | high | count
--------+------+--------+--------+-------
SMITH | 800 | 720 | 880 | 8
JAMES | 950 | 855 | 1045 | 10
ADAMS | 1100 | 990 | 1210 | 13
WARD | 1250 | 1125 | 1375 | 13
MARTIN | 1250 | 1125 | 1375 | 13
MILLER | 1300 | 1170 | 1430 | 13
TURNER | 1500 | 1350 | 1650 | 13
ALLEN | 1600 | 1440 | 1760 | 13
CLARK | 2450 | 2205 | 2695 | 14
BLAKE | 2850 | 2565 | 3135 | 14
JONES | 2975 | 2677.5 | 3272.5 | 14
FORD | 3000 | 2700 | 3300 | 14
SCOTT | 3000 | 2700 | 3300 | 14
KING | 5000 | 4500 | 5500 | 14
(14 rows)
