tencent cloud

TDSQL Boundless

DocumentaçãoTDSQL BoundlessUser GuideRead-Only Analysis InstanceExecuting QueriesWindow Function Use Instructions

Window Function Use Instructions

Download
Modo Foco
Tamanho da Fonte
Última atualização: 2026-07-08 17:25:19
Window functions, also known as OLAP (Online Analytical Processing) functions, perform real-time analysis and computation on data within partitions. They are a core capability of analytical databases.
The method for using window functions in the read-only analysis instance is largely consistent with that in MySQL 8.0.
The syntax for window functions 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 illustrates the following: within each group (departmentid, classid), rows are numbered in ascending order of the math column.
Note:
The expressions for PARTITION BY and ORDER BY must be column references from a table. Arbitrary expressions are not currently supported.
The ORDER BY clause does not currently support NULLS FIRST or NULLS LAST. NULL values are treated as the minimum value by default in sorting operations.
The IGNORE NULLS, RESPECT NULLS, FROM FIRST, and FROM LAST modifiers are not currently supported.

WindowFrame

A window frame is used to further define the set of rows within a window that participate in calculations. The analysis engine supports the ROWS and RANGE frame types. The GROUPS frame and the EXCLUDE clause are not currently supported.
Frame
ROWS Scenario
RANGE Scenario
CURRENT ROW
Current row.
All rows whose ORDER BY column values are the same as the current row.
UNBOUNDED PRECEDING
To the first row of the partition.
To the first row of the partition.
UNBOUNDED FOLLOWING
To the last row of the partition.
To the last row of the partition.
PRECEDING
Offset N rows forward from the current row.
To the row whose ORDER BY column value is greater than or equal to (current row value - N).
FOLLOWING
Offset N rows backward from the current row.
To the row whose ORDER BY column value is less than or equal to (current row value + N).
Supported frame syntax:
ROWS | RANGE <Frame>.
ROWS | RANGE BETWEEN <Frame> AND <Frame>.
Note:
Sorting window functions (ROW_NUMBER, RANK, DENSE_RANK) and value-fetching functions (LEAD, LAG) are not restricted by <WindowFrame>.

WindowFunction

The window functions currently supported by the analysis engine are listed in the following table:
Function Name
Capability Description
Function Parameter
Supported Type
ROW_NUMBER()
Assigns a row number to the data within each partition according to the ORDER BY sequence.
-
-
RANK()
Performs a non-dense ranking on the data within each partition (identical values share the same rank, and the next rank is skipped).
-
-
DENSE_RANK()
Performs a dense ranking on the data within each partition (identical values share the same rank, and the next rank is consecutive).
-
-
LEAD(, , )
Returns the value of <expression> at the row offset <offset> backward from the current row, and returns <default> when out of bounds.
[Required]: The column to compute.
All types.
[Optional]: The number of rows to offset backward from the current row, with a default value of 1.
Integer types (INT, BIGINT, and so on)
[Optional] <default>: The default value to return when out of bounds, with a default of NULL.
The type must be consistent with the type of the specified column, or it can be accommodated by the type of that column.
LAG(, , )
Returns the value of <expression> at the row offset <offset> forward from the current row, and returns <default> when out of bounds.
[Required]: The column to compute.
All types.
[Optional]: The number of rows to offset forward from the current row, with a default value of 1.
Integer types (INT, BIGINT, and so on)
[Optional] <default>: The default value to return when out of bounds, with a default of NULL.
The type must be consistent with the type of the specified column, or it can be accommodated by the type of that column.
FIRST_VALUE()
Returns the value of <expression> from the first row within the window frame.
[Required]: The column to compute.
All types.
LAST_VALUE()
Returns the value of <expression> from the last row within the window frame.
[Required]: The column to compute.
All types.
MIN()
Returns the minimum value of <expression> within the window frame.
[Required]: The column to compute.
Numeric, character, and time types.
MAX()
Returns the maximum value of <expression> within the window frame.
[Required]: The column to compute.
Numeric, character, and time types.
COUNT()
Returns the number of rows with non-NULL values of <expression> within the window frame.
[Required]: The column to compute.
All types.
SUM()
Returns the sum of <expression> within the window frame.
[Required]: The column to compute.
Value type
AVG()
Returns the average value of <expression> within the window frame.
[Required]: The column to compute.
Value type
Type description:
Numeric types: INT, BIGINT, FLOAT, DOUBLE, DECIMAL.
Character types: CHAR, VARCHAR.
Time types: DATE, TIME, DATETIME, TIMESTAMP.
Attention:
Window functions such as NTILE, PERCENT_RANK, CUME_DIST, and NTH_VALUE are not currently supported.

Detailed Examples

The following examples use a unified table creation statement and data:
DROP TABLE IF EXISTS test.tb_window;
CREATE TABLE test.tb_window (
    c1 INT NOT NULL PRIMARY KEY,
    c2 INT,
    c3 INT
);
INSERT INTO test.tb_window VALUES
    (1, 1, 1), (2, 1, 1), (3, 1, 2),
    (4, 1, 4), (5, 1, 6), (6, 1, 6);
Note:
Execute the query on the window function analysis engine. Ensure that columnar storage capability has been enabled for the table before executing the following queries.

ROWS Keyword

Note:
This keyword defines the window size based on the number of rows and calculates the data within the window.
-- Sample statement
tdsql> SELECT c2, c3,
              COUNT(c1) OVER (PARTITION BY c2 ORDER BY c3
                              ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS 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 follows)
|  1 |  6 |  1 |  -- Window row index range: current (no data follows)

