ウィンドウ関数
TiDB でのウィンドウ関数の使用方法は、MySQL 8.0 の場合と同様です。詳細については、 MySQL ウィンドウ関数参照してください。
TiDB では、次のシステム変数を使用してウィンドウ関数を制御できます。
tidb_enable_window_function: ウィンドウ関数はパーサー内で追加のキーワード予約するため、TiDB はウィンドウ関数を無効にするためにこの変数を提供します。TiDB をアップグレードした後に SQL ステートメントの解析エラーが発生する場合は、この変数をOFFに設定してみてください。tidb_enable_pipelined_window_function: この変数を使用すると、ウィンドウ関数のパイプライン実行アルゴリズムを無効にすることができます。windowing_use_high_precision: この変数を使用すると、ウィンドウ関数の高精度モードを無効にすることができます。
ウィンドウ関数ここに記載はTiFlashにプッシュダウンできます。
GROUP_CONCAT()とAPPROX_PERCENTILE()を除き、 TiDB はGROUP BY集計関数つすべてをウィンドウ関数として使用することをサポートしています。 さらに、 TiDB は次のウィンドウ関数をサポートしています。
| 関数名 | 機能の説明 |
|---|---|
CUME_DIST() | 値のグループ内の値の累積分布を返します。 |
DENSE_RANK() | パーティション内の現在の行のランクを返します。ランクにはギャップはありません。 |
FIRST_VALUE() | 現在のウィンドウの最初の行の式の値を返します。 |
LAG() | パーティション内の現在の行の N 行前の行から式の値を返します。 |
LAST_VALUE() | 現在のウィンドウの最後の行の式の値を返します。 |
LEAD() | パーティション内の現在の行から N 行後の行の式の値を返します。 |
NTH_VALUE() | 現在のウィンドウの N 行目から式の値を返します。 |
NTILE() | パーティションを N 個のバケットに分割し、パーティション内の各行にバケット番号を割り当て、パーティション内の現在の行のバケット番号を返します。 |
PERCENT_RANK() | 現在の行の値より小さいパーティション値の割合を返します。 |
RANK() | パーティション内の現在の行のランクを返します。ランクにはギャップがある場合があります。 |
ROW_NUMBER() | パーティション内の現在の行番号を返します。 |
CUME_DIST()
CUME_DIST() 、値のグループ内の値の累積分布を計算します。値のグループをソートするには、 ORDER BY節とCUME_DIST()使用する必要があることに注意してください。そうしないと、この関数は期待される値を返しません。
WITH RECURSIVE cte(n) AS (
SELECT 1
UNION
SELECT
n+2
FROM
cte
WHERE
n<6
)
SELECT
*,
CUME_DIST() OVER(ORDER BY n)
FROM
cte;
+------+------------------------------+
| n | CUME_DIST() OVER(ORDER BY n) |
+------+------------------------------+
| 1 | 0.25 |
| 3 | 0.5 |
| 5 | 0.75 |
| 7 | 1 |
+------+------------------------------+
4 rows in set (0.00 sec)
DENSE_RANK()
DENSE_RANK()関数は、現在の行の順位を返しますRANK()と似ていますが、同順位 (同じ値と順序条件を共有する行) の場合はギャップを残しません。
SELECT
*,
DENSE_RANK() OVER (ORDER BY n)
FROM (
SELECT 5 AS 'n'
UNION ALL
SELECT 8
UNION ALL
SELECT 5
UNION ALL
SELECT 30
UNION ALL
SELECT 31
UNION ALL
SELECT 32) a;
+----+--------------------------------+
| n | DENSE_RANK() OVER (ORDER BY n) |
+----+--------------------------------+
| 5 | 1 |
| 5 | 1 |
| 8 | 2 |
| 30 | 3 |
| 31 | 4 |
| 32 | 5 |
+----+--------------------------------+
6 rows in set (0.00 sec)
FIRST_VALUE()
FIRST_VALUE(expr)ウィンドウ内の最初の値を返します。
次の例では、2 つの異なるウィンドウ定義を使用しています。
PARTITION BY n MOD 2 ORDER BY nテーブルaのデータを1, 3と2, 42 つのグループに分割します。したがって、これらのグループの最初の値である1または2返されます。PARTITION BY n <= 2 ORDER BY nテーブルaのデータを1, 2と3, 42 つのグループに分割します。したがって、nどのグループに属しているかに応じて、1または3返します。
SELECT
n,
FIRST_VALUE(n) OVER (PARTITION BY n MOD 2 ORDER BY n),
FIRST_VALUE(n) OVER (PARTITION BY n <= 2 ORDER BY n)
FROM (
SELECT 1 AS 'n'
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
) a
ORDER BY
n;
+------+-------------------------------------------------------+------------------------------------------------------+
| n | FIRST_VALUE(n) OVER (PARTITION BY n MOD 2 ORDER BY n) | FIRST_VALUE(n) OVER (PARTITION BY n <= 2 ORDER BY n) |
+------+-------------------------------------------------------+------------------------------------------------------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 1 | 3 |
| 4 | 2 | 3 |
+------+-------------------------------------------------------+------------------------------------------------------+
4 rows in set (0.00 sec)
LAG()
LAG(expr [, num [, default]])関数は、現在の行のnum行前の行からexprの値を返します。そのような行が存在しない場合は、 defaultが返されます。デフォルトでは、指定されていない場合、 numは1 default NULLになります。
次の例では、 num指定されていないため、 LAG(n)前の行のnの値を返します。 nが 1 の場合、前の行が存在せず、 default指定されていないため、 LAG(1) NULL返します。
WITH RECURSIVE cte(n) AS (
SELECT 1
UNION
SELECT
n+1
FROM
cte
WHERE
n<10
)
SELECT
n,
LAG(n) OVER ()
FROM
cte;
+------+----------------+
| n | LAG(n) OVER () |
+------+----------------+
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 6 | 5 |
| 7 | 6 |
| 8 | 7 |
| 9 | 8 |
| 10 | 9 |
+------+----------------+
10 rows in set (0.01 sec)
LAST_VALUE()
LAST_VALUE()関数はウィンドウ内の最後の値を返します。
WITH RECURSIVE cte(n) AS (
SELECT
1
UNION
SELECT
n+1
FROM
cte
WHERE
n<10
)
SELECT
n,
LAST_VALUE(n) OVER (PARTITION BY n<=5)
FROM
cte
ORDER BY
n;
+------+----------------------------------------+
| n | LAST_VALUE(n) OVER (PARTITION BY n<=5) |
+------+----------------------------------------+
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
| 6 | 10 |
| 7 | 10 |
| 8 | 10 |
| 9 | 10 |
| 10 | 10 |
+------+----------------------------------------+
10 rows in set (0.00 sec)
LEAD()
LEAD(expr [, num [,default]])関数は、現在の行からnum行後の行からexprの値を返します。そのような行が存在しない場合は、 defaultが返されます。デフォルトでは、指定されていない場合、 numは1 default NULLになります。
次の例では、 numが指定されていないため、 LEAD(n)現在の行の次の行のnの値を返します。 nが 10 の場合、次の行が存在せず、 defaultが指定されていないため、 LEAD(10) NULL返します。
WITH RECURSIVE cte(n) AS (
SELECT
1
UNION
SELECT
n+1
FROM
cte
WHERE
n<10
)
SELECT
n,
LEAD(n) OVER ()
FROM
cte;
+------+-----------------+
| n | LEAD(n) OVER () |
+------+-----------------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 7 | 8 |
| 8 | 9 |
| 9 | 10 |
| 10 | NULL |
+------+-----------------+
10 rows in set (0.00 sec)
NTH_VALUE()
NTH_VALUE(expr, n)関数はウィンドウのn番目の値を返します。
WITH RECURSIVE cte(n) AS (
SELECT
1
UNION
SELECT
n+1
FROM
cte
WHERE
n<10
)
SELECT
n,
FIRST_VALUE(n) OVER w AS 'First',
NTH_VALUE(n, 2) OVER w AS 'Second',
NTH_VALUE(n, 3) OVER w AS 'Third',
LAST_VALUE(n) OVER w AS 'Last'
FROM
cte
WINDOW
w AS (PARTITION BY n<=5)
ORDER BY
n;
+------+-------+--------+-------+------+
| n | First | Second | Third | Last |
+------+-------+--------+-------+------+
| 1 | 1 | 2 | 3 | 5 |
| 2 | 1 | 2 | 3 | 5 |
| 3 | 1 | 2 | 3 | 5 |
| 4 | 1 | 2 | 3 | 5 |
| 5 | 1 | 2 | 3 | 5 |
| 6 | 6 | 7 | 8 | 10 |
| 7 | 6 | 7 | 8 | 10 |
| 8 | 6 | 7 | 8 | 10 |
| 9 | 6 | 7 | 8 | 10 |
| 10 | 6 | 7 | 8 | 10 |
+------+-------+--------+-------+------+
10 rows in set (0.00 sec)
NTILE()
NTILE(n)関数はウィンドウをnグループに分割し、各行のグループ番号を返します。
WITH RECURSIVE cte(n) AS (
SELECT
1
UNION
SELECT
n+1
FROM
cte
WHERE
n<10
)
SELECT
n,
NTILE(5) OVER (),
NTILE(2) OVER ()
FROM
cte;
+------+------------------+------------------+
| n | NTILE(5) OVER () | NTILE(2) OVER () |
+------+------------------+------------------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 4 | 2 | 1 |
| 5 | 3 | 1 |
| 6 | 3 | 2 |
| 7 | 4 | 2 |
| 8 | 4 | 2 |
| 9 | 5 | 2 |
| 10 | 5 | 2 |
+------+------------------+------------------+
10 rows in set (0.00 sec)
PERCENT_RANK()
PERCENT_RANK()関数は、現在の行の値よりも小さい値を持つ行の割合を示す 0 から 1 までの数値を返します。
SELECT
*,
PERCENT_RANK() OVER (ORDER BY n),
PERCENT_RANK() OVER (ORDER BY n DESC)
FROM (
SELECT 5 AS 'n'
UNION ALL
SELECT 8
UNION ALL
SELECT 5
UNION ALL
SELECT 30
UNION ALL
SELECT 31
UNION ALL
SELECT 32) a;
+----+----------------------------------+---------------------------------------+
| n | PERCENT_RANK() OVER (ORDER BY n) | PERCENT_RANK() OVER (ORDER BY n DESC) |
+----+----------------------------------+---------------------------------------+
| 5 | 0 | 0.8 |
| 5 | 0 | 0.8 |
| 8 | 0.4 | 0.6 |
| 30 | 0.6 | 0.4 |
| 31 | 0.8 | 0.2 |
| 32 | 1 | 0 |
+----+----------------------------------+---------------------------------------+
6 rows in set (0.00 sec)
RANK()
RANK()関数はDENSE_RANK()に似ていますが、同点の場合 (同じ値と順序条件を共有する行) にはギャップを残します。つまり、絶対的な順位付けが提供されます。たとえば、順位が 7 の場合、それより低い順位の行が 6 つあることを意味します。
SELECT
*,
RANK() OVER (ORDER BY n),
DENSE_RANK() OVER (ORDER BY n)
FROM (
SELECT 5 AS 'n'
UNION ALL
SELECT 8
UNION ALL
SELECT 5
UNION ALL
SELECT 30
UNION ALL
SELECT 31
UNION ALL
SELECT 32) a;
+----+--------------------------+--------------------------------+
| n | RANK() OVER (ORDER BY n) | DENSE_RANK() OVER (ORDER BY n) |
+----+--------------------------+--------------------------------+
| 5 | 1 | 1 |
| 5 | 1 | 1 |
| 8 | 3 | 2 |
| 30 | 4 | 3 |
| 31 | 5 | 4 |
| 32 | 6 | 5 |
+----+--------------------------+--------------------------------+
6 rows in set (0.00 sec)
ROW_NUMBER()
ROW_NUMBER()は結果セット内の現在の行の行番号を返します。
WITH RECURSIVE cte(n) AS (
SELECT
1
UNION
SELECT
n+3
FROM
cte
WHERE
n<30
)
SELECT
n,
ROW_NUMBER() OVER ()
FROM
cte;
+------+----------------------+
| n | ROW_NUMBER() OVER () |
+------+----------------------+
| 1 | 1 |
| 4 | 2 |
| 7 | 3 |
| 10 | 4 |
| 13 | 5 |
| 16 | 6 |
| 19 | 7 |
| 22 | 8 |
| 25 | 9 |
| 28 | 10 |
| 31 | 11 |
+------+----------------------+
11 rows in set (0.00 sec)