こんにちは！kossyです！
さて、今回はRailsでpostgreSQLのrank関数SQLを直叩きしてランキングをつける方法について、
ブログに残してみたいと思います。
事前準備
例として社員の売り上げを管理するようなテーブル構成を作ってみます。
ancestryを使ったりしていますが、後々部署ごとの売り上げを出したい！みたいなユースケースに対応するために用いています。(今回はやりません)
gemfile
gem 'ancestry' gem 'gimei' # テストデータ作成向け gem 'faker' # テストデータ作成向け
各種migrationファイル
class CreateCompanies < ActiveRecord::Migration[6.0] def change create_table :companies do |t| t.string :name, null: false t.timestamps end end end
class CreatePositions < ActiveRecord::Migration[6.0] def change create_table :positions do |t| t.string :name, null: false t.references :company, null: false, foreign_key: true, index: true t.timestamps end end end
class CreateDepartments < ActiveRecord::Migration[6.0] def change create_table :departments do |t| t.string :name, null: false t.string :ancestry t.integer :ancestry_depth t.references :company, null: false, foreign_key: true, index: true t.timestamps end end end
class CreateEmployees < ActiveRecord::Migration[6.0] def change create_table :employees do |t| t.string :first_name, null: false t.string :last_name, null: false t.string :first_name_kana, null: false t.string :last_name_kana, null: false t.string :middle_name t.string :middle_name_kana t.boolean :sex, null: false t.date :hired_at, null: false t.date :retired_at t.references :company, null: false, foreign_key: true, index: true t.references :position, null: false, foreign_key: true, index: true t.references :department, null: false, foreign_key: true, index: true t.timestamps end end end
class CreateOrders < ActiveRecord::Migration[6.0] def change create_table :orders do |t| t.string :customer_name, null: false t.integer :price, null: false t.integer :status, null: false t.date :ordered_at, null: false t.references :employee, null: false, foreign_key: true, index: true t.references :company, null: false, foreign_key: true, index: true t.timestamps end end end
各種モデル
class Company < ApplicationRecord with_options dependent: :destroy do has_many :employees has_many :departments has_many :orders has_many :positions end end
class Department < ApplicationRecord belongs_to :company has_many :employees has_ancestry cache_depth: true end
class Employee < ApplicationRecord belongs_to :company belongs_to :department belongs_to :position has_many :orders enum sex: { male: false, female: true } end
class Order < ApplicationRecord belongs_to :employee belongs_to :company enum status: [:un_official, :fixed] end
class Position < ApplicationRecord belongs_to :company has_many :employees end
seedデータ
ActiveRecord::Base.transaction do # 会社 company = Company.create!(name: "日本株式会社") puts "successed create #{company.name}" # 役職 position_0 = Position.create!(company: company, name: '本部長') position_1 = Position.create!(company: company, name: '支店長') position_2 = Position.create!(company: company, name: '課長') position_3 = Position.create!(company: company, name: '係長') position_4 = Position.create!(company: company, name: '一般社員') puts "successed create positions" # 部 department_1 = Department.create!(company: company, name: "北海道営業部") department_2 = Department.create!(company: company, name: "東北営業部") department_3 = Department.create!(company: company, name: "関東営業部") department_4 = Department.create!(company: company, name: "北陸営業部") department_5 = Department.create!(company: company, name: "近畿営業部") department_6 = Department.create!(company: company, name: "四国営業部") department_7 = Department.create!(company: company, name: "中国営業部") department_8 = Department.create!(company: company, name: "九州沖縄営業部") puts "successed create root departments" # 部署・支店 ## 北海道 department_1_1 = department_1.children.create!(company: company, name: "札幌支店") department_1_2 = department_1.children.create!(company: company, name: "函館支店") department_1_3 = department_1.children.create!(company: company, name: "旭川支店") ## 東北 department_2_1 = department_2.children.create!(company: company, name: "宮城支店") department_2_2 = department_2.children.create!(company: company, name: "秋田支店") department_2_3 = department_2.children.create!(company: company, name: "福島支店") ## 関東 department_3_1 = department_3.children.create!(company: company, name: "埼玉支店") department_3_2 = department_3.children.create!(company: company, name: "千葉支店") department_3_3 = department_3.children.create!(company: company, name: "東京支店") ## 北陸 department_4_1 = department_4.children.create!(company: company, name: "山形支店") department_4_2 = department_4.children.create!(company: company, name: "新潟支店") department_4_3 = department_4.children.create!(company: company, name: "富山支店") ## 近畿 department_5_1 = department_5.children.create!(company: company, name: "大阪支店") department_5_2 = department_5.children.create!(company: company, name: "兵庫支店") department_5_3 = department_5.children.create!(company: company, name: "京都支店") ## 四国 department_6_1 = department_6.children.create!(company: company, name: "徳島支店") department_6_2 = department_6.children.create!(company: company, name: "高知支店") department_6_3 = department_6.children.create!(company: company, name: "香川支店") ## 中国 department_7_1 = department_7.children.create!(company: company, name: "鳥取支店") department_7_2 = department_7.children.create!(company: company, name: "島根支店") department_7_3 = department_7.children.create!(company: company, name: "山口支店") ## 九州沖縄 department_8_1 = department_8.children.create!(company: company, name: "福岡支店") department_8_2 = department_8.children.create!(company: company, name: "鹿児島支店") department_8_3 = department_8.children.create!(company: company, name: "沖縄支店") puts "successed create child departments" ROOT_DEPARTMENT = [ "北海道営業部", "東北営業部", "関東営業部", "北陸営業部", "近畿営業部", "四国営業部", "中国営業部", "九州沖縄営業部" ] # 社員 Department.all.each do |department| # 本部長データの作成(本部長は売り上げを持たない) if ROOT_DEPARTMENT.include?(department.name) gimei = Gimei.name sex = [:male, :female].sample gimei = if sex == :male Gimei.male else Gimei.female end Employee.create!( company: company, position: position_0, department: department, last_name: gimei.last.kanji, first_name: gimei.first.kanji, last_name_kana: gimei.last.katakana, first_name_kana: gimei.first.katakana, sex: sex, hired_at: Faker::Date.between(from: '1990-04-01', to: '2000-04-01') ) puts "successed create #{department.name} account" next end # 支店長以下の役職のデータを作成 4.times do |i| i += 1 position = case i when 1 position_1 when 2 position_2 when 3 position_3 when 4 position_4 end gimei = Gimei.name sex = [:male, :female].sample gimei = if sex == :male Gimei.male else Gimei.female end employee = Employee.create!( company: company, position: position, department: department, last_name: gimei.last.kanji, first_name: gimei.first.kanji, last_name_kana: gimei.last.katakana, first_name_kana: gimei.first.katakana, sex: sex, hired_at: Faker::Date.between(from: '2005-04-01', to: '2018-04-01') ) puts "successed create #{department.name} account" if i == 4 # 売り上げデータの作成 30.times do |i| i += 1 Order.create!( customer_name: Faker::Company.name, employee: employee, company: company, price: "#{rand(10) + 1}000000".to_i, status: Order.statuses.keys.sample, ordered_at: Faker::Date.between(from: '2018-07-01', to: '2021-04-01') ) puts "successed create order" if i == 30 end end end end
Orderモデルにscopeを記述する
find_by_sqlメソッドを使って、SQLを直に叩いていきます。
class Order # 省略 # 社員ごとの累計の売り上げTOP10をランキング付きで出力する scope :get_total_earning_group_by_employee_top_ten, -> () { sub_query = <<-SQL SELECT orders.employee_id, SUM(orders.price) as total_earning FROM orders GROUP BY employee_id SQL query = <<-SQL SELECT employee_id, total_earning, RANK () OVER (ORDER BY total_earning DESC) as ranking FROM (#{sub_query}) as total_earnings LIMIT 10 SQL find_by_sql(query) }
上記で行っていることを解説します。
サブクエリ
sub_query = <<-SQL SELECT orders.employee_id, SUM(orders.price) as total_earning FROM orders GROUP BY employee_id SQL
employee_idでグルーピングを行い、SUM関数で社員ごとの売り上げの総額を求めて、別名(total_earning)を付与しています。
サブクエリ とすることで、後述のSQLはこのサブクエリで構築したビューからデータを引いてくるようにします。
サブクエリ化しているのは、GROUP BYとORDER BYを一緒に使えなかったからです、、、
qiita.com
クエリ
query = <<-SQL SELECT employee_id, total_earning, RANK () OVER (ORDER BY total_earning DESC) as ranking FROM (#{sub_query}) as total_earnings LIMIT 10 SQL find_by_sql(query)
先ほどのサブクエリで構築したビューにtotal_earningsという別名をつけ、employee_id、total_earning、そしてrank関数を使ってランキングをつけています。
ranking列は降順で取得するようにしたので、最も高い売上が上位にきます。
rank関数の基本構文は以下です。
RANK () (ORDER BY ランキングしたい列名 ASC/DESC)
PARTITION BYという関数を使うこともありますが、今回は使っていません。
zukucode.com
LIMIT句はソートされたデータの上から10件を取得しています。トップ10ですね。
そしてfind_by_sql(query)でSQLを直接叩いています。
これでRails consoleで動作確認をしてみましょう。
動作確認
$ rails c $ Order.get_total_earning_group_by_employee_top_ten => [ #<Order:0x000055b201711070 id: nil, employee_id: 13>, #<Order:0x000055b1ff1f3dd8 id: nil, employee_id: 39>, #<Order:0x000055b1ff1f3d10 id: nil, employee_id: 15>, #<Order:0x000055b1ff1f3c48 id: nil, employee_id: 72>, #<Order:0x000055b1ff1f3b58 id: nil, employee_id: 54>, #<Order:0x000055b1ff1f3a68 id: nil, employee_id: 43>, #<Order:0x000055b1ff1f39a0 id: nil, employee_id: 66>, #<Order:0x000055b1ff1f38d8 id: nil, employee_id: 59>, #<Order:0x000055b1ff1f3810 id: nil, employee_id: 11>, #<Order:0x000055b1ff1f3748 id: nil, employee_id: 89> ]
データが引けました。total_earningやrankingがありませんが、
$ Order.get_total_earning_group_by_employee_top_ten.first.ranking => 1 $ Order.get_total_earning_group_by_employee_top_ten.first.total_earning => 210000000
きっちり取得できています。これで全社員の売り上げをランキング化することができました！
日付指定で取得したい
上記のクエリは累計の売り上げからランキングをつけていますが、「月ごとの売り上げをランキング化したい」場合はどうしましょう。
これも簡単で、find_by_sqlは引数に配列を渡すことで、 ? の演算子に値を入れることが可能です。
class Order # 省略 # 社員ごとの累計の売り上げTOP10をランキング付きで日付指定で出力する scope :get_total_earning_group_by_employee_from_to, -> (from, to) { sub_query = <<-SQL SELECT orders.employee_id, SUM(orders.price) as total_earning FROM orders WHERE orders.ordered_at >= ? AND orders.ordered_at < ? GROUP BY employee_id SQL query = <<-SQL SELECT employee_id, total_earning, RANK () OVER (ORDER BY total_earning DESC) as ranking FROM (#{sub_query}) as total_earnings LIMIT 10 SQL find_by_sql([query, from, to]) }
サブクエリにWHERE句を追加して、日付で絞り込みを行えるようにしました。
また、find_by_sqlに配列[query, from, to]を渡したことで、?演算子にfromとtoが入るようになります。
動作確認をしてみます。
動作確認
$ from = Date.today.beginning_of_month $ to = Date.today.end_of_month $ result = Order.get_total_earning_group_by_employee_from_to(from, to) => [ #<Order:0x000055b2006d2d30 id: nil, employee_id: 88>, #<Order:0x000055b2006d28a8 id: nil, employee_id: 103>, #<Order:0x000055b2006d2650 id: nil, employee_id: 27>, #<Order:0x000055b2006d24c0 id: nil, employee_id: 74>, #<Order:0x000055b2006d2358 id: nil, employee_id: 22>, #<Order:0x000055b2006d2268 id: nil, employee_id: 78>, #<Order:0x000055b2006d21a0 id: nil, employee_id: 23>, #<Order:0x000055b2006d20d8 id: nil, employee_id: 60>, #<Order:0x000055b2006d1fe8 id: nil, employee_id: 41>, #<Order:0x000055b2006d1e80 id: nil, employee_id: 44> ] $ result.first.total_earning => 22000000
実行結果はあなたの環境によって異なるかと思いますが、先ほどよりも低い数字が出たかと思われます。
このように、find_by_sqlに配列を渡してやることで、動的に値を取得することができます。
まとめ
find_by_sqlすごいなと思いました。笑
とはいえ、ActiveRecordがサポートしていない関数を使いたい時等に留めるのがいいかと思います。