+----+----+----+
6 rows in set (0.06 sec)

RANGE Keyword

Note:
This keyword defines the window range based on the values in the ORDER BY column and calculates the data within the window. For example, the following SQL sample defines a window that includes all rows from the current row's ORDER BY column value to that value plus 2.
-- Sample statement
tdsql> SELECT c2, c3,
              COUNT(c1) OVER (PARTITION BY c2 ORDER BY c3
                              RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) AS cn
       FROM test.tb_window;
+----+----+----+

| c2 | c3 | cn |

+----+----+----+

|  1 |  1 |  3 |  -- Window range: rows corresponding to c3 in [1, 3] [0 ~ 2]
|  1 |  1 |  3 |
|  1 |  2 |  2 |  -- Window range: rows corresponding to c3 in [2, 4] [2 ~ 3]
|  1 |  4 |  3 |  -- Window range: rows corresponding to c3 in [4, 6] [3 ~ 5]
|  1 |  6 |  2 |  -- Window range: rows corresponding to c3 in [6, 8] [4 ~ 5]
|  1 |  6 |  2 |

+----+----+----+
6 rows in set (0.06 sec)

ROW_NUMBER

Note:
This function assigns sequence numbers to data within a partition according to the ORDER BY clause, and is not subject to the <WindowFrame> restriction.
-- Sample statement
tdsql> SELECT c2, c3,
              ROW_NUMBER() OVER (PARTITION BY c2 ORDER BY c3) AS 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 and DENSE_RANK

RANK function: performs non-dense ranking on data within a partition and is not subject to the <WindowFrame> restriction.
DENSE_RANK function: performs dense ranking on data within a partition and is not subject to the <WindowFrame> restriction.
-- Sample statement
SELECT c2, c3,
       RANK()       OVER (PARTITION BY c2 ORDER BY c3) AS rk,
       DENSE_RANK() OVER (PARTITION BY c2 ORDER BY c3) AS 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 and LAG

I. Single-Parameter Scenario

LEAD(<expr>): returns the value of <expr> from the row following the current row within the partition, returns NULL when out of bounds, and is not subject to the <WindowFrame> restriction.
LAG(<expr>): returns the value of <expr> from the row preceding the current row within the partition, returns NULL when out of bounds, and is not subject to the <WindowFrame> restriction.
-- Sample statement
tdsql> SELECT c2, c3,
              LEAD(c3) OVER (PARTITION BY c2 ORDER BY c3) AS ld,
              LAG(c3)  OVER (PARTITION BY c2 ORDER BY c3) AS 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)

II. Dual-Parameter Scenario

LEAD(<expr>, <offset>): returns the value of <expr> from the row that is <offset> rows after the current row within the partition, returns NULL when out of bounds, and is not subject to the <WindowFrame> restriction.
LAG(<expr>, <offset>): returns the value of <expr> from the row that is <offset> rows before the current row within the partition, returns NULL when out of bounds, and is not subject to the <WindowFrame> restriction.
-- Sample statement
tdsql> SELECT c2, c3,
              LEAD(c3, 2) OVER (PARTITION BY c2 ORDER BY c3) AS ld,
              LAG(c3, 2)  OVER (PARTITION BY c2 ORDER BY c3) AS 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)

III. Three-Parameter Scenario

LEAD(<expr>, <offset>, <default>): returns the value of <expr> from the row that is <offset> rows after the current row within the partition, returns <default> when out of bounds, and is not subject to the <WindowFrame> restriction.
LAG(<expr>, <offset>, <default>): returns the value of <expr> from the row that is <offset> rows before the current row within the partition, returns <default> when out of bounds, and is not subject to the <WindowFrame> restriction.
-- Sample statement
tdsql> SELECT c2, c3,
              LEAD(c3, 2, 1000) OVER (PARTITION BY c2 ORDER BY c3) AS ld,
              LAG(c3, 2, 1000)  OVER (PARTITION BY c2 ORDER BY c3) AS 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 and LAST_VALUE

FIRST_VALUE(<expr>): returns the value of <expr> from the first row within the window frame.
LAST_VALUE(<expr>): returns the value of <expr> from the last row within the window frame.
Note:
When the ORDER BY column contains duplicate values, the assignment of the first and last rows may be affected by sorting stability. It is recommended to append a unique column to the ORDER BY clause to ensure stable results.
-- Sample statement
tdsql> SELECT c2, c3,
              FIRST_VALUE(c3) OVER (PARTITION BY c2 ORDER BY c3
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fv,
              LAST_VALUE(c3)  OVER (PARTITION BY c2 ORDER BY c3
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 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 and MAX

MIN(<expr>): returns the minimum value of <expr> within the window frame.
MAX(<expr>): returns the maximum value of <expr> within the window frame.
-- Sample statement
tdsql> SELECT c2, c3,
              MIN(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS mi,
              MAX(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 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:
Returns the number of rows within the window frame where <expr> is not NULL.
-- Sample statement
tdsql> SELECT c2, c3,
              COUNT(c3) OVER (PARTITION BY c2 ORDER BY c3
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 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:
Returns the sum of <expr> within the window frame.
-- Sample statement
tdsql> SELECT c2, c3,
              SUM(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 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:
Returns the average value of <expr> within the window frame.
-- Sample statement
tdsql> SELECT c2, c3,
              AVG(c3) OVER (PARTITION BY c2 ORDER BY c3
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 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)
Anterior: CTE Syntax Usage InstructionsPróximo: Executing SQL and Viewing the Execution Plan

Ajuda e Suporte

Esta página foi útil?

comentários