標準SQLの分析関数の概念
分析関数は、行のグループの値を計算し、各行のasingle結果を返します。 これは、行のグループに対して単一の結果を返す集計関数とは異なります。
分析関数にはOVER句が含まれており、評価される行の周りの行のウィンドウを定義します。 各行について、分析関数resultisは、選択された行のウィンドウを入力として使用して計算され、集計を行う可能性があります。
分析関数を使用すると、移動平均、ランク項目、calculatecumulative和を計算し、他の分析を実行することができます。ナビゲーション関数、番号付け関数、および集約分析関数
分析関数構文
analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }window_specification: ] ] window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }
表記規則
- 角括弧””はオプションの句を示します。
- 括弧”()”はリテラル括弧を示します。
- 縦棒”/”は論理ORを示します。
- 中括弧”{}”はオプションのセットを囲みます。
- コンマの後に角括弧””内の省略記号が続くと、前の項目がコンマ区切りのリストで繰り返すことができることを示します。
説明
分析関数は、行のグループの結果を計算します。 次の構文を使用して、分析関数を作成できます。
-
analytic_function_nameRANK()をここで使用できます。 -
argument_list:分析関数に固有の引数。いくつかの機能はそれらを持っていますが、そうでない機能もあります。 -
OVEROVER句の前にある分析関数構文で必要なキーワード。 -
over_clause:分析関数を使用するテーブル内のgroupof行を定義するウィンドウを参照します。 -
window_specification:ウィンドウの仕様を定義します。 -
window_frame_clause:ウィンドウのウィンドウフレームを定義します。 -
rows_range:ウィンドウフレームの物理行またはalogical範囲を定義します。
注意事項
分析関数は、クエリ内のスカラー式オペランドintwoの場所として現れることができます。
-
SELECTSELECTOVER句は、同じ選択リストに導入されたエイリアスを参照できません。Li> -
ORDER BYORDER BYSELECTリストエイリアスを参照できます。
分析関数は、別名を介して間接的にも、引数リストまたはそのOVER句内の別の分析関数を参照することはできません。
分析関数は集計後に評価されます。 たとえば、GROUP BY句と非分析集計関数が最初に評価されます。集計関数は分析関数の前に評価されるため、集計関数は分析関数への入力オペランドとして使用できます。
は、入力の各行に対して単一の結果を返します。
は、入力の各行に対して単一の結果を返します。
は、入力の各行に対して
OVER句の定義
analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }説明
OVER句は、分析関数を使用するtableupon内の行のグループを定義するウィンドウを参照します。 クエリで定義されているnamed_windowを指定するか、新しいウィンドウの仕様を定義することができます。
注意事項
名前付きウィンドウもウィンドウ指定も指定されていない場合、allinput行はすべての行のウィンドウに含まれます。
OVER句を使用した例
これらのクエリは、ウィンドウ仕様を使用します。
- 総計を計算します
- 小計を計算します
- 累積合計を計算します
- 移動平均を計算します
- 範囲内のアイテムの数を計算します
- 各カテゴリで最も人気のあるアイテムを取得します
- 範囲内の最後の値を取得する
- compute rank
これらのクエリは名前付きウィンドウを使用します:
- 範囲内の最後の値を取得する
- ウィンドウフレーム句で名前付きウィンドウを使用する
ウィンドウ仕様を定義する
window_specification: ] ] 説明
ウィンドウの仕様を定義します。
-
named_windowWINDOW句で定義された既存のウィンドウの名前。
-
PARTITION BY:入力行を別々のパーティションに分割し、分析関数が独立して評価されます。-
PARTITION BY句では、複数のパーティション式が許可されています。 - 式には、浮動小数点型、グループ化できない型、定数、または分析関数を含めることはできません。
- このオプションの句を使用しない場合、入力テーブル内のすべての行は単一のパーティションを構成します。
-
-
ORDER BY:パーティション内で行の順序を定義します。この句は、ほとんどの状況では省略可能ですが、ナビゲーション関数の一部のケースでは必須です。 window_frame_clause-
window_frame_clause: Aggregate analyticfunctionsの場合、現在のパーティション内のウィンドウフレームを定義します。ウィンドウ枠は、ウィンドウに何を含めるかを決定します。この句を使用する場合、ORDER BYは、完全にバインドされていないウィンドウを除いて必要です。
注意事項
ORDER BY句もwindow frame句も存在しない場合、ウィンドウフレームにはそのパーティション内のすべての行が含まれます。集計分析関数の場合、ORDER BY句が存在するが、window frame句が存在しない場合、次のwindow frame句がデフォルトで使用されます:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWたとえば、次のクエリは同等です。
SELECT book, LAST_VALUE(item) OVER (ORDER BY year)FROM LibrarySELECT book, LAST_VALUE(item) OVER ( ORDER BY year RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)FROM Libraryウィンドウ仕様で名前付きウィンドウを使用するためのルール
ウィンドウ仕様で名前付きウィンドウを使用する場合は、次のルールが適用されます。
- 指定されたウィンドウの仕様は、WINDOW SPECIFICATION句で定義した新しい仕様を使用して拡張できます。
- 冗長な定義を持つことはできません。 指定されたウィンドウとウィンドウ指定句に
ORDER BYclausがある場合、anerrorがスローされます。 -
句の順序は重要です。
PARTITION BYORDER BYwindow_frame_clauseが続く必要があります。 名前付きウィンドウを追加すると、そのウィンドウの指定が最初に処理されます。p>--this works:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW item_window AS (ORDER BY purchases)--this does not work:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popularFROM ProduceWINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) -
名前付きウィンドウと
PARTITION BYPARTITION BYORDER BY句、外部クエリ、またはサブクエリで名前付きウィンドウを参照することはできません。
ウィンドウ仕様を使用する例
これらのクエリは、分析関数内のパーティションを定義します。
- 小計を計算する
- 累積合計を計算する
- 各カテこれらのクエリには、ウィンドウ指定に名前付きウィンドウが含まれます:
- 範囲内の最後の値を取得する
- ウィンドウフレーム句で名前付きウィンドウを使用する
これらのクエリは、パーティション内の行の順:
- 小計を計算する
- 累積合計を計算する
- 移動平均を計算する
- 範囲内の項目数を計算する
- 各カテゴリで最も人気のある項目を取得する
- 範囲内の最後の値を取得する
- ランクを計算する
- ウィンドウフレーム句で名前付きウィンドウを使用する
ウィンドウフレーム句を定義する
window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }frame_between: { BETWEEN unbounded_preceding AND frame_end_a | BETWEEN numeric_preceding AND frame_end_a | BETWEEN current_row AND frame_end_b | BETWEEN numeric_following AND frame_end_c }frame_start: { unbounded_preceding | numeric_preceding | }frame_end_a: { numeric_preceding | current_row | numeric_following | unbounded_following }frame_end_b: { current_row | numeric_following | unbounded_following }frame_end_c: { numeric_following | unbounded_following }unbounded_preceding: UNBOUNDED PRECEDINGnumeric_preceding: numeric_expression PRECEDINGunbounded_following: UNBOUNDED FOLLOWINGnumeric_following: numeric_expression FOLLOWINGcurrent_row: CURRENT ROWwindow frame句は、分析関数が評価される現在の行withinaパーティションの周りのウィンドウフレームを定義します。集計分析関数のみがwindow frame句を使用できます。
-
rows_range:物理行または論理範囲を持つウィンドウフレームを定義する句。-
ROWS:現在の行からの物理オフセットに基づいてウィンドウフレームを計算します。 たとえば、現在の行の前後に2つの行を含めることができます。 -
RANGEORDER BYORDER BY句に正確にoneexpressionがあり、式は数値型でなければなりません。ヒント：日付で範囲を使用する場合は、ORDER BYUNIX_DATE()UNIX_SECONDS()UNIX_MILLIS()UNIX_MICROS()関数を使用します。 -
frame_between:上下の境界を持つウィンドウフレームを作成します。最初の境界は下の境界を表します。 第二の境界上部の境界を表します。 前述の構文に示すように、特定の境界の組み合わせのみを使用できます。- ウィンドウフレームの先頭を
unbounded_precedingnumeric_precedingnumeric_followingcurrent_rowで定義します。-
unbounded_preceding:ウィンドウフレームはpartitionの先頭から始まります。li> -
numeric_precedingnumeric_following:ウィンドウフレームの開始は現在の行に相対的です。 -
current_row:ウィンドウフレームは現在の行から始まります。
-
- ウィンドウフレームの末尾を
numeric_precedingnumeric_followingcurrent_rowunbounded_followingで定義します。-
numeric_precedingnumeric_following:ウィンドウフレームの終わりは、現在の行に相対的です。 -
current_row: ウィンドウフレームは現在の行で終了します。 -
unbounded_following:ウィンドウフレームはpartitionの最後で終了します。
-
- ウィンドウフレームの先頭を
-
-
frame_start:下の境界を持つウィンドウフレームを作成します。ウィンドウフレームは現在の行で終了します。-
unbounded_preceding:ウィンドウフレームはpartitionの先頭から始まります。 -
numeric_preceding:ウィンドウフレームの開始は現在の行からの相対的なものです。 -
current_row:ウィンドウフレームは現在の行から始まります。/li>
-
-
numeric_expression:数値型を表す式。数値式は、負ではない定数integerorパラメーターでなければなりません。
注意事項
境界がパーティションの先頭または末尾を超えて拡張されている場合、ウィンドウフレームにはそのパーティション内の行のみが含
RANK()などのナビゲーション関数や番号付け関数では、ウィンドウフレーム句を使用することはできません。ウィンドウフレーム句を使用した例
これらのクエリは、
ROWSで値を計算します:- 累積合計を計算します
- 移動平均を計算します
- 各カテゴリで最も人気のあるアイテムを取得します
- 範囲内の最後の値を取得します
- ウィンドウフレーム句で名前付きウィンドウを使用します
これらのクエリは、
RANGE:- 範囲内のアイテムの数を計算します
これらのクエリは、部分的または完全にバインドされていないウィンドウで値を計算します:
- 総計を計算する
- 小計を計算する
- 累積合計を計算する
- 各カテゴリで最も人気のあるアイテムを取得する
- ランクを計算する
これらのクエリは、数値境界を持つ値を計算します。
- 累積合計を計算する
- 移動平均を計算します
- 範囲内のアイテムの数を計算します
- 範囲内の最後の値を取得する
- ウィンドウフレーム句で名前付きウィンドウを使用する
これらのクエリは、現在の行を境界として値を計算:
- 総計を計算する
- 小計を計算する
- 累積合計を計算する
名前付きウィンドウを参照する
SELECT query_expr, analytic_function_name ( ) OVER over_clauseFROM from_itemWINDOW named_window_expression over_clause: { named_window | ( ) }window_specification: ] ] named_window_expression: named_window AS { named_window | ( ) }名前付きウィンドウは、分析関数を使用するテーブル内の行のグループを表します。 名前付きウィンドウは、
WINDOWOVERで参照されますclause.InOVER句では、名前付きウィンドウは、それ自体で表示することも、ウィンドウ指定内でembeddedwithで表示することもできます。例
- 範囲内の最後の値を取得する
- ウィンドウフレーム句で名前付きウィンドウを使用する
ナビゲーション関数の概念
ナビゲーショ
OVER句の構文は、ナビゲーション関数によって異なります。OVER句の要件:-
PARTITION BY:オプションです。 ORDER BY-
ORDER BY:- Disallowed for
PERCENTILE_CONTandPERCENTILE_DISC. - Required for
FIRST_VALUELAST_VALUENTH_VALUELEADandLAG.
- Disallowed for
-
window_frame_clause:- Disallowed for
PERCENTILE_CONTPERCENTILE_DISCLEADandLAG. - Optional for
FIRST_VALUELAST_VALUE, andNTH_VALUEvalue_expressionと同じ型になります。
番号付け関数の概念
番号付け関数は、指定されたウィンドウ内の位置に基づいて、各行に整数値を割り当てます。
RANK()DENSE_RANK()ROW_NUMBER()の例：WITH Numbers AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 10 UNION ALL SELECT 10)SELECT x, RANK() OVER (ORDER BY x ASC) AS rank, DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY x) AS row_numFROM Numbers+---------------------------------------------------+| x | rank | dense_rank | row_num |+---------------------------------------------------+| 1 | 1 | 1 | 1 || 2 | 2 | 2 | 2 || 2 | 2 | 2 | 3 || 5 | 4 | 3 | 4 || 8 | 5 | 4 | 5 || 10 | 6 | 5 | 6 || 10 | 6 | 5 | 7 |+---------------------------------------------------+-
RANK()-
RANK()DENSE_RANK()ROW_NUMBER()WITH Numbers AS (SELECT 1 as x UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 10 UNION ALL SELECT 10)SELECT x, RANK() OVER (ORDER BY x ASC) AS rank, DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY x) AS row_numFROM Numbers+---------------------------------------------------+| x | rank | dense_rank | row_num |+---------------------------------------------------+| 1 | 1 | 1 | 1 || 2 | 2 | 2 | 2 || 2 | 2 | 2 | 3 || 5 | 4 | 3 | 4 || 8 | 5 | 4 | 5 || 10 | 6 | 5 | 6 || 10 | 6 | 5 | 7 |+---------------------------------------------------+-
RANK()- iv id=”: X=5の場合、
rankRANK()は前のウィンドウ順序グループのピアの数だけ増分します。 -
DENSE_RANK():x=5の場合、dense_rankDENSE_RANK()は常に1ずつ増加し、値をスキップすることはありません。 -
ROW_NUMBER():x=5の場合、row_numは4です。
集計分析関数の概念
集計関数は、値のasetに対して計算を実行する関数です。 ほとんどの集計関数は、分析関数で使用できます。 これらの集計関数は集計分析関数と呼ばれます。
集計分析関数では、
OVER句がaggregate関数呼び出しに追加されます。対応する集計関数と同様に、これらの分析関数は、具体的には各行の関連するウィンドウフレーム上でperformaggregationsを実行します。これらの分析関数の結果データ型は、それらの集計関数の対応するデータ型と同じです。分析関数の例
これらの例では、強調表示された項目は現在の行です。 Boldeditemsは、分析に含まれる行です。
例で使用される共通テーブル
後続の集計analyticqueryの例では、
ProduceEmployeesFarmProduce:P>WITH Produce AS (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category UNION ALL SELECT 'orange', 2, 'fruit' UNION ALL SELECT 'cabbage', 9, 'vegetable' UNION ALL SELECT 'apple', 8, 'fruit' UNION ALL SELECT 'leek', 2, 'vegetable' UNION ALL SELECT 'lettuce', 10, 'vegetable')SELECT * FROM Produce+-------------------------------------+| item | category | purchases |+-------------------------------------+| kale | vegetable | 23 || orange | fruit | 2 || cabbage | vegetable | 9 || apple | fruit | 8 || leek | vegetable | 2 || lettuce | vegetable | 10 |+-------------------------------------+従業員テーブル
いくつかの例は、
Employees:WITH Employees AS (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29) UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24) UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23) UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11) UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))SELECT * FROM Employees+-------------------------------------+| name | department | start_date |+-------------------------------------+| Isabella | 2 | 1997-09-28 || Anthony | 1 | 1995-11-29 || Daniel | 2 | 2004-06-24 || Andrew | 1 | 1999-01-23 || Jacob | 1 | 1990-07-11 || Jose | 2 | 2013-03-17 |+-------------------------------------+ファームテーブル
いくつかの例は、
Farmファームテーブル
いくつかの例は、
Farmファームテーブル
いくつかの例は、
Farm:WITH Farm AS (SELECT 'cat' as animal, 23 as population, 'mammal' as category UNION ALL SELECT 'duck', 3, 'bird' UNION ALL SELECT 'dog', 2, 'mammal' UNION ALL SELECT 'goose', 1, 'bird' UNION ALL SELECT 'ox', 2, 'mammal' UNION ALL SELECT 'goat', 2, 'mammal')SELECT * FROM Farm+-------------------------------------+| animal | category | population |+-------------------------------------+| cat | mammal | 23 || duck | bird | 3 || dog | mammal | 2 || goose | bird | 1 || ox | mammal | 2 || goat | mammal | 2 |+-------------------------------------+総計を計算します
これは、
Produceテーブル内のすべての項目の総計を計算します。- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (オレンジ、リンゴ、ニラ、キャベツ、レタス、ケール)=54合計購入
- (orange,apple,leek,cabbage,letas,kale)=54購入合計
SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 54 || leek | 2 | vegetable | 54 || apple | 8 | fruit | 54 || cabbage | 9 | vegetable | 54 || lettuce | 10 | vegetable | 54 || kale | 23 | vegetable | 54 |+-------------------------------------------------------+小計を計算します
これは、
SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 54 || leek | 2 | vegetable | 54 || apple | 8 | fruit | 54 || cabbage | 9 | vegetable | 54 || lettuce | 10 | vegetable | 54 || kale | 23 | vegetable | 54 |+-------------------------------------------------------+小計を計算します
これは、
SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 54 || leek | 2 | vegetable | 54 || apple | 8 | fruit | 54 || cabbage | 9 | vegetable | 54 || lettuce | 10 | vegetable | 54 || kale | 23 | vegetable | 54 |+-------------------------------------------------------+小計を計算します
これは、
SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 54 || leek | 2 | vegetable | 54 || apple | 8 | fruit | 54 || cabbage | 9 | vegetable | 54 || lettuce | 10 | vegetable | 54 || kale | 23 | vegetable | 54 |+-------------------------------------------------------+Produceテーブル。- フルーツ
- (オレンジ、リンゴ)=10合計購入
- (オレンジ、リンゴ)=10合計購入
- 野菜
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
- 合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 10 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 44 || cabbage | 9 | vegetable | 44 || lettuce | 10 | vegetable | 44 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+累積合計を計算
これは、
ProduceORDER BY句を使用して定義されたtheorderに関して計算されます。- フルーツ
- (オレンジ、リンゴ)=2合計購入
- (オレンジ、リンゴ)=10合計購入
- 野菜
- (ニラ、キャベツ、レタス、ケール)=2合計購入
- (ニラ、キャベツ、レタス、ケール)=11合計購入
- (ニラ、キャベツ、レタス、ケール)=21合計購入
- (ニラ、キャベツ、レタス、ケール)=21合計購入
- (ニラ、キャベツ、レタス、ケール)=21合計購入
- (ニラ、キャベツ、レタス、ケール)=21合計購入
- (ニラ、キャベツ、レタス、ケール)=21合計購入
- (ネギ、キャベツ、レタス、ケール)=44合計購入
SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 2 || cabbage | 9 | vegetable | 11 || lettuce | 10 | vegetable | 21 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+これは前の例と同じことを行います。 読みやすくしたい場合を除き、境界として
CURRENT ROWSELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS UNBOUNDED PRECEDING ) AS total_purchasesFROM Produceテーブル内のすべての項目がパーティションに含まれています。 先行する行のみが分析されます。 分析は、パーティション内の現在の行の前に2つのローを開始します。
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=NULL
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=NULL
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=2
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=4
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=12
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=12
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=12
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=12
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=12
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=12
- li>
- (オレンジ、ネギ、リンゴ、キャベツ、レタス、ケール)=21
SELECT item, purchases, category, SUM(purchases) OVER ( ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING ) AS total_purchasesFROM Produce;+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | NULL || leek | 2 | vegetable | NULL || apple | 8 | fruit | 2 || cabbage | 9 | vegetable | 4 || lettuce | 10 | vegetable | 12 || kale | 23 | vegetable | 21 |+-------------------------------------------------------+移動平均を計算
これは、
Produceテーブルの移動平均を計算します。下の境界は現在の行の前の1行です。 上の境界は、現在の行の後の1行です。- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=2平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=4平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=6.3333平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=9平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=6.3333平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=9平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=9平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=9平均購入
- (オレンジ、ニラ、リンゴ、キャベツ、レタス、ケール)=9平均購入
- 、キャベツ、レタス、ケール）=14平均購入
- （オレンジ、ネギ、リンゴ、キャベツ、レタス、ケール）=16。5平均購入
SELECT item, purchases, category, AVG(purchases) OVER ( ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_purchasesFROM Produce+-------------------------------------------------------+| item | purchases | category | avg_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || leek | 2 | vegetable | 4 || apple | 8 | fruit | 6.33333 || cabbage | 9 | vegetable | 9 || lettuce | 10 | vegetable | 14 || kale | 23 | vegetable | 16.5 |+-------------------------------------------------------+範囲内のアイテムの数を計算
この例では、
Farmテーブルで同様の人口を持つ動物の数を取得します。- （ガチョウ、犬、牛、ヤギ、アヒル、猫）=4匹の個体群の範囲は0-2です。
- （ガチョウ、犬、牛、ヤギ、アヒル、猫）=人口の範囲1-3の間の5匹の動物。
- （ガチョウ、犬、牛、ヤギ、アヒル、猫）=人口の範囲1-3の間の5匹の動物。
- （ガチョウ、犬、牛、ヤギ、アヒル、猫）=人口の範囲1-3の間の5匹の動物。
- （ガチョウ、犬、牛、ヤギ、アヒル、猫）=人口の範囲2-4の間の4匹の動物。
- （ガチョウ、犬、牛、ヤギ、アヒル、猫）=1個体群の範囲22-24の間の動物。
SELECT animal, population, category, COUNT(*) OVER ( ORDER BY population RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS similar_populationFROM Farm;+----------------------------------------------------------+| animal | population | category | similar_population |+----------------------------------------------------------+| goose | 1 | bird | 4 || dog | 2 | mammal | 5 || ox | 2 | mammal | 5 || goat | 2 | mammal | 5 || duck | 3 | bird | 4 || cat | 23 | mammal | 1 |+----------------------------------------------------------+各カテゴリで最も人気のあるアイテムを取得
この例では、各カテゴリで最も人気のあるアイテムを取得します。 これは、ウィンドウ内の行が各パーティションでどのようにパーティション化され、順序付けされるかを定義します。
Produceテーブルが参照されています。- 果物
- （オレンジ、リンゴ）=リンゴが最も人気があります
- （オレンジ、リンゴ）=リンゴが最も人気があります
- 野菜
- （ニラ、キャベケールが最も人気があります
- (ネギ、キャベツ、レタス、ケール)=ケールが最も人気があります
SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | kale || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+範囲内の最後の値を取得
この例では、
Producemost_popular列をよく見てください。特定のカテゴリで最も人気のあるアイテムを取得する代わりに、そのカテゴリの特定の範囲で最も人気のあるアイテムを取得します。- 果物
- （オレンジ、リンゴ）=リンゴが最も人気があります
- （オレンジ、リンゴ）=リンゴが最も人気があります
- 野菜
- （ニラ、キャベ最も人気があります
- (ネギ、キャベツ、レタス、ケール)=ケールが最も人気があります
SELECT item, purchases, category, LAST_VALUE(item) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM Produce+----------------------------------------------------+| item | purchases | category | most_popular |+----------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | cabbage || cabbage | 9 | vegetable | lettuce || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+----------------------------------------------------+この例は、前の例と同じ結果を返しますが、
item_windowOVER句で直接定義され、一部は名前付きウィンドウで定義されます。SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases)Compute rank
この例では、開始日に基づいて、部門内の各従業員のランクを計算します。 ウィンドウ仕様は、
OVEREmployeesテーブルが参照されています。p>- department1
- (Jacob,Anthony,Andrew)=ランク1をJacobに割り当て
- (Jacob,Anthony,Andrew)=ランク2をAnthonyに割り当て
- (Jacob,Anthony,Andrew)=ランク3をAndrewに割り当て
- department2
- (Isabella,Daniel,Jose)=ランク1をIsabellaに割り当て
- (Isabella,Daniel,Jose)=ランク1をIsabellaに割り当て
- (Isabella,Daniel,Jose)=ランク1をIsabellaに割り当て
- (Isabella,Daniel,Jose)=ランク1をIsabellaに割り当て
- (Isabella,Daniel,Jose)=ランク1をIsabellaに割り当て
- )=ランク2をダニエルに割り当てる
- (isabella,Daniel,Jose)=ランク3をホセに割り当てる
SELECT name, department, start_date, RANK() OVER (PARTITION BY department ORDER BY start_date) AS rankFROM Employees;+--------------------------------------------+| name | department | start_date | rank |+--------------------------------------------+| Jacob | 1 | 1990-07-11 | 1 || Anthony | 1 | 1995-11-29 | 2 || Andrew | 1 | 1999-01-23 | 3 || Isabella | 2 | 1997-09-28 | 1 || Daniel | 2 | 2004-06-24 | 2 || Jose | 2 | 2013-03-17 | 3 |+--------------------------------------------+ウィンドウフレーム句で名前付きウィンドウを使用する
名前付きウィンドウでロジックの一部を定義することができ、そのうちのいくつかは名前付きウィンドウで定義することができる。 awindowフレーム句。 この論理は結合されます。 ここでは、
Produceテーブルを使用した例を示します。SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)+-------------------------------------------------------+| item | purchases | category | most_popular |+-------------------------------------------------------+| orange | 2 | fruit | apple || apple | 8 | fruit | apple || leek | 2 | vegetable | lettuce || cabbage | 9 | vegetable | kale || lettuce | 10 | vegetable | kale || kale | 23 | vegetable | kale |+-------------------------------------------------------+これらの例でも前の結果を得ることができます。
SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING), item_window AS (c)SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), item_window AS (b)ウィンドウフレーム句が二度定義されているため、次の例ではエラーが生成されます。
SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
- iv id=”: X=5の場合、
-
-
-
- Disallowed for
Leave a Reply