LoginSignup
6
6

More than 5 years have passed since last update.

dplyr で素数列挙 SQL #rstatsj

Last updated at Posted at 2015-08-14

@gg_hatano さんのこういう話がある。

これを dplyr でやってみる。

まずは DB テーブルの用意。

R
library(dplyr)

df <- data.frame(num=1:100)
srcs <- temp_srcs("sqlite")
tbls <- dplyr:::temp_load(srcs, list(df=df))
temp_tbl <- tbls$sqlite$df

print(temp_tbl)
結果
Source: sqlite 3.8.6
From: tvjegatmvj [100 x 1]

   num
1    1
2    2
3    3
4    4
5    5
6    6
7    7
8    8
9    9
10  10
.. ...

こうして作られた一時テーブル tvjegatmvj から、素数だけを取り出したい。

戦略は元記事と同じなのでそちらを参照のこと。
dplyr ではサブクエリを直接書けないようなので、まずはサブクエリを作って SQL を取り出す。

R
subq <- temp_tbl %>% 
  rename(s2.num=num) %>%
  collapse %>%
  filter(num >= s2.num * s2.num) %>% 
  filter(s2.num > 1) %>%
  filter(num %% s2.num == 0) %>%
  { .$query$sql }

print(subq)
結果
<SQL> SELECT "s2.num"
FROM (SELECT "num" AS "s2.num"
FROM "tvjegatmvj") AS "_W19"
WHERE "num" >= "s2.num" * "s2.num" AND "s2.num" > 1.0 AND "num" % "s2.num" = 0.0

このサブクエリを使ってクエリを作成する。

R
q <- temp_tbl %>%
  filter(num > 1) %>%
  filter(`not exists`(subq))

print(q$query)
結果
<Query> SELECT "num"
FROM "tvjegatmvj"
WHERE "num" > 1.0 AND NOT EXISTS(SELECT "s2.num"
FROM (SELECT "num" AS "s2.num"
FROM "tvjegatmvj") AS "_W25"
WHERE "num" >= "s2.num" * "s2.num" AND "s2.num" > 1.0 AND "num" % "s2.num" = 0.0)
<SQLiteConnection>

これを実行して結果を取得する。

R
prime_nums <- q %>% collect

print(prime_nums)
結果
Source: local data frame [25 x 1]

   num
1    2
2    3
3    5
4    7
5   11
6   13
7   17
8   19
9   23
10  29
.. ...

無事、素数が取り出せた。

Enjoy!

関連

6
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
6