SQL 第2版ゼロからはじめるデータベース操作（ミック）｜翔泳社の本を読んでいるけどSQLが今ひとつわからない。{dplyr}に対応付ければきっと理解できると思うので詰まったらメモしていく。

支度を整える

ローカルにPostgreSQL 9.6.2がインストールしてある状態。とりあえずRから繋ぐ。

パッケージの準備.R

install.packages("DBI")
install.packages("RPostgreSQL")
install.packages("dplyr")
library(RPostgreSQL)
library(dplyr)

クエリを送って実行するためには{RPostgreSQL}等のパッケージで接続する。

# RPostgreSQL
con <- dbConnect(PostgreSQL(),
                 host = "localhost",
                 port = 5432,
                 dbname = "shop",
                 user = "your_user_name",
                 password = "your_password")

{dplyr}からも接続できて、この場合はdplyrっぽい書き方からクエリが生成されて実行されるので、よく考えるとこれで事は済む気もする。

# dplyr
con_dp <- src_postgres(
  host = "localhost",
  port = 5432,
  dbname = "shop",
  user = "your_user_name",
  password = "your_password")
shohin_tbl <- tbl(con_dp, from = "shohin")

userとpasswordは適宜書き換え。データベースはテキストに従って作ってあるものとする。

クエリを送って実行するのはこんな感じ。

クエリの実行_RPostgreSQL.R

> query <- "
+ select * from shohin;
+ "
> dbGetQuery(con, query)
  shohin_id     shohin_mei shohin_bunrui hanbai_tanka shiire_tanka   torokubi shohin_mei_kana
1      0001        Tシャツ          衣服         1000          500 2009-09-20            <NA>
2      0002   穴あけパンチ      事務用品          500          320 2009-09-11            <NA>
3      0003 カッターシャツ          衣服         4000         2800       <NA>            <NA>
4      0004           包丁  キッチン用品         3000         2800 2009-09-20            <NA>
5      0005         圧力鍋  キッチン用品         6800         5000 2009-01-15            <NA>
6      0006       フォーク  キッチン用品          500           NA 2009-09-20            <NA>
7      0007     おろしがね  キッチン用品          880          790 2009-04-28            <NA>
8      0008     ボールペン      事務用品          100           NA 2009-11-11            <NA>

dplyrからもtbl()とsql()でクエリを送って実行できる。

クエリの実行_dplyr

tbl(con_dp, sql(query))

dplyrはいつものdplyrだけどshow_query()に送ればクエリを確認できる。

クエリの実行_dplyr.R

> shohin_tbl
Source:   query [?? x 7]
Database: postgres 9.6.2 [nozma@localhost:5432/shop]

  shohin_id     shohin_mei shohin_bunrui hanbai_tanka shiire_tanka   torokubi shohin_mei_kana
      <chr>          <chr>         <chr>        <int>        <int>     <date>           <chr>
1      0001        Tシャツ          衣服         1000          500 2009-09-20            <NA>
2      0002   穴あけパンチ      事務用品          500          320 2009-09-11            <NA>
3      0003 カッターシャツ          衣服         4000         2800       <NA>            <NA>
4      0004           包丁  キッチン用品         3000         2800 2009-09-20            <NA>
5      0005         圧力鍋  キッチン用品         6800         5000 2009-01-15            <NA>
6      0006       フォーク  キッチン用品          500           NA 2009-09-20            <NA>
7      0007     おろしがね  キッチン用品          880          790 2009-04-28            <NA>
8      0008     ボールペン      事務用品          100           NA 2009-11-11            <NA>
> shohin_tbl %>% show_query
<SQL>
SELECT *
FROM "shohin"

SQL vs `{dplyr}`

相関サブクエリ

ここでは、商品分類(shohin_bunrui)毎に販売単価(hanbai_tanka)の平均値を算出し、それを商品毎に販売単価と比較して、分類毎の平均単価より平均単価が高い商品を抽出したい。

SQL

> query <- "
+ select shohin_bunrui, shohin_mei, hanbai_tanka
+ from shohin as s1
+ where hanbai_tanka > (select avg(hanbai_tanka)
+                       from shohin as s2
+                       where s1.shohin_bunrui = s2.shohin_bunrui
+                       group by shohin_bunrui);
+ "
> dbGetQuery(con, query)
  shohin_bunrui     shohin_mei hanbai_tanka
