転職した

調べた事、学んだ事、思った事、考えた事を個人的にまとめているだけなので内容は保証しないYO!

SQLで増分の一覧取得する

Rails ActiveRecordでの増分の計算方法について。 - QA@IT

こういう類いの問題はRailsでごにょごにょやるより、SQLを直接書いてしまった方が後で理解しやすいと思うんだけどどうなんだろ。個人的にはSQLを直接書く派。

 

まあ、ともかく、この質問(一時間ごとの行同士の増分の計算を行う)のような順序のあるデータが格納されたテーブルから直後のデータとの差を取得したいという要望は基幹系システムだと結構ある。逆に増分データは保持しているから累積計算した一覧が欲しいという場合もあったりするが、それは今度書こう。

 

この問題の考え方としては、

  1. あるレコードのcreated_at(G.created_at)以降で最小のcreated_at(G3.created_at)のレコードのkilowatthour(G2.kilowatthour)を取得すれば次のデータのkilowatthourが求められる。
  2. あるレコードのcreated_at(G.created_at)のkilowatthour(G.kilowatthour)と先ほどのkilowatthour(G2.kilowatthour)との差分を求めれば増分を求める事ができる。

の2点。

SQLで書くとこんな感じ。

select 
  G.created_at,
  (
    select min(G2.kilowatthour)
    from genreps as G2
    where
      G2.created_at = (
          select min(G3.created_at)
          from genreps as G3
          where G.created_at < G3.created_at)
  ) - G.kilowatthour as zoubun
from genreps as G
order by G.created_at;

ポイントは相関サブクエリで次のデータのcreated_atを求めている部分(G2.created_atと比較しているサブクエリ)、min(G2.kilowatthour)とすることでcreated_atに重複があってもとりあえずSQLがコケないようにスカラサブクエリにしている部分ぐらいかな(結果は要件としては正しくないだろうけど)。あと、created_atにインデックスを付ける必要があるだろうなぁ。

 

実行例

mysql> select * from genreps;

+---------------------+--------------+

| created_at          | kilowatthour |

+---------------------+--------------+

| 2013-04-12 08:00:00 |          0.0 |

| 2013-04-12 09:00:00 |          2.0 |

| 2013-04-12 10:00:00 |          3.5 |

| 2013-04-12 11:00:00 |          4.7 |

+---------------------+--------------+

4 rows in set (0.00 sec)

 

mysql> select 

    ->   G.created_at,

    ->   (

    ->     select min(G2.kilowatthour)

    ->     from genreps as G2

    ->     where

    ->       G2.created_at = (

    ->           select min(G3.created_at)

    ->           from genreps as G3

    ->           where G.created_at < G3.created_at)

    ->   ) - G.kilowatthour as zoubun

    -> from genreps as G

    -> order by G.created_at;

+---------------------+--------+

| created_at          | zoubun |

+---------------------+--------+

| 2013-04-12 08:00:00 |    2.0 |

| 2013-04-12 09:00:00 |    1.5 |

| 2013-04-12 10:00:00 |    1.2 |

| 2013-04-12 11:00:00 |   NULL |

+---------------------+--------+

4 rows in set (0.00 sec)