21519


現在RailsとMySQLを利用したwebアプリケーションでレコードを集計しております。

ActiveRecordでは、groupを用いる事で上手く日毎、週間、月間のレコードを集められているのですが、特定の期間毎に集計する方法に悩んでいます。

例えば

[code]

    User.group("WEEK(created_at)").count
    User.group("MONTH(created_at)").count
    User.group("DATE_FORMAT(created_at,'%Y-%m-%d')").count
    
[/code]

です。

これらはhashになって日付と個数でかえってくるので非常に利便性が高いのですが、  
一方で例えば3日毎に、2週間毎に、3ヶ月毎に集計する等の特定期間での集計が分からず困っています。どなたか教えて頂けますと幸いです....

Googleのこちら([Google
LineChart](http://qiita.com/succi0303/items/745fddfc3689a867759c))を用いて出力もするので、可能であれば前述の３つのようにhashでかえってくるととてもありがたいです。

どなたかお願いします！


CC BY-SA 3.0
2016-01-30T11:16:14.547
21510
2016-04-25T01:56:53.843
2016-01-30T14:30:19.157
76
14189
question
ruby-on-rails mysql sql rails-activerecord
特定の期間ごとに集計する方法


この手の集計処理は生SQLでガツンと集計するのが手っ取り早いと思います。  
以下は2016年1月で3日おきに集計する例です。

[code]

    sql = <<-SQL
    SELECT
    c.dt_from,
    c.dt_to,
    COUNT(u.id) AS cnt
    FROM
    (SELECT
       @f:='2016-01-01' AS dt_from,
       DATE_ADD(@f, INTERVAL 2 DAY) AS dt_to
     UNION ALL
     SELECT
       @f:=DATE_ADD(@f, INTERVAL 3 DAY) AS dt_from,
       DATE_ADD(@f, INTERVAL 2 DAY) AS dt_to
     FROM
       users
     WHERE
       @f < '2016-01-31'
    ) AS c -- cはcalendarのc
    LEFT OUTER JOIN users u
    ON c.dt_from <= DATE(u.created_at) AND DATE(u.created_at) <= c.dt_to
    GROUP BY c.dt_from, c.dt_to
    ORDER BY c.dt_from
    SQL
    
    users = User.find_by_sql(sql)
    users.map(&:attributes)
    # => [{"dt_from"=>"2016-01-01", "dt_to"=>"2016-01-03", "cnt"=>3}, {"dt_from"=>"2016-01-04", "dt_to"=>"2016-01-06", "cnt"=>7}, ...] 
    
[/code]

SQLの実行結果はこちらで確認することができます。

<http://sqlfiddle.com/#!9/9b381c/4>

ただし、このSQLはタイムゾーンを特に考慮していないので、環境によっては日本時間におけるx月x日の集計とは多少ずれるかもしれません。

## 追伸

週間、月間の集計ですが、

[code]

    User.group("WEEK(created_at)").count
    User.group("MONTH(created_at)").count
    
[/code]

このままだと他の年もごちゃまぜに集計されてしまうので、年の違いを考慮した方がいいと思います。以下は一例です。

[code]

    User.group("YEARWEEK(created_at)").count
    User.group("CONCAT(YEAR(created_at), MONTH(created_at))").count
    
[/code]


CC BY-SA 3.0
2016-01-30T23:26:55.877
21519
2016-01-30T23:35:41.407
2016-01-30T23:35:41.407
85
85
21510
answer


要はgroup()では、指定されたSQL式の計算結果に応じて分類集計する(例えば`"MONTH(created_at)"`なら「計算結果」は1から12なので、1から12で集計されるわけです)だけで、内部的にはSQLのGROUP
BYを使っているので、GROUP BYに指定してうまく動くような式を考えてgroup()の引数に指定してやれば良いということになります。

例えば四半期であれば、MySQL関数にQUARTERというのがありますので、こんな感じにすれば期待の結果が得られると思います。

[code]

    User.group("CONCAT(YEAR(created_at), QUARTER(created_at))").count
    
[/code]

_3日毎に、2週間毎に、_
というのは年をまたいだときにどう分類すれば良いのか細かい仕様がわからないとなんとも言えないのですが、仕様を明確化してストアドファンクションを作ってしまって、そのファンクションを記載するのが手っ取り早いでしょう。

[code]

    User.group("every3days(created_at)").count
    
[/code]

every3daysは日付を引数に取り3日ごとの代表日(2016-01-01,2016-01-02,2016-01-03に対しては2016-01-01、2016-01-04を含む3日には2016-01-04等々)を返すように作ったストアドファンクションという想定です。


CC BY-SA 3.0
2016-01-31T00:15:24.007
21520
2016-01-31T00:15:24.007
13972
21510
answer


Junichi Itoさんが投稿されてる  
ユーザ変数を使う方法ですが

OracleACEのAketiJyuuzouさんとyoku0825さんと  
日本オラクルの木村明治さんによると  
MySQLのユーザ変数は評価順序が未定義です。  
<http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98>

なので、結果が保証されません。


CC BY-SA 3.0
2016-04-25T01:56:53.843
24319
2016-04-25T01:56:53.843
2020-06-17T08:14:45.997
-1
15293
21510
answer