SQL learning window function

The window function is also called OLAP function .OLAP yes OnLine AnalyticalProcessing For short , It means real-time analysis and processing of database data .

The general form of window function ：

< Window function > OVER ([ PARTITION BY < Name >] 
ORDER BY
	< Column names for sorting >)  

• PARTITON BY Is used to group , That is, select which window you want to see , Be similar to GROUP BY Clause grouping function , however PARTITION BY Clause does not have GROUP BY The summary function of clause , It does not change the number of rows recorded in the original table .
• ORDER BY It's used to sort , That is, in the decision window , According to that rule ( Field ) To sort .

for instance ：

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

Here we use product_type Make a classification , Then sort the prices under each category .

Window function classification

Sort special window function

• RANK function
  There is the same bit skip , Such as 1,2,2,4
• DENSE_RANK
  Won't skip bits , Such as 1,1,1,2
• ROW_NUMBER
  The same order will not appear , Even the same will be in different order

as follows ：

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

The use of aggregate functions in Windows

Aggregate functions become cumulative when used in window functions ：

SELECT
	product_id,
	product_name,
	sale_price,
	SUM( sale_price ) OVER ( ORDER BY product_id ) AS current_sum,
	AVG( sale_price ) OVER ( ORDER BY product_id ) AS current_avg   
FROM
	product;

Calculate the moving average

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

have access to PRECEDING and FOLLOWING To specify the scope of the aggregate function .
Like above ,ROWS 2 PRECEDING Indicates starting from the current line , Add the data of the first two lines of the current line , Then average .
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING Indicates the current line and the line before and after the current line , Then average .
give the result as follows ：

GROUPING Operator

ROLLUP - Calculate total and subtotal

SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type, regist_date WITH ROLLUP

Exercises

5.1

The execution result of the following statement

SELECT  product_id
       ,product_name
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
  FROM product

The execution result of this statement is the highest selling price in all current records from top to bottom .

5.2

Continue to use product surface , Calculate according to the registration date （regist_date） The sales unit price of each date in ascending order （sale_price） Total of . Sorting is required to set the registration date to NULL Of “ motion T T-shirt ” The record is at 1 position （ That is, think of it as earlier than other dates ）

SELECT
	product_id,
	product_name,
	sale_price,
	sum( sale_price ) OVER (
		
	ORDER BY
	IF
	( ISNULL( regist_date ), 0, 1 )) AS Current_sum_price 
FROM
	product;

5.3

Thinking questions

① The window function does not specify PARTITION BY What is the effect of ？

② Why is it that window functions can only be used in SELECT Used in clauses ？ actually , stay ORDER BY Clause using the system does not report an error .