メインコンテンツへスキップ
見出し画像

第12回:相関サブクエリを使った累計和

    1.今回のテーマ

    今回のテーマは、相関サブクエリを使って累計和を求めることです。

    SQLでSUM関数を使用すると、複数行の数値を合計できます。

    例えば、映画の上映時間が次のようになっている場合です。

    • 1本目:81分

    • 2本目:95分

    • 3本目:93分

    通常のSUM関数であれば、これらの合計である269分を取得できます。

    では、次のように、各行の時点までの合計を表示したい場合はどうでしょうか。

    • 1本目:81分

    • 2本目:176分(81+95)

    • 3本目:269分(81+95+93)

    このように、データが進むにつれて、それまでの数値を積み上げた合計を「累計和」と呼びます。

    累計和は、実務でも非常によく使われる考え方です。

    例えば、

    • 商品の入出庫履歴から、その時点の在庫残高を求める

    • 銀行口座などの入出金履歴から、その時点の残高を求める

    • 日々の売上実績から、当月の累計売上を求める

    • 生産実績から、その時点までの累計生産数を求める

    といった処理に活用できます。

    今回は、相関サブクエリとSUM関数を組み合わせて、この累計和を求めるSQLを練習します。

    特に重要なのは、現在行を基準として、集計対象となる行の範囲を決めるという考え方です。

    今回も、SQLを「息をするように書ける」ことを目指して練習していきましょう。

    2.練習に使用するSQLBolt

    今回の練習には、無料でSQLを実行できる学習サイト「SQLBolt」を使用します。

    SQLBolt Lesson 1:SELECT queries 101

    今回使用するのは、映画情報を管理する movies テーブルです。

    映画ごとに公開年と上映時間が登録されているため、公開年を基準に上映時間の累計を求める練習に利用できます。

    なお、SQLBoltのLesson 1自体が「相関サブクエリを使った累計和」を学ぶためのレッスンというわけではありません。

    今回のテーマを練習するうえで、テーブル構成やデータが使いやすいため、おすすめしています。

    SQLBoltの演習画面を開き、今回紹介するSQLを実際に入力して試してみてください。

    3.使用するテーブル

    今回使用するのは、movies テーブルです。

    映画のタイトル、監督、公開年、上映時間などを管理しています。

    主な列は以下のとおりです。

    • id:映画ID

    • title:映画タイトル

    • director:監督名

    • year:公開年

    • length_minutes:上映時間(分)

    画像

    今回注目するのは、次の2つの列です。

    year(公開年)

    累計の集計範囲を決めるために使用します。

    length_minutes(上映時間)

    累計する数値として使用します。

    今回のデータでは、公開年が古い映画から順番に、上映時間を積み上げていきます。

    4.今回の練習問題

    次の条件を満たすSQLを作成してください。

    練習問題

    moviesテーブルを使用して、各映画の公開年までに公開された映画の上映時間を合計し、累計和を表示してください。

    その際、以下の条件を満たすものとします。

    1. moviesテーブルのすべての列を表示する。

    2. 現在行の映画の公開年を基準とする。

    3. 現在行の公開年以前の映画をすべて集計対象にする。

    4. 集計対象となった映画の上映時間をSUM関数で合計する。

    5. 計算結果を RuikeiSum という列名で表示する。

    例えば、2003年公開のFinding Nemoの場合、2003年までに公開された映画の上映時間をすべて合計します。

    つまり、現在行の上映時間だけでなく、それ以前の映画の上映時間も含めて合計するということです。

    どのようなSQLになるか、まずは考えてみてください。

    5.解答SQL

    今回の解答SQLはこちらです。

    select a.*,
        (select sum(b.length_minutes)
        from movies b
        where b.year<=a.year
        ) as RuikeiSum
    from movies a

    今回のSQLは、比較的短い構文で記述できます。

    しかし、この短いSQLの中に、相関サブクエリの重要な考え方が含まれています。

    特に注目してほしいのは、次の条件です。

    where b.year<=a.year

    この条件が、累計和を求めるための中心となります。

    実行結果

    画像

    実行結果を見ると、RuikeiSum 列に上映時間の累計が表示されていることが分かります。

    最初のToy Storyでは81分ですが、次のA Bug's Lifeでは176分、Toy Story 2では269分と、公開年が進むにつれて累計が増えていきます。

    最後のUpでは、今回のデータに登録されている映画10本分の上映時間の合計である1021分になります。

    それでは、SQLの仕組みを詳しく見ていきましょう。

    6.SQLの考え方を詳しく解説

    ① メインクエリで現在行の映画を取得する

    まず、SQLの一番外側を確認しましょう。

    select a.*
    from movies a

    ここでは、moviesテーブルに a という別名を付けています。

    a.* は、moviesテーブルのすべての列を取得するという意味です。

    今回のSQLでは、この a が累計計算の基準となる現在行を表します。

    例えば、Finding Nemoの行を処理している場合、次のようになります。

    • a.title:Finding Nemo

    • a.year:2003

    • a.length_minutes:107

    この現在行の公開年を基準にして、累計の集計範囲を決めます。

    ② サブクエリで上映時間を合計する

    次に、サブクエリを確認しましょう。

    select sum(b.length_minutes)
    from movies b
    where b.year<=a.year

    ここでは、moviesテーブルに b という別名を付けています。

    そして、SUM関数を使用して上映時間を合計しています。

    sum(b.length_minutes)

    SUM関数は、指定した列の数値を合計する集約関数です。

    ただし、今回のポイントはSUM関数そのものではありません。

    重要なのは、どの行をSUM関数の集計対象にするかという部分です。

    その集計範囲を決めているのが、次のWHERE句です。

    where b.year<=a.year

    ③ WHERE句で現在行までのデータに限定する

    今回のSQLで最も重要な部分です。

    where b.year<=a.year

    この条件を分解して考えてみましょう。

    b.year

    サブクエリで参照しているmoviesテーブルの公開年です。

    a.year

    メインクエリの現在行の公開年です。

    つまり、この条件は、

    「サブクエリ側の公開年が、現在行の公開年以下であること」

    を意味しています。

    ここで使用している <= は、「以下」を表す比較演算子です。

    現在行と同じ公開年も集計対象に含めます。

    例えば、現在行がFinding Nemo(2003年)の場合、条件は次のように考えられます。

    where b.year<=2003

    つまり、2003年以前に公開された映画がすべて集計対象になります。

    このように、メインクエリの値をサブクエリ内で参照する書き方を、相関サブクエリと呼びます。

    ④ Finding Nemoを例に累計を計算する

    それでは、具体的な計算を確認しましょう。

    現在行がFinding Nemoの場合、公開年は2003年です。

    そのため、サブクエリは概念的には次の条件で集計します。

    select sum(b.length_minutes)
    from movies b
    where b.year<=2003

    集計対象になるのは、2003年以前に公開された5本の映画です。

    • Toy Story:81分

    • A Bug's Life:95分

    • Toy Story 2:93分

    • Monsters, Inc.:92分

    • Finding Nemo:107分

    これらを合計すると、

    81+95+93+92+107=468

    となります。

    したがって、Finding Nemoの RuikeiSum には468が表示されます。

    ⑤ 次の映画では集計対象がさらに広がる

    次に、2004年公開のThe Incrediblesを考えてみましょう。

    現在行の公開年は2004年なので、条件は次のようになります。

    where b.year<=2004

    すると、2003年までの映画に加えて、2004年公開のThe Incredibles(116分)も集計対象になります。

    したがって、

    468+116=584

    となります。

    この584が、The Incrediblesの RuikeiSum に表示されます。

    つまり、現在行の公開年が進むにつれて、SUM関数で集計する範囲が広がっていくということです。

    これが、今回のSQLで累計和を求められる仕組みです。

    7.今回の最大のポイント:集計対象の集合が広がっていく

    今回のSQLを理解するうえで、ぜひ意識してほしい考え方があります。

    それは、現在行に応じて、集計対象となるデータの集合が広がっていくということです。

    例えば、公開年ごとの集計対象を考えると、次のようになります。

    1995年の場合

    1995年以前の映画が対象です。

    • Toy Story

    累計:81分

    1998年の場合

    1998年以前の映画が対象です。

    • Toy Story

    • A Bug's Life

    累計:176分

    1999年の場合

    1999年以前の映画が対象です。

    • Toy Story

    • A Bug's Life

    • Toy Story 2

    累計:269分

    2001年の場合

    2001年以前の映画が対象です。

    ここまでの4本が集計対象となり、累計は361分になります。

    2003年の場合

    2003年以前の5本が集計対象となり、累計は468分になります。

    このように、現在行の公開年が進むたびに、集計対象となる集合が広がっていきます。

    イメージとしては、

    1995年までの集合

    ↓

    1998年までの集合

    ↓

    1999年までの集合

    ↓

    2001年までの集合

    ↓

    2003年までの集合

    というように、以前の集合を含みながら、対象範囲が拡大していく形です。

    数学的には、集合が段階的に拡大していくような構造として捉えることもできます。

    ただし、今回のSQLは、再帰SQLを実行しているわけではありません。

    各行について、相関サブクエリの条件に一致するデータを集計しているという仕組みです。

    この違いは押さえておきましょう。

    今回の重要なポイントは、SUM関数の使い方だけでなく、WHERE句によって集計対象の集合をどのように作るかという部分にあります。

    8.実務ではどのように活用できるか

    今回の累計和は、実務でも非常に活用範囲の広いSQLです。

    特に、履歴データを扱うシステムでは、各時点までの累計を表示したい場面がよくあります。

    例① 入出庫履歴(受払履歴)から在庫残高を求める

    製造業や物流業では、商品の入庫・出庫を履歴として管理することがあります。

    例えば、ある商品の入出庫履歴が次のようになっているとします。

    • 4月1日:100個入庫

    • 4月3日:30個出庫

    • 4月5日:50個入庫

    • 4月8日:20個出庫

    入庫をプラス、出庫をマイナスとして扱うと、次のようになります。

    日付入出庫数量在庫残高4月1日+1001004月3日-30704月5日+501204月8日-20100

    この在庫残高は、各履歴行の時点までの入出庫数量を合計することで求められます。

    今回のSQLと同じ考え方です。

    例えば、次のような条件を使用します。

    where b.商品コード=a.商品コード
      and b.処理日時<=a.処理日時

    これにより、同じ商品の履歴について、現在行の処理日時以前のデータを集計対象にできます。

    実際の業務システムでは、Accessの受払履歴画面などで、このような累計計算の考え方を利用できます。

    ただし、同じ処理日時に複数の履歴が存在する場合は、履歴IDなどを使って順序を明確にする必要があります。

    また、期首在庫がある場合は、その数量も加味する必要があります。

    例② 入出金履歴から口座残高を求める

    もう1つの代表的な活用例が、入出金履歴です。

    例えば、次のような取引があったとします。

    • 4月1日:50,000円入金

    • 4月2日:12,000円出金

    • 4月5日:30,000円入金

    • 4月7日:8,000円出金

    入金をプラス、出金をマイナスとして扱うと、次のようになります。

    日付入出金額残高4月1日+50,00050,0004月2日-12,00038,0004月5日+30,00068,0004月7日-8,00060,000

    この残高も、各取引時点までの入出金額を合計することで求められます。

    例えば、次のような考え方です。

    select a.*,
        (select sum(b.入出金額)
         from 入出金履歴 b
         where b.口座番号=a.口座番号
           and b.取引日時<=a.取引日時
        ) as 残高
    from 入出金履歴 a

    このSQLは、今回のmoviesテーブルを使用した練習SQLと、基本的な構造が同じです。

    違うのは、集計対象となるテーブルや列です。

    • moviesテーブルでは上映時間を累計する

    • 入出金履歴では入出金額を累計する

    また、口座番号を条件に追加することで、同じ口座の取引だけを集計対象にしています。

    なお、同じ日時に複数の取引がある場合は、取引番号などによって順序を明確にする必要があります。期首残高がある場合は、それも加味します。

    例③ 売上実績の累計

    日々の売上実績を管理している場合にも応用できます。

    例えば、月初から現在日までの売上を累計することで、当月の累計売上を表示できます。

    この場合は、現在行の日付以前という条件に加えて、同じ年月のデータに限定する条件を設定します。

    これにより、月が変わったときに累計をリセットするような処理も考えられます。

    実務で意識したいこと

    今回のSQLは、累計和の基本的な考え方を理解するための練習です。

    実務では、次のような点も重要になります。

    • 商品コードや口座番号など、同じ対象のデータだけを集計する

    • 日時が同じデータについて、処理順序を明確にする

    • 期首在庫や期首残高を加味する

    • データ件数が多い場合は、処理速度にも注意する

    また、データベースによっては、ウィンドウ関数を使って累計和を求める方法もあります。

    ただし、今回はまず、相関サブクエリを使って集計対象の範囲を制御する考え方を身につけることを目標とします。

    この考え方を理解しておくと、ほかのSQLの組み立てにも応用できます。

    9.「息をするように書ける」ための練習ポイント

    今回のSQLも、いきなり全体を暗記するのではなく、段階的に組み立てる練習がおすすめです。

    ステップ1:メインクエリを書く

    まずは、moviesテーブルのすべての列を取得します。

    select a.*
    from movies a

    ここでは、累計計算の基準となるテーブルに a という別名を付けます。

    ステップ2:SUM関数で上映時間の合計を求める

    次に、上映時間を合計するSQLを書いてみましょう。

    select sum(b.length_minutes)
    from movies b

    このSQLでは、moviesテーブルに登録されているすべての映画の上映時間が合計されます。

    ただし、これでは各行の時点までの累計にはなりません。

    ステップ3:現在行以前のデータに限定する

    次に、SUM関数を相関サブクエリとして組み込み、現在行の公開年以前の映画に限定します。

    select a.*,
        (select sum(b.length_minutes)
         from movies b
         where b.year<=a.year
        ) as RuikeiSum
    from movies a

    これで完成です。

    今回、特に繰り返し練習してほしいのは、次の部分です。

    where b.year<=a.year

    ここを見たときに、

    「現在行の公開年までのデータをすべて集計するための条件だな」

    と自然に理解できるようになることが目標です。

    ステップ4:条件を変えてみる

    さらに理解を深めるために、WHERE句の比較演算子を変更してみましょう。

    例えば、次のように変更するとどうなるでしょうか。

    where b.year<a.year

    <= から < に変更しています。

    この場合、現在行と同じ公開年の映画は集計対象に含まれません。

    つまり、現在行の公開年より前の映画だけを合計することになります。

    今回のデータでは、最初のToy Storyより前の映画が存在しないため、Toy Storyの集計結果はNULLになります。

    このように、比較演算子を少し変更するだけでも、集計対象が変わります。

    累計和を理解するうえでは、この違いも重要です。

    ステップ5:自分の業務データに置き換えて考える

    最後に、moviesテーブルの代わりに、自分の業務で使用しているテーブルを当てはめてみましょう。

    例えば、

    • 公開年 → 処理日時

    • 上映時間 → 入出庫数量

    • 映画 → 入出庫履歴

    と置き換えることで、在庫残高を求めるSQLの考え方につながります。

    同じように、

    • 公開年 → 取引日時

    • 上映時間 → 入出金額

    • 映画 → 入出金履歴

    と置き換えれば、口座残高を求めるSQLの考え方になります。

    「何を累計するのか」「どの行までを集計対象にするのか」

    この2つを考えられるようになると、今回のSQLをさまざまな業務に応用できます。

    10.まとめ

    今回は、相関サブクエリを使って累計和を求めるSQLを練習しました。

    今回のポイントを振り返ります。

    • SUM関数を使うことで、複数行の数値を合計できる。

    • 相関サブクエリでは、メインクエリの現在行の値を参照できる。

    • WHERE b.year<=a.year によって、現在行の公開年以前のデータを集計対象にできる。

    • 現在行の公開年が進むにつれて、集計対象の集合が広がっていく。

    • 集計範囲を適切に設定することで、各行の時点までの累計和を求められる。

    • 入出庫履歴の在庫残高や、入出金履歴の口座残高など、実務にも応用できる。

    今回のSQLは短いですが、実務で役立つ重要な考え方が含まれています。

    特に、相関サブクエリのWHERE句によって、現在行に応じた集計対象を作るという部分は、ぜひ繰り返し練習して身につけてください。

    SQLを「息をするように書ける」ようになるためには、構文を暗記するだけでなく、やりたい処理から自然に条件を組み立てられることが重要です。

    今回の累計和も、ぜひ自分のSQLの引き出しに加えていきましょう。


    SQLステップアップ講座|講座のご案内・記事一覧

    前回の記事

    第11回:サブクエリを組み合わせた直近データとの差分比較

    次回の記事

    第13回:集合的な全称条件を扱うSQL

    次回は、「すべての条件を満たす」といった集合的な判定をSQLで表現する方法を練習します。

     
     
    SQL初心者から、実務で使える中級者へ。SQLBoltを活用した16テーマの実践トレーニングと、製造業の業務データを題材にしたオリジナルSQL演習問題集(全20問)を公開中。SQLを「息をするように書ける」力を身につけることを目指します。

    あなたへのおすすめ