特定の期間ごとに集計する方法


現在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でかえってくるととてもありがたいです。

どなたかお願いします！




この手の集計処理は生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]




この手の集計処理は生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]

