DBPLYR examples

CONFIGURAR DBPLYR

library(dplyr)
library(dbplyr)
## read csv
argencon<-readr::read_csv("~/argencon.csv") 
## Create SQLite connection `in-memory`
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
## copy argencon data frame to SQLite
copy_to(con, argencon,name = "argencon_db")
## crate table
argencon_tbl <- tbl(con, "argencon_db")

Queries

Número de observaciones por subtipo de dominio DGA

argencon_tbl %>% group_by(label) %>% summarise(n=n()) %>% show_query()

<SQL>
SELECT `label`, COUNT(*) AS `n`
FROM `argencon_db`
GROUP BY `label`

Número de observaciones por subtipo de dominio DGA

argencon_tbl %>% filter(label=="dga") %>% group_by(source) %>% summarise(n=n()) %>% show_query()

<SQL>
SELECT `source`, COUNT(*) AS `n`
FROM `argencon_db`
WHERE (`label` = 'dga')
GROUP BY `source`

Número de observaciones por subtipo de dominio NORMAL

argencon_tbl %>% filter(label=="normal") %>% group_by(source) %>% summarise(n=n()) %>% show_query()

<SQL>
SELECT `source`, COUNT(*) AS `n`
FROM `argencon_db`
WHERE (`label` = 'normal')
GROUP BY `source`

Longitud promedio de los dominios DGA y NORMAL

argencon_tbl %>% group_by(label) %>% summarise(len=mean(length(domain)) ) %>% show_query()

<SQL>
SELECT `label`, AVG(length(`domain`)) AS `len`
FROM `argencon_db`
GROUP BY `label`

Seleccionar de manera aleatoria un 20% del conjunto de datos.

sample_tbl %>% filter( row_number() < total *.2) %>% show_query()

<SQL>
SELECT `domain`, `label`, `source`, `date`, `id`
FROM (SELECT `domain`, `label`, `source`, `date`, `id`, ROW_NUMBER() OVER (ORDER BY random()) AS `q01`
FROM (SELECT `domain`, `label`, `source`, `date`, `id`
FROM (SELECT `domain`, `label`, `source`, `date`, `id`, ROW_NUMBER() OVER (ORDER BY random()) AS `q01`
FROM `argencon_db`)
WHERE (`q01` <= 2918496)))
WHERE (`q01` < 2918496 * 0.2)

Seleccionar de manera aleatoria un 10% del conjunto de datos distinto y excluyente al 20% anterior.

sample_tbl %>% filter( row_number() > total *.2 & row_number() < total *.3) %>% show_query()

<SQL>
SELECT `domain`, `label`, `source`, `date`, `id`
FROM (SELECT `domain`, `label`, `source`, `date`, `id`, ROW_NUMBER() OVER (ORDER BY random()) AS `q01`, ROW_NUMBER() OVER (ORDER BY random()) AS `q02`
FROM (SELECT `domain`, `label`, `source`, `date`, `id`
FROM (SELECT `domain`, `label`, `source`, `date`, `id`, ROW_NUMBER() OVER (ORDER BY random()) AS `q01`
FROM `argencon_db`)
WHERE (`q01` <= 2918496)))
WHERE (`q01` > 2918496 * 0.2 AND `q02` < 2918496 * 0.3)