サブクエリ　移動平均など

投稿日：2016年11月21日

前回の応用編です。

日付、入出金、残高はできましたが、ここからさらに、現在のレコードから3行以内(3行あれば3行、なければそれ以内でできるだけ)のレコードの合計値を出す計算を考えます。

結果だけ先に書くと下記のようになります。

prc_date | prc_amt | sum
————+———+——-
2006-10-26 | 12000 | 12000
2006-10-28 | 2500 | 14500
2006-10-31 | -15000 | -500
2006-11-03 | 34000 | 21500
2006-11-04 | -5000 | 14000
2006-11-06 | 7200 | 36200
2006-11-11 | 11000 | 13200

SQL自体ですが、これは前回の下記のSQLに制限をかけるとわかりやすいです。

SELECT
  a1.prc_date,
  a1.prc_amt,
  ( SELECT SUM( prc_amt ) FROM Accounts a2 WHERE a2.prc_date <= a1.prc_date ) AS onhand_amt
FROM
  Accounts a1

SELECT

a1.prc_date,

a1.prc_amt,

( SELECT SUM( prc_amt ) FROM Accounts a2 WHERE a2.prc_date <= a1.prc_date ) AS onhand_amt

FROM

Accounts a1

4行目の合計値に制限を付けます。

要はすべての合計値ではなく、a1.prc_dateとa2.prc_dateの間が常に3行以内になるデータが存在するということをSQLで表現します。

イメージでいうとEXISTSみたいなかんじに近いですかね。

要は1.prc_dateとa2.prc_dateの間に3行以内のレコードが存在すればいいので、下記のようなSQLを追加することになります。

SELECT
  prc_date,
  prc_amt ,
  ( SELECT SUM(prc_amt) FROM accounts a2 
    WHERE 
      a2.prc_date <= a1.prc_date  
        and 
       -- これが追加された条件です。
      (  SELECT COUNT(*) FROM accounts a3  WHERE a3.prc_date BETWEEN a2.prc_date AND a1.prc_date ) <= 3
  ) 
FROM
  accounts a1

SELECT

prc_date,

prc_amt ,

( SELECT SUM(prc_amt) FROM accounts a2

WHERE

a2.prc_date <= a1.prc_date

and

-- これが追加された条件です。

( SELECT COUNT(*) FROM accounts a3 WHERE a3.prc_date BETWEEN a2.prc_date AND a1.prc_date ) <= 3

)

FROM

accounts a1

といっても私は自力では思いつけなかったです。達人に学ぶSQL徹底指南書の模範解答をみて、具体値を入れた後、図に書き出して初めてイメージがつかめました。

こういうところはプログラムって数学に近いですね。本には非常にわかりやすい説明が載っていますが・・・・

時間の重複

以前、予約管理システムをつくったときにもやりましたが、宿泊のシステムなどを作った時に重複しているか否かをみるロジックをSQLで書いてみます。

以下のようなタイムテーブルがあるとします。

例によってデータはすべてここにあります。

reserver | start_date | end_date
———-+————+————
木村 | 2006-10-26 | 2006-10-27
荒木 | 2006-10-28 | 2006-10-31
堀 | 2006-10-31 | 2006-11-01
山本 | 2006-11-03 | 2006-11-04
内田 | 2006-11-03 | 2006-11-05
水谷 | 2006-11-06 | 2006-11-06

ここから重複を導くSQLは下記のようになります。基本的に数直線を書けばすぐわかるかと思います。

SELECT
  r1.*
FROM
  reservations r1
WHERE
  EXISTS(
    SELECT 
      *
    FROM
      reservations r2
    WHERE
      --自分は絶対に存在するのでこれを入れないと全行出ます。
      r1.reserver <> r2.reserver
        AND
      --カッコの位置に気を付けましょう。これまたはずすと全然求めたい条件と合致しません。
      (  r1.start_date BETWEEN r2.start_date AND r2.end_date  
       OR 
         r1.end_date BETWEEN r2.start_date AND r2.end_date  )
  );

SELECT

r1.*

FROM

reservations r1

WHERE

EXISTS(

SELECT

FROM

reservations r2

WHERE

--自分は絶対に存在するのでこれを入れないと全行出ます。

r1.reserver <> r2.reserver

AND

--カッコの位置に気を付けましょう。これまたはずすと全然求めたい条件と合致しません。

( r1.start_date BETWEEN r2.start_date AND r2.end_date

r1.end_date BETWEEN r2.start_date AND r2.end_date )

);

以前にこのエントリーで紹介しましたが、こういった書き方もできます。ただBetweenを使ったほうが直感的でわかりやすいかな・・・

SELECT
  r1.*
FROM
  reservations r1
WHERE
  EXISTS(
    SELECT 
      *
    FROM
      reservations r2
    WHERE
      r1.reserver <> r2.reserver
        AND
        (  r2.start_date <=  r1.end_date AND  r1.start_date <= r2.end_date  )
  );

SELECT

r1.*

FROM

reservations r1

WHERE

EXISTS(

SELECT

FROM

reservations r2

WHERE

r1.reserver <> r2.reserver

AND

( r2.start_date <= r1.end_date AND r1.start_date <= r2.end_date )

);

これまた数直線を書いていろいろイメージしてみるとすぐにわかるかと思います。

サブクエリ 移動平均など

サブクエリ　移動平均など