tencent cloud

TencentDB for MySQL

DokumentasiTencentDB for MySQL

Window Function Use Instructions

Mode fokus
Ukuran font
Terakhir diperbarui: 2025-05-09 11:51:23
Window functions, also known as Online Analytical Processing (OLAP) functions, can process and analyze data in real-time and are very important for an analytical database.
The usage method of window functions in the read-only analysis engine is basically the same as that in MySQL 8.0. However, the read-only analysis engine enabled in MySQL 5.7 can still use window functions and the window syntax.
The specific window syntax is as follows:
SELECT
    <WindowFunction> OVER (PARTITION BY <expr_list> ORDER BY <expr_list> [ASC / DESC] [<WindowFrame>])
FROM
    tb_test_window;
SQL example:
select studentid,departmentid,classid,math,
row_number() over(partition by departmentid,classid order by math) as row_num
from student_scores;
This example sorts the data within the groups of departmentid and classid by math.

WindowFrame

Frame
ROWS Scenario
RANGE Scenario
CURRENT ROW
Current row.
All rows identical to the current row.
UNBOUNDED PRECEDING
Go to the first row.
Go to the first row.
UNBOUNDED
FOLLOWING
Go to the last row.
Go to the last row.
<N> PRECEDING
First N rows.
Go to the row whose value is greater than or equal to the value obtained by subtracting <N> from the OrderBy column value.
<N> FOLLOWING
Last N rows.
Go to the row whose value is less than or equal to the value obtained by adding <N> to the OrderBy column value.
ROWS | RANGE <Frame>.
ROWS | RANGE BETWEEN <Frame> AND <Frame>.

WindowFunction

The following table describes the window functions supported in the window syntax.
Function Name
Feature Description
Function Parameter
Supported Type
ROW_NUMBER()
Marks row numbers for data in each partition.
-
-
RANK()
Performs non-intensive data sorting in each partition.
-
-
DENSE_RANK()
Performs intensive data sorting in each partition.
-
-
LEAD(<expr>, <offset>, <default>)
Calculates the values of <offset> rows after the current row. If no rows meet the condition, <default> is returned.
[Required] <expr>: Calculated column.
All types (three parameters except the Time type).
[Optional] <offset>: Number of rows to offset after the current row. The default value is 1.
Value type
[Optional] <default>: Default return value when the calculated row does not meet the condition. The default value is NULL.
Consistent with the <expr> type.
LAG(<expr>, <offset>, <default>)
Calculates the values of <offset> rows before the current row. If no rows meet the condition, <default> is returned.
[Required] <expr>: Calculated column.
All types (three parameters except the Time type).
[Optional] <offset>: Number of rows to offset before the current row. The default value is 1.
Value type
[Optional] <default>: Default return value when the calculated row does not meet the condition. The default value is NULL.
Consistent with the <expr> type.
FIRST_VALUE(<expr>)
Calculates the first value in a window in a partition.
[Required] <expr>: Calculated column.
All types.
LAST_VALUE(<expr>)
Calculates the last value in a window in a partition.
[Required] <expr>: Calculated column.
All types.
MIN(<expr>)
Calculates the <expr> value of the row corresponding to the minimum value in the OrderBy column in a window in a partition.
[Required] <expr>: Calculated column.
All types.
MAX(<expr>)
Calculates the <expr> value of the row corresponding to the maximum value in the OrderBy column in a window in a partition.
[Required] <expr>: Calculated column.
All types.
COUNT(<expr>)
Counts the total number of rows of data in a window in a partition.
[Required] <expr>: Calculated column.
All types.
SUM(<expr>)
Calculates the total data volume in a window in a partition.
[Required] <expr>: Calculated column.
Value type
AVG(<expr>)
Calculates the average value of data in a window in a partition.
[Required] <expr>: Calculated column.
Value type
Numerical types: int, bigint, float, double, and decimal.
Character types: char and varchar.
Time types: date, time, datetime, and timestamp.

Detailed Examples

Example table creation statement:
drop table if exists test.tb_window;
create table test.tb_window (c1 int not null primary key, c2 int, c3 int);
create table test.tb_window (c1 Int32, c2 Nullable(Int32), c3 Nullable(Int32)) engine = LibraTree order by (c1);
insert into test.tb_window values (1, 1, 1), (2, 1, 1), (3, 1, 2), (4, 1, 4), (5, 1, 6), (6, 1, 6);

ROWS Keyword

