# Window Functions
__*Window functions*__
__*RANK function*__
+ A window function calculates the output for each input row within particular partition in the result set
+ In addition to the **`RANK()`** in the example, `TiDB` supports following window functions:
  + **`CUME_DIST()`**, **`DENSE_RANK()`**, **`FIRST_VALUE()`**, **`LAG()`**, **`LAST_VALUE()`**, **`LEAD()`**, **`NTH_VALUE()`**, **`NTITLE()`**, **`PERCENT_RANK()`**, **`ROW_NUMER()`** 
```
tidb> SELECT
    -> name, mass, gravity, category_id,
    -> ~!@RANK() OVER (PARTITION BY category_id ORDER BY mass desc)@!~ as mass_rank_within_category
    -> FROM universe.planets;
+--------------------+--------+---------+-------------+---------------------------+
| name               | mass   | gravity | category_id | mass_rank_within_category |
+--------------------+--------+---------+-------------+---------------------------+
| Proxima Centauri b | 7.5819 |    11.3 |           1 |                         1 |
| Earth              |   5.97 |     9.8 |           1 |                         2 |
| Venus              |   4.87 |     8.9 |           1 |                         3 |
| Mars               |  0.642 |     3.7 |           1 |                         4 |
| Mercury            |   0.33 |     3.7 |           1 |                         5 |
| Pluto              |  0.013 |     0.7 |           3 |                         1 |
| Jupiter            |   1898 |    23.1 |           2 |                         1 |
| Saturn             |    568 |     9.0 |           2 |                         2 |
| Neptune            |    102 |    11.0 |           2 |                         3 |
| Uranus             |   86.8 |     8.7 |           2 |                         4 |
+--------------------+--------+---------+-------------+---------------------------+
10 rows in set (0.01 sec)
```

------------------------------------------------------------
- The use of window function in TiDB is basically the same as MySQL 8.0. Since some reserved keyword are used for window function, SQL statements that can be executed normally may not be parsed after upgrading TiDB. At this time, tidb_enable_window_function can be set to 0. The default value is 1.

- The window function supported by TiDB are as follows:
  - **`CUME_DIST()`**  return the cumulative distribution in a set of values
  - **`DENSE_RANK()`**  return the rank of the current row in the partition, and the ranking is continuous
  - **`FIRST_VALUE()`**  Expression value in the first row of the current window
  - Expression value for **`LAG()`**  the Nth row before the current row in the partition
  - **`LAST_VALUE()`**  Expression value in the last row of the current window
  - Expression value on **`LEAD()`**  the Nth row after the current row in the partition
  - **`NTH_VALUE()`**  The value of the expression in the Nth row of the current window
  - **`NTILE()`**  Divide the partition into N buckets and assign a barrel number to each row in the partition
  - **`PERCENT_RANK()`**  return the percentage of the partition that is less than the current row
  - **`RANK()`**  return the rank of the current row in the partition, which may not be consecutive
  - **`ROW_NUMBER()`**  return the number of the current row in the partition

- For example, mark the line number on the return record (knowledge about subquery will be covered in other sections):
```
tidb> SELECT ROW_NUMBER() OVER(), name, mass
    -> FROM
    ->   (SELECT name, mass
    ->    FROM universe.planets
    ->    ORDER BY mass DESC) v; 
+---------------------+--------------------+--------+
| ROW_NUMBER() OVER() | name               | mass   |
+---------------------+--------------------+--------+
|                   1 | Jupiter            |   1898 |
|                   2 | Saturn             |    568 |
|                   3 | Neptune            |    102 |
|                   4 | Uranus             |   86.8 |
|                   5 | Proxima Centauri b | 7.5819 |
|                   6 | Earth              |   5.97 |
|                   7 | Venus              |   4.87 |
|                   8 | Mars               |  0.642 |
|                   9 | Mercury            |   0.33 |
|                  10 | Pluto              |  0.013 |
+---------------------+--------------------+--------+
10 rows in set (0.00 sec)
```