Window Functions in MySQL, Advanced and extremely useful (Part I).

Tram Ho

Hello everyone, I just got to know the Window Functions topic, I personally feel quite good and I appreciate it as an advanced part. Because few people know it, Window Functions finds it very rarely used, instead of lengthy subquery like the message to a crush , and others understand that the query is as short as the answer of the crush “yes” . Now I will introduce to everyone what Window Functions are, and which problems apply.

I. What is Window Functions?

Mysql supports window functions, ie, for each row from the query, perform calculations using the rows associated with that row. Most of the Aggregate Functions (the functions we often use like COUNT, SUM, MAX, MIN,..) Can also be used as Window Functions when combined with OVER() Hmm … still a bit difficult understand, let’s go into a simple example to visualize the face of Window Functions like.

Suppose there is 1 table of products :

Use Aggregate Functions – SUM as usual, to get the total number of products in stock by category

But what if I want to get the details of the products with the total number of products in stock by category . Normally, we will do a subquery and then join is done, right?

Pretty wordy, but let’s see if you use the Window Functions .

If you pay attention, you will see that Query1 and Query3 differ only OVER () – what it is, please calm down. But itself helps MYSQL distinguish Window functions and Aggregate Functions , it will create a window including rows related to it based on partititon to SUM.

Both ways will give the same result, but you can see that using Window Functions is more convenient, right. If you understand the meaning and usage of Window Function , I bet you will choose it instead of writing the long subquery like a pump.

Note: MySQL currently only supports using Window Functions since version 8.0

II. Analyze the Window Functions component

A Window Function is defined when there is an OVER() clause following the function call.

  • partiiton_clause: For Aggregate Functions when we want to group related rows together, we use GROUP BY , in Window Functions also, that is PARTITION BY , the partition will group rows related to the current row into 1 window to perform calculations. As an example in Query3 I have used [partition by category]
  • order_clause: in the same way as normal ORDER BY, sorts the rows in the workbook
  • frame_clouse: in case you want to limit rows in partition -> Window Frame

Note: when using frame_clouse, it is imperative to use order_clause otherwise MYSQL will not know the position of the rows to create the Frame.

In the case of EVER empty, what about: OVER () -> all data of the table is considered 1 block – window This paragraph I learn it takes a lot of time to understand how it works. So I will demo an example for everyone to easily grasp. For details, everyone can see the doc here .

In column total , each resulting row will be the sum of the current row, the previous 2 rows and the 2 rows after. Conversely, if you replace ROWS with RANGE , each resulting row will be the sum of the current row for all rows with a quantity_warehouse +/- 2.

III. Several of the Window Functions are advanced and extremely useful.

Function namedescription
cume_dist ()(Number of records in window <= current row) / total records
dense_rank ()Ranking of current row, with no gaps
rank ()Rank the current row in the window based on order by, with a space
row_number ()Numbering order in the window
first_value (expr)Get the first value in the window
last_value (expr)Get the final value in the window
nth_value (expr, n)Get the value of n in the window
lag (exp, n, default)Get the value before n units (n, default: option nullable)
lead (exp, n, default)Get the value then n units (n, default: option nullable)
ntile ()Divide the window into n groups -> returns the group number
percent_rank ()(rank – 1) / (rows – 1)

 

Again : partiiton_clause groups rows related to the current row into one block – the window. There are also Aggregate Functions when combined with OVER (). To find out the details of Window Functions ‘ operations, please see Part II in the article (Soon ^^).
The next part only has practice, so everyone try to master the knowledge so that they do not get eaten later, in the next part, we will have some extreme problems, solved after going to the interview x10 salary =)) Wait! …
Window Functions in MySQL, Advanced and extremely useful (Part II)
Thank you to everyone who followed my post! Have a nice working day!

Related document

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html .

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html .

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html .

Share the news now

Source : Viblo