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_bouding AND win_bouding
| {ROWS | RANGE} win_bouding
win_bouding:
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 | 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 | The boundary of the window.
|
| INTERVAL expr date_unit | 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 rows with unique values in 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 |
+-----------+--------+----------+