読者です 読者をやめる 読者になる 読者になる

ITの隊長のブログ

ITの隊長のブログです。いや、まだ隊長と呼べるほどには至っていないけど、日々がんばります。CakePHPとPlayFrameworkを使って仕事しています。最近はAngular2をさわりはじめたお(^ω^ = ^ω^)

MySQLって変数使えたんですね(´;ω;`)ブワッ

SQL MySQL

スポンサードリンク

しかも、自分の中で問題になっていた。「存在しない日付の月集計」もこれを使えば解決できました。

select
  *
FROM
  (SELECT 
    CAST(DATE_FORMAT('2014-01-01', '%Y%m') + CAST(VIRTUAL.generate_series AS UNSIGNED) as CHAR) AS ym
  FROM
    (select 
    0 generate_series
  from dual where
    (@num:=1 - 2) * 0 union all select 
    @num:=@num + 1
  from
    `information_schema`.columns
  limit 12) VIRTUAL) VIRTUAL_BY_MONTH_PARE
  LEFT OUTER JOIN
  (SELECT 
    DATE_FORMAT('2014-01-01', '%Y%m') + CAST(VIRTUAL.generate_series AS UNSIGNED) AS ym
  FROM
    (SELECT 
    0 generate_series
  FROM DUAL WHERE
    (@num_s:=1 - 2) * 0 UNION ALL SELECT 
    @num_s:=@num_s + 1
  FROM
    `information_schema`.COLUMNS
  LIMIT 12) VIRTUAL) VIRTUAL_BY_MONTH_PARE2 
  on (VIRTUAL_BY_MONTH_PARE.ym = VIRTUAL_BY_MONTH_PARE2.ym)

このSQLは、ダミーのテーブルを2つ作成してそれを紐付ける既存のDBに関係のないコードです。

見づらいので抜粋しますが、下記コードを2つ用意しています。

SELECT 
    CAST(DATE_FORMAT('2014-01-01', '%Y%m') + CAST(VIRTUAL.generate_series AS UNSIGNED) as CHAR) AS ym
  FROM
    (select 
    0 generate_series
  from dual where
    (@num:=1 - 2) * 0 union all select 
    @num:=@num + 1
  from
    `information_schema`.columns
  limit 12) VIRTUAL

これだけを実行すると

+--------+
| ym     |
+--------+
| 201401 |
| 201402 |
| 201403 |
| 201404 |
| 201405 |
| 201406 |
| 201407 |
| 201408 |
| 201409 |
| 201410 |
| 201411 |
| 201412 |
+--------+
12 rows in set (0.04 sec)

と、なるわけですよ。

んで、大元を実行すると

+--------+--------+
| ym     | ym     |
+--------+--------+
| 201401 | 201401 |
| 201402 | 201402 |
| 201403 | 201403 |
| 201404 | 201404 |
| 201405 | 201405 |
| 201406 | 201406 |
| 201407 | 201407 |
| 201408 | 201408 |
| 201409 | 201409 |
| 201410 | 201410 |
| 201411 | 201411 |
| 201412 | 201412 |
+--------+--------+
12 rows in set (0.07 sec)

ってなるわけですよ。

しかし、私はなぜかこの結果がでなかった。でも理由がわかったのでメモ。

変数を使いまわしているせいでした。

    (@num:=1 - 2) * 0 union all select 
    @num:=@num + 1

抜粋していますが、最初上記コードを書き上げた時は、VIRTUAL_BY_MONTH_PAREVIRTUAL_BY_MONTH_PARE2のどちらのサブクエリも@numで記載していましたが、そうするとjoinで失敗します。上書きするので、VIRTUAL_BY_MONTH_PAREで201401〜12まで用意しても、VIRTUAL_BY_MONTH_PARE2のクエリの結果は201413〜24になっているからでした。。。orz

はぁあーーーーーーーーーーーーーーorz これで2時間よ!2時間(^ω^#

ま、これは解決したのでおkとして。

んで、ちょっと問題が。。。

SQLだんだん長くなってきてね・・・?

PHPでループさせたくないから、可能な限りSQL側で対応していますが、凝った集計になるとSQLが膨れます。

PHP側はコードがすっきりしますが、SQL側のメンテは大丈夫なのだろうか。。。?

ちと不安です。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)