すべてのデータベースユーザーは、テーブル全体を操作し、GROUP BY句で使用される通常の集計 しかし、SQLでウィンドウ関数を使用する人はほとんどいません。 これらは一連の行に対して動作し、各行に対して単一の集計値を返します。
通常の集計関数よりもウィンドウ関数を使用する主な利点は次のとおりです: ウィンドウ関数では、行が単一の出力行にグループ化されることはなく、行は別々のidを保持し、集計された値が各行に追加されます。ウィンドウ関数がどのように機能するかを見て、実際にそれを使用するいくつかの例を見て、物事が明確であることを確認し、SQLと出力がSUM()関数の
いつものように、特にデータベースで新しいことを試している場合は、完全にバックアップされていることを確認してください。
ウィンドウ関数の概要
ウィンドウ関数は行のセットを操作し、行ごとに単一の集計値を返します。 “ウィンドウ”という用語は、関数が動作するデータベース内の行のセットを表します。 OVER()句を使用して、ウィンドウ(関数が動作する行のセット)を定義します。 OVER()句については、以下の記事で詳しく説明します。
Types of Window functions
Syntax
|
1
2
3
4
|
window_function ( expression )
OVER ( )
|
Arguments
window_function
Specify the name of the window function
ALL
ALL is an optional keyword. すべてを含めると、重複した値を含むすべての値がカウントされます。 DISTINCTは、ウィンドウ関数ではサポートされていません
式
関数が操作するターゲット列または式。 つまり、集計値が必要な列の名前です。 たとえば、注文金額を含む列で、受け取った注文の合計を確認できます。
OVER
集計関数のウィンドウ句を指定します。
PARTITION BY partition_list
ウィンドウ関数のウィンドウ（ウィンドウ関数が動作する行のセット）を定義します。 PARTITION BY句の後にpartitionのフィールドまたはフィールドのリストを提供する必要があります。 通常どおり、複数のフィールドをコンマで区切る必要があります。 PARTITION BYが指定されていない場合は、テーブル全体でグループ化が行われ、それに応じて値が集計されます。
ORDER BY order_list
各パーティション内の行をソートします。 ORDER BYが指定されていない場合、ORDER BYはテーブル全体を使用します。
Examples
テーブルを作成し、ダミーレコードを挿入して、さらにクエリを書き込みましょう。 以下のコードを実行します。
集計ウィンドウ関数
SUM()
私たちは皆SUM()集計関数を知っています。 指定されたグループ（都市、州、国など）の指定されたフィールドの合計を行います。 groupが指定されていない場合は、テーブル全体に適用されます。 通常のSUM（）集計関数とwindow SUM（）集計関数の出力がどうなるかを確認します。以下は、通常のSUM()集計関数の例です。 これは、各都市の注文金額を合計します。
結果セットから、通常の集計関数が複数の行を単一の出力行にグループ化し、個々の行がidを失うことがわかります。 /div>
|
1
2
3
|
|
1
2
3
4
|
都市を選択し、合計（order_amount）total_order_amount
から。 都市によるグループ化
|
これはウィンドウ集約関数では発生しません。 行はそのidを保持し、各行の集計値も表示されます。 つまり、各都市のデータを集計し、各都市の合計注文金額の合計を表示します。 ただし、クエリでは、各行がそのidを保持するように、合計注文金額の別の列が挿入されるようになりました。 Grand_totalとマークされた列は、次の例の新しい列です。
AVG()
AVGまたはAverageは、ウィンドウ関数とまったく同じように動作します。
次のクエリは、各都市と各月の平均注文金額を示します（簡単にするために、1か月分のデータのみを使用しました）。
パーティションリストに複数のフィールドを指定することで、複数の平均を指定します。 また、以下のクエリに示すように、MONTH（order_date）のようなリストで式を使用できることにも注意してください。 これまでのように、構文が正しい限り、これらの式を必要に応じて複雑にすることができます！上の画像から、2017年4月のArlington cityでは平均して12,333件の注文を受けていることがわかります。
平均注文金額=合計注文金額/合計注文
= (20,000 + 15,000 + 2,000) / 3た、SUM()&COUNT()関数の組み合わせを使用して平均を計算することもできます。
MIN()
MIN()集計関数は、指定されたグループまたはgroupが指定されていない場合はテーブル全体の最小値を検索します。
たとえば、次のクエリを使用する各都市の最小注文（最小注文）を探しています。
MAX()
MIN()関数が最小値を与えるのと同じように、MAX()関数は、指定された行のグループまたはグループが指定されていない場合 各都市の最大注文（最大注文金額）を見つけてみましょう。
P>
COUNT()関数はレコード/行をカウントします。
DISTINCTはwindow COUNT()関数ではサポートされていませんが、通常のCOUNT()関数ではサポートされていることに注意してください。 DISTINCTは、指定されたフィールドの個別の値を見つけるのに役立ちます。 たとえば、2017年4月に何人の顧客が注文したかを確認したい場合、すべての顧客を直接数えることはできません。 同じ顧客が同じ月に複数の注文を行った可能性があります。 COUNT(customer_name)は重複をカウントするため、誤った結果を返します。 COUNT(DISTINCT customer_name)は、各一意の顧客を1回だけカウントするため、正しい結果を提供します。
通常のCOUNT()関数で有効:/div>
|
1
2
3
|
|
1
2
3
4
5
|
都市を選択し、count(distinct customer_name)number_of_customers
から。 /div>
都市によるグループ化
|
ウィンドウCOUNT()関数で無効です。
ウィンドウ関数を使用した上記のクエリは、以下のエラーを与えます。ここで、window COUNT()関数を使用して、各都市で受信された注文の合計を見つけましょう。
ウィンドウ関数のランキング
ウィンドウ集計関数が指定されたフィールドの値を集計するのと同じように、ランキング関数は指定されたフィールドの値をランク付けし、それらのランクに応じて分類します。
ランキング関数の最も一般的な使用法は、特定の値に基づいてtop(N)レコードを検索することです。 たとえば、トップ10の最高賃金の従業員、トップ10ランクの学生、トップ50の最大の注文などです。 RANK()、DENSE_RANK()、ROW_NUMBER()、NTILE()
それらを一つずつ説明しましょう。
RANK()
rank()関数は、給与、注文金額などの指定された値に基づいて各レコードに一意のランクを与えるために使用されます。
二つのレコードが同じ値を持つ場合、RANK()関数は次のランクをスキップして両方のレコードに同じランクを割り当てます。 つまり、ランク2に同じ値が2つある場合、同じランク2を両方のレコードに割り当て、ランク3をスキップしてランク4を次のレコードに割り当てます。
各注文を注文金額でランク付けしましょう。/div>
|
1
2
3
|
|
1
2
3
4
5
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
上の画像から、同じランク（3）が2つの同一のレコード（各注文量が15,000）に割り当4）と次のレコードにランク5を割り当てます。
DENSE_RANK()
dense_RANK()関数は、ランクをスキップしない点を除いて、RANK()関数と同じです。 これは、2つの同一のレコードが見つかった場合、DENSE_RANK()は両方のレコードに同じランクを割り当てますが、スキップしないで次のランクをスキップすることを意味します。これが実際にどのように機能するかを見てみましょう。
上ではっきりと見ることができるように、同じランクが2つの同一のレコード（それぞれが同じ注文金額を持つ）に与えられ、次のランク番号がランク値をスキップせずに次のレコードに与えられます。
ROW_NUMBER()
名前は一目瞭然です。 これらの関数は、各レコードに一意の行番号を割り当てます。
PARTITION BYが指定されている場合、行番号はパーティションごとにリセットされます。 ROW_NUMBER()がPARTITION BYなしで、PARTITION BYを使用してどのように機能するかを見てみましょう。P>
ROW_NUMBER()によるパーティションなし
ROW_NUMBER()によるパーティション付き
cityでパーティションを行っていることに注意してください。 これは、行番号が都市ごとにリセットされることを意味し、再び1で再起動します。 ただし、行の順序は注文金額によって決定されるため、任意の都市で最大の注文金額が最初の行になり、行番号1が割り当てられます。
NTILE()
NTILE()は非常に便利なウィンドウ関数です。 これは、特定の行がどの百分位数（または四分位数、またはその他の細分）に該当するかを識別するのに役立ちます。 これは、100行があり、指定された値フィールドに基づいて4つの四分位数を作成する場合、簡単に行うことができ、各四分位数に分類される行の数を確認で
例を見てみましょう。 以下のクエリでは、注文金額に基づいて4つの四分位数を作成するように指定しました。 次に、各四分位数にどれだけの注文が入るかを見たいと思います。
NTILEは、次の式に基づいてタイルを作成します。
NTILEは、次の式に基づいてタイルを作成します。
NTILEは、次の式に基づいてタイルを作成します。
NTILEは、:
各タイルの行数=結果セットの行数/指定されたタイルの数
ここでは、合計10行があり、4つのタイルがクエリで指定されているため、各タイルの行数は2.5（10/4）になります。 行の数は10進数ではなく整数でなければなりません。 SQLエンジンは、最初の2つのグループに3行、残りの2つのグループに2行を割り当てます。
値ウィンドウ関数
LAG()とLEAD()
LEAD()とLAG()関数は非常に強力ですが、説明するのが複雑になる可能性があります。 これは以下の入門記事であるため、それらを使用する方法を説明するための非常に簡単な例を見ています。
これは非常に簡単な例を見ています。
LAG関数を使用すると、SQL結合を使用せずに、同じ結果セット内の前の行のデータにアクセスできます。 以下の例では、LAG関数を使用して、以前の注文日を見つけました。
LAG()関数を使用して前の注文日を検索するスクリプト：
LEAD関数は、SQL結合を使用せずに同じ結果セット内の次の行のデータにアクセ 次の注文日を見つけたLEAD関数を使用して、以下の例で見ることができます。
LEAD()関数を使用して次の注文日を検索するスクリプト：
FIRST_VALUE()とLAST_VALUE()
これらの関数は、PARTITION BYが指定されていない場合、パーティションま
既存のデータセットから各都市の最初と最後の順序を見つけましょう。 注ORDER BY句は、FIRST_VALUE()およびLAST_VALUE()関数に必須です
上の画像から、最初の注文は2017-04-02に受信され、最後の注文は2017-04-11に受信されたことがはっきりとわかりますアーリントン市とそれは他の都市でも同じように動作します。
便利なリンク
- バックアップタイプ&SQLデータベースの戦略
- Over句に関するTechNetの記事
- DENSE_RANKに関するMSDNの記事
Benの他の偉大な記事
SQL Serverがデッドロックの犠牲者を選択する方法
ウィンドウ関数を使用する方法p>
- 著者
- 最近の投稿
ベン*リチャードソンによるすべての投稿を表示
- Power BI:Waterfall Charts and Combined Visuals-January19,2021
- Power BI:Waterfall Charts and Combined Visuals-January19,2021
- Power BI: Power BI：SQL ServerおよびMySQLからデータをインポートする-2021年1月12日