SQL 第2版 ゼロからはじめるデータベース操作（ミック） ｜ 翔泳社の本を読んでいるけどSQLが今ひとつわからない。{dplyr}に対応付ければきっと理解できると思うので詰まったらメモしていく。
支度を整える
ローカルにPostgreSQL 9.6.2がインストールしてある状態。とりあえず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は適宜書き換え。データベースはテキストに従って作ってあるものとする。
クエリを送って実行するのはこんな感じ。
> 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()でクエリを送って実行できる。
tbl(con_dp, sql(query))
dplyrはいつものdplyrだけどshow_query()に送ればクエリを確認できる。
> 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)の平均値を算出し、それを商品毎に販売単価と比較して、分類毎の平均単価より平均単価が高い商品を抽出したい。
> 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を対応付けるのがポイント。
> 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}のほうが少し特殊。
select str1, str2,
str1 || str2 as str_concat
from samplestr;
dplyrの例はDatabasesのSQL transrationの部分に説明があるが、クエリに変換される際、{dplyr}が変換方法を知らない関数はそのまま送られるので、例えばpaste()やpaste0()を使ってもダメ。これらは自動で変換されないし、同名の関数はPostgreSQLには無いので。
samplestr %>%
transmute(str1, str2,
str_concate = paste0(str1, str2))
逆に言えば、Rに無かろうがDBMSが解釈できる関数なら使えるということ。二項演算子であれば%||%のようにパーセント記法を使う。
samplestr %>%
transmute(str1, str2,
str_concat = (str1 %||% str2))
もちろん次の形でもOK。
samplestr %>%
transmute(str1, str2,
str_concat = concat(str1, str2))
SUBSTRING
これはRが特殊な例。
select str1,
substring(str1 from 3 for 2) as sbu_str
from samplestr;
dplyrは次の形では通らない。
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)
samplestr %>%
transmute(str1,
sbu_str = substring(str1 %from% 3L %for% 2L))
DISTINCT
DISTINCTは重複を除いた結果を返す。
select distinct hanbai_tanka from shohin;
shohin_tbl %>% distinct(hanbai_tanka)
最初select()とunique()組み合わせてやろうとして少し悩んでしまったけど、落ち着いて探したらそのままの関数あった。