Shulou(Shulou.com)06/01 Report--

MySQL 8.0 adds window functions, which are similar to oracle window functions and belong to one of the major features of mysql

1.ROW_NUMBER () OVER () generates a new sequence and sorts it according to c2 to generate a new sequence.

Grouped according to C1, and sorted by c2 to generate sequences

2.cume_dist () OVER () under certain sorting conditions, the number of rows less than or equal to the current row value / the total number of rows

Group by C1, sort by c2, and calculate the number of rows / total rows of the current row value.

3.rank () OVER () is similar to row_number () to generate a new sort

Group according to C1 and sort by c2 to generate a new sequence. When the two sorting values are the same, the rank is the same.

4.PERCENT_RANK () OVER () current RANK value-1 / total number of rows-1

5.DENSE_RANK () OVER () in order to solve the problem of rank () numbering, for example, if there are two ones in the rank value, it will skip 2, but DENSE_RANK will not.

6.FIRST_VALUE () OVER () sort in a certain way, finding the first value

7.LAST_VALUE () OVER () gets the last value according to some sort of grouping

8.NTH_VALUE () OVER () sort the value of a row in a certain way

9.LAG (id) OVER () sorts the value of the previous row in some way

10.lead () OVER () sorts the value of the next row in some way

11. Divide the data into N groups according to some sort

12.CTE common expressions, non-recursive CTE, query for newly re-registered users

13.CTE common expressions, recursive CTE

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.