SQLでの実行中の合計は、いくつかの方法で計算できます。 この記事では、結合とウィンドウ関数の2つの方法について説明します。
まず、INNER JOINを使用して実行中の合計を計算する方法を見ていきます。 そうすることで、結合条件についての詳細を学ぶだけでなく、結果を取得してそれを要約して、実行中の合計を取得する方法を確認できます。
“古い学校”スタイルを行う方法を見たら、OVER句を使用してウィンドウ関数を使用して実行中の合計を計算します。 このメソッドは、より新しく、より簡潔に使用できます。
このレッスンのすべての例は、Microsoft SQL Server Management StudioおよびWideWorldImportersデータベースに基づいています。 あなたは私のガイドを使用して、これらの無料のツールを使用して開始することができますSQL Server2016の使用を開始します。
実行合計とは何ですか？
私たちの目標は、TransactionDateが変更されるたびにリセットされる実行合計を計算することです。 TransactionAmountを合計します。 トランザクション日付内の後続の各請求書について、RunningTotalは、以前のInvoiceIDの実行中の合計に現在のTransactionAmountを加えたものと等しくする必要があります。
次の例では、これが実際に表示されます。 請求書3のRunningTotalは、3110.75の以前のRunningTotalに請求書3の取引金額を加えたものです。103.50
内部結合を使用してSQLで実行中の合計を計算します
最初に内部結合を使用して実行中の合計を計算します。 この方法は、パーティションを使用するよりも、実行中の合計を計算する仕組みの多くを明らかにします。 そのため、内部結合を理解し、それらの概念を別のユースケースに適用する別の機会が得られます。
この問題を解決するには三つのステップがあります:
- 実行合計の行の取得
- 内部結合を使用した実行合計の設定の詳細
- データを集計して実行合計を計算します。
始めましょう！
ステップ1–実行合計の行を取得する
実行合計を計算するために、CustomerTransactionsテーブルを照会します。 結果にInvoiceID、TransactionDate、TransactionAmountを含めます。 もちろん、実行中の合計はTransactionAmountから計算されます。
ここでは、基本的なデータを取得するためのクエリです。
SELECT InvoiceID ,TransactionDate ,TransactionAmountFROM Sales.CustomerTransactionsWHERE TransactionTypeID = 1ORDER BY TransactionDate
ここでは、我々が作業することになりますデータです。
本当に、このステップはあなたが基本的な情報を知ることを意図しています。 本当にそれをする必要はありません。 しかし、私は時々データを見るためだけに基本的なクエリをお勧めしたいと思います。 さらに、私が対応する必要がある異常や特別な状況がないことを保証するために。
ステップ2–内部結合を使用して合計を実行するための設定の詳細
このステップでは、実行合計を計算できるように詳細を設定します。 これを行うために、各InvoiceIDに取引金額とすべての取引金額を事前に取得します。
これを行うには、CustomerTransactionsテーブルを自分自身に結合します。
結合条件なしでこれを行うと、トランザクションのすべての組み合わせが取得されますが、これは私たちが望むものではありません。
各テーブルから行の適切な組み合わせを取得するために、2つの結合条件を追加します。 各請求書とそれ以前の請求書（緑色）を取得するもの。
二つ目は、同じ取引日の請求書のみを含めることを保証します（赤)
SELECT T1.InvoiceID ,T2.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,T2.TransactionAmountFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1ORDER BY T1.InvoiceID, T1.TransactionAmount
これがどのように動作するか見てみましょう。
最も理解しやすい条件は、TransactionDateと一致する場所です。 これにより、請求書の一致に共通の取引日が設定されます。 これが唯一の結合であった場合、日付内のすべてのトランザクションの小計を計算します。
実行中の合計を計算したいので、請求書とその前のすべての請求書のTransactionAmountを各InvoiceIDに対して何らかの形で取得する必要があります。 つまり、請求書が合計しようとしている対応する請求書以上の一致するすべての行を返します。
上記の結果を見ると、最初の列にリストされている請求書ごとにそれが表示されます（T1。2番目の列（T2）のInvoiceIDより大きいか等しいです。インボイス）。
これは結合条件T1の結果です。インボイスID>=T2。インボイス-ド-ド-ド-ド
この結合と結合条件の結果は、実行中の合計を計算するための原材料が得られたことです。
最初の列、3番目の列、4番目の列がどのように繰り返されるかに注意してください。 これを使用して、結果を要約して実行中の合計に到達することができます。
ステップ3–行を要約することによって実行中の合計を計算する
手元の詳細な情報を使用して、最後のステップは行を要約することです。 そうすることで、実行中の合計を計算することができます。
要約を実行するために使用するクエリは次のとおりです:
SELECT T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmount ,Sum(T2.TransactionAmount) RunningTotalFROM Sales.CustomerTransactions T1 INNER JOIN Sales.CustomerTransactions T2 ON T1.InvoiceID >= T2.InvoiceID AND T1.TransactionDate = T2.TransactionDateWHERE T1.TransactionTypeID = 1GROUP BY T1.InvoiceID ,T1.TransactionDate ,T1.TransactionAmountORDER BY T1.InvoiceID ,T1.TransactionAmount
T1でグループ化する方法に注意してください。インボイス、T1。とT1。トランザクションマウント。 これらは、ステップ2の詳細データで繰り返された値です。
実行中の合計はT2から派生します。トランザクションマウント。 請求書が表示される前のすべての請求書から、これらの値がTransactionAmountであることを思い出してください。 つまり、表示される請求書はそれ以上です。
これにより、実行中の合計を構築することができます。リスト内の後続の各請求書は、その請求書とその前の請求書からのすべてのTransactionAmountを合計することによって、RunningTotal値を計算しています。
running totalに到達する伝統的な方法を見て、おそらくそれを解決するためにjoinとjoin条件を使用する方法をより深く理解したので、SQL、partitionsの新機能の一つを見て、それらを使用して同じ結果を達成する方法を見てみましょう。
OVER句を使用してSQLで実行中の合計を計算します
非常に強力な文の場合はOVER句。 これにより、操作が影響する結果セット内で行のセットを定義できます。
OFFSETとFETCHと同様に、結果セットから特定の範囲の行を取得することができますが、OVER句を使用すると、特定の列に対して現在の行を基準にした同様の操
OVERを使用すると、指定された行セットの上にウィンドウを定義し、sumなどの関数を適用できます。
あなたが概念を理解するために、これを二つのステップに分割します:
- OVER句を使用してデータを分割します。
ロールしよう。
ステップ1–OVER句を使用したデータの分割
TransactionDate内のすべての請求書の累計を作成したいと言うときは、TransactionDateでデータを分割します。 データを分割するには、over句を使用できます。
次のステートメントの通知では、TransactionAmountを合計し、合計の後にOVER句があります。また、GROUP BY句がないことにも注意してください。 これは驚くべきことですが、通常、SUMなどの集計関数にはGROUP BY句が必要です。
OVER句を使用しているため、SUMはウィンドウ関数と見なされ、OVER句で定義されているすべての行に対して動作します。
使用するウィンドウ関数は次のとおりです。
SUM(TransactionAmount)OVER(PARTITION BY TransactionDate)RunningTotal
これをwindows関数にするのは、OVER句です。 トランザクション日付によってパートパーティションをチェックアウトします。 これは、SUMが同じTransactionDateを持つすべての行で動作することを意味します。 これにより、SUM関数が影響する行のウィンドウが定義されます。
これまでのクエリは次のとおりです。
SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount
Step2–Order BY
を使用したパーティションの順序この時点までにデータをパーティション化し、TransactionDate内のすべてのTransactionAmount値の小計を計算できます。 次のステップは、小計を計算することです。
これを行うには、OVER句内でORDER BYを使用して、window関数の「スコープ」を定義できます。 ORDER BYは、ウィンドウ関数が動作する論理的な順序を指定しました。
ここで使用するウィンドウ関数は次のとおりです:
SUM(TransactionAmount)OVER(PARTITION BY TransactionDate ORDER BY InvoiceID)RunningTotal
このウィンドウ関数と最初のステップの違いは、ORDER BY InvoiceIDです。 これは、パーティション内で処理する論理的な順序を指定します。
論理的な順序による順序なしでは、合計を計算するためにウィンドウの最後になるまで待つことです。 ORDER BYを指定すると、論理的な順序は、ウィンドウ内の以前のTransactionAmount値を含む各行の合計を計算することです。
SELECT InvoiceID ,TransactionDate ,TransactionAmount ,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotalFROM Sales.CustomerTransactions T1WHERE TransactionTypeID = 1ORDER BY InvoiceID ,TransactionAmount
クエリを実行した結果は次のとおりです。
このクエリを実行したとき、内部結合を使用するクエリよりもどれくらい速く実行されたかに気付きましたか？ 私は驚いた。 INNER JOIN操作は、組み合わせや行が大きくなるにつれて多くのリソースを消費することを知っていますが、OVERを使用したソリューションでも同じケースがあ
各クエリのクエリプランを見ることをお勧めします。 これを開始すると、SQLについてかなりのことを学び始めます。