What are window functions

Window functions, also called OLAP functions (Online Anallytical Processing), can analyze and process database data in real time.

The basic syntax for window functions is as follows:

< window function > over (partition by < partition by > order by >)Copy the code

1) PARTITION BY: You just think of it as the GROUP BY clause, but in the window function, you write PARTITION BY. Group by changes the number of rows in the table to a single category. The partiition by and rank functions will not reduce the number of rows in the original table, as shown in the figure below:

2) ORDER BY: ORDER BY is no different from ORDER BY in normal query statements.

What are the window functions

1) Special window functions, including rank, dense_rank, row_number and other special window functions to be discussed later.

2) Aggregate functions, such as sum. Avg, count, Max, min, etc

Window function function

1) It has both partition by and order by functions

2) Does not reduce the number of rows in the original table, so it is often used to rank within each group

Matters needing attention

Window functions can in principle only be written in the SELECT clause

Window function usage scenarios

Business requirements are “ranked within each group”, such as:

Question: identify the topN employees in each department and reward them

The instance

Write an SQL query to implement the score ranking.

If two scores are the same, they Rank the same. Note that the next place after the split should be the next consecutive integer value. In other words, there should be no “gaps” between rankings.

+ – + — — — — — — — + | | Id Score | + – + — — — — — — — + | 1 | | 3.50 3.65 | | 2 | 3 | | 4.00 | | | | 3.85 4 5 4.00 | | | | | 3.65 6 | + – + — — — — — — — +, for example, according to the given Scores table, your query should return (sorted by score from high to low) :

+ — — — — — — – + — — — — — — + | Score | Rank | + — — — — — — – + — — — — — – + 4.00 | 1 | | | | 1 | 4.00 3.85 2 | | | | | 3 | 3.65 3.65 | | 3 | | 3.50 4 | | + — — — — — – + — — — — — – +

Source: LeetCode link: leetcode-cn.com/problems/ra… Copyright belongs to the Collar buckle network. Commercial reprint please contact official authorization, non-commercial reprint please indicate the source.

Examples of the above topics:

Instead of using window functions, you might write:

SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS ‘Rank’ FROM Scores s ORDER BY Score DESC ;

Using window functions:

select Score, dense_rank() over (order by Score desc) as Rank from Scores order by Score desc;

Advantages of window functions

simple

Window functions are easier to use. In the example above, using the window function takes only one more line to get the desired result, compared to using the aggregate function and then merging the result.

fast

Related to the previous point, using window functions is much faster than using alternatives. This is very useful when you’re dealing with hundreds or thousands of gigabytes of data.

versatility

Most importantly, the window function has a variety of functions that are not mentioned in this article, including adding moving averages, adding line numbers and lagging data, and so on.