SQL Advanced -3- sort and window functions

When using the database to make various statistical data, it is necessary to sort the data, such as “score, sales volume, number of people” and other numerical values. There are usually two kinds of sorting methods:

  • Skip the subsequent order sorting
  • Do not skip the order sorting after

The window function

Window functions are only supported in the latest version of MySQL!

Window functions are only supported in the latest version of MySQL!

Window functions are only supported in the latest version of MySQL!

Reference data: https://zhuanlan.zhihu.com/p/92654574

What is a window function

Window function, also called OLAP function (Online Anallytical Processing, Online analysis Processing), can be used for real-time analysis and Processing of database data.

grammar

Basic syntax for window functions:

< window function > over (partition by < name of the column used for grouping > -- partition clause can be omitted, do not specify group order by < name of the column used for sorting >)Copy the code

There are two types of functions in the < window function > position:

  • Dedicated window functions,Rank, dense_rank, and row_numberEtc.
  • Aggregate functions, such asSum, avg, count, Max, minEtc.

Because window functions operate on the results of the WHERE or group by clause, “window functions can only be written in the SELECT clause in principle.”

function

  • It also has the function of grouping and sorting
  • Do not change the number of rows in the original table
  • In principle, window functions can only be written inselectclause

The actual scene

  • Ranking problem: Each department is ranked by performance
  • topNProblem: Find out where each department ranks firstNTo reward employees

rank/dense_rank/row_number

The instance

  • rank: Tie jump ranking
  • dense_rank: tie for consecutive ranking
  • row_number: Sequential ranking

The difference between these three functions can be clearly seen in one example:

select 
	name,price,
	rank() over (order by price desc) as rank_1,
	dense_rank() (order by price desc) as rank_2,
	row_number() (order by price desc) as rank_3
from products;
Copy the code

conclusion

Orange 100 1 1 1 watermelon 80 2 2 apple 50 3 3 banana 50 3 3 3 1 grape 50 3 3 3 1 lemon 30 6 4 6Copy the code

Conclusion:

  • rank()After the occurrence of the same order, the same order is skipped
  • dense_rank()The same order is not skipped
  • row_number()Arrange them in the order of the natural numbers

❝

In the three dedicated window functions above, the parenthesis after the function does not take any arguments. Just leave () blank.

❞

Zhihu example

Realize the rank ()

Select *, rank() over (partition by class order by rank desc) as ranking from classCopy the code

Don’t change the number of rows

Non-equivalent join to implement rank()

select p1.name,p1.price,
			(select count(p2.price) 
       from products p2
       where p2.price > p1.price) + 1
       as rank_1
from products
order by rank_1;
Copy the code
  • The function of a subquery is to calculate the ratio itself(p1)High record, and take it as their rank
  • For example, after the weight of the price,80,50 {100}Analyze and sort, compare100The number greater than 80 is 1, and the number greater than 50 is 2
  • + 1The rest of the rankings are actually what will be mentioned belowdense_rank()Ranking of functions
The price ranking + 1
100 0 1
80 1 2
50 2 3

“If you want the sort to start at 0, remove the plus 1” :

Non-equivalent connections implement dense_rank()

mysql> select p1.name, p1.price,
    (select count(distinct p2.price) from products p2 where p2.price > p1.price) + 1 as rank_1
from products p1 
order by rank_1;
Copy the code

Implement row_number() with a variable

Implement variables in MySQL5.7.28 to implement the row_number function

mysql> select p.name, p.price, (@pro_rank := @pro_rank + 1) row_Number
    -> from products p,(select @pro_rank := 0) r
    -> order by price desc;
Copy the code

For more advanced MySQL versions, use the row_number() function directly

select name, price,
		row_number() over (partition by name order by price desc) as rowNumber
from products
Copy the code

Aggregate functions serve as window functions

Aggregate window functions are used in exactly the same way as the dedicated window functions mentioned above. You just need to write “aggregate function” in place of the window function

  • “Functions must not be empty inside parentheses“
  • You need to specify the column name for the aggregation

Must be implemented in an advanced MySQL version or Hive

mysql> select *,
    -> sum(price) over (order by name) as rank_sum,
    -> avg(price) over (order by name) as rank_avg,
    -> max(price) over (order by name) as rank_max,
    -> count(price) over (order by name) as rank_count
    -> from products;
Copy the code