【Active Record】カウントを集計してランクづけしたい
状況
任意の期間における合計カウントにもとづいてレコードをランク付けしたい。カウントは、アクセス数、販売数などを想定している。これを先週、先月、去年などの単位で集計する。
前提
テーブル
カウントがあるテーブル
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
【Active Records】find_by_sqlで動的に組み立てたクエリを発行したい
状況
CASE式をつかったSQLをActive Recordsで実行したい。（たぶん）Active RecordsでCASEに対応するメソッドはないので、生クエリを実行するしかない。
また、WHERE句は複数の値をとる。だからクエリは動的に組み立てる必要がある。
解決
クエリはヒアドキュメントに書く。動的に変わる箇所は変数を展開させる。そして改行をスペースに置換してからfind_by_sqlに渡す。
コード
クエリ。
.strip_heredocはスペースのインデントを削除してくれる。
sql = <<-SQL.strip_heredoc SELECT CASE WHEN price > #{standard_price} THEN 'expensive' -- 高い WHEN price <= #{standard_price} THEN 'affordable' -- お手頃価格 ELSE NULL END AS price_evaluation FROM books b SQL
実行可能なかたちに変換する。
readily_sql = sql.split("\n").map(&:strip).join(' ')
実行する。
Book.find_by_sql(sql)
雑感
このサンプルはシンプルだったが、現実世界の複雑な問題を解決するためのクエリは複雑になりがちである。複雑なクエリをActive Recordsに翻訳する技能がないとき、SQLでがんばろうとおもう。そんなときに、半ば無理やりというか、あれこれテクニックを動員して生クエリを組み立てて実行する術を知っておくのはよいことのはずだ。
ただ、長ったらしい生クエリよりも複雑なActive Records DSLの方がまだ読みやすいのではという気がする。できるだけActive Recordsのメソッドを使っていきたい。
【MySQL】日付を操作したい
状況
指定範囲の期間でレコードを絞り込みたい。たとえば、きのう、先週、先月。
解決
日付操作用の関数を組み合わせる。
DATE_SUB、YEARWEEK、DATE_FORMATなどなど。
コード
CREATE TABLE articles ( id int, title varchar, created_at date )
本日から8〜14日の間に作成されたレコードを取得する。
SELECT * FROM articles WHERE date = BETWEEN DATE_SUB( CURRENT_DATE(), interval 14day ) AND DATE_SUB( CURRENT_DATE(), interval 8 day );
あるいは週番号を西暦につなげて返すYEARWEEKをつかえばもっと簡単だ。
SELECT * FROM articles WHERE YEARWEEK(date) = YEARWEEK( CURRENT_DATE()) -1;
このときYEARWEEKは直に引き算できる。
SELECT YEARWEEK(CURRENT_DATE())-1; -- 201826 (2018/07/12時点）
ハマりポイント
除算できない形式に対して除算してしまう。返り値がおかしくなるかエラーになる。
例：yyyy-mm-dd形式の文字列を除算する
SELECT DATE_FORMAT( CURRENT_DATE(), '%Y-%m-%d') -1; -- 2018-07-12 ではなく 2017が返ってくる！！！
だが次の場合は期待する結果が得られる。日付フォーマットからハイフンの区切りを省いた。
SELECT DATE_FORMAT( CURRENT_DATE(), '%Y%m%d') -1; -- 20180712 - 1 => 20180711 (^^)
さらに年月も計算できる。
除算
SELECT DATE_FORMAT( CURRENT_DATE(), '%Y%m') -1; -- 201807 - 1 => 201806 (^^)
加算
SELECT DATE_FORMAT( CURRENT_DATE(), '%Y%m') +1; -- 201807 + 1 => 201807
雑感
うっかり文字列の日付を直に演算してバグらせてしまった。何がおかしいのかしばらくわからなかった。正しいと思っているコードのおかしい箇所に気づくのはむずかしい。
【Ruby】文字列からURLを抽出する
状況
平文に含まれるURLを抽出したい。関係ない文字は省きたい。
解決
URI.extractを使う。
コード
require 'open-uri' urls = [] Articles.all.each do |article| urls << URI.extract(article, ["http", "https"]) End
雑感
これはとても便利なメソッドで、ふつうの文章に紛れ込んだURLをしっかりと抜き出してくれる。クローラー作るときに便利に使える。
【Ruby on Rails】DBからユニークなデータを配列で取りたい
状況
テーブルの任意のカラムから重複を省いた要素を配列で取り出したい。
解決
pluck()してcompact.uniqする。
コード
ids = Music.where(is_favorite: true).pluck(:id).compact.uniq
雑感
あえてidを配列で取得してからActive Recordsに渡して絞りまなければならない場合、こうすればうまいこと一意なidを取り出せる。
【Ruby】配列または文字列どちらも受け取れる関数を作りたい
状況
成り行きで関数に文字列と配列の両方が入ってくるコードができあがった。配列が来たらeachで回して要素を処理すればOK、と思っていたら引数が文字列の場合にエラーが出た。両方対応できるようにしたい。
解決
instance_of?(Array)を使って配列を判定する。引数が配列のときにtrueを返す。
コード
引数を受けて配列なら要素べつに処理、文字列ならそいつ自身を処理して配列を返す関数にできる。
def do_stuff(chunk) ret = [] if chunk.instance_of?(Array) chunk.each do |element| # do something ret << hogehoge(element) end else ret = fugafuga(chunk) end return ret.present? ? ret.flatten.compact.uniq : [] end
雑感
こういう処理は、有名なモジュールのソースコードをみればうまい書き方が学べる気がする。「こういうときはこう」というベストプラクティス的な書き方は知っておくと役に立つ。
あと、配列変数を宣言したあとでarry << ['hoge', 'fuga']とすると、
[['hoge', 'fuga']]
みたいに配列がネストするのでフラットにしてから返している。なんだけど、もっとうまいやり方ありそう。
