メインコンテンツまでスキップ
バージョン: 3.2

ウィンドウ関数

背景​

ウィンドウ関数は、特別なクラスの組み込み関数です。集計関数と同様に、複数の入力行に対して計算を行い、単一のデータ値を取得します。違いは、ウィンドウ関数が特定のウィンドウ内で入力データを処理する点で、「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 列でソートされた 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)]

例:

現在の行から数学のパーティション内の最初の行まで、90 を超える数学のスコアの出現回数をカウントします。この例では、サンプルテーブル 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 |
+------+-------+---------+-------+-------------+

CUME_DIST()​

CUME_DIST() 関数は、パーティションまたはウィンドウ内の値の累積分布を計算し、そのパーティション内での相対的な位置をパーセンテージで示します。グループ内の最高値または最低値の分布を計算するためによく使用されます。

  • データが昇順にソートされている場合、この関数は現在の行の値以下の値のパーセンテージを計算します。
  • データが降順にソートされている場合、この関数は現在の行の値以上の値のパーセンテージを計算します。

累積分布は 0 から 1 の範囲内にあります。パーセンタイル計算やデータ分布分析に役立ちます。

この関数は v3.2 からサポートされています。

構文:

CUME_DIST() OVER (partition_by_clause order_by_clause)
  • partition_by_clause: 任意。指定されていない場合、結果セット全体が単一のパーティションとして処理されます。
  • order_by_clause: この関数は、パーティション行を目的の順序にソートするために ORDER BY と一緒に使用する必要があります。

CUME_DIST() は NULL 値を含み、最小値として扱います。

例:

以下の例では、各 subject グループ内の各スコアの累積分布を示します。この例では、サンプルテーブル scores のデータを使用します。

SELECT *, 
    cume_dist() 
      OVER (
        PARTITION BY subject
        ORDER BY score
      ) AS cume_dist 
FROM scores;
+------+-------+---------+-------+---------------------+
| id   | name  | subject | score | cume_dist           |
+------+-------+---------+-------+---------------------+
|    6 | amber | NULL    |    90 |                   1 |
|    3 | jack  | english |  NULL |                 0.2 |
|    5 | mike  | english |    85 |                 0.4 |
|    4 | amy   | english |    92 |                 0.6 |
|    2 | tom   | english |    98 |                 0.8 |
|    1 | lily  | english |   100 |                   1 |
|    1 | lily  | math    |  NULL | 0.16666666666666666 |
|    5 | mike  | math    |    70 |  0.3333333333333333 |
|    2 | tom   | math    |    80 |  0.6666666666666666 |
|    4 | amy   | math    |    80 |  0.6666666666666666 |
|    6 | amber | math    |    92 |  0.8333333333333334 |
|    3 | jack  | math    |    95 |                   1 |
|    2 | tom   | physics |  NULL | 0.16666666666666666 |
|    1 | lily  | physics |    60 |  0.3333333333333333 |
|    5 | mike  | physics |    85 |                 0.5 |
|    4 | amy   | physics |    99 |  0.8333333333333334 |
|    3 | jack  | physics |    99 |  0.8333333333333334 |
|    6 | amber | physics |   100 |                   1 |
+------+-------+---------+-------+---------------------+
  • 最初の行の cume_dist の場合、NULL グループには1行しかなく、その行自体のみが「現在の行以下」の条件を満たしています。累積分布は 1 です。
  • 2 行目の cume_dist の場合、english グループには5行あり、その行自体 (NULL) のみが「現在の行以下」の条件を満たしています。累積分布は 0.2 です。
  • 3 行目の cume_dist の場合、english グループには5行あり、2行 (85 と NULL) が「現在の行以下」の条件を満たしています。累積分布は 0.4 です。

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)

例:

以下の例では、数学のスコアをランキングします（降順にソート）。この例では、サンプルテーブル 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 値が結果に含まれるかどうかを決定するために使用されます。デフォルトでは、NULL 値は offset 行がカウントされるときに含まれ、目的の行の値が NULL の場合、NULL が返されます。例1を参照してください。IGNORE NULLS を指定すると、NULL 値は offset 行がカウントされるときに無視され、システムは 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 値が結果に含まれるかどうかを決定するために使用されます。デフォルトでは、NULL 値は offset 行がカウントされるときに含まれ、目的の行の値が NULL の場合、NULL が返されます。例1を参照してください。IGNORE NULLS を指定すると、NULL 値は offset 行がカウントされるときに無視され、システムは 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 が返されます。これは 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(score)
        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 の数で可能な限り均等に分割し、それぞれのバケットに分割された行を格納し、1 から始まる [1, 2, ..., 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 型のデータを返します。

例:

以下の例では、パーティション内のすべての行を2つのバケットに分割します。この例では、サンプルテーブル scores のデータを使用します。

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に割り当てられます。

PERCENT_RANK()​

結果セット内の行の相対ランクをパーセンテージとして計算します。

PERCENT_RANK() は、以下の式を使用して計算されます。ここで Rank はパーティション内の現在の行のランクを表します。

(Rank - 1)/(Rows in partition - 1)

返される値は 0 から 1 の範囲です。この関数は、パーセンタイル計算やデータ分布の分析に役立ちます。v3.2 からサポートされています。

構文:

PERCENT_RANK() OVER (partition_by_clause order_by_clause)

この関数は、パーティション行を目的の順序にソートするために ORDER BY と一緒に使用する必要があります。

例:

以下の例では、math グループ内の各 score の相対ランクを示します。この例では、サンプルテーブル scores のデータを使用します。

SELECT *,
    PERCENT_RANK()
        OVER (
            PARTITION BY subject
            ORDER BY score
        ) AS `percent_rank`
FROM scores where subject in ('math');
+------+-------+---------+-------+--------------+
| id   | name  | subject | score | percent_rank |
+------+-------+---------+-------+--------------+
|    1 | lily  | math    |  NULL |            0 |
|    5 | mike  | math    |    70 |          0.2 |
|    2 | tom   | math    |    80 |          0.4 |
|    4 | amy   | math    |    80 |          0.4 |
|    6 | amber | math    |    92 |          0.8 |
|    3 | jack  | math    |    95 |            1 |
+------+-------+---------+-------+--------------+

RANK()​

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

構文:

RANK() OVER(partition_by_clause order_by_clause)

例:

グループ内の数学のスコアをランキングします。この例では、サンプルテーブル 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)

例:

グループ内の数学のスコアをランキングします。この例では、サンプルテーブル 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 を含むクエリの句の実行順序は次の順序で評価されます:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. ウィンドウ
  6. QUALIFY
  7. DISTINCT
  8. ORDER BY
  9. LIMIT

SUM()​

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

構文:

SUM(expr) [OVER (analytic_clause)]

例:

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 |
+------+-------+---------+-------+-------------------------+