３．副問い合わせをSELECT文の後に使う-SQL経験者のためのR入門

SELECT文のフィールドリスト内で副問い合わせ（スカラーサブクエリ）を使用するケースについて、R言語での対応方法を解説します。

---

🔍 SELECT句での副問い合わせ（スカラーサブクエリ）

最初にSQL文から提示します。

このサンプルでは、Employees（従業員）テーブルとDepartments（部署）テーブルがあるとし、各従業員のレコードに対し、その従業員が所属する部署の平均給与を付加するケースを考えます。

---

1. テーブル構成

テーブル名	列名	説明
Employees	EmployeeID	従業員ID（主キー）
	Name	従業員名
	Salary	給与
	DepartmentID	部署ID（外部キー）
Departments	DepartmentID	部署ID（主キー）
	DepartmentName	部署名

---

データのイメージです

#   EmployeeID    Name Salary DepartmentID
# 1          1   Alice  60000          101
# 2          2     Bob  75000          102
# 3          3 Charlie  50000          101
# 4          4   David  85000          103
# 5          5     Eve  70000          102
# 6          6   Frank  55000          101

2. SQLクエリ

SQL

SELECT
    E.Name,
    E.Salary,
    (
        SELECT AVG(Salary)
        FROM Employees AS E2
        WHERE E2.DepartmentID = E.DepartmentID
    ) AS AvgDepartmentSalary
FROM
    Employees AS E;

SQLの動作

1. 主クエリは Employees テーブル (E) の各行を順に処理します。
2. 各行について、副問い合わせ (SELECT AVG(Salary) FROM Employees AS E2 WHERE E2.DepartmentID = E.DepartmentID) が実行されます。
   • この副問い合わせは、現在処理中の従業員 (E) と同じ部署 (E.DepartmentID) の従業員 (E2) の平均給与を計算し、単一の値として返します。
3. その結果が AvgDepartmentSalary という新しい列の値として、元の従業員行に追加されます。

R言語 (dplyr) での対応方法

R言語の dplyr パッケージでは、この「グループごとの集計値を元の行に保持したまま追加する」操作は、group_by() と mutate() の組み合わせで最も効率的かつ直感的に実現されます。

result_df <- Employees %>%
  # 1. DepartmentIDでデータをグループ化します (SQLの相関副問い合わせのWHERE句に相当)
  group_by(DepartmentID) %>%
  # 2. グループごとに新しい列を追加します
  #    mutate()はグループ化されている場合、グループ内の集計値を計算し、
  #    その値をグループ内のすべての行に適用します (SQLのSELECT句の副問い合わせに相当)
  mutate(
    AvgDepartmentSalary = mean(Salary)
  ) %>%
  # 3. 必要に応じて、グループ化を解除します (後続の処理でグループを意識しなくて良くなる)
  ungroup() %>%
  # 4. 表示のために列を選択します (SQLのSELECT句の列選択に相当)
  select(Name, Salary, DepartmentID, AvgDepartmentSalary)

print(result_df)

🚀 Rコードの解説

dplyr 関数	SQLの役割	動作の説明
group_by(DepartmentID)	WHERE E2.DepartmentID = E.DepartmentID の関連付け	データフレームを部署IDで区切ります。これ以降の操作は、各部署グループ内でのみ実行されます。
mutate(...)	SELECT AVG(Salary) の計算と列の追加	グループ化されたデータフレームに対して新しい列を追加します。mean(Salary) はグループ内で実行されるため、SQLの副問い合わせの集計結果と同じになります。
ungroup()	(明示的なSQL操作なし)	後続の操作（例：filter や arrange）がグループを意識しないように、グループ化を解除します。

ご提示のコードの実行ステップ

上記のRコードを、作成されたデータフレームの動作に沿って解説します。

1. グループ化:REmployees %>% group_by(DepartmentID) データが以下のように論理的に分割されます。
   • グループ 101: Alice, Charlie, Frank
   • グループ 102: Bob, Eve
   • グループ 103: David
2. 新しい列の作成 (mutate):Rmutate(AvgDepartmentSalary = mean(Salary))
   • グループ 101 の平均: $(60000 + 50000 + 55000) / 3 = 55000$
   • グループ 102 の平均: $(75000 + 70000) / 2 = 72500$
   • グループ 103 の平均: $85000 / 1 = 85000$
   この計算結果が、グループ内のすべての行に新しい列として追加されます。
3. 最終結果:

Name	Salary	DepartmentID	AvgDepartmentSalary
Alice	60000	101	55000
Charlie	50000	101	55000
Frank	55000	101	55000
Bob	75000	102	72500
Eve	70000	102	72500
David	85000	103	85000

まとめ

SQLの相関サブクエリは、R言語では**group_by() と mutate() の組み合わせ**によって、極めて簡潔かつ高速に再現できます。このパターンは、Rでのデータ分析において非常に重要で基本的なテクニックです。