バージョン: 3.1

ウィンドウ関数

ウィンドウ関数

背景

ウィンドウ関数は、特別なクラスの組み込み関数です。集計関数と同様に、複数の入力行に対して計算を行い、単一のデータ値を取得します。違いは、ウィンドウ関数が特定のウィンドウ内で入力データを処理することで、「group by」メソッドを使用しないことです。各ウィンドウ内のデータは、over()句を使用してソートおよびグループ化できます。ウィンドウ関数は、各行に対して個別の値を計算し、グループごとに1つの値を計算するのではありません。この柔軟性により、ユーザーはselect句に追加の列を追加し、結果セットをさらにフィルタリングできます。ウィンドウ関数は、selectリストおよび句の最外部の位置にのみ表示できます。クエリの最後、つまりjoin、where、およびgroup by操作が実行された後に効果を発揮します。ウィンドウ関数は、トレンドの分析、外れ値の計算、大規模データのバケット分析によく使用されます。

使用法

構文

function(args) OVER(partition_by_clause order_by_clause [window_clause])
partition_by_clause ::= PARTITION BY expr [, expr ...]
order_by_clause ::= ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...]

PARTITION BY句

Partition By句は、Group Byに似ています。指定された1つ以上の列で入力行をグループ化します。同じ値を持つ行は一緒にグループ化されます。

ORDER BY句

Order By句は基本的に外部のOrder Byと同じです。入力行の順序を定義します。Partition Byが指定されている場合、Order Byは各Partitionグループ内の順序を定義します。唯一の違いは、OVER句のOrder By n（nは正の整数）が操作なしと同等であるのに対し、外部のOrder Byのnはn番目の列でのソートを示すことです。

例:

この例では、eventsテーブルのdate_and_time列でソートされた1, 2, 3などの値を持つid列をselectリストに追加します。

SELECT row_number() OVER (ORDER BY date_and_time) AS id,
    c1, c2, c3, c4
FROM events;

ウィンドウ句

ウィンドウ句は、操作のための行の範囲を指定するために使用されます（現在の行に基づく前後の行）。次の構文をサポートします: AVG(), COUNT(), FIRST_VALUE(), LAST_VALUE(), およびSUM()。MAX()およびMIN()の場合、ウィンドウ句はUNBOUNDED PRECEDINGから開始を指定できます。

構文:

ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

ウィンドウ関数サンプルテーブル

このセクションでは、サンプルテーブルscoresを作成します。このテーブルを使用して、以下の多くのウィンドウ関数をテストできます。

CREATE TABLE `scores` (
    `id` int(11) NULL,
    `name` varchar(11) NULL,
    `subject` varchar(11) NULL,
    `score` int(11) NULL
  )
DISTRIBUTED BY HASH(`score`) BUCKETS 10;

INSERT INTO `scores` VALUES
  (1, "lily", "math", NULL),
  (1, "lily", "english", 100),
  (1, "lily", "physics", 60),
  (2, "tom", "math", 80),
  (2, "tom", "english", 98),
  (2, "tom", "physics", NULL),
  (3, "jack", "math", 95),
  (3, "jack", "english", NULL),
  (3, "jack", "physics", 99),
  (4, "amy", "math", 80),
  (4, "amy", "english", 92),
  (4, "amy", "physics", 99),
  (5, "mike", "math", 70),
  (5, "mike", "english", 85),
  (5, "mike", "physics", 85),
  (6, "amber", "math", 92),
  (6, "amber", NULL, 90),
  (6, "amber", "physics", 100);

関数の例

このセクションでは、StarRocksでサポートされているウィンドウ関数について説明します。

AVG()

指定されたウィンドウ内のフィールドの平均値を計算します。この関数はNULL値を無視します。

構文:

AVG(expr) [OVER (*analytic_clause*)]

例:

以下の例では、株式データを例に使用します。

CREATE TABLE stock_ticker (
    stock_symbol  STRING,
    closing_price DECIMAL(8,2),
    closing_date  DATETIME
)
DUPLICATE KEY(stock_symbol)
COMMENT "OLAP"
DISTRIBUTED BY HASH(closing_date);

INSERT INTO stock_ticker VALUES 
    ("JDR", 12.86, "2014-10-02 00:00:00"), 
    ("JDR", 12.89, "2014-10-03 00:00:00"), 
    ("JDR", 12.94, "2014-10-04 00:00:00"), 
    ("JDR", 12.55, "2014-10-05 00:00:00"), 
    ("JDR", 14.03, "2014-10-06 00:00:00"), 
    ("JDR", 14.75, "2014-10-07 00:00:00"), 
    ("JDR", 13.98, "2014-10-08 00:00:00")
;

現在の行とその前後の行の平均終値を計算します。

select stock_symbol, closing_date, closing_price,
    avg(closing_price)
        over (partition by stock_symbol
              order by closing_date
              rows between 1 preceding and 1 following
        ) as moving_average
from stock_ticker;

出力:

+--------------+---------------------+---------------+----------------+
| stock_symbol | closing_date        | closing_price | moving_average |
+--------------+---------------------+---------------+----------------+
| JDR          | 2014-10-02 00:00:00 |         12.86 |    12.87500000 |
| JDR          | 2014-10-03 00:00:00 |         12.89 |    12.89666667 |
| JDR          | 2014-10-04 00:00:00 |         12.94 |    12.79333333 |
| JDR          | 2014-10-05 00:00:00 |         12.55 |    13.17333333 |
| JDR          | 2014-10-06 00:00:00 |         14.03 |    13.77666667 |
| JDR          | 2014-10-07 00:00:00 |         14.75 |    14.25333333 |
| JDR          | 2014-10-08 00:00:00 |         13.98 |    14.36500000 |
+--------------+---------------------+---------------+----------------+

例えば、最初の行の12.87500000は、"2014-10-02"の終値（12.86）、その前日"2014-10-01"（null）、およびその翌日"2014-10-03"（12.89）の平均値です。

COUNT()

指定されたウィンドウ内で条件を満たす行の総数を計算します。

構文:

COUNT(expr) [OVER (analytic_clause)]

例:

現在の行からmathパーティション内の最初の行までの間で、90を超えるmathスコアの出現回数をカウントします。この例では、サンプルテーブルscoresのデータを使用します。

select *,
    count(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and current row
        ) as 'score_count'
from scores where subject in ('math') and score > 90;

+------+-------+---------+-------+-------------+
| id   | name  | subject | score | score_count |
+------+-------+---------+-------+-------------+
|    6 | amber | math    |    92 |           1 |
|    3 | jack  | math    |    95 |           2 |
+------+-------+---------+-------+-------------+

DENSE_RANK()

DENSE_RANK()関数はランキングを表すために使用されます。RANK()とは異なり、DENSE_RANK()は空白のない番号を持ちます。例えば、1位が2つある場合、DENSE_RANK()の3番目の番号は2のままですが、RANK()の3番目の番号は3になります。

構文:

DENSE_RANK() OVER(partition_by_clause order_by_clause)

例:

以下の例では、mathスコアのランキングを示しています（降順）。この例では、サンプルテーブルscoresのデータを使用します。

select *,
    dense_rank()
        over (
            partition by subject
            order by score desc
        ) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    2 | tom   | math    |    80 |    3 |
|    4 | amy   | math    |    80 |    3 |
|    5 | mike  | math    |    70 |    4 |
|    1 | lily  | math    |  NULL |    5 |
+------+-------+---------+-------+------+

結果データには、スコアが80の行が2つあります。これらはすべて3位です。次のスコア70の順位は4位です。これにより、DENSE_RANK()は空白のない番号を持つことが示されています。

FIRST_VALUE()

FIRST_VALUE()はウィンドウ範囲の最初の値を返します。

構文:

FIRST_VALUE(expr [IGNORE NULLS]) OVER(partition_by_clause order_by_clause [window_clause])

IGNORE NULLSはv2.5.0からサポートされています。これは、exprのNULL値を計算から除外するかどうかを決定するために使用されます。デフォルトでは、NULL値は含まれており、フィルタリングされた結果の最初の値がNULLの場合、NULLが返されます。IGNORE NULLSを指定すると、フィルタリングされた結果の最初の非NULL値が返されます。すべての値がNULLの場合、IGNORE NULLSを指定してもNULLが返されます。

例:

subjectでグループ化し、各グループ内のメンバーごとの最初のscore値を返します。この例では、サンプルテーブルscoresのデータを使用します。

select *,
    first_value(score IGNORE NULLS)
        over (
            partition by subject
            order by score desc
        ) as first
from scores;

+------+-------+---------+-------+-------+
| id   | name  | subject | score | first |
+------+-------+---------+-------+-------+
|    1 | lily  | english |   100 |   100 |
|    2 | tom   | english |    98 |   100 |
|    4 | amy   | english |    92 |   100 |
|    5 | mike  | english |    85 |   100 |
|    3 | jack  | english |  NULL |   100 |
|    6 | amber | physics |   100 |   100 |
|    3 | jack  | physics |    99 |   100 |
|    4 | amy   | physics |    99 |   100 |
|    5 | mike  | physics |    85 |   100 |
|    1 | lily  | physics |    60 |   100 |
|    2 | tom   | physics |  NULL |   100 |
|    6 | amber | NULL    |    90 |    90 |
|    3 | jack  | math    |    95 |    95 |
|    6 | amber | math    |    92 |    95 |
|    2 | tom   | math    |    80 |    95 |
|    4 | amy   | math    |    80 |    95 |
|    5 | mike  | math    |    70 |    95 |
|    1 | lily  | math    |  NULL |    95 |
+------+-------+---------+-------+-------+

LAST_VALUE()

LAST_VALUE()はウィンドウ範囲の最後の値を返します。これはFIRST_VALUE()の逆です。

構文:

LAST_VALUE(expr [IGNORE NULLS]) OVER(partition_by_clause order_by_clause [window_clause])

IGNORE NULLSはv2.5.0からサポートされています。これは、exprのNULL値を計算から除外するかどうかを決定するために使用されます。デフォルトでは、NULL値は含まれており、フィルタリングされた結果の最後の値がNULLの場合、NULLが返されます。IGNORE NULLSを指定すると、フィルタリングされた結果の最後の非NULL値が返されます。すべての値がNULLの場合、IGNORE NULLSを指定してもNULLが返されます。

