Task05: window functions, etc.  Tianchi Longzhu program SQL training camp
Original tutorial link

Recommended reference books: SQL basics tutorial
Window function
Window functions are also called OLAP functions. OLAP is short for online analytical processing, which means realtime analysis and processing of database data.

Conventional SELECT statements are used to query the whole table, and the window function allows us to selectively summarize, calculate and sort some data.
General form of window function:
<Window function> OVER ([PARTITION BY <Listing>] ORDER BY <Column name for sorting>)

PARTITION BY is used for grouping, that is, to select which window to look at, which is similar to the grouping function of the GROUP BY clause. However, the PARTITION BY clause does not have the summary function of the GROUP BY clause and does not change the number of rows recorded in the original table.

If the partton by keyword is not added, the whole table is a window

Note that MySql is a window for the line and the previous behavior



ORDER BY is used to sort, that is, to determine the sort rule (field) in the window.


example:

SELECT product_name ,product_type ,sale_price ,RANK() OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM product

In this case, press product_type group, and then within the group, press sale_price sort. Note that press product here_ The type grouping does not merge the of a group into one row.

result:

Where window functions are used
Generally, it can only be used in the select clause
 Syntactically, in addition to the SELECT clause, the ORDER BY clause or the SET clause of the UPDATE statement can also be used. However, there are few actual business examples, so at the beginning, we just need to remember that "it can only be used in the SELECT clause".
Why？
 In DBMS, window function is used to operate the "result" processed by WHERE clause or group by clause
Window function classification
It can be divided into two categories:

First, aggregate functions such as SUM, MAX and MIN are used in window functions

Second, RANK and DENSE_RANK and other special window functions for sorting
Special window functions:

RANK
 When there are 3 records in the first place: 1, 1, 1, 4

DENSE_RANK
 When there are three records in the first place: 1, 1, 1, 2

ROW_NUMBER
 When there are three records in the first place: 1, 2, 3 and 4

example:

SELECT product_name ,product_type ,sale_price ,RANK() OVER (ORDER BY sale_price) AS ranking ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num FROM product

Application of aggregate function in window function
When the aggregate function is used in the window function, it calculates the aggregate of all data accumulated to the current row.
All aggregate functions can be used as window functions, and their syntax is exactly the same as that of special window functions.
example:

SELECT product_id, product_name, sale_price, SUM (sale_price) OVER (ORDER BY product_id) AS current_sum FROM Product;
 Result: calculate the total value of the sales unit price of the goods with the commodity number "less than yourself".
Calculate moving average
You can specify the summary range in more detail for the rows contained in the window: the summary range is called the frame.

example

 Code listing 86 Specify the nearest 3 rows as the summary object SELECT product_id, product_name, sale_price, AVG (sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg FROM Product;

Rows 2 predicting the first 2 rows and this row, or: the 3 rows closest up
 Here, we use the keywords "ROWS" and "predicting" to specify the frame as "up to the previous ~ ROWS". Therefore, "ROWS 2 predicting" is to specify the frame as "up to the previous 2 ROWS", that is, to limit the records as summary objects to the following "closest 3 ROWS".

If the number in the condition is changed to "rows 5 predicting", it means "up to the previous 5 rows" (the nearest 6 rows)


Such a statistical method is called moving average
allied:

Specify the frame as 2 lines after the current record (the nearest 3 lines):

ROWS 2 FOLLOWING


If you want to use the front and back lines of the current record as summary objects, you can use the keywords "PRECEDING" and "FOLLOWING" at the same time.

SELECT product_id, product_name, sale_price, AVG (sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM Product;

Take the interval of "1 predicting" (previous line) and "1 FOLLOWING" (subsequent line) as the summary object
● record of the previous line
● self (current record)
● record on the next line



be careful:

ORDER BY in the OVER clause is only used to determine the order in which window functions are calculated, and has no effect on the order of results.

If you want the records to be sorted according to the ORDER BY clause in the window function, you need to sort the results after the main query statement:

SELECT product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) AS ranking FROM Product ORDER BY ranking;

Finally, ORDER BY ranking is added;


GROUPING operator
Only ROLLUP is introduced here. For the rest, please refer to the books recommended at the beginning

The GROUPING operator includes the following three types

ROLLUP

CUBE

GROUPING SETS


ROLLUP

Subtotal line and total line can be calculated at the same time

example:

SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type, regist_date with ROLLUP;



For more details, it is recommended to refer to the books listed at the beginning of the article
Problem solving:
5.2
Continue to use the product table to calculate the total sales_price of each date in ascending order of registration date. The sorting is to rank the "sports Tshirt" record with NULL registration date in the first place (that is, it is regarded as earlier than other dates)
Answer:
 incorrect null Answer for processing arranged in the first line: SELECT product_type,regist_date,SUM(sale_price) AS sum_price FROM product GROUP BY regist_date,product_type WITH ROLLUP ;