USER MANUALS

分析関数

分析関数 (ウィンドウ関数) は、指定した行の結果をその行のウィンドウフレームから派生させる関数です。これらの関数は集計関数に類似します。どちらの関数も、行のグループに基づいて集計値を計算するためです。違いとして、集計関数は行のグループごとに 1 つの値を返すのに対し、分析関数はグループごとに複数の行を返すことができます。

重要

Virtual DataPort は、これらの関数をデータベースに委任できますが、実行することはできません。そのため、クエリがこれらの関数のいずれかを使用していて、その関数をデータベースに委任できない場合、クエリは失敗します。

「 分析関数を実行するための対応策 」を参照してください。

次のように、すべての分析関数に共通する構文がいくつかあります。

分析関数: 共通の構文
<over clause> ::=
  OVER ( [ <window partition clause> ]
  [ <window order by clause> [ <windowing_frame_clause> ] ] )

<window partition clause> ::=
  PARTITION BY <expression>

<window order by clause> ::=
  ORDER BY <expression> [ ASC | DESC ]

<window_frame_clause> ::=
    <window frame units>
    { <window_frame_preceding> | <window_frame_between> }

<window frame units> ::=
    ROWS
  | RANGE

<window_frame_between> ::=
    BETWEEN <window_frame_bound> AND <window_frame_bound>

<window frame bound> ::=
    <window_frame_preceding>
  | <window_frame_following>

<window frame preceding> ::=
    UNBOUNDED PRECEDING
  | <unsigned_value_specification> PRECEDING
  | CURRENT ROW

<window frame following> ::=
    UNBOUNDED FOLLOWING
  | <unsigned_value_specification> FOLLOWING
  | CURRENT ROW

<unsigned value specification> ::=
  <unsigned integer literal>

AVG

説明

AVG 関数は、パーティション内の式の平均を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

AVG ( <expression> ) OVER (
    [ <window partition clause> ]
    [ <window order by clause> ]
    [ <window_frame_clause> ]
) : number

COUNT

説明

COUNT 関数は、クエリによって返された行の数を返します。

重要

Virtual DataPort は、この関数を Oracle、SAP HANA、Snowflake、および SQL Server にプッシュダウンできますが (他のデータベースにはプッシュダウンできません)、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

COUNT (
    {
        *
      | <expression>
    }
) OVER (
    [ <window partition clause> ]
    <window order by clause>
) : number

CUME_DIST

説明

CUME_DIST 関数は、同じパーティションの他の行に関する現在の行の累積分布を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

CUME_DIST() OVER (
    [ <window partition clause> ]
    <window order by clause>
) : number

DENSE_RANK

説明

DENSE_RANK 関数は、 <window order by clause> によって設定された順序に基づいて、行のウィンドウパーティション内でのその行のランクを返します。

グループ内の行は ORDER BY 句によって並べ替えられ、その後、この関数が 1 から順番に各行の番号を返します。値が等しい行については同じ値を返します (NULL 値は等しいとみなされます)。

関数 DENSE_RANK 、 RANK (RANK) および ROW_NUMBER (ROW_NUMBER) にはいくつかの類似点があります。相違点は以下のとおりです。

  • DENSE_RANK はランク付けをスキップしませんが、 RANK はスキップします。

  • ROW_NUMBER によって返される値は常に一意です。 RANK および DENSE_RANK は、等しい行に対しては同じ値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

DENSE_RANK( ) OVER (
    [ <window partition clause> ]
    <window order by clause>
 ) : numeric

FIRST_VALUE

説明

FIRST_VALUE 関数は、テーブルまたはパーティションの最初の値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

FIRST_VALUE( <expression> ) OVER (
    [ <window partition clause> ]
    [ <window order by clause> ]
    [ <window_frame_clause> ]
) : <type of the input expression>

LAG

説明

LAG 関数は、指定したオフセット分、ウィンドウの現在の行の 前 にある式の値を返します。