Note:
This keyword measures the window size by row and calculates the data in the window.
-- Sample statement
mysql> select c2, c3, COUNT(c1) over (partition by c2 order by c3 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) cn from test.tb_window;
+----+----+----+
| c2 | c3 | cn |
+----+----+----+
|  1 |  1 |  3 |  -- Window row index range: current -> next 2 rows [0–2]
|  1 |  1 |  3 |  -- Window row index range: current -> next 2 rows [1–3]
|  1 |  2 |  3 |  -- Window row index range: current -> next 2 rows [2–4]
|  1 |  4 |  3 |  -- Window row index range: current -> next 2 rows [3–5]
|  1 |  6 |  2 |  -- Window row index range: current -> next 1 row [4–5] (Only one row behind)
|  1 |  6 |  1 |  -- Window row index range: current (no data behind)
+----+----+----+
6 rows in set (0.06 sec)

RANGE Keyword

Note:
This keyword measures the window size by value and calculates the data in the window. In the following SQL example, the current row to the row corresponding to the current row value in the C3 column plus 2 is a window.
-- Sample statement
mysql> select c2, c3, COUNT(c1) over (partition by c2 order by c3 RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) cn from test.tb_window;
+----+----+----+
| c2 | c3 | cn |
+----+----+----+
|  1 |  1 |  3 |  -- Window row index range: current -> row index corresponding to 3 [0–2]
|  1 |  1 |  3 |
|  1 |  2 |  2 |  -- Window row index range: current -> row index corresponding to 4 [2–3]
|  1 |  4 |  3 |  -- Window row index range: current -> row index corresponding to 6 [3–5]
|  1 |  6 |  2 |  -- Window row index range: current -> row index corresponding to 8 [4–5]
|  1 |  6 |  2 |
+----+----+----+
6 rows in set (0.06 sec)

ROW_NUMBER

Note:
This function numbers the data within the partition and is not restricted by <WindowFrame>.
-- Sample statement
mysql> select c2, c3, ROW_NUMBER() over (partition by c2 order by c3) rn from test.tb_window;
+----+----+------+
| c2 | c3 | rn   |
+----+----+------+
|  1 |  1 |    1 |
|  1 |  1 |    2 |
|  1 |  2 |    3 |
|  1 |  4 |    4 |
|  1 |  6 |    5 |
|  1 |  6 |    6 |
+----+----+------+
6 rows in set (0.04 sec)

RANK & DENSE_RANK

RANK function: Performs non-intensive data sorting in a partition. This function is not restricted by <WindowFrame>.
DENSE_RANK function: Performs intensive data sorting in a partition. This function is not restricted by <WindowFrame>.
-- Sample statement
select 
    c2, c3, 
    RANK() over (partition by c2 order by c3) rk, 
    DENSE_RANK() over (partition by c2 order by c3) drk
from test.tb_window;
+------+------+------+------+
| c2   | c3   | rk   | drk  |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    1 |
|    1 |    2 |    3 |    2 |
|    1 |    4 |    4 |    3 |
|    1 |    6 |    5 |    4 |
|    1 |    6 |    5 |    4 |
+------+------+------+------+
6 rows in set (0.05 sec)

LEAD & LAG

1. One-Parameter Scenarios

LEAD(<expr>) function: Calculates the data of the row after the current row in the partition. If no next row is available, NULL is filled by default. This function is not restricted by <WindowFrame>.
LAG(<expr>) function: Calculates the data of the row before the current row in the partition. If no previous row is available, NULL is filled by default. This function is not restricted by <WindowFrame>.
-- Sample statement
mysql> select 
    c2, c3, 
    LEAD(c3) over (partition by c2 order by c3) ld, 
    LAG(c3) over (partition by c2 order by c3) lg
from test.tb_window;
+------+------+------+------+
| c2   | c3   | ld   | lg   |
+------+------+------+------+
|    1 |    1 |    1 | NULL |
|    1 |    1 |    2 |    1 |
|    1 |    2 |    4 |    1 |
|    1 |    4 |    6 |    2 |
|    1 |    6 |    6 |    4 |
|    1 |    6 | NULL |    6 |
+------+------+------+------+
6 rows in set (0.11 sec)

1. Two-Parameter Scenarios

LEAD(<expr>, <offset>) function: Calculates the data of <offset> rows after the current row in the partition. If no next <offset> rows are available, NULL is filled by default. This function is not restricted by <WindowFrame>.
LAG(<expr>, <offset>) function: Calculates the data of <offset> rows before the current row in the partition. If no previous <offset> rows are available, NULL is filled by default. This function is not restricted by <WindowFrame>.
-- Sample statement
mysql> select 
    c2, c3, 
    LEAD(c3, 2) over (partition by c2 order by c3) ld, 
    LAG(c3, 2) over (partition by c2 order by c3) lg
from test.tb_window;
+------+------+------+------+
| c2   | c3   | ld   | lg   |
+------+------+------+------+
|    1 |    1 |    2 | NULL |
|    1 |    1 |    4 | NULL |
|    1 |    2 |    6 |    1 |
|    1 |    4 |    6 |    1 |
|    1 |    6 | NULL |    2 |
|    1 |    6 | NULL |    4 |
+------+------+------+------+
6 rows in set (0.07 sec)

