SQL、Ruby on Rails、Rubyを使ってGROUP BYとCOUNTによる集計処理をマスター

ActiveRecordでGROUP BYとCOUNTを使って集計処理を行う

このチュートリアルでは、ActiveRecordを使用してSQLのGROUP BY句とCOUNT関数を使って集計処理を行う方法を説明します。

このチュートリアルでは、以下の内容を学習します。

• groupメソッドを使って集計列を指定する方法
• countメソッドを使ってレコードの数をカウントする方法
• 複数のカラムをグループ化する方法
• 集計結果をソートする方法

前提知識

このチュートリアルを理解するには、以下の知識が必要です。

• Ruby on Rails
• ActiveRecord
• SQLの基礎知識

使用するモデル

class Post < ApplicationRecord
  belongs_to :user
end

このモデルは、user_idカラムを持つpostsテーブルに対応しています。

集計処理の例

記事の投稿者ごとの記事数

以下のコードは、記事の投稿者ごとに記事数を集計します。

Post.group(:user_id).count

このコードは、以下のSQLを発行します。

SELECT user_id, COUNT(*) AS count_all
FROM posts
GROUP BY user_id;

このクエリは、user_idカラムごとにレコードをグループ化し、各グループのレコード数をカウントします。結果は、以下のようになります。

{
  1 => 10,
  2 => 5,
  3 => 7
}

特定の条件に合致する記事の投稿者ごとの記事数

Post.where(status: 'published').group(:user_id).count

SELECT user_id, COUNT(*) AS count_all
FROM posts
WHERE status = 'published'
GROUP BY user_id;

{
  1 => 5,
  2 => 3
}

Post.group(:user_id, :category).count

SELECT user_id, category, COUNT(*) AS count_all
FROM posts
GROUP BY user_id, category;

{
  1 => {
    "category1" => 4,
    "category2" => 3
  },
  2 => {
    "category3" => 2
  }
}

Post.group(:user_id).order(count: :desc).count

SELECT user_id, COUNT(*) AS count_all
FROM posts
GROUP BY user_id
ORDER BY count_all DESC;

{
  3 => 7,
  1 => 10,
  2 => 5
}

このチュートリアルで学んだことを参考に、様々な集計処理を構築することができます。

• [ActiveRecordにおけるGROUP BYの使い方 #Ruby - Qiita](https

# 記事の投稿者ごとの記事数
Post.group(:user_id).count

# 特定の条件に合致する記事の投稿者ごとの記事数
Post.where(status: 'published').group(:user_id).count

# 複数のカラムをグループ化
Post.group(:user_id, :category).count

# 集計結果をソート
Post.group(:user_id).order(count: :desc).count

class Post < ApplicationRecord
  belongs_to :user
end

説明

• groupメソッドは、集計列を指定します。引数として、グループ化するカラム名を指定します。
• whereメソッドは、クエリに条件を追加します。引数として、条件式を指定します。

出力例

# 記事の投稿者ごとの記事数
{
  1 => 10,
  2 => 5,
  3 => 7
}

# 特定の条件に合致する記事の投稿者ごとの記事数
{
  1 => 5,
  2 => 3
}

# 複数のカラムをグループ化
{
  1 => {
    "category1" => 4,
    "category2" => 3
  },
  2 => {
    "category3" => 2
  }
}

# 集計結果をソート
{
  3 => 7,
  1 => 10,
  2 => 5
}

ActiveRecordでGROUP BYとCOUNTを使って集計処理を行う：その他の方法

selectメソッドを使用して、明示的に集計カラムを指定することができます。

この方法は、以下の利点があります。

• 集計カラムに名前を付けることができる
• 集計関数以外にも、その他のSQL関数を使用することができます。

Post.group(:user_id).select(:user_id, count: :id, avg: :published_at).order(count: :desc)

SELECT user_id, COUNT(*) AS count, AVG(published_at) AS avg_published_at
FROM posts
GROUP BY user_id
ORDER BY count DESC;

[
  { user_id: 3, count: 7, avg_published_at: "2024-06-20" },
  { user_id: 1, count: 10, avg_published_at: "2024-06-15" },
  { user_id: 2, count: 5, avg_published_at: "2024-06-12" }
]

havingメソッドを使用して、集計結果に対して条件を追加することができます。

• 集計結果に対して、より複雑な条件を設定することができます。

Post.group(:user_id).select(:user_id, count: :id, avg: :published_at).having('count(*) >= 5').order(count: :desc)

SELECT user_id, COUNT(*) AS count, AVG(published_at) AS avg_published_at
FROM posts
GROUP BY user_id
HAVING count(*) >= 5
ORDER BY count DESC;

[
  { user_id: 3, count: 7, avg_published_at: "2024-06-20" },
  { user_id: 1, count: 10, avg_published_at: "2024-06-15" }
]

サブクエリを使用して、より複雑な集計処理を行うことができます。

• 他のテーブルからデータを参照した集計処理を行うことができます。

Post.group(:user_id).select(:user_id, count: :id, comment_count: subquery(Comment.where(post_id: :post_id).count))

SELECT user_id, COUNT(*) AS count, (
  SELECT COUNT(*)
  FROM comments
  WHERE comments.post_id = posts.id
) AS comment_count
FROM posts
GROUP BY user_id;

[
  { user_id: 3, count: 7, comment_count: 15 },
  { user_id: 1, count: 10, comment_count: 22 },
  { user_id: 2, count: 5, comment_count: 8 }
]

上記以外にも、ActiveRecordでGROUP BYとCOUNTを使って集