質問: RubyonRailsで記事の投稿を集計(時間別)する処理の実装をしております。 現在、次のようなSQLを実行し、集計した結果をDBに保存し、Web上の画面に表示させるということをやりたいです。 SQL > > SELECT date_format(created_at, '%Y-%m-%d-%H') as hour, count(id) > > FROM `articles` WHERE (created_at >= "2016-05-29" AND created_at < "2016-05-30") > > GROUP BY hour; > > > 集計用テーブル > > Report > > id(integer) > > hour(datetipe) > > count(integer) > > > SQLを実行してから、DBに保存するところの処理はまだ考えている途中のため、実装できてませんが、 現在、問題視している点は、hour(datetipe)の部分です。 というのも、集計するときにdate_format(created_at, '%Y-%m-%d-%H')という風にしているため、 そのまま、DBに保存ができなくなってしまいます。 %Y-%m-%d-%Hという風に整形している理由は後でも述べますが、2016年05月29日(水)というふうに表示させるため、区切りを認識する文字をいれておきたかったためです。 それに対して私が考えた方法は次の2つです。 ``` 1) hourをstring型に変更する 2) SQL実行の際にdate_format(created_at, '%Y-%m-%d-%H')を使わず、上手く時間別に集計する ``` 1)については、string型で保存をし、画面を表示する際に、Controllerで stringからdatetime型に変換する処理を入れるといいかなと思っております。 (変換する理由は曜日を取得したいためです) また、画面側では 2016年05月29日(水) と言った風に表示できるようにしたいと思っております。 1)のやり方だと頑張ればできそうな気がするのですが、そもそもstring型で日付を保存することに違和感を感じてしまいます。 しかし、2)については方法がわからず困っております。 以上をふまえまして、アドバイスを頂けますと幸いです。 よろしくお願いします。 追記 -- 表題の問題はmetropolis様のご回答により解決しました。 今回は時間別に集計するため、集計キーをDATE_FORMAT で %Y-%m-%d %H:00:00 に変換して DATETIME 型にキャストするという方法ができました。 しかし、今後、もし月別に集計したいとなったとき、集計キーは(2016-04)みたいな形になります。 こういった場合はDATETIME型もしくはDATE型にキャストできないのかなと思いました。 こういう場合は、String型で保存するしかないでしょうか?
回答: PostgreSQLであれば、date_trunc を実行した列で集約すれば、望みの値が得られるように思います SQLのサンプルも置いておきます ``` WITH articles AS ( SELECT id, created_at FROM ( VALUES (1, TIMESTAMP '2016-07-15 00:00:00'), (2, TIMESTAMP '2016-07-15 00:01:00'), (3, TIMESTAMP '2016-07-15 01:00:00'), (4, TIMESTAMP '2016-07-15 01:01:00'), (5, TIMESTAMP '2016-07-15 01:02:00'), (6, TIMESTAMP '2016-07-15 02:00:00') ) AS T(id, created_at) ) SELECT hour_trunc, count(*) AS hour_count FROM ( SELECT id, date_trunc('hour', created_at) AS hour_trunc, count(created_at) over (partition by date_trunc('hour', created_at)) FROM articles ) T GROUP BY hour_trunc ORDER BY hour_trunc ; ``` 集約絡みでうまくいかないことがあれば、window functionを使うと多くの問題が解決できると思います 機会があれば挑戦してみてください (でも、RailsのSQLbuilderは生成してくれないと思います...) mysqlでは TIME_FORMAT するか TO_SECONDS の剰余を取るほかないようです... ``` SELECT sec, FROM_UNIXTIME(sec * 3600 - TO_SECONDS('1970-01-01 00:00:00')) as my_date_time, count(sec) AS count FROM ( SELECT id, updated_at, CAST(TO_SECONDS(updated_at) / 3600 AS SIGNED INTEGER) AS sec, TIME_FORMAT(updated_at, '%Y%m%d%h') AS ymdh FROM ( SELECT 1 AS id, TIMESTAMP '2016-07-15 00:00:00' AS updated_at FROM DUAL UNION ALL SELECT 2, TIMESTAMP '2016-07-15 00:01:00' FROM DUAL UNION ALL SELECT 3, TIMESTAMP '2016-07-15 01:00:00' FROM DUAL UNION ALL SELECT 4, TIMESTAMP '2016-07-15 01:01:00' FROM DUAL UNION ALL SELECT 5, TIMESTAMP '2016-07-15 01:02:00' FROM DUAL UNION ALL SELECT 6, TIMESTAMP '2016-07-15 02:00:00' FROM DUAL ) AS T ) AS T GROUP BY sec ORDER BY sec ; ```