デフォルトでは、LAST_VALUE()はrows between unbounded preceding and current rowを計算し、現在の行とその前のすべての行を比較します。各パーティションに1つの値のみを表示する場合は、ORDER BYの後にrows between unbounded preceding and unbounded followingを使用します。

例:

subjectでグループ化し、グループ内のメンバーごとの最後のscoreを返します。この例では、サンプルテーブルscoresのデータを使用します。

select *,
    last_value(score IGNORE NULLS)
        over (
            partition by subject
            order by score desc
            rows between unbounded preceding and unbounded following
        ) as last
from scores;

+------+-------+---------+-------+------+
| id   | name  | subject | score | last |
+------+-------+---------+-------+------+
|    1 | lily  | english |   100 |   85 |
|    2 | tom   | english |    98 |   85 |
|    4 | amy   | english |    92 |   85 |
|    5 | mike  | english |    85 |   85 |
|    3 | jack  | english |  NULL |   85 |
|    6 | amber | physics |   100 |   60 |
|    3 | jack  | physics |    99 |   60 |
|    4 | amy   | physics |    99 |   60 |
|    5 | mike  | physics |    85 |   60 |
|    1 | lily  | physics |    60 |   60 |
|    2 | tom   | physics |  NULL |   60 |
|    6 | amber | NULL    |    90 |   90 |
|    3 | jack  | math    |    95 |   70 |
|    6 | amber | math    |    92 |   70 |
|    2 | tom   | math    |    80 |   70 |
|    4 | amy   | math    |    80 |   70 |
|    5 | mike  | math    |    70 |   70 |
|    1 | lily  | math    |  NULL |   70 |
+------+-------+---------+-------+------+

LAG()

現在の行からoffset行遅れた行の値を返します。この関数は、行間の値を比較し、データをフィルタリングするためによく使用されます。

LAG()は、次のタイプのデータをクエリするために使用できます:

数値: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL
文字列: CHAR, VARCHAR
日付: DATE, DATETIME
BITMAPおよびHLLはStarRocks v2.5からサポートされています。

構文:

LAG(expr [IGNORE NULLS] [, offset[, default]])
OVER([<partition_by_clause>] [<order_by_clause>])

パラメータ:

expr: 計算したいフィールド。
offset: オフセット。正の整数である必要があります。このパラメータが指定されていない場合、デフォルトは1です。
default: 一致する行が見つからない場合に返されるデフォルト値。このパラメータが指定されていない場合、デフォルトはNULLです。defaultは、exprと互換性のあるタイプの任意の式をサポートします。
IGNORE NULLSはv3.0からサポートされています。これは、exprのNULL値が結果に含まれるかどうかを決定するために使用されます。デフォルトでは、offset行がカウントされるときにNULL値が含まれ、目的の行の値がNULLの場合、NULLが返されます。例1を参照してください。IGNORE NULLSを指定すると、offset行がカウントされるときにNULL値が無視され、システムはoffsetの非NULL値を検索し続けます。offsetの非NULL値が見つからない場合、NULLまたは（指定されている場合）defaultが返されます。例2を参照してください。

例1: IGNORE NULLSが指定されていない場合

テーブルを作成し、値を挿入します:

CREATE TABLE test_tbl (col_1 INT, col_2 INT)
DISTRIBUTED BY HASH(col_1);

INSERT INTO test_tbl VALUES 
    (1, NULL),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, 7),
    (7, 6),
    (8, 5),
    (9, NULL),
    (10, NULL);

このテーブルからデータをクエリし、offsetは2で、これは前の2行をたどることを意味します。defaultは0で、一致する行が見つからない場合に0が返されます。

出力:

