Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to quickly understand the SQL window function". In the daily operation, I believe many people have doubts about how to quickly understand the SQL window function. The editor consulted all kinds of materials and sorted out a simple and easy-to-use method of operation. I hope it will be helpful for you to answer the doubt of "how to quickly understand the SQL window function". Next, please follow the editor to study!

What is a window function / analysis function?

Window functions are similar to functions that can return aggregate values, such as SUM (), COUNT (), MAX ().

But the window function is different from the ordinary aggregate function in that it does not group the results so that the number of rows in the output is the same as the number of rows in the input.

Analysis of window function

A window function looks something like this:

SELECT SUM () OVER (PARTITION BY___ ORDER BY___) FROM Table

Here are three points to keep in mind:

1. Aggregation function: in the above example, we used SUM (), but you can also use computing functions such as COUNT (), AVG (), etc.

2. PARTITION BY: you just need to think of it as a GROUP BY clause, but in the window function, you write PARTITION BY

3. ORDER BY:ORDER BY is no different from ORDER BY in ordinary query statements. Note that the order of the output should be carefully considered

Example: aggregate function VS window function

Suppose we have the following table:

If you want to get the average GPA by gender, you can use the aggregate function and run the following query:

SELECT Gender, AVG (GPA) as avg_gpa FROM students GROUP BY Gender

The results are as follows:

The next step is key!

Now we want to get the following results:

Of course we can use the aggregate function we just mentioned, and then join the result to the initial table, but this takes two steps.

But if we use window functions, we can do it in one step and get the same result:

SELECT *, AVG (GPA) OVER (PARTITION BY Gender) as avg_gpa FROM table

Through the above query, we are breaking down the data by gender and calculating the average GPA for each gender. It then creates a new column called avg_gpa and appends the associated average GPA for each row.

Advantages of window functions

simple

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

fast

This is related to the previous point, which is much faster to use window functions than alternative methods. This is very useful when you are dealing with hundreds of gigabytes of data.

Multi-function

The most important thing is that window functions have a variety of functions, which are not mentioned in this article, such as adding moving averages, adding line numbers and lagging data, and so on.

At this point, the study on "how to quickly understand the SQL window function" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.