1      事務用品   穴あけパンチ          500
2          衣服 カッターシャツ         4000
3  キッチン用品           包丁         3000
4  キッチン用品         圧力鍋         6800

サブクエリの中のshohinと外のshohinを対応付けるのがポイント。

dplyr

> shohin_tbl %>%
+   group_by(shohin_bunrui) %>%
+   filter(hanbai_tanka > mean(hanbai_tanka)) %>%
+   select(shohin_bunrui, shohin_mei, hanbai_tanka)
Source:   query [?? x 3]
Database: postgres 9.6.2 [nozma@localhost:5432/shop]
Groups: shohin_bunrui

  shohin_bunrui     shohin_mei hanbai_tanka
          <chr>          <chr>        <int>
1          衣服 カッターシャツ         4000
2      事務用品   穴あけパンチ          500
3  キッチン用品         圧力鍋         6800
4  キッチン用品           包丁         3000

dplyrではSQLと逆っぽい順序になる。この例ではselectを先に実行しても支障はないが、グループ化に使う変数や集計に使う変数を必ずしも表示したいとは限らない点を考慮すると後回しにするのが懸命。

実際に生成されてるクエリはこんな感じ。

> shohin_tbl %>%
+   group_by(shohin_bunrui) %>%
+   filter(hanbai_tanka > mean(hanbai_tanka)) %>%
+   select(shohin_bunrui, shohin_mei, hanbai_tanka) %>% show_query
<SQL>
SELECT "shohin_bunrui" AS "shohin_bunrui", "shohin_mei" AS "shohin_mei", "hanbai_tanka" AS "hanbai_tanka"
FROM (SELECT "shohin_id", "shohin_mei", "shohin_bunrui", "hanbai_tanka", "shiire_tanka", "torokubi", "shohin_mei_kana"
FROM (SELECT "shohin_id", "shohin_mei", "shohin_bunrui", "hanbai_tanka", "shiire_tanka", "torokubi", "shohin_mei_kana", avg("hanbai_tanka") OVER (PARTITION BY "shohin_bunrui") AS "zzz10"
FROM "shohin") "liiegqoyxv"
WHERE ("hanbai_tanka" > "zzz10")) "donsqvrcih"

文字列連結

これはどちらかというと{dplyr}のほうが少し特殊。

postgreSQL

select str1, str2,
       str1 || str2 as str_concat
  from samplestr;

dplyrの例はDatabasesのSQL transrationの部分に説明があるが、クエリに変換される際、{dplyr}が変換方法を知らない関数はそのまま送られるので、例えばpaste()やpaste0()を使ってもダメ。これらは自動で変換されないし、同名の関数はPostgreSQLには無いので。

dplyr

samplestr %>%
  transmute(str1, str2,
            str_concate = paste0(str1, str2))

逆に言えば、Rに無かろうがDBMSが解釈できる関数なら使えるということ。二項演算子であれば%||%のようにパーセント記法を使う。

dplyr

samplestr %>%
  transmute(str1, str2, 
            str_concat = (str1 %||% str2))

もちろん次の形でもOK。

samplestr %>%
  transmute(str1, str2, 
            str_concat = concat(str1, str2))

SUBSTRING

これはRが特殊な例。

PostgreSQL

select str1,
  substring(str1 from 3 for 2) as sbu_str
from samplestr;

dplyrは次の形では通らない。

dplyr_wrong

samplestr %>%
  transmute(str1, 
            sbu_str = substring(str1 %from% 3 %for% 2))

なぜかというと、Rでは数値をそのまま入力するとnumericになるので、例えば3は3.0としてクエリに変換されてしまう。

Rでintegerを得る方法はいくつかあるので、いずれかで対処する。

末尾にLをつける: 3L
コロン演算子を使う: 3:3
as.integer()を使う: as.integer(3)

dplyr

samplestr %>%
  transmute(str1, 
            sbu_str = substring(str1 %from% 3L %for% 2L))

DISTINCT

DISTINCTは重複を除いた結果を返す。

SQL

select distinct hanbai_tanka from shohin;

dplyr

shohin_tbl %>% distinct(hanbai_tanka)

最初select()とunique()組み合わせてやろうとして少し悩んでしまったけど、落ち着いて探したらそのままの関数あった。