[Oracle] analysis functions (1)-syntax

Syntax Overview

Analytic-Function

PARTITION clause of Analytic_Clause

Order by clause of Analytic_Clause

Analyze wing clause of Analytic_Clause

Example:

The following is an example of a simple Syntax:

Example:
Sum (sal) over (partition by deptno order by ename) new_alias

Sum is the function name.

(Sal) is the parameter of the analysis function. Each function has 0 ~ Three parameters, which can be expressions, such as sum (sal + comm)

Over () is a window function, which is the starting point for enabling analysis functions. Oracle cannot identify functions that can be used as clustering functions and analysis functions, the over function must be used to identify this function as an analysis function. This function cannot be saved!

Partition by deptno is an optional partition clause. If no partition clause exists, all result sets can be considered as a single region.
ORDER by is a window rule;
ROW | range... AND is the window range (row is like finding the corresponding location through the front AND back physical seats of its own location, which is relatively fixed; AND range is to find the location through certain conditions, which requires calculation to determine the corresponding location, for example, if I am 3 years old and I am two years old .)

There are many ways to retrieve the number of window ranges, which seem complicated and not difficult. We will analyze them slowly:

Three key locations: 1. Grouping the first ROW Unbounded preceding 2. Current ROW of the Current ROW 3. Grouping the last ROW Unbounded following

Next, three simplest fetch ranges are generated:

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

Why is the course analysis function very complicated? Let's take a look at two ways to get the number of window ranges. One is based on the physical location, that is, the row keyword, one is to get the number based on the logical location, that is, range. Then the range of the number is changed from 3 to 6, as shown below:

The first row of the ROWS group-> the current row, the first row of the group-> the last row of the Group, the current row-> the last row of the Group
The first row of the RANGE Group-> the current row, the first row of the group-> the last row of the Group, the current row-> the last row of the Group

However, have you considered all of these scenarios? There are still some missed considerations. Well, let's continue to look at the possibilities:

From the first row of the group-> the first n rows of the current row rows between unbounded preceding and n rows of preceding
From the first row of the group-> n rows after the current row rows between unbounded preceding and n rows following

From the current row to the next n rows of the current row rows between current row and n rows of following
From the first n rows of the current row-> current row rows between n rows preceding and CURRENT ROW

From the first n rows of the current row-> the last row of the group rows between n rows preceding and unbounded following
From the next n rows of the current row-> the last row of the group rows between n rows following and unbounded following
From the front n1 row of the current row to the front n2 row of the current row, rows between n1 row preceding and n2 row preceding
From row n1 after row n1> to row n2 after row n2 rows between n1 following and row n2 following

No, yes!

Rows BETWEEN n1 row preceding and n2 row following from the beginning of the current row to the end of the current row

There are also n rows from the last row of the Group to the current row, and n rows from the current row to the last row of the group?
Oh, no. By the way, you are not confused enough. remember so much ???

Finally, there is a window starting from the current row and ending with the current row. However, this is meaningless. What should we do? Therefore, there are at least nine computations.
Considering rows and range, there are 18 more troops. Add the first six, that is, 24. However, the 18 ones that come out later are less likely to be used than the first six.

By the way, there is another important thing to explain.

If order BY clause is used, between unbounded preceding AND current row are grouped into the first row to the current row.
Without between AND, without order BY, it is the first row of the Group to the last row of the group; BETWEEN unbounded preceding and unbounded following

Analysis functions VS Aggregate functions

From the above example, we can know that the main differences between analysis functions and Aggregate functions are:

1. Analyze the number of rows returned by each group of functions (for example, one more pseudo column ). The aggregate function aggregates all rows into one row no matter how many rows in each group.

2. the order by function of the analysis function is different from the order by function of the aggregate function. The order BY Mark of the analysis function defines the range from the first row of the Group to the current row, and then according to this range, start aggregation and accumulation based on this order. Oracle by of Aggregate functions does not affect data, but only sorting.