1. Three-Parameter Scenarios

LEAD(<expr>, <offset>, <default>>) function: Calculates the data of <offset> rows after the current row in the partition. If no next <offset> rows are available, <default> is filled by default. This function is not restricted by <WindowFrame>.
LAG(<expr>, <offset>, <default>>) function: Calculates the data of <offset> rows before the current row in the partition. If no previous <offset> rows are available, <default> is filled by default. This function is not restricted by <WindowFrame>.
-- Sample statement
mysql> select 
    c2, c3, 
    LEAD(c3, 2, 1000) over (partition by c2 order by c3) ld, 
    LAG(c3, 2, 1000) over (partition by c2 order by c3) lg
from test.tb_window;
+------+------+------+------+
| c2   | c3   | ld   | lg   |
+------+------+------+------+
|    1 |    1 |    2 | 1000 |
|    1 |    1 |    4 | 1000 |
|    1 |    2 |    6 |    1 |
|    1 |    4 |    6 |    1 |
|    1 |    6 | 1000 |    2 |
|    1 |    6 | 1000 |    4 |
+------+------+------+------+
6 rows in set (0.10 sec)

FIRST_VALUE & LAST_VALUE

FIRST_VALUE(<expr>) function: Calculates the first value in a window in a partition. (If OrderBy c3 is adopted and the first value in the c3 column is duplicated with another value, first_value(c4) may be unstable.)
LAST_VALUE(<expr>) function: Calculates the last value in a window in a partition. (If OrderBy c3 is adopted and the last value in the c3 column is duplicated with another value, last_value(c4) may be unstable.)
-- Sample statement
mysql> select 
    c2, c3, 
    FIRST_VALUE(c3) over (partition by c2 order by c3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv, 
    LAST_VALUE(c3) over (partition by c2 order by c3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
from test.tb_window;
+------+------+------+------+
| c2   | c3   | fv   | lv   |
+------+------+------+------+
|    1 |    1 |    1 |    6 |
|    1 |    1 |    1 |    6 |
|    1 |    2 |    1 |    6 |
|    1 |    4 |    1 |    6 |
|    1 |    6 |    1 |    6 |
|    1 |    6 |    1 |    6 |
+------+------+------+------+
6 rows in set (0.07 sec)

MIN & MAX

MIN(<expr>) function: Calculates the minimum value in a window in a partition.
MAX(<expr>) function: Calculates the maximum value in a window in a partition.
-- Sample statement
mysql> select 
    c2, c3, 
    MIN(c3) over (partition by c2 order by c3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) mi, 
    MAX(c3) over (partition by c2 order by c3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ma
from test.tb_window;
+------+------+------+------+
| c2   | c3   | mi   | ma   |
+------+------+------+------+
|    1 |    1 |    1 |    6 |
|    1 |    1 |    1 |    6 |
|    1 |    2 |    1 |    6 |
|    1 |    4 |    1 |    6 |
|    1 |    6 |    1 |    6 |
|    1 |    6 |    1 |    6 |
+------+------+------+------+
6 rows in set (0.07 sec)

COUNT

Note:
It calculates the total number of rows of data in a window in a partition.
-- Sample statement
mysql> select c2, c3, COUNT(c3) over (partition by c2 order by c3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) c from test.tb_window;
+------+------+----+
| c2   | c3   |  c |
+------+------+----+
|    1 |    1 |  6 |
|    1 |    1 |  6 |
|    1 |    2 |  6 |
|    1 |    4 |  6 |
|    1 |    6 |  6 |
|    1 |    6 |  6 |
+------+------+----+
6 rows in set (0.04 sec)

SUM

Note:
It calculates the total data volume in a window in a partition.
-- Sample statement
mysql> select c2, c3, SUM(c3) over (partition by c2 order by c3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) s from test.tb_window;
+------+------+------+
| c2   | c3   | s    |
+------+------+------+
|    1 |    1 |   20 |
|    1 |    1 |   20 |
|    1 |    2 |   20 |
|    1 |    4 |   20 |
|    1 |    6 |   20 |
|    1 |    6 |   20 |
+------+------+------+
6 rows in set (0.06 sec)

AVG

Note:
It calculates the average value of data in a window in a partition.
-- Sample statement
mysql> select c2, c3, AVG(c3) over (partition by c2 order by c3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) a from test.tb_window;
+------+------+--------+
| c2   | c3   | a      |
+------+------+--------+
|    1 |    1 | 3.3333 |
|    1 |    1 | 3.3333 |
|    1 |    2 | 3.3333 |
|    1 |    4 | 3.3333 |
|    1 |    6 | 3.3333 |
|    1 |    6 | 3.3333 |
+------+------+--------+
6 rows in set (0.06 sec)
Topik selanjutnya: Tutorial Pengguna

Bantuan dan Dukungan

Apakah halaman ini membantu?

masukan