あらびき日記

RとSQLを対応付けてみた

R

2011/06/15 追記: subsetの例とdroplevelsについての補足を追加しました

いくつかの言語を知っていると、あの言語のこれに相当することってどうやったらできるのかと思うことがよくありますよね。

最近SQLみたいな操作をRでやるにはどうすればいいんだろ？と思うことがあったのでまとめてみました。

SQLはどの言語を使っている人でも比較的馴染みがあると思うんで、Rを知らない人でも何をやっているかわかりやすいですよねー

準備

サンプルデータとして次のものを使用します。

set.seed(1000)
product <- data.frame(id = 101:110, name = sample(LETTERS, 10), price = sample(5:20 * 100, 10))
n <- 100
makeOrder <- function(term, n) {
    data.frame(order_id = sort(sample(seq(1001, len = n * 2), n)),
               date = sort(sample(paste("2011-05-", term, sep = ""), n, replace = TRUE)),
               customer_id = sample(10001:10100, n, replace = TRUE),
               product_id = sample(product$id, n, replace = TRUE),
               number = sample(1:5, n, replace = TRUE))
}

order1 <- makeOrder(21:24, n)
order2 <- makeOrder(25:28, n)

受注に関するデータと、商品に関するデータといった感じです。

order1, order2 は同じ構造のデータで、order_id（伝票番号みたいなもの）、date（受注日）、customer_id（顧客ID）、product_id（製品ID）、number（購入個数）から成り、productはid（製品ID）、name（製品名）、price（価格）から成ります。

> head(order1)
+----------+------------+-------------+------------+--------+
| order_id | date       | customer_id | product_id | number |
+----------+------------+-------------+------------+--------+
|     1002 | 2011-05-21 |       10078 |        106 |      3 |
|     1003 | 2011-05-21 |       10038 |        109 |      4 |
|     1004 | 2011-05-21 |       10039 |        109 |      4 |
|     1006 | 2011-05-21 |       10036 |        101 |      1 |
|     1007 | 2011-05-21 |       10042 |        110 |      3 |
|     1009 | 2011-05-21 |       10060 |        110 |      1 |
+----------+------------+-------------+------------+--------+
> head(order2)
+----------+------------+-------------+------------+--------+
| order_id | date       | customer_id | product_id | number |
+----------+------------+-------------+------------+--------+
|     1002 | 2011-05-25 |       10036 |        106 |      3 |
|     1003 | 2011-05-25 |       10031 |        106 |      4 |
|     1004 | 2011-05-25 |       10039 |        106 |      5 |
|     1005 | 2011-05-25 |       10024 |        108 |      5 |
|     1006 | 2011-05-25 |       10025 |        101 |      4 |
|     1007 | 2011-05-25 |       10099 |        109 |      1 |
+----------+------------+-------------+------------+--------+
> head(product)
+-----+------+-------+
| id  | name | price |
+-----+------+-------+
| 101 | I    |  1000 |
| 102 | S    |  1600 |
| 103 | C    |   900 |
| 104 | P    |  1900 |
| 105 | L    |  1400 |
| 106 | B    |   500 |
+-----+------+-------+

※表示には拙作のprint.mysqlike関数を使っています

確認のため、今回は次のようにRからMySQLにorder1, order2, productというテーブルを作成し、データを追加しました。

m <- dbDriver("MySQL")
con <- dbConnect(m, dbname = "test", user = "test")
dbGetQuery(con, "CREATE TABLE product (id INT PRIMARY KEY, name CHAR(1), price INT)")
dbGetQuery(con, "CREATE TABLE order1 (order_id INT PRIMARY KEY, date DATE, customer_id INT, product_id INT, number INT)")
dbGetQuery(con, "CREATE TABLE order2 (order_id INT PRIMARY KEY, date DATE, customer_id INT, product_id INT, number INT)")
dbWriteTable(con, "product", product, append = TRUE, row.names = FALSE)
dbWriteTable(con, "order1", order1, append = TRUE, row.names = FALSE)
dbWriteTable(con, "order2", order2, append = TRUE, row.names = FALSE)

ちななみに今回使ったコードは

https://gist.github.com/793bfe6b193afdc0a6fb

にアップしてあるので、実際に実行してみたい方はお使いください。

※MySQLの結果を見たければRMySQLをインストールしておくと便利です（要libmysql）

基本操作

列の抽出

SQL

SELECT order_id, customer_id FROM order1

R

order1[c("order_id", "customer_id")]

LIMIT

SQL

SELECT * FROM order1 LIMIT 5

R

head(order1, n = 5)

---

SQL

SELECT * FROM order1 LIMIT 2, 5

R

order1[seq(3, len = 5),]

