Rails + SQLでランキング機能を実装

はじめに

ランキング機能を実装するためにActive Recordを利用しようと調べましたが、要件に合うものがなかったため、SQLで実装しました。学習内容のメモとして記載いたします。

開発環境

• Mac
• Ruby 2.7.2
• Rails 6.1.3.1
• PostgreSQL 13.2

前提

現在、お菓子を止めたい人に向けた習慣化アプリを作成しており、そのアプリの一機能として、1ヶ月以内にお菓子を止めた日数でユーザごとのラウンキング機能を実装したいと考えました。ランキングを作成する元となるテーブルは以下となります。（本来はもっとカラムがございますが、今回使用しないカラムにつきましては省略しております。）

実装

ランキング機能の要件は以下の通りです。

• 1ヶ月以内でお菓子を止めた日数の算出方法は以下の通り。
  
  本日 - 月初（もし、月初以降にアプリを使い始めた場合、アプリを使い始めた日） - お菓子を食べてしまった日数
• 同じ順位のユーザが複数いた場合、次の順位のユーザは上位のユーザの順位＋1位とする。
  例えば、1位が2名いた場合、次の順位は2位とする。
• ランキングに表示可能なユーザは最大100名までとする。

まずは以下の要件から実装していきます。

• 1ヶ月以内でお菓子を止めた日数を算出方法は以下計算式。
  本日 - 月初（もし、月初以降にアプリを使い始めた場合、アプリを使い始めた日） - お菓子を食べてしまった日数

上記を実装するにあたり必要なSQLを調べたところ、以下です。

• 今日の日付を出力するためには「CURRENT_DATE」

• 月初を出力するためには「DATE_TRUNC('MONTH', NOW())」

• 「本日　- 月初」を行うために、「CURRENT_DATE」はDate型であり、「DATE_TRUNC('MONTH', NOW())」はTimestamp型のため、データ型を揃える必要があります。「DATE_TRUNC('MONTH', NOW())」をTimestamp型からDate型に変換するために、「CAST(カラム)) AS DATE」で行えます。

posticoで実際にSQLを実行してみたところ、想定通りの日数が出力されました。記事を執筆したのは11/14のため、月初である11/1から引き算したら、13が出力されております。

以下についても考えてみます。ユーザがもし11/3のように月初よりも後にサービスを使用し始めた場合は上記のSQLでは対応できないため、条件式で実装できると思いました。

もし、月初以降にアプリを使い始めた場合、アプリを使い始めた日

以下のように条件式CASE WHENを使用して、ユーザがアプリに登録した日（カラム名：CREATED_AT）が月初よりも後日の場合、「本日 - アプリを使い始めた日」で計算します。条件に一致しない場合、「本日 - 月初」で計算します。

SELECT
  CASE
      WHEN [条件] THEN [条件を満たしたら表示する内容]
      ELSE [条件を満たしていなかったら表示する内容]
  END
FROM [テーブル名];

• 1ヶ月以内でお菓子を止めた日数を算出方法は以下計算式。
  本日 - 月初（もし、月初以降にアプリを使い始めた場合、アプリを使い始めた日） - お菓子を食べてしまった日数

上記の内容を実装してみると以下SQLになります。

SELECT ID, NAME, IMAGE,
  (CASE
      WHEN CAST(CREATED_AT AS DATE) > CAST(DATE_TRUNC('MONTH', NOW()) AS DATE)
      THEN CURRENT_DATE - CAST(CREATED_AT AS DATE) - EAT_DAY_MONTH
      ELSE CURRENT_DATE - CAST(DATE_TRUNC('MONTH', NOW()) AS DATE) - EAT_DAY_MONTH END) AS STOP_DAY
FROM USERS

続いては以下について考えてみます。

• 同じ順位のユーザが複数いた場合、次の順位のユーザは上位のユーザの順位＋1位とする。
  例えば、1位が2名いた場合、次の順位は2位とする。

「DENSE_RANK()」というウィンドウ関数を使用すれば実装ができることが分かりました。

SELECT
    DENSE_RANK() OVER (ORDER BY [カラム名] DESC)
FROM [テーブル名];

最後に以下について考えてみます。今後アプリを使用するユーザ数が増えた場合にランキングに表示するユーザ数に制限を設けていないと、アプリのパフォーマンスに影響が出てしまうため、100名まで表示を絞ることにしました。

• ランキングに表示可能なユーザ数は最大100名までとする。

以下のマニュアルの通り、「LIMIT」を使用すれば、表示数を制限できます。

また、SQLをRailsのコントローラーで実装するためにはどのように行えば良いのか調べたところ、以下を参考にしたところ、「モデル.find_by_sql(SQL文)」で実装できることがわかりました。コントローラーでSQLを実装後にビューでユーザのランキングだけでなく、ユーザ名やユーザのアイコン画像もブラウザー上に表示させたいため、「select_all」ではなく、「find_by_sql」の方が実装しやすいと思いました。ビュー側でユーザ名を表示するため、「user.name」やユーザアイコンを表示するために「user.image.url」などを実行したかったためです。

結果的に以下のようなコードになりました。

コントローラー

  def index
    @users = User.find_by_sql(
    "SELECT *, DENSE_RANK() OVER(ORDER BY STOP_DAY DESC) AS RANK
     FROM (SELECT ID, NAME, IMAGE,
            (CASE
              WHEN CAST(CREATED_AT AS DATE) > CAST(DATE_TRUNC('MONTH', NOW()) AS DATE)
              THEN CURRENT_DATE - CAST(CREATED_AT AS DATE) - EAT_DAY_MONTH
              ELSE CURRENT_DATE - CAST(DATE_TRUNC('MONTH', NOW()) AS DATE) - EAT_DAY_MONTH END) AS STOP_DAY
           FROM USERS) AS STOP_DAY_COUNT
     LIMIT 100")
  end

まとめ

今回の経験で発想の転換が大切だと思いました。今回の記事を作成する前はActive Recordとrubyでランキング機能を実装していたため、コントローラーだけでなく、ビューにもロジックを書いており、役割分担が出来ていませんでした。また、コード量も多く、後で見直してもどのような処理が行われているのか思い出すまでに時間がかかってしまい、可読性が悪かったです。発想を変えてSQLで複雑な処理を書くことで、ビュー側にロジックを書かずにコード量も以前より少なくできました。まずはActive Recordで実装できないかを検討し、Active Recordでも実装できない場合はSQLで実装することが良いのかと思いました。今後とも現状に満足せずに改善することを心がけていきたいです。