[Oracle] Analytic functions (1)-syntax

Syntax overview

Analytic-function

Partition clause of Analytic_clause

ORDER BY clause of Analytic_clause

Windowing clause of Analytic_clause

Example:

Here is an example of a simple syntax:

Cases:
SUM (SAL) over (partition by Deptno ORDER by ename) New_alias

Sum is the name of the function

(SAL) is the parameter of the analytic function, each function has 0~3 parameters, the argument can be an expression, for example: sum (SAL+COMM)

Over () is the open window function, which is the starting point of the open analysis function, for both the aggregation function and function as an analysis function, Oracle is not recognized, must use over to identify this function as an analysis function, here is not save!

Partition by Deptno is an optional partitioning clause, and if no partition clauses exist, then all result sets can be considered a single large region
ORDER by is the window rule;
ROW |range between ... and is the window range (row is like a physical seat through its own position to find the corresponding position, relatively fixed, and range is a condition to find the location, need to calculate the location, such as I am 3 years old and the ego two years old person. )


There are a number of ways to take the window range, seemingly very complex, in fact, it is not difficult, we began to slowly analyze:

The key position is 3:1. Group the first row unbounded preceding 2. Current row 3. Group last row unbounded following

Next, the simplest three fetch ranges are generated:

Group first row-current row rows [between] unbounded preceding [and present row],
Group first row, group last row rows between unbounded preceding and unbounded following,
Current row, group last row, rows between, unbounded following

Why the course analysis function can make people feel very complex, we take a look, this window range has two ways to take the number, one is based on the physical location, is the row keyword, one is based on the logical location of the number, is range, then just the number of the range from 3 into 6, as follows:

Rows first row, current row, group first row, grouping last row, current row, group last row
Group first row, current row, grouping first row, group last row, current row, group last row

However, this scenario is considered thoughtful, there is no leakage of consideration, yes, we continue to see what is possible:


From the first row of the group, the current row before n rows rows between unbounded preceding and n rows preceding
From the first row, the current row after n rows rows between unbounded preceding and N rows following

n rows from the current row, after the current row, rows between, and N rows following
Rows between n rows preceding and current row from the current row, top n rows

Groups the last row from the top n rows of the current row rows between n rows preceding and unbounded following
Group the last row from the current row, n lines, rows between n rows following and unbounded following
N2 rows from the current line before N1 to the current line rows between n1 rows preceding and N2 rows preceding
N2 rows after N1 lines from the current line to the current line rows between N1 rows following and N2 rows following

There is no, there!

N2 rows from the current line before the N1 line to the current row between N1 row preceding and N2 row following

Is there anything like from the first row after n to the current row, from the current row to the last row in the group N rows?
Oh, that is not, right, you are not enough brains, remember so many???

It's over, but there's a window that starts at the current line and ends at the current line, but it's pointless, what do you want to do? So, count, at least 9.
Considering the rows and range two, there are 18 more of them. Plus the first 6, that's 24. But there are 18 more of them, and the odds are less than the first 6.


Yes, there's one more important thing to explain.

Do not write between and, in the case of an order by, is the first row to the current line between unbounded preceding and present row
Do not write between and, in the absence of an order by, is the first line of grouping to the last row of grouping; between unbounded preceding and unbounded following

Analytic functions VS Aggregate functions

As we can see from the above example, the main differences between the analysis function and the aggregate function are:

1. Analyze how many rows are returned for each group (like more than one pseudo-column). Aggregate functions are aggregated into one row, regardless of how many rows are in each group.

2. The ORDER BY and aggregate functions of an analytic function are not the same as the concept of order by ordering, and the order by tag of an analytic function is delineated from the range of the first row to the current line, and then, based on that range, the aggregation accumulates. The Oracle by of the aggregate function does not affect the data, only the sorting.