Oracle development topic: OLAP functions (rows 2 preceding/unbounded preceding)

Oracle OLAP Functions
This topic has been used a lot recently. I have summarized it.

Syntax: function_name (,,...)
Over ()

OLAP function Syntax:
1. The function itself is used to operate the data in the window.
2. Partitioning clause is used Result set Partition ;
3. Order by clause is used to sort data in a partition.
4. Wing wing clause is used to define a set of rows on which a function operates, that is, the range affected by the function.

I. Influence of order by On Window
Excluding order:
SQL> select deptno, Sal, sum (SAL) over () from EMP;
If order by is not included, the default window is from the first row of the result set to the end.

For orders:
SQL> select deptno, Sal, sum (SAL) over (order by deptno) as sumsal from EMP;
When order by is included, the default window is from the first row to the last row of the current group.

2. functions used for sorting
SQL> select empno, deptno, Sal,

Rank () over (partition by deptno order by Sal DESC nulls last) as rank,
Dense_rank () over (partition by deptno order by Sal DESC nulls last) as dense_rank,
Row_number () over (partition by deptno order by Sal DESC nulls last) as row_number
From EMP;

Iii. Aggregate functions
SQL> select deptno, Sal,
Sum (SAL) over (partition by deptno) as sumsal,
AVG (SAL) over (partition by deptno) as avgsal,
Count (*) over (partition by deptno) as count,
Max (SAL) over (partition by deptno) as maxsal
From EMP;

IV, Window Opening Statement

1. Rows window: "rows 5 preceding" applies to any type and can order by multiple columns.

SQL> select deptno, ename, Sal,
sum (SAL) over (order by deptno rows 2 preceding) sumsal
from EMP;
rows 2 preceding : divides the current row and the first two rows into a window, so the sum function is used in this Three rows above

SQL> select deptno, ename, Sal,
Sum (SAL) over (partition by deptno order by ename rows 2 preceding) sumsal
From EMP order by deptno, ename;
After the partiton by partition is added, the rows 2 preceding (window) takes effect only in the current partition and does not affect the rows outside the partition.

SQL> select ename, Sal, hiredate,
First_value (ename) over (order by hiredate ASC rows 5 preceding) first_ename,
First_value (hiredate) over (order by hiredate ASC rows 5 preceding) first_hiredate
From EMP order by hiredate ASC;
Order by hiredate ASC rows 5 preceding:After order by, take the first five rows of the current row + the current row as the window (6 rows in total ).

2. "range unbounded preceding"

Range unbounded precedingAll All rows Include, but when partition:

SQL> select deptno, ename, Sal,
Sum (SAL) over (partition by deptno order by deptno range unbounded preceding) sumsal
From EMP;

SQL> select deptno, ename, Sal,
Sum (SAL) over (order by deptno range unbounded preceding) sumsal
From EMP;

This SQL sentence is equivalent to the following SQL statement:
Select deptno, ename, Sal, sum (SAL) over (order by deptno) sumsal from EMP;
Because the default window of order by is always from the first of the result set Row opening To the last row of the group.
The default window of partiton by always starts from the first row of the partition.

3. range window: "Range 100 preceding"

This clause applies only to number and date, and can only be an order by column.
If the ASC is arranged in over (), it means [number-100, number] Closed Interval Is its window.
If DESC is arranged in over (), the window is [number, number + 100.

4. Window Summary
1. Unbounded preceding: starts from the first row of the current partition and ends with the end of the current row.
2. Current row: starts from the current row and ends with the current row.
3. [numeric expression] preceding: For rows, it starts from the row [numeric expression] before the current row and ends with the current row. For range, it starts from the value smaller than the value of the numeric expression to the end of the current row.
4. [numeric expression] following: opposite to [numeric expression] preceding.