SELECT col_1, col_2, LAG(col_2,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+---------------------------------------------+
| col_1 | col_2 | lag(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+---------------------------------------------+
|     1 |  NULL |                                           0 |
|     2 |     4 |                                           0 |
|     3 |  NULL |                                        NULL |
|     4 |     2 |                                           4 |
|     5 |  NULL |                                        NULL |
|     6 |     7 |                                           2 |
|     7 |     6 |                                        NULL |
|     8 |     5 |                                           7 |
|     9 |  NULL |                                           6 |
|    10 |  NULL |                                           5 |
+-------+-------+---------------------------------------------+

最初の2行については、前の2行が存在せず、デフォルト値0が返されます。

3行目のNULLについては、2行前の値がNULLであり、NULLが許可されているため、NULLが返されます。

例2: IGNORE NULLSが指定されている場合

前述のテーブルとパラメータ設定を使用します。

SELECT col_1, col_2, LAG(col_2 IGNORE NULLS,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+---------------------------------------------+
| col_1 | col_2 | lag(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+---------------------------------------------+
|     1 |  NULL |                                           0 |
|     2 |     4 |                                           0 |
|     3 |  NULL |                                           0 |
|     4 |     2 |                                           0 |
|     5 |  NULL |                                           4 |
|     6 |     7 |                                           4 |
|     7 |     6 |                                           2 |
|     8 |     5 |                                           7 |
|     9 |  NULL |                                           6 |
|    10 |  NULL |                                           6 |
+-------+-------+---------------------------------------------+

1行目から4行目については、前の行で2つの非NULL値を見つけることができず、デフォルト値0が返されます。

7行目の値6については、2行前の値がNULLであり、IGNORE NULLSが指定されているため、NULLが無視されます。システムは非NULL値を検索し続け、4行目の2が返されます。

LEAD()

現在の行からoffset行先の行の値を返します。この関数は、行間の値を比較し、データをフィルタリングするためによく使用されます。

LEAD()でクエリできるデータ型は、LAG()でサポートされているものと同じです。

構文:

LEAD(expr [IGNORE NULLS] [, offset[, default]])
OVER([<partition_by_clause>] [<order_by_clause>])

パラメータ:

expr: 計算したいフィールド。
offset: オフセット。正の整数である必要があります。このパラメータが指定されていない場合、デフォルトは1です。
default: 一致する行が見つからない場合に返されるデフォルト値。このパラメータが指定されていない場合、デフォルトはNULLです。defaultは、exprと互換性のあるタイプの任意の式をサポートします。
IGNORE NULLSはv3.0からサポートされています。これは、exprのNULL値が結果に含まれるかどうかを決定するために使用されます。デフォルトでは、offset行がカウントされるときにNULL値が含まれ、目的の行の値がNULLの場合、NULLが返されます。例1を参照してください。IGNORE NULLSを指定すると、offset行がカウントされるときにNULL値が無視され、システムはoffsetの非NULL値を検索し続けます。offsetの非NULL値が見つからない場合、NULLまたは（指定されている場合）defaultが返されます。例2を参照してください。

例1: IGNORE NULLSが指定されていない場合

テーブルを作成し、値を挿入します:

CREATE TABLE test_tbl (col_1 INT, col_2 INT)
DISTRIBUTED BY HASH(col_1);

INSERT INTO test_tbl VALUES 
    (1, NULL),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, 7),
    (7, 6),
    (8, 5),
    (9, NULL),
    (10, NULL);

このテーブルからデータをクエリし、offsetは2で、これは後の2行をたどることを意味します。defaultは0で、一致する行が見つからない場合に0が返されます。

出力:

SELECT col_1, col_2, LEAD(col_2,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+----------------------------------------------+
| col_1 | col_2 | lead(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+----------------------------------------------+
|     1 |  NULL |                                         NULL |
|     2 |     4 |                                            2 |
|     3 |  NULL |                                         NULL |
|     4 |     2 |                                            7 |
|     5 |  NULL |                                            6 |
|     6 |     7 |                                            5 |
|     7 |     6 |                                         NULL |
|     8 |     5 |                                         NULL |
|     9 |  NULL |                                            0 |
|    10 |  NULL |                                            0 |
+-------+-------+----------------------------------------------+

最初の行については、2行先の値がNULLであり、NULLが返されます。

最後の2行については、後の2行が存在せず、デフォルト値0が返されます。

例2: IGNORE NULLSが指定されている場合

前述のテーブルとパラメータ設定を使用します。

SELECT col_1, col_2, LEAD(col_2 IGNORE NULLS,2,0) OVER (ORDER BY col_1) 
FROM test_tbl ORDER BY col_1;
+-------+-------+----------------------------------------------+
| col_1 | col_2 | lead(col_2, 2, 0) OVER (ORDER BY col_1 ASC ) |
+-------+-------+----------------------------------------------+
|     1 |  NULL |                                            2 |
|     2 |     4 |                                            7 |
|     3 |  NULL |                                            7 |
|     4 |     2 |                                            6 |
|     5 |  NULL |                                            6 |
|     6 |     7 |                                            5 |
|     7 |     6 |                                            0 |
|     8 |     5 |                                            0 |
|     9 |  NULL |                                            0 |
|    10 |  NULL |                                            0 |
+-------+-------+----------------------------------------------+

7行目から10行目については、後の行で2つの非NULL値を見つけることができず、デフォルト値0が返されます。

最初の行については、2行先の値がNULLであり、IGNORE NULLSが指定されているため、NULLが無視されます。システムは2番目の非NULL値を検索し続け、4行目の2が返されます。

MAX()

現在のウィンドウ内の指定された行の最大値を返します。

構文:

MAX(expr) [OVER (analytic_clause)]

例:

現在の行の後の行までの最初の行からの最大値を計算します。この例では、サンプルテーブルscoresのデータを使用します。

select *,
    max(scores)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and 1 following
        ) as max
from scores
where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | max  |
+------+-------+---------+-------+------+
|    1 | lily  | math    |  NULL |   70 |
|    5 | mike  | math    |    70 |   80 |
|    2 | tom   | math    |    80 |   80 |
|    4 | amy   | math    |    80 |   92 |
|    6 | amber | math    |    92 |   95 |
|    3 | jack  | math    |    95 |   95 |
+------+-------+---------+-------+------+

以下の例では、math科目のすべての行の最大スコアを計算します。

select *,
    max(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following
        ) as max
from scores
where subject in ('math');

StarRocks 2.4以降、行範囲をrows between n preceding and n followingとして指定でき、現在の行の前のn行と後のn行をキャプチャできます。

例文:

select *,
    max(score)
        over (
            partition by subject
            order by score
            rows between 3 preceding and 2 following) as max
from scores
where subject in ('math');

MIN()

現在のウィンドウ内の指定された行の最小値を返します。

構文:

MIN(expr) [OVER (analytic_clause)]

例:

math科目のすべての行の最低スコアを計算します。この例では、サンプルテーブルscoresのデータを使用します。

select *, 
    min(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following)
            as min
from scores
where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | min  |
+------+-------+---------+-------+------+
|    1 | lily  | math    |  NULL |   70 |
|    5 | mike  | math    |    70 |   70 |
|    2 | tom   | math    |    80 |   70 |
|    4 | amy   | math    |    80 |   70 |
|    6 | amber | math    |    92 |   70 |
|    3 | jack  | math    |    95 |   70 |
+------+-------+---------+-------+------+

StarRocks 2.4以降、行範囲をrows between n preceding and n followingとして指定でき、現在の行の前のn行と後のn行をキャプチャできます。

例文:

select *,
    min(score)
        over (
            partition by subject
            order by score
            rows between 3 preceding and 2 following) as max
from scores
where subject in ('math');

NTILE()

NTILE()関数は、パーティション内のソートされた行を指定されたnum_bucketsの数でできるだけ均等に分割し、分割された行をそれぞれのバケットに格納し、各行が属するバケット番号を返します。

バケットのサイズについて:

行数が指定されたnum_bucketsの数で正確に割り切れる場合、すべてのバケットは同じサイズになります。
行数が指定されたnum_bucketsの数で正確に割り切れない場合、2つの異なるサイズのバケットが存在します。サイズの差は1です。より多くの行を持つバケットは、より少ない行を持つバケットの前にリストされます。

構文:

NTILE (num_buckets) OVER (partition_by_clause order_by_clause)

num_buckets: 作成されるバケットの数。値は2^63 - 1を最大とする定数の正の整数でなければなりません。

ウィンドウ句はNTILE()関数では許可されていません。

NTILE()関数はBIGINTタイプのデータを返します。

例:

select *,
    ntile(2)
        over (
            partition by subject
            order by score
        ) as bucket_id
from scores;

出力:

+------+-------+---------+-------+-----------+
| id   | name  | subject | score | bucket_id |
+------+-------+---------+-------+-----------+
|    6 | amber | NULL    |    90 |         1 |
|    1 | lily  | math    |  NULL |         1 |
|    5 | mike  | math    |    70 |         1 |
|    2 | tom   | math    |    80 |         1 |
|    4 | amy   | math    |    80 |         2 |
|    6 | amber | math    |    92 |         2 |
|    3 | jack  | math    |    95 |         2 |
|    3 | jack  | english |  NULL |         1 |
|    5 | mike  | english |    85 |         1 |
|    4 | amy   | english |    92 |         1 |
|    2 | tom   | english |    98 |         2 |
|    1 | lily  | english |   100 |         2 |
|    2 | tom   | physics |  NULL |         1 |
|    1 | lily  | physics |    60 |         1 |
|    5 | mike  | physics |    85 |         1 |
|    3 | jack  | physics |    99 |         2 |
|    4 | amy   | physics |    99 |         2 |
|    6 | amber | physics |   100 |         2 |
+------+-------+---------+-------+-----------+

上記の例のように、num_bucketsが2の場合:

最初の行については、このパーティションにはこのレコードしかなく、1つのバケットに割り当てられます。
2行目から7行目については、パーティションには6つのレコードがあり、最初の3つのレコードがバケット1に割り当てられ、他の3つのレコードがバケット2に割り当てられます。

RANK()

RANK()関数はランキングを表すために使用されます。DENSE_RANK()とは異なり、RANK()は空白のある番号が現れます。例えば、1位が2つある場合、RANK()の3番目の番号は2ではなく3になります。

構文:

RANK() OVER(partition_by_clause order_by_clause)

例:

グループ内のmathスコアをランク付けします。この例では、サンプルテーブルscoresのデータを使用します。

select *, 
    rank() over(
        partition by subject
        order by score desc
        ) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    4 | amy   | math    |    80 |    3 |
|    2 | tom   | math    |    80 |    3 |
|    5 | mike  | math    |    70 |    5 |
|    1 | lily  | math    |  NULL |    6 |
+------+-------+---------+-------+------+

結果データには、スコアが80の行が2つあります。これらはすべて3位です。次のスコア70の順位は5位です。

ROW_NUMBER()

パーティションの各行に対して1から始まる連続的に増加する整数を返します。RANK()やDENSE_RANK()とは異なり、ROW_NUMBER()によって返される値は繰り返しやギャップがなく、連続して増加します。

構文:

ROW_NUMBER() OVER(partition_by_clause order_by_clause)

例:

グループ内のmathスコアをランク付けします。この例では、サンプルテーブルscoresのデータを使用します。

select *, row_number() over(
    partition by subject
    order by score desc) as `rank`
from scores where subject in ('math');

+------+-------+---------+-------+------+
| id   | name  | subject | score | rank |
+------+-------+---------+-------+------+
|    3 | jack  | math    |    95 |    1 |
|    6 | amber | math    |    92 |    2 |
|    2 | tom   | math    |    80 |    3 |
|    4 | amy   | math    |    80 |    4 |
|    5 | mike  | math    |    70 |    5 |
|    1 | lily  | math    |  NULL |    6 |
+------+-------+---------+-------+------+

QUALIFY()

QUALIFY句はウィンドウ関数の結果をフィルタリングします。SELECT文で、QUALIFY句を使用して列に条件を適用し、結果をフィルタリングできます。QUALIFYは集計関数のHAVING句に類似しています。この関数はv2.5からサポートされています。

QUALIFYはSELECT文の記述を簡素化します。

QUALIFYが使用される前のSELECT文は次のようになります:

SELECT *
FROM (SELECT DATE,
             PROVINCE_CODE,
             TOTAL_SCORE,
             ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) AS SCORE_ROWNUMBER
      FROM example_table) T1
WHERE T1.SCORE_ROWNUMBER = 1;

QUALIFYが使用された後、文は次のように短縮されます:

SELECT DATE, PROVINCE_CODE, TOTAL_SCORE
FROM example_table 
QUALIFY ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) = 1;

QUALIFYは次の3つのウィンドウ関数のみをサポートします: ROW_NUMBER(), RANK(), およびDENSE_RANK()。

構文:

SELECT <column_list>
FROM <data_source>
[GROUP BY ...]
[HAVING ...]
QUALIFY <window_function>
[ ... ]

パラメータ:

<column_list>: データを取得したい列。

<data_source>: データソースは通常テーブルです。

<window_function>: QUALIFY句の後にはウィンドウ関数のみが続くことができます。ROW_NUMBER(), RANK(), およびDENSE_RANK()を含みます。

例:

-- テーブルを作成します。
CREATE TABLE sales_record (
   city_id INT,
   item STRING,
   sales INT
) DISTRIBUTED BY HASH(`city_id`);

-- テーブルにデータを挿入します。
insert into sales_record values
(1,'fruit',95),
(2,'drinks',70),
(3,'fruit',87),
(4,'drinks',98);

-- テーブルからデータをクエリします。
select * from sales_record order by city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       2 | drinks |    70 |
|       3 | fruit  |    87 |
|       4 | drinks |    98 |
+---------+--------+-------+

例1: テーブルから行番号が1より大きいレコードを取得します。

SELECT city_id, item, sales
FROM sales_record
QUALIFY row_number() OVER (ORDER BY city_id) > 1;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       2 | drinks |    70 |
|       3 | fruit  |    87 |
|       4 | drinks |    98 |
+---------+--------+-------+

例2: テーブルの各パーティションから行番号が1のレコードを取得します。テーブルはitemで2つのパーティションに分割され、各パーティションの最初の行が返されます。

SELECT city_id, item, sales
FROM sales_record 
QUALIFY ROW_NUMBER() OVER (PARTITION BY item ORDER BY city_id) = 1
ORDER BY city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       2 | drinks |    70 |
+---------+--------+-------+
2 rows in set (0.01 sec)

例3: テーブルの各パーティションから売上が1位のレコードを取得します。テーブルはitemで2つのパーティションに分割され、各パーティションで最も高い売上の行が返されます。

SELECT city_id, item, sales
FROM sales_record
QUALIFY RANK() OVER (PARTITION BY item ORDER BY sales DESC) = 1
ORDER BY city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       4 | drinks |    98 |
+---------+--------+-------+

使用上の注意:

QUALIFYは次の3つのウィンドウ関数のみをサポートします: ROW_NUMBER(), RANK(), およびDENSE_RANK()。
QUALIFYを含むクエリの句の実行順序は次の順序で評価されます:

FROM
WHERE
GROUP BY
HAVING
ウィンドウ
QUALIFY
DISTINCT
ORDER BY
LIMIT

SUM()

指定された行の合計を計算します。

構文:

SUM(expr) [OVER (analytic_clause)]

例:

subjectでデータをグループ化し、グループ内のすべての行のスコアの合計を計算します。この例では、サンプルテーブルscoresのデータを使用します。

select *,
    sum(score)
        over (
            partition by subject
            order by score
            rows between unbounded preceding and unbounded following
        ) as 'sum'
from scores;

+------+-------+---------+-------+------+
| id   | name  | subject | score | sum  |
+------+-------+---------+-------+------+
|    6 | amber | NULL    |    90 |   90 |
|    1 | lily  | math    |  NULL |  417 |
|    5 | mike  | math    |    70 |  417 |
|    2 | tom   | math    |    80 |  417 |
|    4 | amy   | math    |    80 |  417 |
|    6 | amber | math    |    92 |  417 |
|    3 | jack  | math    |    95 |  417 |
|    3 | jack  | english |  NULL |  375 |
|    5 | mike  | english |    85 |  375 |
|    4 | amy   | english |    92 |  375 |
|    2 | tom   | english |    98 |  375 |
|    1 | lily  | english |   100 |  375 |
|    2 | tom   | physics |  NULL |  443 |
|    1 | lily  | physics |    60 |  443 |
|    5 | mike  | physics |    85 |  443 |
|    3 | jack  | physics |    99 |  443 |
|    4 | amy   | physics |    99 |  443 |
|    6 | amber | physics |   100 |  443 |
+------+-------+---------+-------+------+

VARIANCE, VAR_POP, VARIANCE_POP

式の母分散を返します。VAR_POPおよびVARIANCE_POPはVARIANCEのエイリアスです。これらの関数はv2.5.10以降、ウィンドウ関数として使用できます。

構文:

VARIANCE(expr) [OVER (partition_by_clause)]

ヒント

2.5.13、3.0.7、3.1.4以降、このウィンドウ関数はORDER BYおよびウィンドウ句をサポートします。

パラメータ:

exprがテーブル列の場合、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、またはDECIMALに評価される必要があります。

例:

この例では、サンプルテーブルscoresのデータを使用します。

select *,
    variance(score)
        over (
            partition by subject
            order by score
        ) as 'variance'
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | variance           |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 | 22.222222222222225 |
|    4 | amy   | math    |    80 | 22.222222222222225 |
|    6 | amber | math    |    92 |  60.74999999999997 |
|    3 | jack  | math    |    95 |  82.23999999999998 |
+------+-------+---------+-------+--------------------+

VAR_SAMP, VARIANCE_SAMP

式の標本分散を返します。これらの関数はv2.5.10以降、ウィンドウ関数として使用できます。

構文:

VAR_SAMP(expr) [OVER (partition_by_clause)]

ヒント

2.5.13、3.0.7、3.1.4以降、このウィンドウ関数はORDER BYおよびウィンドウ句をサポートします。

パラメータ:

exprがテーブル列の場合、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、またはDECIMALに評価される必要があります。

例:

この例では、サンプルテーブルscoresのデータを使用します。

select *,
    VAR_SAMP(score)
       over (partition by subject
            order by score) as VAR_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | VAR_SAMP           |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 | 33.333333333333336 |
|    4 | amy   | math    |    80 | 33.333333333333336 |
|    6 | amber | math    |    92 |  80.99999999999996 |
|    3 | jack  | math    |    95 | 102.79999999999997 |
+------+-------+---------+-------+--------------------+

STD, STDDEV, STDDEV_POP

式の標準偏差を返します。これらの関数はv2.5.10以降、ウィンドウ関数として使用できます。

構文:

STD(expr) [OVER (partition_by_clause)]

ヒント

2.5.13、3.0.7、3.1.4以降、このウィンドウ関数はORDER BYおよびウィンドウ句をサポートします。

パラメータ:

exprがテーブル列の場合、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、またはDECIMALに評価される必要があります。

例:

この例では、サンプルテーブルscoresのデータを使用します。

select *, STD(score)
    over (
        partition by subject
        order by score) as std
from scores where subject in ('math');
+------+-------+---------+-------+-------------------+
| id   | name  | subject | score | std               |
+------+-------+---------+-------+-------------------+
|    1 | lily  | math    |  NULL |              NULL |
|    5 | mike  | math    |    70 |                 0 |
|    4 | amy   | math    |    80 | 4.714045207910317 |
|    2 | tom   | math    |    80 | 4.714045207910317 |
|    6 | amber | math    |    92 | 7.794228634059946 |
|    3 | jack  | math    |    95 | 9.068627239003707 |
+------+-------+---------+-------+-------------------+

STDDEV_SAMP

式の標本標準偏差を返します。この関数はv2.5.10以降、ウィンドウ関数として使用できます。

構文:

STDDEV_SAMP(expr) [OVER (partition_by_clause)]

ヒント

2.5.13、3.0.7、3.1.4以降、このウィンドウ関数はORDER BYおよびウィンドウ句をサポートします。

パラメータ:

exprがテーブル列の場合、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、またはDECIMALに評価される必要があります。

例:

この例では、サンプルテーブルscoresのデータを使用します。

select *, STDDEV_SAMP(score)
    over (
        partition by subject
        order by score
        ) as STDDEV_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | STDDEV_SAMP        |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |               NULL |
|    5 | mike  | math    |    70 |                  0 |
|    2 | tom   | math    |    80 |  5.773502691896258 |
|    4 | amy   | math    |    80 |  5.773502691896258 |
|    6 | amber | math    |    92 |  8.999999999999998 |
|    3 | jack  | math    |    95 | 10.139033484509259 |
+------+-------+---------+-------+--------------------+

select *, STDDEV_SAMP(score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as STDDEV_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+--------------------+
| id   | name  | subject | score | STDDEV_SAMP        |
+------+-------+---------+-------+--------------------+
|    1 | lily  | math    |  NULL |                  0 |
|    5 | mike  | math    |    70 | 7.0710678118654755 |
|    2 | tom   | math    |    80 |  5.773502691896258 |
|    4 | amy   | math    |    80 |  8.999999999999998 |
|    6 | amber | math    |    92 | 10.139033484509259 |
|    3 | jack  | math    |    95 | 10.139033484509259 |
+------+-------+---------+-------+--------------------+

COVAR_SAMP

2つの式の標本共分散を返します。この関数はv2.5.10からサポートされています。また、集計関数でもあります。

構文:

COVAR_SAMP(expr1,expr2) [OVER (partition_by_clause)]

ヒント

2.5.13、3.0.7、3.1.4以降、このウィンドウ関数はORDER BYおよびウィンドウ句をサポートします。

パラメータ:

exprがテーブル列の場合、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、またはDECIMALに評価される必要があります。

例:

この例では、サンプルテーブルscoresのデータを使用します。

select *, COVAR_SAMP(id, score) 
    over (
        partition by subject
        order by score) as covar_samp
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | covar_samp           |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                 NULL |
|    5 | mike  | math    |    70 |                    0 |
|    2 | tom   | math    |    80 |   -6.666666666666668 |
|    4 | amy   | math    |    80 |   -6.666666666666668 |
|    6 | amber | math    |    92 |                  4.5 |
|    3 | jack  | math    |    95 | -0.24999999999999822 |
+------+-------+---------+-------+----------------------+

select *, COVAR_SAMP(id,score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as COVAR_SAMP
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | COVAR_SAMP           |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                    0 |
|    5 | mike  | math    |    70 |                   -5 |
|    4 | amy   | math    |    80 |   -6.666666666666661 |
|    2 | tom   | math    |    80 |    4.500000000000004 |
|    6 | amber | math    |    92 | -0.24999999999999467 |
|    3 | jack  | math    |    95 | -0.24999999999999467 |

COVAR_POP

2つの式の母共分散を返します。この関数はv2.5.10からサポートされています。また、集計関数でもあります。

構文:

COVAR_POP(expr1, expr2) [OVER (partition_by_clause)]

ヒント

2.5.13、3.0.7、3.1.4以降、このウィンドウ関数はORDER BYおよびウィンドウ句をサポートします。

パラメータ:

exprがテーブル列の場合、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、またはDECIMALに評価される必要があります。

例:

この例では、サンプルテーブルscoresのデータを使用します。

select *, COVAR_POP(id, score)
    over (
        partition by subject
        order by score) as covar_pop
from scores where subject in ('math');
+------+-------+---------+-------+----------------------+
| id   | name  | subject | score | covar_pop            |
+------+-------+---------+-------+----------------------+
|    1 | lily  | math    |  NULL |                 NULL |
|    5 | mike  | math    |    70 |                    0 |
|    2 | tom   | math    |    80 |  -4.4444444444444455 |
|    4 | amy   | math    |    80 |  -4.4444444444444455 |
|    6 | amber | math    |    92 |                3.375 |
|    3 | jack  | math    |    95 | -0.19999999999999857 |
+------+-------+---------+-------+----------------------+

CORR

2つの式間のピアソン相関係数を返します。この関数はv2.5.10からサポートされています。また、集計関数でもあります。

構文:

CORR(expr1, expr2) [OVER (partition_by_clause)]

ヒント

2.5.13、3.0.7、3.1.4以降、このウィンドウ関数はORDER BYおよびウィンドウ句をサポートします。

パラメータ:

exprがテーブル列の場合、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、またはDECIMALに評価される必要があります。

例:

この例では、サンプルテーブルscoresのデータを使用します。

select *, CORR(id, score)
    over (
        partition by subject
        order by score) as corr
from scores where subject in ('math');
+------+-------+---------+-------+-----------------------+
| id   | name  | subject | score | corr                  |
+------+-------+---------+-------+-----------------------+
|    5 | mike  | math    |    70 | -0.015594571538795355 |
|    1 | lily  | math    |  NULL | -0.015594571538795355 |
|    2 | tom   | math    |    80 | -0.015594571538795355 |
|    4 | amy   | math    |    80 | -0.015594571538795355 |
|    3 | jack  | math    |    95 | -0.015594571538795355 |
|    6 | amber | math    |    92 | -0.015594571538795355 |
+------+-------+---------+-------+-----------------------+

select *, CORR(id,score)
    over (
        partition by subject
        order by score
        rows between unbounded preceding and 1 following) as corr 
from scores where subject in ('math');
+------+-------+---------+-------+-------------------------+
| id   | name  | subject | score | corr                    |
+------+-------+---------+-------+-------------------------+
|    1 | lily  | math    |  NULL | 1.7976931348623157e+308 |
|    5 | mike  | math    |    70 |                      -1 |
|    2 | tom   | math    |    80 |     -0.7559289460184546 |
|    4 | amy   | math    |    80 |     0.29277002188455997 |
|    6 | amber | math    |    92 |   -0.015594571538795024 |
|    3 | jack  | math    |    95 |   -0.015594571538795024 |
+------+-------+---------+-------+-------------------------+

背景​

使用法​

構文​

PARTITION BY句​

ORDER BY句​

ウィンドウ句​

ウィンドウ関数サンプルテーブル​

関数の例​

AVG()​

COUNT()​

DENSE_RANK()​

FIRST_VALUE()​

LAST_VALUE()​

LAG()​

LEAD()​

MAX()​

MIN()​

NTILE()​

RANK()​

ROW_NUMBER()​

QUALIFY()​

SUM()​

VARIANCE, VAR_POP, VARIANCE_POP​

VAR_SAMP, VARIANCE_SAMP​

STD, STDDEV, STDDEV_POP​

STDDEV_SAMP​

COVAR_SAMP​

COVAR_POP​

CORR​

このドキュメントについてどう思いましたか？

背景

使用法

構文

PARTITION BY句

ORDER BY句

ウィンドウ句

ウィンドウ関数サンプルテーブル

関数の例

AVG()

COUNT()

DENSE_RANK()

FIRST_VALUE()

LAST_VALUE()

LAG()

LEAD()

MAX()

MIN()

NTILE()

RANK()

ROW_NUMBER()

QUALIFY()

SUM()

VARIANCE, VAR_POP, VARIANCE_POP

VAR_SAMP, VARIANCE_SAMP

STD, STDDEV, STDDEV_POP

STDDEV_SAMP

COVAR_SAMP

COVAR_POP

CORR