The MySQL8 window function is a special function. It can perform an aggregate operation in a group of query lines, but it will not fold the query line into a single output line, but generates a result for each query line.The window function can be used to process complex report statistical analysis scenarios, such as calculating the moving average, accumulation and, ranking, etc.Among them, bloggers believe that the main power of it is that "it can directly add new aggregate fields on the basis of not modifying the output results of the original statement."
The window function syntax is as follows:
Functions can be a polymer or non -polymer function.MySQL8 supports the following types of window functionsPune Investment. Serial number function: It is used to generate a serial number for each line in the window, such as, etc.Distribution function: The relative positions of each line in the window in the window, such as such as, such as.The front and rear functions: It is used to obtain the value of a certain line of the current line in the window, such as, such as.Head and tail function: used to obtain the value of the first or last line in the window, such as, etc.Polymerization function: Used to calculate the aggregation value of a field in the window, such as, etc., such as.
MySQL official website provides
Keywords are very important. It is used to identify whether the window function is used, and the grammar is as follows
Both forms define how the window function should handle the query line.The difference between them is whether the window is directly defined in the middle, or it can be reused based on the words.Regular usage, the window specifications directly appear between the brackets in the clause.Based on the name of the window specification defined by other places in the query, it can be reused.This article will be explained follow -up.
The clause is used to divide the query results into different partitions. The window function is executed separately on each partition. The grammar is as follows
The clause is used to sort the query results in each partition. The window function will be calculated in the order of sorting. The grammar is as follows
It is an optional decorative sentence of the window function. It is used to specify the data range in each partition, which can be static or dynamic.The grammar is as follows
Among them, the unit that indicates the range of the window may be or.Indicates the number of rows, indicating the value -based.The starting position and end position of the range of the window can be the following forms :: indicates the current line.: Means the first line in the partition.: Means the last line in the partition.: Indicate the value of the current line.: Express the value of the current line.
For example, if you specify, the window range includes the current line, the first two lines and the latter line.If specified, the window range includes all rows that are currently reduced within 10 in the current row.If there is no specification, the default is, that is, starting from partition to the current line.
MySQL8 refers to the window defined and named in the clause, which can be quoted through the window name in the clause.The advantage of using is to avoid repeatedly defining the same window in multiple classes, but only need to be defined once in the clause, and then quoted in the clause.For example, the following query uses three same windows:
It can be used to simplify it:
In this way, you only need to define a window called in the clause, and then quote it in three clauses.
If a clause is used instead of, you can add other sentences behind the reference window to modify the window.For example, the inquiry below defines a window containing partitions, and uses different sorts in two classes to modify the window:
In this way, the first and last year of each country can be obtained according to different signs.
The definition of a name window itself can also start with a window.This can realize the reference between the windows, but cannot form a cycle.For example, the following query defines three naming windows, of which the second and third are quoted the first:
This can calculate the accumulation of each value based on different scope.
The following uses a simple example table to illustrate the usage of the MySQL8 window function, and prepare the SQL script in advance as followsBangalore Stock Exchange
This is a sales information list, including four fields: year, country, product and profit.Let us perform some statistical analysis based on the window function, such as:
Calculate the annual total profit of each country and sort according to the country and year
It can be seen that the total profit of each country is calculated each year, but it is not folded to a single output line, but generates a result for each query line.
Here it reflects the power of the blogger’s saying that it does not modify the original results and adds the power of polymerization fields.
Calculate the sales ranking of each country in each country, and sort according to the country and rankings
It can be seen that the sales ranking of each product in each country has been calculated. With a function, it will distribute the same ranking to the same profit and jump after the ranking.Careful friends may find that after the sales ranking of products of the same country is repeated, the next place will jump. If you don’t want to use functions,
Calculate the cumulative profit of each country, and sort according to the country and profit
It can be seen that the cumulative profits of each product of each country have been calculated, using functions, and specifying the scope of the window, indicating that starting from partition to the current bank.
Based on rewriting problem three, SQL is as follows
Advantages: The window function can operate, sorting, ranking and other operations on the query lines in each partition without changing the number of original table rows, which improves the flexibility and efficiency of data analysis.The window function can use the sliding window to process the dynamic data range, such as calculating the moving average, accumulation, and so on.The window function can be used in combination with ordinary agglomeration functions, child query, etc. to achieve more complex query logic.
Disadvantages: The grammar of the window function is more complicated, and you need to pay attention to the meaning and role of each parameter in the clause.The execution efficiency of the window function may not be as good as that of ordinary polymerization functions, because it needs to calculate each query line in each partition instead of folding into a single output line.The window function can only be used in lists and phrases, and cannot be used in, waiting for sentence sentences.
Regarding querying performance here, the performance of the window function depends on multiple factors, such as the type of window function, the size of the window, the number of partitions, and the cost of sorting.Generally speaking, the performance of the window function is better than the method of using a sub -query or connection, because the window function only needs to scan the data once, and the child query or connection may need to scan or connect multiple times.
However, not all window functions can be calculated efficiently.Some window functions, such as …, etc., only need to sort the data in the partition, and then calculate the results according to the current position of the line. The performance of these window functions is better.Other window functions, such as,,, etc., need to aggregate the data in the partition or in the window, the performance of these window functions is poor.
In order to improve the performance of the window function, the following methods can be used: select the appropriate window function to avoid using complex or repeated window functions.Use to define and reference windows to avoid repeating the same window in multiple clauses.Minimize the cost of partitions and sorting, and use indexes or materialized views to accelerate partitions and sorting.Minimize the size of the window and use the appropriate to limit the data range in the window.Try to use parallel processing to accelerate the calculation of the window function, and use multi -core or distributed systems to improve efficiency.
The application scenarios of the window function are very wide, and many data analysis and mining tasks can be completed.The MySQL8 support window function is a great feature, which greatly improves the competitiveness of MySQL in the field of data analysis.I hope that through this article, I can help everyone have a preliminary understanding of the Mysql8 window function.
Mumbai Investment