指定したオフセット分、 後 にある値を取得するには、 LEAD 関数 (「 LEAD 」) を使用します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

LAG (
      <expression>
    [ , <offset:number>
    [, <default value:type of the input expression> ] ]
) OVER (
    [ <window partition clause> ]
    <window order by clause>
): <type of the input expression>

  • expression: 評価する式。

  • offset: 遅れを示す 0 より大きい数値。指定されていない場合、オフセットは 1 (直前の行) です。

  • default value: offset がパーティションの境界の外である場合に返される値。たとえば、 offset が 1 の場合、この関数はパーティションの最初の行のデフォルト値を返します。

  • <window partition clause>: 「 Analytic functions: common syntax 」を参照してください。

  • <window order by clause>: 「 Analytic functions: common syntax 」を参照してください。

LAST_VALUE

説明

LAST_VALUE 関数は、テーブルまたはパーティションの最後の値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

LAST_VALUE( <expression> ) OVER (
    [ <window partition clause> ]
    [ <window order by clause> ]
    [ <window_frame_clause> ]
) : <type of the input expression>

LEAD

説明

LEAD 関数は、指定したオフセット分、ウィンドウの現在の行の 後 にある式の値を返します。

指定したオフセット分、 前 にある値を取得するには、 LAG 関数 (「 LAG 」) を使用します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

LEAD(
      <expression>
    [ , <offset:number>
    [, <default value:type of the input expression> ] ]
) OVER (
    [ <window partition clause> ]
    <window order by clause>
): <type of the input expression>

  • expression: 評価する式。

  • offset: 先行を示す 0 より大きい数値。指定されていない場合、オフセットは 1 (直後の行) です。

  • default value: offset がパーティションの境界の外部である場合に返される値。たとえば、 offset が 1 の場合、この関数はパーティションの最後の行のデフォルト値を返します。

  • <window partition clause>: 「 Analytic functions: common syntax 」を参照してください。

  • <window order by clause>: 「 Analytic functions: common syntax 」を参照してください。

LISTAGG

説明

LISTAGG 関数は、各グループ内のデータを ORDER BY 句で指定したとおりに並べ替えてから、メジャー列の値を連結します。各値は「delimiter expression」で区切られます。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

LISTAGG (
      <measure expression>
    , <delimiter expression:text>
) WITHIN GROUP ( <window order by clause> )
  OVER ( [ <window partition clause> ] ) : text

  • measure expression: 式。

  • delimiter expression: メジャー値を区切る文字列。値の間に区切り文字を入れたくない場合は、空の文字列 ('') を指定します。

  • <window order by clause>: 「 Analytic functions: common syntax 」を参照してください。

  • <window partition clause>: 「 Analytic functions: common syntax 」を参照してください。

テキスト値を返します。

MAX

説明

MAX 関数は、ウィンドウ内の式の最大値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

MAX ( <expression> ) OVER (
    [ <window partition clause> ]
    [ <window order by clause> ]
    [ <window_frame_clause> ]
) : <type of the input expression>

MIN

説明

MIN 関数は、ウィンドウ内の式の最小値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

MIN ( <expression> ) OVER (
    [ <window partition clause> ]
    [ <window order by clause> ]
    [ <window_frame_clause> ]
) : <type of the input expression>

NTILE

説明

NTILE 関数は、順序付けられたデータセット (パーティション) をウィンドウ内の複数のサブセットに分割し、バケット (サブセット) に 1 ～ <value> の番号を付けます。たとえば、<value> が 4 の場合、パーティション内の各行には、1 ～ 4 の番号が割り当てられます。パーティションに 40 行が含まれる場合は、最初の 10 行に 1、次の 10 行に 2 というように割り当てられます。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

