SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

達人に学ぶSQL

相関サブクエリで行と行を比較する

集合指向言語としてのSQL:その3


  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード サンプルコード (2.0 KB)

移動累計と移動平均

 累計、または累積和とは、経時的に記録された数値を、ある時点まで足しあげたもののことです。例えば、銀行口座の入出金を記録するテーブルから、累計を求めることを考えます。

Accounts
処理日(prc_date) 処理金額(prc_amt)
2006/10/26 12,000
2006/10/28 2,500
2006/10/31 -15,000
2006/11/03 34,000
2006/11/04 -5,000
2006/11/06 7,200
2006/11/11 11,000

 正数の日は入金、負数の日は出金を意味します。すると各処理日までの処理金額の累計を求めるとは、その時点での口座残高を求めるということです。これを求めるには、OLAP関数を使う方法もありますが、まだ実装に依存します。SQL-92では次のように書きます。

累計を求める:ノイマン型再帰集合の利用
SELECT prc_date, A1.prc_amt,
      (SELECT SUM(prc_amt)
         FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt
  FROM Accounts A1
 ORDER BY prc_date;
結果
prc_date    prc_amt    onhand_amt
----------  ---------  ------------
2006/10/26   12,000    12,000   -- 12,000
2006/10/28    2,500    14,500   -- 12,000 + 2,500
2006/10/31  -15,000      -500   -- 12,000 + 2,500 + (-15,000)
2006/11/03   34,000    33,500   -- 12,000 + 2,500 + (-15,000) + 34,000
2006/11/04   -5,000    28,500   -- 以下同様
2006/11/06    7,200    35,700   --    :
2006/11/11   11,000    46,700   --    :

 説明なしでいきなり答えを書いてしまいしたが、このクエリ、どこかで見た覚えがないでしょうか? 実はこれ、『自己結合の使い方』の回で取り上げたランキングの算出方法とまったく同型のクエリなのです。ただ、COUNTSUMに置き換えただけです。累計もまた、ノイマン型の再帰集合を利用して求められるわけです。気付きました?

 さて、ここまでは復習。本題はここからです。今、累計ということで、特に期間の指定なしに1番古いデータから足しこみました。今度は、「処理3回単位の累計」、つまり移動累計を求める方法を考えてみましょう。「移動」という語が表すように、累計対象を3行に限定して、1行ずつずらしていきます。

 

 考え方としては、さっきの累計を求めるクエリの条件に、「A2の処理日とA1の処理日の間に含まれるレコードが3行以内である」という条件を追加します。これは、次のようにスカラ・サブクエリで行数のカウントを取ることで実現できます。

移動累計を求める その1:3行に満たない期間も出力する
SELECT prc_date, A1.prc_amt,
      (SELECT SUM(prc_amt)
         FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date
          AND (SELECT COUNT(*)
                 FROM Accounts A3
                WHERE A3.prc_date 
                  BETWEEN A2.prc_date AND A1.prc_date  ) <= 3 ) 
               AS mvg_sum
  FROM Accounts A1
 ORDER BY prc_date;
結果
prc_date    prc_amt    mvg_sum
----------  ---------  ----------
2006/10/26   12,000    12,000      --12,000
2006/10/28    2,500    14,500      --12,000 + 2,500
2006/10/31  -15,000      -500      --12,000 + 2,500 + (-15,000)
2006/11/03   34,000    21,500      --2,500 + (-15,000) + 34,000
2006/11/04   -5,000    14,000      --以下同様
2006/11/06    7,200    36,200      --  :
2006/11/11  11,000     13,200      --  :

 A3.prc_dateが始点(A2.prc_date)と終点(A1.prc_date)の間を動くと考えてください。「<= 3」の数を変えることで、4行単位でも5行単位でも、好きな幅で集計対象の期間を移動させることができます。このクエリでは最初の2行についても、一応使える限りでのデータを足して値を表示していますが、3行に満たない間は無効扱いとすることもできます。次のように、HAVING句で要素数がちょうど3の集合を見つけます(「<=3」の条件を「=3」に変える、というのではうまくいきません。なぜだか分かりますか?)。

移動累計を求める その2:3行に満たない期間は無効扱い
SELECT prc_date, A1.prc_amt,
 (SELECT SUM(prc_amt)
    FROM Accounts A2
   WHERE A1.prc_date >= A2.prc_date
     AND (SELECT COUNT(*)
            FROM Accounts A3
           WHERE A3.prc_date 
             BETWEEN A2.prc_date AND A1.prc_date  ) <= 3
   HAVING  COUNT(*) =3) AS mvg_sum  --3行未満は非表示
  FROM  Accounts A1
 ORDER BY prc_date;
結果
prc_date    prc_amt     mvg_sum
----------  ---------   ----------
2006/10/26   12,000                  --3行未満なので非表示
2006/10/28    2,500                  --3行未満なので非表示
2006/10/31  -15,000       -500       --3行そろったので出力
2006/11/03   34,000     21,500
2006/11/04   -5,000     14,000
2006/11/06    7,200     36,200
2006/11/11   11,000     13,200

 もしこのクエリの動作が分かりにくかったら、一度、非グループ化した結果を表示して、中身を見てみると理解しやすいでしょう。

非グループ化して表示
SELECT A1.prc_date AS A1_date, 
       A2.prc_date A2_date, 
       A2.prc_amt AS amt
  FROM Accounts A1,  Accounts A2
 WHERE A1.prc_date >= A2.prc_date
   AND (SELECT COUNT(*)
          FROM Accounts A3
         WHERE A3.prc_date BETWEEN A2.prc_date  AND A1.prc_date  ) <= 3
 ORDER BY A1_date, A2_date;
結果
A1_date       A2_date       amt
------------  ------------  ---------
2006/10/26    2006/10/26     12,000

2006/10/28    2006/10/26     12,000
2006/10/28    2006/10/28      2,500

2006/10/31    2006/10/26     12,000      
2006/10/31    2006/10/28      2,500     …S1:-500
2006/10/31    2006/10/31    -15,000     

2006/11/03    2006/10/28      2,500
2006/11/03    2006/10/31    -15,000     …S2:21,500
2006/11/03    2006/11/03     34,000

2006/11/04    2006/10/31    -15,000
2006/11/04    2006/11/03     34,000     …S3:14,000 
2006/11/04    2006/11/04     -5,000

2006/11/06    2006/11/03     34,000
2006/11/06    2006/11/04     -5,000     …S4:36,200
2006/11/06    2006/11/06      7,200

2006/11/11    2006/11/04     -5,000
2006/11/11    2006/11/06      7,200     …S5:13,200
2006/11/11    2006/11/11     11,000

 こうして展開すると、基本的な考え方はノイマン型の再帰集合と同じですが、入れ子ではなく、部分的に重なりあいつつ「ずれて」いく、幾つもの集合を作っていることが分かります。S3などすべての集合と共通部分を持っています。

部分的に重なり合う集合群
部分的に重なり合う集合群

 この集合群をノイマン型の同心円的な入れ子集合と比べてみると、集合の作り方にもいろいろなヴァリエーションがあることが分かって、なかなか興味深いものがあります。自己結合の回のキーワードが「入れ子(再帰)」だったとすれば、今回のキーワードは、差し詰め「ずらし」です。

 また、今回は累計を考えたため、SUM関数を使いましたが、移動平均(moving average)を求めたいなら、SUMAVGで置き換えるだけでOKです。

次のページ
オーバーラップする期間を調べる

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
達人に学ぶSQL連載記事一覧

もっと読む

この記事の著者

ミック(ミック)

日本では、主にBI/DWHの設計からチューニングまでを専門とするデータベースエンジニアとして活動。2018年より米国シリコンバレーに活動拠点を移し、技術調査とビジネス開発に従事している。主な著書・訳書:『達人に学ぶSQL徹底指南書 第2版』(2018)『SQL実践入門』(2015)Joe Celko『プログラマのためのSQL 第4版』(2015)翔泳社 - 著者ページ:https://www.shoeisha.co.jp/book/author/3964著者個人ページ:http://mickindex.sakura.ne.jp/

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/907 2008/08/22 19:38

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング