🐬

Rのdbplyrでサブクエリを構築すると分かりやすい

2024/05/06に公開

MySQL

SQL

サブクエリ

tech

本記事は最近読んだ次の記事からインスピレーションを得ました。

RのdplyrやPythonのpolarsのようなパッケージでデータフレームの操作に慣れている人ならば、Rのdbplyrを使うことで、バグが少ない上に早くサブクエリを構築することができます。

何千回も実行するSQLならば時間をかけてチューニングされたSQLを構築したほうがよいと思いますが、分析の試行錯誤のサイクルを早く回したい場合など数十回ぐらいしか実行しないSQLならば、dbplyrから実行したほうがよいでしょう。

それではざっくり元記事に沿って例を説明します。

カラムのサブクエリ

大分類（major_category）で絞って、該当する作品を表示する例をお借りします。

まず素直にms_categoriesテーブルから該当するcategory_idを抜き出しておいて、%in%で求めると、

category_id_fiction <- tbl_ms_categories |> 
  filter(major_category == 'フィクション') |> 
  pull(category_id)

tbl <- tbl_works |>
  filter(category_id %in% category_id_fiction) |> 
  select(title, category_id)

tbl |> show_query()

SELECT `title`, `category_id`
FROM `works`
WHERE (`category_id` IN (3, 5, 6))

となり、INのところにcategory_idが展開されます。これが嫌な場合には、inner_joinを使うのがよいと思います。

tbl_fiction <- tbl_ms_categories |>
  filter(major_category == 'フィクション') |>
  select(category_id)

tbl <- tbl_works |> 
  inner_join(tbl_fiction, by = 'category_id') |> 
  select(title, category_id)

tbl |> show_query()

SELECT `title`, `works`.`category_id` AS `category_id`
FROM `works`
INNER JOIN (
  SELECT `category_id`
  FROM `ms_categories`
  WHERE (`major_category` = 'フィクション')
) AS `RHS`
  ON (`works`.`category_id` = `RHS`.`category_id`)

テーブルのサブクエリ（導出テーブル）

作者IDとカテゴリで検索するクエリの例をお借りします。

テーブルusersからフォロワー100以上で絞ってtbl_users_follower100を作り、テーブルworksからcategory_idで絞ってからinner_joinで結合するのが分かりやすいです。

tbl_users_follower100 <- tbl_users |>
  filter(follower_num >= 100L)

tbl <- tbl_works |>
  filter(category_id == 2L) |>
  inner_join(tbl_users_follower100, by = 'user_id')

tbl |> show_query()

SELECT `LHS`.*, `follower_num`
FROM (
  SELECT `works`.*
  FROM `works`
  WHERE (`category_id` = 2)
) AS `LHS`
INNER JOIN (
  SELECT `users`.*
  FROM `users`
  WHERE (`follower_num` >= 100)
) AS `RHS`
  ON (`LHS`.`user_id` = `RHS`.`user_id`)

外部を参照するサブクエリ（相関サブクエリ）

自己結合する相関サブクエリ

元記事の例はfollower_numの大きさでソートすればよさそうで恩恵がしっくりこないので、このQiitaの記事の例を使わせていただきます。

集約した情報を使ってフィルターするなどの処理はデータフレームの得意とする部分です。先に集約したテーブル（tbl_avg_age）を作っておいて、あとからleft_joinしてfilterすればOKです。

ungroupはグループ情報を解除するための関数で必要ない場面も多々ありますが、たまにハマるので慣れていない人は常に入れる癖にしたほうがよいでしょう（出力されるSQL文には影響しません）。

tbl_avg_age <- tbl_Employees |>
  group_by(department) |> 
  summarise(avg_age = mean(age)) |> 
  ungroup()

tbl <- tbl_Employees |>
  left_join(tbl_avg_age, by = 'department') |> 
  filter(age <= avg_age)

tbl |> show_query()

SELECT `q01`.*
FROM (
  SELECT `Employees`.*, `avg_age`
  FROM `Employees`
  LEFT JOIN (
    SELECT `department`, AVG(`age`) AS `avg_age`
    FROM `Employees`
    GROUP BY `department`
  ) AS `RHS`
    ON (`Employees`.`department` = `RHS`.`department`)
) AS `q01`
WHERE (`age` <= `avg_age`)

