Railsでの製作Tips

ActiveRecord4での集計をいろいろまとめた

投稿日:2015年4月25日 更新日:

集計の仕方いろいろ

ActiveRecordを使った集計方法についていろいろ調べたのでまとめた

集計用のメソッド〜count、sum、minimum、maximum、average

【初級編⑪】SQLのGROUP BYでレコードのグループ化と集計を行う

リンクのページはSQLサーバーのページだけどわかりやすい.
nilはaverageなどの総数に含まれないことに注意.
また、返ってくるのはActiveRecord_Relationでなく、単なる数値になることに気をつける.

中央値をとる〜active_median

github.com/ankane/active_median
統計をとるときに正規の分布でない場合で、平均値だけでは信用できないというときは、補助的な指標として中央値を使うことがある.
active_medianは中央値を取ってくれるgem

selectを使う

例えば、sumとcountを同時に求めたい場合、selectを使えば簡単にできる.

で、asなどで名付けておくとActiveRecordの方でも取りやすくなる

このときは、ActiveRecord_Relationが返ってくるので、firstをつけるのに注意.
そして、ActiveRecordなら、json化も簡単

この場合、idは邪魔なので除外しておくとよい

to_jsonはHashではなくStringなので注意.

pluckを使う

selectはSQLのSELECTを発行するのですぐわかるけど、pluckは指定のカラムを抜き出してarrayにしてくれる命令のこと.
結局、selectを発行しているので、同じ感じで求めることができる.
ただし、返ってくるのがArrayなので、その後の処理方法でselectとpluckを使い分ける.
個人的には、Arrayに違う意味の値を入れることになるので、この場合はselectを使いたい.

二重にArrayで帰って来ちゃうので[0]をつけておくと後で加工しやすい.

GROUP BYでグループ分け〜group

使い方

割合などを求めるのに、groupを使って集計結果をグループ分けできる.

例えば、user_idごとに個数を求めたい場合

また、groupは重ねて使うこともできる.
例えば、user_idでグループ分けして、さらにproject_idでもグループ分けしたい場合

グループ分け結果を操作する

Havingでグループ分け結果の絞り込み

このようなデータで

こんな感じで絞り込みができる

Whereで集計元の絞り込み

ちなみにhavingはgroupの集計結果に対する絞り込みで、普通にwhereでも集計元を絞り込みできる

結果に対してorderをかける

参考:ActiveRecordでgroup Byしてカウントをして、カウント数でorderする

発行しているsqlをみると、自動でカラムに名付けているので、それを利用してorderとかもできる.
例えば、以下だとcount(*)に対して、AS句でcount_allと名付けていることがわかる.

なので、count_allでorderできる.

gem hightopを使う

countとorderの組み合わせに限定すれば、hightopというgemが便利そう
github.com/ankane/hightop

年月日ごとにグループ分けする方法

応用として、DateTimeなどのデータを、まず、年でグループ分けして、月でグループ分けして、日でグループ分けしてみる.
やり方は、データベースの関数を使い、使っているデータベースごとに関数が異なる

sqlite3の場合

striftimeを使う

MySqlの場合

YEAR、MONTH、DAYといった関数が使える

PostgreSqlの場合

今回試してないけど、date_truncでいけるぽい
PostgreSQL query to count/group by day and display days with no data

gem groupdateを使う

groupdateというgemを使うと、データベースの違いを気にせずにgroup_by_day(:created_on)などで簡単に期間でグルーピングできる.
github.com/ankane/groupdate
さらに、Chartkickというグラフのライブラリと相性がよく、ActiveRecordのデータから簡単にグラフに出力できる.
chartkick.com/
ただし、欠点があって、railsのtimezoneをutcで保存しないと使えない.
そして、TimeZoneはgroupdate上で指定することになる.
これは今のところ仕様だからしょうがない.
Groupdate won’t work with config.active_record.default_timezone = :local

groupdateをベースにしたgem daysliceなんていうのもできてた.こちらはtimezoneを無視する.今度試してみたい.
github.com/HappyFunCorp/dateslices

値0やnullの日も含む結果

ActiveRecord query GROUP and COUNT zero values
generate days from date range

gem by_starを使う

DateTimeのデータを1ヶ月単位などで絞り込む場合はby_starというgemが便利.
github.com/radar/by_star
ただ、たぶんby_starでは月ごとのグルーピングはできなさそう.

DateTimeのカラムについてその日のデータを絞り込む

by_starを使わない場合はActiveSupportを使って次のようにとってIN句を使う.

TimeZoneを考慮するのは、TimeとTimeZoneだけだった.
参考: RubyとRailsにおけるTime, Date, DateTime, TimeWithZoneの違い

そして、Timeにはall_dayという便利なメソッドがあった.

結合を使って、関連先の名前を取ってくる

また、スタックオーバーフローにお世話になりました
ActiveRecordで外部キーで GROUP BYして集計するとき関連先のカラムを使ってグループ分けするには?

例えば、次のような環境の場合

こんな感じにできる.

情報

ActiveRecord使い方まとめ
多対多の関係でまだ関連を持っていない候補を探す〜active_record、pluck、not in
sqlで度数分布表、ヒストグラムを書くイディオム

Ruby on Rails 4 アプリケーションプログラミング

Ruby on Rails 4 アプリケーションプログラミング [書籍]

著者山田 祥寛

出版社技術評論社

出版日2014年4月11日

商品カテゴリー大型本

ページ数544

ISBN4774164100

Supported by amazon Product Advertising API

-Railsでの製作Tips
-,

執筆者:

関連記事

MiniTest::Unitにおけるネストしたテストケース

By: Elliott Brown – CC BY 2.0 目次1 How to2 実行結果 How to クラスを親子で書けばOK. そのときに継承とかも自由に使える. RSpecに慣れ …

Pryの関係gemが最初からいろいろはいったjazz_fingersを試してみた

By: george ruiz – CC BY 2.0 目次1 jazz_fingersのインストール2 インストールされるGem3 Pry4 awesome_print5 hirb6 p …

Rails5でAjax処理をActionCableを使ってリアルタイム同期化

By: Mike Johnston – CC BY 2.0 目次1 はじめに2 サンプル掲示板3 ActionCableでリアルタイム化4 チャンネルを購読して、ストリームで分配する5 c …

Railsでdate_fieldにjquery uiのdatepickerを使おうとしたらchromeのdatepickerとかち合ってしまったとき

By: Randy Heinitz – CC BY 2.0 text_fieldにしとけば、jQuery UI datepickerは使える. [crayon-58d5e86da4e383 …

Selenium IDEからRubyのテストケースを書き出す

By: Kenny Louie – CC BY 2.0 目次1 Selenium IDE2 Selenium IDEのインストール3 Selenium IDEの立ち上げ方4 はじめてのSe …