ここから始めますか？このレッスンは、SQLをデータ分析に使用するための完全なチュートリアルの一部です。冒頭を確認してください。
このレッスンでは、以下について説明します。
- ウィンドウ関数の概要
- 基本的なウィンドウ構文
- 通常の容疑者：SUM、COUNT、およびAVG
- ROW_NUMBER（）
- RANK（）およびDENSE_RANK（）
- NTILE
- LAGとLEAD
- ウィンドウエイリアスの定義
- 高度なウィンドウ手法
このレッスンでは、ワシントンDCのCapital BikeshareProgramからのデータを使用します。彼らのウェブサイト上の詳細な旅行レベルの履歴データ。データは2014年2月にダウンロードされましたが、2012年の第1四半期に収集されたデータに限定されています。各行は1回の乗車を表します。 rider_typeを除いて、ほとんどのフィールドは自明です。「登録済み」はライドシェアプログラムの月額メンバーシップを示し、「カジュアル」はライダーが3日間のパスを購入したことを示します。 start_timeフィールドとend_timeフィールドは、SQLの日付形式に合わせて元のフォームからクリーンアップされ、タイムスタンプとしてこのテーブルに保存されます。
ウィンドウ関数の概要
PostgreSQLのドキュメントは、ウィンドウ関数の概念を紹介する優れた機能を備えています。
ウィンドウ関数は、現在の行に何らかの形で関連するテーブル行のセット全体で計算を実行します。これは、集計関数で実行できる計算のタイプに相当します。ただし、通常の集計関数とは異なり、ウィンドウの使用関数によって行が単一の出力行にグループ化されることはありません—行は個別のIDを保持します。舞台裏では、ウィンドウ関数はクエリ結果の現在の行以外にもアクセスできます。
これの最も実用的な例は現在の合計です：
上記のクエリが集計を作成することがわかります（running_total）GROUP BYを使用しません。構文を分解して、どのように機能するかを見てみましょう。
基本的なウィンドウ構文
上記の集計の最初の部分SUM(duration_seconds) 、他の集計とよく似ています。OVERを追加すると、ウィンドウ関数として指定されます。上記の集計は、「duration_seconds結果セット全体をstart_timeの順に並べます。 “
ウィンドウをデータセット全体から内の個々のグループに絞り込みたい場合データセットの場合、PARTITION BYを使用してこれを行うことができます。
上記のクエリは、start_terminalでクエリをグループ化して並べ替えます。 。 start_terminalの各値内で、start_timeの順に並べられ、現在の行と
。 start_terminalの値が変わるまで下にスクロールすると、running_totalが最初からやり直しになっていることがわかります。これは、PARTITION BYを使用してグループ化すると発生します。それでもORDER BYに困惑している場合は、指定された列で並べ替えるだけです。 （s）ORDER BY句と同じ方法ですが、すべてのパーティションを個別に扱う点が異なります。また、現在の合計も作成されます。ORDER BYがない場合、各値は、それぞれのivid内のすべてのduration_seconds値の合計になります。 = “8ba480cb82″>
。 ORDER BYを使用せずに上記のクエリを実行して、アイデアを見つけてください。
ORDERおよびPARTITIONは、「ウィンドウ」と呼ばれるもの、つまり計算が行われるデータの順序付けられたサブセットを定義します。
注：ウィンドウ関数と標準の集計を同じもので使用することはできません。クエリ。具体的には、GROUP BY句にウィンドウ関数を含めることはできません。
練習問題
上記のクエリ例のクエリ修正を記述して、各start_terminalからライダーが発生した合計時間のパーセンテージとして各ライドの期間を表示します
試してみてください回答を参照してください
通常の容疑者：SUM、COUNT、およびAVG
ウィンドウ関数を使用する場合、通常の状況で使用するのと同じ集計を適用できます— SUM、COUNT、およびAVG。これらを理解する最も簡単な方法は、いくつかの追加機能を使用して前の例を再実行することです。作成
または、ORDER BYと同じ関数を作成します。
前の2つのクエリをモードに接続して実行してください。この次の練習問題は例と非常に似ているので、最初から始めるのではなく、上記のコードを変更してみてください。
練習問題
自転車に乗る時間の現在の合計を示すクエリを記述します（最後の例）、ただしend_terminalでグループ化され、乗車時間は降順で並べ替えられています。
試してみる答えを見る
ROW_NUMBER（）
ROW_NUMBER()は、そのように聞こえます—指定された行の番号を表示します。開始は1で、ウィンドウステートメントのORDER BY部分に従って行に番号を付けます。 ROW_NUMBER()では、括弧内に変数を指定する必要はありません。
PARTITION BY句を使用すると、次のことが可能になります。各パーティションで再び1を数え始めます。次のクエリは、各端末のカウントを最初からやり直します：
RANK（）およびDENSE_RANK（）
RANK()はROW_NUMBER()とは少し異なります。たとえば、start_timeで注文した場合、一部の端末で2つの同じ開始時刻の乗り物がある場合があります。この場合、それらには同じランクが与えられますが、ROW_NUMBER()には異なる番号が与えられます。次のクエリでは、start_terminal 31000の4番目と5番目の観測値に気づきました。どちらもランク4が与えられ、次の結果はランク6を受け取ります。
アプリケーションによっては、RANK()の代わりにDENSE_RANK()を使用することもできます。 3つのエントリが同じ値を持つ状況を想像してみてください。いずれかのコマンドを使用すると、すべて同じランクになります。この例のために、「2」としましょう。 2つのコマンドが次の結果を異なる方法で評価する方法は次のとおりです。
-
RANK()は、同じ行にランク2を与え、ランク3をスキップします。 4なので、次の結果は5になります。 -
DENSE_RANK()でも、同じ行すべてにランク2が与えられますが、次の行は3になります。ランクはスキップされます。
練習問題
5つの最長ライドを示すクエリを作成します各開始ターミナルから、ターミナル順に、各ターミナル内の最長から最短の乗車。2012年1月8日より前に発生した乗車に制限します。
試してみてください回答を参照してください
NTILE
ウィンドウ関数を使用して、特定の行がどの百分位数（または四分位数、またはその他の下位区分）に該当するかを識別できます。構文はNTILE(*# of buckets*)。この場合、ORDER BYは、四分位数（または任意の数の「タイル数」を決定するために使用する列を決定します。指定）。例：
上記のクエリの結果を見ると、percentile列が期待どおりに正確に計算されていないことがわかります。 2つのレコードがあり、パーセンタイルを測定している場合、1つのレコードが1番目のパーセンタイルを定義し、もう1つのレコードが100番目のパーセンタイルを定義することを期待します。 NTILE関数を使用すると、実際に表示されるのは、1パーセンタイルに1つのレコード、2パーセンタイルに1つのレコードです。これは、 31000- percentile列は数値ランキングのように見えます。start_terminal 31007まで下にスクロールすると、 start_terminalには100を超えるレコードがあるため、パーセンタイルが適切に計算されていることがわかります。非常に小さいウィンドウで作業している場合は、これを念頭に置いて、四分位または同様に小さいウィンドウの使用を検討してください。バンド。
練習問題
旅行の期間とパーセンタイルのみを表示するクエリを作成します。その期間は短くなります（データセット全体で、端末で分割されません）。
試してみる答えを見る
LAGとLEAD
特に、行を前後の行と比較すると便利なことがよくあります。 「意味のある順序でデータを取得した場合。LAGまたはLEADを使用して、他の値から値を取得する列を作成できます。行-実行する必要があるのは、プルする列と、プルを実行する行数を入力することだけです。 LAGは前の行からプルし、LEADは次の行からプルします。
これは、計算する場合に特に便利です。行間の違い：
difference列の最初の行は、プルする前の行がないためnullです。同様に、LEADを使用すると、データセットの最後にnullが作成されます。結果を少しわかりやすくしたい場合は、外部クエリでラップしてnullを削除できます。
ウィンドウエイリアスの定義
書き込みを計画している場合同じウィンドウを使用して、同じクエリで複数のウィンドウ関数を使用して、エイリアスを作成できます。上記のNTILEの例を見てください。
これは次のように書き直すことができます：
WINDOW 句を含める場合は、常にWHERE句の後に配置する必要があります。
高度なウィンドウ処理手法
ウィンドウの完全なリストを確認できます。 PostgresドキュメントのPostgres（モードが使用する構文）の関数。 「接続されたデータベースでウィンドウ関数を使用している場合は、システムに適した構文ガイドを参照する必要があります。