多段でEXISTSするサブクエリ

1つ以上の市に存在するのはどのような種類のお店ですか？

1つ以上の市に存在する店の種類のテーブル（tbl_exist）を作っておいて、あとからsemi_joinすればOKです。semi_joinはinner_joinと似ていますが、返される列が左辺のテーブルのみになります。

tbl_exist <- tbl_cities_stores |>
  select(store_type) |> 
  distinct()

tbl <- tbl_stores |>
  select(store_type) |> 
  distinct() |> 
  semi_join(tbl_exist, by = 'store_type')

tbl |> show_query()

SELECT `LHS`.*
FROM (
  SELECT DISTINCT `store_type`
  FROM `stores`
) AS `LHS`
WHERE EXISTS (
  SELECT 1 FROM (
  SELECT DISTINCT `store_type`
  FROM `cities_stores`
) AS `RHS`
  WHERE (`LHS`.`store_type` = `RHS`.`store_type`)
)

どの市にも存在しないのはどのような種類のお店ですか？

1つ以上の市に存在する店の種類のテーブル（tbl_exist）を作っておいて、あとからanti_joinすればOKです。anti_joinは右辺に存在しなかったものを返します。

tbl_exist <- tbl_cities_stores |>
  select(store_type) |> 
  distinct()

tbl <- tbl_stores |>
  select(store_type) |> 
  distinct() |> 
  anti_join(tbl_exist, by = 'store_type')

tbl |> show_query()

SELECT `LHS`.*
FROM (
  SELECT DISTINCT `store_type`
  FROM `stores`
) AS `LHS`
WHERE NOT EXISTS (
  SELECT 1 FROM (
  SELECT DISTINCT `store_type`
  FROM `cities_stores`
) AS `RHS`
  WHERE (`LHS`.`store_type` = `RHS`.`store_type`)
)

すべての市に存在するのはどのような種類のお店ですか？

元記事とは異なるアプローチで求めます。すべての市の数N_citiesを求めておきます（ここでは10とします）。cities_storesからcityとstore_typeを抜き出してdistinctしたのちに、store_typeごとに市の数を集計して求めます（N）。その市の数がすべての市の数と一致した行を抜き出せばOKです。MySQLの公式よりこっちの方が自然と思ったのですがいかがでしょうか。

N_cities <- 10L

tbl <- tbl_cities_stores |>
  select(city, store_type) |> 
  distinct() |> 
  group_by(store_type) |> 
  summarise(N = n()) |> 
  ungroup() |> 
  filter(N == N_cities) |> 
  select(store_type)

tbl |> show_query()

SELECT `store_type`
FROM (
  SELECT DISTINCT `cities_stores`.*
  FROM `cities_stores`
) AS `q01`
GROUP BY `store_type`
HAVING (COUNT(*) = 10)

ラテラル導出テーブル（FROM句で外部を参照するテーブルのサブクエリ）

ユーザーごとに一番人気の作品を出す例をお借りします。

さきにユーザーごとに一番人気の作品のテーブル（tbl_favorite）を作っておいて、あとからleft_joinなりで結合すればOKでしょう。slice_max関数によってtotal_starが最大の行を抜き出します。with_tiesオプションをFALSEにすると、star最大が複数作品あったとしてもはじめの作品をとります。

tbl_favorite <- tbl_works |>
  group_by(user_id) |> 
  slice_max(total_star, with_ties = FALSE) |> 
  ungroup() |> 
  select(user_id, title, total_star)

tbl <- tbl_users |> 
  left_join(tbl_favorite, by = 'user_id') |> 
  select(pen_name, title, total_star)

tbl |> show_query()

SELECT `pen_name`, `title`, `total_star`
FROM `users`
LEFT JOIN (
  SELECT `user_id`, `title`, `total_star`
  FROM (
    SELECT
      `works`.*,
      ROW_NUMBER() OVER (PARTITION BY `user_id` ORDER BY `total_star` DESC) AS `col01`
    FROM `works`
  ) AS `q01`
  WHERE (`col01` <= 1)
) AS `RHS`
  ON (`users`.`user_id` = `RHS`.`user_id`)

参考資料

大変参考になりました。共感しています。

Enjoy!