【Active Record】カウントを集計してランクづけしたい

状況 　

任意の期間における合計カウントにもとづいてレコードをランク付けしたい。カウントは、アクセス数、販売数などを想定している。これを先週、先月、去年などの単位で集計する。

前提 　

• Ruby on Rails 4
• MySQL

テーブル 　

カウントがあるテーブル

create table  "pages" do |t|
  t.integer access_count
  t.date     date
end

ランキングテーブル

create table "rankings" do |t|
  t.integer page_id
  t.integer rank

アソシエーション

class Page << ActiveRecord::Base
  has_many :ranking
end

class Ranking << ActiveRecord::Base
  belongs_to :page
end

解決 　

Active RecordsのgroupとMySQLの日付操作関数を組み合わせる。 　

コード

レコードの取り方とそれに対して何か処理したいときどうするか。

取得

先週のカウントを合計してみる。 　

selectを使うとリレーションで返ってくる。なので戻り値にメソッドをチェーンできる。firstとかlimitとかできる。selectするカラムにつけたエイリアスでorderできる。

Page.select('id, SUM(access_count) AS sum')
　　.where('YEARWEEK(date) = YEARWEEK(CURRENT_DATE())-1')
　　.group(:page_id)
　　.group('YEARWEEK(date)')
　　.order('sum desc')

pluckを使うと配列で返ってくる。

Page
  .where('WEEK(date) = WEEK(CURRENT_DATE())-1')
  .group(:page_id)
  .group('WEEK(date)')
  .pluck('SUM(access_count)')

ランク付け

さっきの方法で取ったレコードをループして、ループのインデックスをrankとして挿入する。

pages = Page.select('id, SUM(access_count) AS sum')
　　.where('YEARWEEK(date) = YEARWEEK(CURRENT_DATE())-1')
　　.group(:page_id)
　　.group('YEARWEEK(date)')
　　.order('sum desc')

pages.each.with_index(1) do |page, rank|
  page.update_columns ( rank: rank)
end

ループのコード実際にためしてないのでなんかおかしい可能性がある。 こんだけでランクがつけられる。each.with_indexのインデックスは0はじまり1位からとなるように引数を渡しておく。その他注意としてeachにドットでwith_indexする。each_with_indexだと思って間違えた。 　

すっきりさせる

期間の条件をscopeにまとめてやるとよりいい感じになりそう。読みやすくメンテしやすくなる。 　

たとえば、週次、月次の条件を書いたりできる。dateを引数にわたす。

先週

scope :lastweek, -> (date) {
  where('WEEK(date) = WEEK(CURRENT_DATE())-1')

先月

scope :lastweek, -> (date) {
  where('MONTH(date) = MONTH(CURRENT_DATE())-1')
}

いっそのことすべての条件をまとめればよいとおもう。order_by_lastweek_access_count だ。

scope :order_by_lastweek_access_count, -> (date) {
  where('MONTH(date) = MONTH(CURRENT_DATE())-1')
　.group(:page_id)
　.group('YEARWEEK(date)')
　.order('sum desc')
}

すると先ほどのコードはこうなる。

pages = Page.select('id, SUM(access_count) AS sum').order_by_lastweek_access_count(date)

このコードも動作確認してない。あとで確かめる。

参考 　

こちらの記事がたいへんわかりやすく参考になった。このページは、本記事の内容を個人的に練習したみた結果を書いている。 blog.scimpr.com