Symfoware

Symfowareについての考察blog

MySQL クエリで連続した日付の仮想表を作成する

例えば、こんな販売実績テーブルがあるとします。


  1. create table sales (
  2. sales_date datetime,
  3. item_count int
  4. )




販売した日時と、販売個数を管理しています。
適当にデータを導入。


insert into sales(sales_date, item_count) values
('2015-04-01 10:00:00',2),
('2015-04-01 12:00:00',1),
('2015-04-01 15:00:00',2),
('2015-04-02 10:00:00',1),
('2015-04-02 12:00:00',1),
('2015-04-04 15:00:00',1),
('2015-04-06 10:00:00',3)






> select * from sales;




+---------------------+------------+
| sales_date         | item_count |
+---------------------+------------+
| 2015-04-01 10:00:00 |         2 |
| 2015-04-01 12:00:00 |         1 |
| 2015-04-01 15:00:00 |         2 |
| 2015-04-02 10:00:00 |         1 |
| 2015-04-02 12:00:00 |         1 |
| 2015-04-04 15:00:00 |         1 |
| 2015-04-06 10:00:00 |         3 |
+---------------------+------------+




3、5、7日は残念ながら販売実績がなかったデータです。
1週間分の販売実績表を作り、グラフ化したい。


> select
date_format(sales_date, '%Y-%m-%d') as sales_date_f
,sum(item_count) as item_count_day

from
sales

group by
sales_date_f;




+--------------+----------------+
| sales_date_f | item_count_day |
+--------------+----------------+
| 2015-04-01 |             5 |
| 2015-04-02 |             2 |
| 2015-04-04 |             1 |
| 2015-04-06 |             3 |
+--------------+----------------+



日毎のグラフにしたいので、3日と5日は「0」というレコードが欲しい。
さてどうしたものか。




日付の仮想表



こちらが参考になりました。
裏MySQLクエリー入門(15) 応用編3 MySQLで連番の仮想表を作成

例は連番の仮想表ですが、これを応用して日付の仮想表を作成。
LIMITで2015-03-31の翌日から何日分の仮想表を作成するかが指定できます。


  1. select
  2. date_format(date_add('2015-03-31', interval td.generate_series day), '%Y-%m-%d') as d
  3. from
  4. (
  5. SELECT 0 generate_series FROM DUAL WHERE (@num:=1-1)*0 UNION ALL
  6. SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 7
  7. ) as td




得られる結果はこちら。


+------------+
| d         |
+------------+
| 2015-04-01 |
| 2015-04-02 |
| 2015-04-03 |
| 2015-04-04 |
| 2015-04-05 |
| 2015-04-06 |
| 2015-04-07 |
+------------+





ここまでくれば、後は先ほどのgroup byした結果を結合ずればOK。


  1. select
  2. d
  3. ,coalesce(item_count_day, 0) as item_count_day
  4. from
  5. (
  6.     select
  7.     date_format(date_add('2015-03-31', interval td.generate_series day), '%Y-%m-%d') as d
  8.     from
  9.     (
  10.         SELECT 0 generate_series FROM DUAL WHERE (@num:=1-1)*0 UNION ALL
  11.         SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 7
  12.     ) as td
  13. ) as t1
  14. left outer join
  15. (
  16.     select
  17.     date_format(sales_date, '%Y-%m-%d') as sales_date_f
  18.     ,sum(item_count) as item_count_day
  19.     from
  20.     sales
  21.     group by
  22.     sales_date_f
  23. ) as t2
  24. on
  25. t1.d = t2.sales_date_f



欲しかった結果が得られました。


+------------+----------------+
| d         | item_count_day |
+------------+----------------+
| 2015-04-01 |             5 |
| 2015-04-02 |             2 |
| 2015-04-03 |             0 |
| 2015-04-04 |             1 |
| 2015-04-05 |             0 |
| 2015-04-06 |             3 |
| 2015-04-07 |             0 |
+------------+----------------+



Excelに貼り付けて、ちまちま無い日付を表追加していたのですが、
これで楽になります。
関連記事

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2015/05/01(金) 21:17:11|
  2. MySQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<Debian 8「Jessie」を最小構成でインストールする(netinst CD使用) | ホーム | TCPDFをバージョンアップしたらconvertHTMLColorToDecでエラー>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1713-9e80a342
この記事にトラックバックする(FC2ブログユーザー)