---

DISTINCT

SQL

SELECT DISTINCT customer_id FROM order1

R

unique(order1["customer_id"])

COUNT

SQL

SELECT COUNT(*) FROM order1

R

nrow(order1)

WHERE

1つの条件

SQL

SELECT * FROM order1 WHERE customer_id = 10038

R

order1[order1$customer_id == 10038,]

または

subset(order1, customer_id == 10038)

※subsetが最終的にやるのは order1[order1$customer_id == 10038,] と同じなので、余計な処理がある分若干遅い

AND条件

SQL

SELECT * FROM order1 WHERE customer_id = 10038 AND date = '2011-05-21'

R

order1[order1$customer_id == 10038 & order1$date == "2011-05-21",]

または

subset(order1, customer_id == 10038 & order1$date == "2011-05-21")

OR条件

SQL

SELECT * FROM order1 WHERE customer_id = 10038 OR customer_id = 10050

R

order1[order1$customer_id == 10038 | order1$customer_id == 10050,]

または

subset(order1, customer_id == 10038 | customer_id == 10050)

IN

SQL

SELECT * FROM order1 WHERE customer_id in (10038, 10050, 10079)

R

order1[order1$customer_id %in% c(10038, 10050, 10079),]

または

subset(order1, customer_id %in% c(10038, 10050, 10079))

ORDER BY

1つのフィールドでソート

SQL

SELECT * FROM order1 ORDER BY customer_id

R

order1[order(order1$customer_id),]

2つのフィールドでソート

SQL

SELECT * FROM order1 ORDER BY customer_id, product_id

R

order1[order(order1$customer_id, order1$product_id),]

JOIN（完全に一致というわけではない…）

INNER JOIN

SQL

SELECT * FROM order1, order2 WHERE order1.order_id = order2.order_id

R

merge(order1, order2, by = "order_id")

LEFT JOIN

SQL

SELECT * FROM order1 LEFT JOIN order2 USING (order_id)

R

merge(order1, order2, by = "order_id", all.x = TRUE)

RIGHT JOIN

SQL

SELECT * FROM order1 RIGHT JOIN order2 USING (order_id)

R

merge(order1, order2, by = "order_id", all.y = TRUE)

FULL JOIN

SQL（MySQLにFULL JOINはない）

MySQL not support FULL JOIN

R

merge(order1, order2, by = "order_id", all = TRUE)

GROUP BY（方法はいろいろあるけれど…）

1つのフィールドでグループ化してCOUNT

SQL

SELECT customer_id, COUNT(*) FROM order1 GROUP BY customer_id

R

as.data.frame(table(order1$customer_id))

2つのフィールドでグループ化してCOUNT

SQL

SELECT customer_id, date, COUNT(*) FROM order1 GROUP BY customer_id, date

R

as.data.frame(table(order1$customer_id, order1$date))

---

1つのフィールドでグループ化して平均

SQL

SELECT customer_id, AVG(number) FROM order1 GROUP BY customer_id

R

aggregate(order1$number, list(order1$customer_id), mean)

---

2つのフィールドでグループ化して平均

SQL

SELECT customer_id, AVG(number) FROM order1 GROUP BY customer_id, date

R

aggregate(order1$number, list(order1$customer_id, order1$date), mean)

---

ざっとこんなもんところかと思います！

SQLと違ってRは結果を変数として持っておくことができるんで、サブクエリ的な操作も必要ないですし。

これでSQLを知ってる人はRが使えるようになったも同然ですね！！

補足

特定の行を抽出した場合、因子の水準で使われないものが出てくることがありますが、そのような水準も依然として残っています

> subset(order1, customer_id == 10038)
+----------+------------+-------------+------------+--------+
| order_id | date       | customer_id | product_id | number |
+----------+------------+-------------+------------+--------+
|     1003 | 2011-05-21 |       10038 |        109 |      4 |
|     1025 | 2011-05-22 |       10038 |        109 |      5 |
|     1038 | 2011-05-22 |       10038 |        103 |      2 |
+----------+------------+-------------+------------+--------+
> subset(order1, customer_id == 10038)$date
[1] 2011-05-21 2011-05-22 2011-05-22
Levels: 2011-05-21 2011-05-22 2011-05-23 2011-05-24

こんな時はdroplevelsを使うと使われていない水準がなくなります

> droplevels(subset(order1, customer_id == 10038))$date
[1] 2011-05-21 2011-05-22 2011-05-22
Levels: 2011-05-21 2011-05-22

参考

データフレームの操作に関してはRデータ自由自在の第8章、第9章にもっと高度な内容が載ってます！

ツイートする

Permalink | コメント(0) | トラックバック(1) | 20:07