Shulou(Shulou.com)06/02 Report--

Editor to share with you how to use Oracle database functions, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1. What is a window function?

Window functions also belong to analysis functions. Oracle has provided window functions since 8.1.6, which are used to calculate certain aggregate values based on groups.

The window function specifies the size of the data window in which the analysis function works, which may vary from row to row.

Unlike the aggregate function, the aggregate function returns multiple rows for each group, while the aggregate function returns only one row for each group.

Basic syntax: < parse function > over (partition by < column name for grouping > order by < column name for sorting). The parsing function in grammar is mainly composed of sequential functions (rank, dense_rank, row_number, etc.) and aggregate functions (sum, avg, count, max, min, etc.) as window functions.

From the composition of the window function, it is the functional combination of group by and order by. After grouping and summarizing, group by changes the number of rows in the table. There is only one category in a row, while partiition by does not reduce the number of rows in the original table.

Just like the composition of the window function, it has the function of grouping and sorting at the same time, and does not reduce the number of rows of the original table.

The OVER keyword means to treat a function as a window function rather than an aggregate function. The SQL standard allows all aggregate functions to be used as window functions, and the OVER keyword is used to distinguish between the two.

2. Window function-- opening window

Options are often added in parentheses after the OVER keyword to change the window range in which aggregation operations are performed. If the option in parentheses after the OVER keyword is empty, the window function aggregates all rows in the result set.

Parsing function over (partition by column name order by column name rows between start position and end position)

Why is it called opening the window?

Because in over () parentheses, the partition () function can open a separate window for the queried data. For example, inquiring about the ranking of students in each class, querying the population of each country over the years, and so on, all open a window in each class and each country to execute orders separately.

Rows and range denote selecting the next few rows and selecting the data range, respectively

Understand the meaning of rows between, also known as the window clause:

Preceding: forward following: backward current row: current line unbounded: borderless, unbounded precending means from the front starting point, unbounded following: indicates the last end note: without partition by, the whole data set is treated as a partition, without order by, it will affect the statistical results of some functions, such as sum ()

3. The usage of some analytical functions.

1. Aggregate function

Aggregate function definition sum () summation max () maximum value min () minimum value avg () average count () statistics

two。 Sequence function

Sequence function definition row_number () produces a self-increasing number when it is sorted by value, does not repeat when the value is equal, does not produce vacancy rank (), produces a self-increasing number when the value is sorted, repeats when the value is equal, produces a self-increasing number when the vacancy dense_rank () is sorted by value, repeats when the value is equal, and does not produce a vacancy.

Row_number ()

Select *, row_number () over (oder by score desc) as ranking from class table

Query results:

+-+ | name | Sex | Class | Grade | Rank | +-+ | Zhang San | male | 1 | | 100 | 1 | | Li Si | female | 3 | 100 | 2 | | Zhang San | female | 1 | 100 | 3 | | Wang Wu | female | 2 | 99 | 4 | | Zhao Si | male | 2 | 90 | 5 | Sun Liu | male | 2 | 90 | 6 | | Pleasant Goat | male | 3 | 85 | | | 7 | | American Sheep | female | 4 | 82 | 8 | | lazy | female | 1 | 80 | 9 | | slow Sheep | female | 2 | 70 | 10 | +-+ |

Rank ()

Select *, rank () over (oder by score desc) as ranking from class table

Query results:

+-+ | name | Sex | Class | Grade | Rank | +-+ | Zhang San | male | 1 | | 100 | 1 | | Li Si | female | 3 | 100 | 1 | | Zhang San | female | 1 | 100 | 1 | Wang Wu | female | 2 | 99 | 4 | | Zhao Si | male | 2 | 90 | 5 | Sun Liu | male | 2 | 90 | 5 | Pleasant Goat | male | 3 | 85 | | | 7 | | American Sheep | female | 4 | 82 | 8 | | lazy | female | 1 | 80 | 9 | | slow Sheep | female | 2 | 70 | 10 | +-+ |

Dense_rank ()

Select *, row_number () over (oder by score desc) as ranking from class table

Query results:

+-+ | name | Sex | Class | Grade | Rank | +-+ | Zhang San | male | 1 | | 100 | 1 | | Li Si | female | 3 | 100 | 1 | | Zhang San | female | 1 | 100 | 1 | Wang Wu | female | 2 | 99 | 2 | 2 | Zhao Si | male | 2 | 90 | 3 | Sun Liu | male | 2 | 90 | 3 | Pleasant Goat | male | 3 | 85 | | | 4 | | American Sheep | female | 4 | 82 | 5 | | lazy | female | 1 | 80 | 6 | | slow Sheep | female | 2 | 70 | 7 | +-+ |

3. Other classes

Other classes define the rank value of the current row in the percent_rank () group-1 / the total number of rows in the group-1lag () is used to count the value of the nth row up in the window. The first parameter is the column name, the second parameter is the nth row up (optional, the default is 1), and the third parameter is the default value (when the nth behavior up is NULL, take the default value, if not specified Then NULLlead () is used to count the nth row value in the statistical window. The first parameter is the column name, the second parameter is the next nth row (optional, default is 1), and the third parameter is the default value (when the next nth behavior NULL, take the default value, if not specified, then NULLntile () is used to divide the grouped data into n slices sequentially, return the current slice value first_value () and sort within the group, as of the current row. After the first value last_value () is sorted within the group, as of the current row, the last value cume_dist () returns the number of rows less than or equal to the current value / the total number of rows in the group 4, OVER () parameter-grouping function

Partition by clause:

The partition by clause in parentheses after the over keyword of the window function can be used to define the partition of the row for aggregate calculation. Unlike the group by clause, the partitions created by the partition by clause are independent of the result set, the partitions created are only for aggregate calculation, and the partitions created by different window functions do not affect each other. 5. OVER () parameter-sorting function

Order by clause:

The order by clause can be used in window functions to specify collations in the options after the over keyword, and some window functions also require that collations be specified. Use the order by clause to sort the result set according to a specified collation and aggregate within a specified range. Syntax: ORDER BY field name RANGE | ROWS BETWEEN boundary rule 1 AND boundary rule 2

The PARTITION BY clause and ORDER BY can be used together to achieve more complex functions

The above is all the contents of the article "how to use Oracle database functions". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.