NTILE ( <value> OVER (
    [ <window partition clause> ]
    <window order by clause>
): <number>

PERCENTILE_CONT

説明

PERCENTILE_CONT 関数は、各行について、ウィンドウ内の各パーティション内の値の中で指定されたパーセンタイルに入る値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

PERCENTILE_CONT ( <percentile:number> )
    WITHIN GROUP ( <window order by clause> )
    OVER ( [ <window partition clause> ] )

PERCENTILE_DISC

説明

PERCENTILE_DISC 関数は、各行について、ウィンドウ内の各パーティション内の値の中で指定されたパーセンタイルに該当する値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

PERCENTILE_DIST ( <percentile:number> )
    WITHIN GROUP ( <window order by clause> )
    OVER ( [ <window partition clause> ] )

PERCENT_RANK

説明

PERCENT_RANK 関数は、ウィンドウ内の値のグループを基準にして、行の相対的なランクを返します。 CUME_DIST に似ていますが、分子で行数ではなくランク値を使用します。

0 と 1 の間の値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

PERCENT_RANK ( ) OVER (
    [ <window partition clause> ]
    <window order by clause>
) : number

RANK

説明

RANK 関数は、 <window order by clause> によって設定された順序に基づいて、行のウィンドウパーティション内でのその行のランクを返します。

グループ内の行は ORDER BY 句によって並べ替えられ、その後、この関数が 1 から順番に各行の番号を返します。値が等しい行については同じ値を返します (この比較では、NULL 値は等しいとみなされます)。

関数 DENSE_RANK (DENSE_RANK)、 RANK 、および ROW_NUMBER (ROW_NUMBER) にはいくつかの類似点があります。相違点は以下のとおりです。

  • DENSE_RANK はランク付けをスキップしませんが、 RANK はスキップします。

  • ROW_NUMBER によって返される値は常に一意です。 RANK および DENSE_RANK は、等しい行に対しては同じ値を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

RANK( ) OVER (
    [ <window partition clause> ]
    <window order by clause>
) : numeric

ROW_NUMBER

説明

ROW_NUMBER 関数は、 <window order by clause> によって設定された順序に基づいて、行のウィンドウパーティション内でのその行の順位を返します。

グループ内の行は ORDER BY 句によって並べ替えられ、その後、この関数が 1 から順番に各行の番号を返します。値が等しい行については同じ値を返します (この比較では、NULL 値は等しいとみなされます)。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

関数 DENSE_RANK (DENSE_RANK)、 RANK (RANK)、および ROW_NUMBER にはいくつかの類似点があります。相違点は以下のとおりです。

  • DENSE_RANK はランク付けをスキップしませんが、 RANK はスキップします。

  • ROW_NUMBER によって返される値は常に一意です。 RANK および DENSE_RANK は、等しい行に対しては同じ値を返します。

構文

ROW_NUMBER ( ) OVER (
     [ <window partition clause> ] <window order by clause>
) : numeric

例

SELECT key
    , amount
    , block
    , ROW_NUMBER( OVER ( ORDER BY key ) row_number
    , ROW_NUMBER() OVER ( PARTITION BY block ORDER BY key ) row_number_with_partition
    , RANK() OVER ( PARTITION BY block ORDER BY amount ) rank
    , DENSE_RANK() OVER ( PARTITION BY block ORDER BY amount ) dense_rank
FROM VIEW
ORDER BY block, key;

key

amount

block

row_number

row_number_with_ partition

rank

dense_rank

1

1

1

1

1

1

1

2

1

1

2

2

1

1

3

2

1

3

3

3

2

4

2

1

4

4

3

2

5

2

1

5

5

3

2

6

4

1

6

6

6

3

7

5

2

7

1

1

1

9

7

2

8

2

2

2

10

8

2

9

3

3

3

11

9

2

10

4

4

4

SUM

説明

SUM 関数は、ウィンドウ内のグループ内の式の合計を返します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

SUM ( <expression> ) OVER (
    [ <window partition clause> ]
    [ <window order by clause> ]
    [ <window_frame_clause> ]
) : number

STDEV

説明

STDEV 関数は、ウィンドウ内のグループに対する現在の行の統計サンプルの標準偏差を計算します。

重要

Virtual DataPort は、この関数をデータベースにプッシュダウンできますが、実行することはできません。そのため、クエリがこの関数を使用していて、関数をデータベースにプッシュダウンできない場合、クエリは失敗します。「 分析関数を実行するための対応策 」を参照してください。

構文

STDEV ( <expression> ) OVER (
    [ <window partition clause> ]
    [ <window order by clause> ]
) : number

分析関数を実行するための対応策

Virtual DataPort は、分析関数をデータベースに委任できますが、実行することはできません。そのため、クエリに分析関数が含まれていて、実行エンジンが分析関数をサポートするデータベースにそれを委任できない場合、クエリは直ちに失敗します。

このような状況が発生する最も一般的なシナリオは、次のとおりです。

  1. クエリの基盤となるデータソースが分析関数をサポートしていない場合。たとえば、データソースが REST API、Excel ファイルなどである場合です。

  2. または、実行エンジンが分析関数をサポートするデータベースにそれらの関数を委任できない場合。たとえば、クエリが、異なるデータソースからデータに分析関数を適用する場合です。

これらのシナリオでは、「 データ移動 」の最適化を使用し、 CONTEXT 句のパラメータ DATAMOVEMENTPLAN を指定して、分析関数をサポートするデータベースにデータを転送します。

例
SELECT region.r_name, nation.n_name, sum(order.o_totalprice), rank() OVER (PARTITION BY region.r_name ORDER BY sum(order.o_totalprice) DESC)
FROM order INNER JOIN customer ON order.o_custkey = customer.c_custkey
INNER JOIN nation ON customer.c_nationkey = nation.n_nationkey
INNER JOIN region ON nation.n_regionkey = region.r_regionkey
WHERE region.r_regionkey > 0
GROUP BY region.r_name, nation.n_name
CONTEXT(DATAMOVEMENTPLAN =
    order : JDBC admin.vdpcachedatasource
    customer : JDBC admin.vdpcachedatasource
    nation : JDBC admin.vdpcachedatasource
    region : JDBC admin.vdpcachedatasource
);

この例では、 DATAMOVEMENTPLAN 句によって、クエリに関連するビューのデータをキャッシュデータベース (admin.vdpcachedatasource) に移動し、そこで分析関数を実行するように実行エンジンに命令しています。オプティマイザーは、WHERE 条件とその他の関数をソースデータベースに委任し、ソースとキャッシュデータベース間で転送する必要があるデータの削減を試みます。この例では、データソースが条件 r_regionkey > 0 をサポートしている場合に、 region のデータを取得するためにソースで実行される SQL クエリに、この条件が含まれます。

サードパーティ製ツール (ビジネスインテリジェンスツールなど) からクエリを実行しているために CONTEXT 句をクエリに追加できない場合は、この句を使用してビューを作成した上で、その新しいビューに対してクエリを実行します。次に例を示します。

CREATE VIEW rank_best_selling_nation_within_region AS
SELECT region.r_name, nation.n_name, sum(order.o_totalprice), rank() OVER (PARTITION BY region.r_name ORDER BY sum(order.o_totalprice) DESC)
FROM order INNER JOIN customer ON order.o_custkey = customer.c_custkey
INNER JOIN nation ON customer.c_nationkey = nation.n_nationkey
INNER JOIN region ON nation.n_regionkey = region.r_regionkey
WHERE region.r_regionkey > 0
GROUP BY region.r_name, nation.n_name
CONTEXT(DATAMOVEMENTPLAN =
    order : JDBC admin.vdpcachedatasource
    customer : JDBC admin.vdpcachedatasource
    nation : JDBC admin.vdpcachedatasource
    region : JDBC admin.vdpcachedatasource
);
Add feedback