Purpose
You can use this clause to define a window.
A window is also called a frame. OceanBase Database supports two types of frame semantics: ROWS and RANGE. The former is based on a physical row offset, and the latter is based on a logical value offset.
Functions applied to the OVER clause are analytic functions, also known as window functions in some databases. For more information about analytic functions, see Analytic functions.
Syntax
[query_partition_clause] [ order_by_clause [win_window]]
win_window:
{ROWS RANGE} BETWEEN win_bounding AND win_bounding
{ROWS RANGE} win_bounding
win_bounding:
CURRENT ROW
win_interval {PRECEDING FOLLOWING}
win_internal:
expr INTERVAL expr date_unit
date_unit:
DAY
DAY_HOUR
DAY_MICROSECOND
DAY_MINUTE
DAY_SECOND
HOUR
HOUR_MICROSECOND
HOUR_MINUTE
HOUR_SECOND
MICROSECOND
MINUTE
MINUTE_MICROSECOND
MINUTE_SECOND
MONTH
QUARTER
SECOND
SECOND_MICROSECOND
WEEK
YEAR
YEAR_MONTH
Parameters
| Parameter | Description |
|---|---|
| ROWS | Sorts rows by using the ORDER BY clause in a physical window, and takes data of the first and last N rows for calculation. |
| RANGE | Specifies the range of values corresponding to the current row in a logical window. |
| CURRENT ROW | Uses the current row as the boundary of the window. |
| win_interval PRECEDING FOLLOWING | Specifies the boundary of the window.
|
| INTERVAL expr date_unit | Specifies the interval of the window.
|
Examples
Create a table named employees and insert data into it.
obclient> CREATE TABLE employees (manager_id INT,last_name VARCHAR(50),hiredate VARCHAR(50),salary INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss', '2019-07-11',13500);
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners', '2018-12-01',14000);
INSERT INTO employees VALUES(200, 'Ross', '2019-06-11',13500);
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part', '2018-08-11',14000);
COMMIT;
Calculate the number of unique rows corresponding to the salary column.
obclient> SELECT last_name, salary, APPROX_COUNT_DISTINCT(salary) OVER (ORDER BY hiredate) "Variance"
FROM employees WHERE manager_id = 100 ORDER BY last_name, salary, "Variance";
+-----------+--------+----------+
LAST_NAME SALARY Variance
+-----------+--------+----------+
De Haan 11000 3
Errazuriz 1400 2
Hartstein 14000 4
Partners 14000 4
Raphaely 1700 1
Raphaely 1700 2
Russell 13000 6
Weiss 13500 5
+-----------+--------+----------+