Recently, after watching the SQL based tutorial, feel the part of window function in the not too clear, also found that many of the online tutorial in a similar situation, mainly reflected in the designated window frame (i.e. ROWS | RANGE BETWEEN) part explain enough clear and thorough, So here with my personal understanding of the window function related content comb again, welcome correction!

1 overview

Window functions are functions that can only be used in the SELECT clause and can return grouped statistics related to a specified number of adjacent rows.

• Basic composition:

  [Function name]OVER([groupingPARTITION BY[Arrange orderORDER BY[window frame])Copy the code

• Order of execution: Group –> order –> execute functions for each window

Such as:

SELECT product_id, product_name, product_type, sale_price,
    	SUM(sale_price) OVER (PARTITION BY product_type
                            	ORDER BY product_id DESC
                            	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum
FROM Product;
Copy the code

The window function does the following in order:

• PARTITION BY product_type: according to theproduct_typegrouping
• ORDER BY product_id DESC: Press within each groupproduct_idDescending order
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: Specifies the current line and two adjacent lines as Windows
• SUM(sale_price): Perform the pairs in each windowsale_priceThe sum of

2 groups:PARTITION BY

Select a variable as a grouping condition, and all subsequent operations, including sorting, window partitioning, and function execution, are carried out within each group without interference between the groups.

3 sort:ORDER BY

Within a group, sort Windows by specified variables.

4 (key!) Window frame:

Specifies which rows participate in the calculation of the current row statistics.

Grammar:

RANGE|ROWS BETWEENceilingANDThe lower limitCopy the code

• Upper and lower:

  [CURRENT ROW] | [<num>|UNBOUNDED PRECEDING|FOLLOWING]
  Copy the code

  • CURRENT ROW: CURRENT ROW

  • Slide up and down:

    • PRECEDINGSwipe up for the current row,FOLLOWINGSlide down for the current row.
    • You can use<num>Specify the number of units of up/down motion (ROWandRANGEThe units have different meanings), e.g2 PRECEDINGRepresentatives include the bank, one unit above the bank and two units above the bank; Or useUNBOUNDED, to select all above and below the current row.

  • If you want to express the current line AND several units above it, you can omit BETWEEN AND

    ROWS|RANGE <num>|UNBOUNDED PRECEDING
    Copy the code

• ROWSwithRANGE

  • ROWSRepresents the absolute position of each row in the table, sliding each unit as a row, can be detachedORDER BYUse.
  • RANGEMust matchORDER BYUse, representORDER BYSpecifies the value of a variable. The unit of sliding is the change of value 1, and the upper and lower limits represent the range of values, such as1 PRECEDINGRepresents an upward change of 1 in the value of the current row. Notice that we’re moving up by 1 instead of adding or subtracting 1, because that’s the same thing asORDER BYIs related to the ascending and descending order. It’s minus in ascending order, plus in descending order, but it doesn’t change that they’re all going up the table.

  Such as:

  SELECT s_id, s_score,
  		SUM(s_score) OVER (ORDER BY s_score 
                                     ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) row_,
  		SUM(s_score) OVER (ORDER BY s_score 
                                     RANGE BETWEEN 9 PRECEDING AND CURRENT ROW) range_9,    
  		SUM(s_score) OVER (ORDER BY s_score 
                                     RANGE BETWEEN 10 PRECEDING AND CURRENT ROW) range_10
  FROM Student;          
  Copy the code

  

• The default value

  • whenORDER BYWhen both and Windows are missing, the default is

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    Copy the code

  • The specifiedORDER BYWhen the window is missing, the default is

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    Copy the code

If the boundary is reached, the provinces outside the boundary are removed, as shown in green in the first example.

5 function

• Special window function
  • RANK: rank, with the rank of the line count more
  • DENSE_RANKRank the row with the rank counts as one
  • ROW_NUMBER: Numbered in sequence

  Such as:

  SELECT s_id, s_score,
         RANK(a)OVER (ORDER BY s_score) rank_ ,
         DENSE_RANK(a)OVER (ORDER BY s_score) dense_rank_  ,
         ROW_NUMBER(a)OVER (ORDER BY s_score) row_num_
  FROM Student;
  Copy the code

  

• Aggregation function
  • COUNT()
  • SUM()
  • AVG()
  • MIN()/MAX()