Как получить общие табличные выражения (CTE) в виде списка R?
Контекст
Я подключен к базе данных PostgreSQL и использую dplyr + dbplyr для запроса. Один из запросов довольно длинный и сложный и использует несколько CTE.
Проблема
Мне нужно использовать эти CTE для построения графиков и анализа в R, но я не знаю, как получить их как «отдельные» кадры данных в R.
Я пытался разбить длинный запрос на более мелкие, но это явно не получается, когда одному CTE нужен предыдущий.
Вопрос
Как получить CTE запроса в виде списка кадров данных в R?
Пример
Ниже приведен небольшой пример, иллюстрирующий вопрос. Запрос очень глупый, но я считаю, что для иллюстрации этого достаточно.
# Packages ----
if (!require("dbplyr")){install.packages("dbplyr")}; library(dbplyr)
if (!require("tidyverse")){install.packages("tidyverse")}; library(tidyverse)
# Set up the example database using the iris dataset ----
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, iris)
# Query using the "WITH" command ----
query = sql(
"WITH
tbl_set AS (SELECT * FROM iris WHERE Species = 'setosa'),
tbl_ver AS (SELECT * FROM iris WHERE Species = 'versicolor'),
tbl_all AS (
SELECT *
FROM tbl_set
UNION ALL SELECT * FROM tbl_ver)
SELECT * FROM tbl_all"
)
Ожидаемый результат
Ожидаемый результат — это объект в среде R, например список, содержащий каждый CTE в виде фреймов данных, ожидаемую структуру см. ниже:
# Target output ----
list(
tbl_set = filter(iris, Species == "setosa"),
tbl_ver = filter(iris, Species == "versicolor"),
tbl_all = collect(tbl(con, query))
)
#> $tbl_set
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3.0 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
#> 4 4.6 3.1 1.5 0.2 setosa
#>
#> $tbl_ver
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1 7.0 3.2 4.7 1.4 versicolor
#> 2 6.4 3.2 4.5 1.5 versicolor
#> 3 6.9 3.1 4.9 1.5 versicolor
#> 4 5.5 2.3 4.0 1.3 versicolor
#>
#> $tbl_all
#> # A tibble: 100 × 5
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> <dbl> <dbl> <dbl> <dbl> <chr>
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
#> 4 4.6 3.1 1.5 0.2 setosa
#> # ℹ 90 more rows
Created on 2024-07-04 with reprex v2.1.0
@NirGraham, спасибо за твой комментарий, я постараюсь объяснить это лучше. Я застрял в извлечении CTE (здесь tbl_set , tbl_ver) из запроса в R-списке. Идея состоит в том, чтобы «перехватить» CTE во время выполнения запроса и сохранить их в среде R. Вывод списка в этом примере предназначен только для имитации структуры ожидаемого вывода. Я ищу способ получить этот объект при выполнении запроса, если это имеет смысл.
Итак, вы пытаетесь это автоматизировать... Я думаю, вам нужно будет написать что-нибудь для анализа текста запроса и определения подзапросов. возможно, с регулярным выражением. но если вам это нужно только для одного сложного запроса; Вероятно, вам будет проще просто скопировать и вставить подзапросы и решить проблему вручную.
@NirGraham, да, я пытался запустить каждый CTE как независимый запрос. Это работает, когда между CTE нет зависимости. Однако когда CTE_3 полагается на предыдущий CTE_2, это терпит неудачу. Из вашего комментария я понимаю, что невозможно на лету захватить CTE и сохранить их в среде R.
Я вижу способ сделать это с помощью регулярных выражений, хотя шаблоны вызовут головную боль. Прежде чем пытаться это сделать, как генерируется исходный запрос? У вас есть тег dbplyr, но в вопросе нет перевода dbplyr.
Возможно, существует более простой способ ответить на этот вопрос, откуда сгенерирован запрос.
@Саймон.С.А. Я вызываю исходный запрос из R с помощью чего-то вроде dplyr::collect(dplyr::tbl(con, dbplyr::sql(readr::read_file(here::here("the_query.sql"))))). Это (пока) не представление в базе данных. Это ответ на ваш комментарий?
Ответы 3
Я действительно не знаю, в чем ваша проблема; Думаю, я покажу вам, как разделить CTE на отдельные таблицы? ваш окончательный запрос может быть изменен, чтобы использовать их или нет, но дело в том, что вы включаете их в свой окончательный список.
Подход сводится к копированию и вставке кода CTE и добавлению шаблонного кода, CREATE TABLE name as чтобы сделать запросы конкретными.
library(dbplyr)
library(tidyverse)
library(DBI)
# Set up the example database using the iris dataset ----
con <- dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, iris)
original_query <- sql("WITH
tbl_set AS (SELECT * FROM iris WHERE Species = 'setosa'),
tbl_ver AS (SELECT * FROM iris WHERE Species = 'versicolor'),
tbl_all AS (
SELECT *
FROM tbl_set
UNION ALL SELECT * FROM tbl_ver)
SELECT * FROM tbl_all")
### different from above as can use the concrete CTE tables we will make in q1 and q2
modified_query <- sql("WITH
tbl_all AS (
SELECT *
FROM tbl_set
UNION ALL SELECT * FROM tbl_ver)
SELECT * FROM tbl_all")
# make the CTE queries explicit ?
q1 <- sql("CREATE TABLE tbl_set as SELECT * FROM iris WHERE Species = 'setosa'")
q2 <- sql("CREATE TABLE tbl_ver as SELECT * FROM iris WHERE Species = 'versicolor'")
dbExecute(conn = con, q1)
dbExecute(conn = con, q2)
list(
tbl_set = tbl(con, "tbl_set") |> collect(),
tbl_ver = tbl(con, "tbl_ver") |> collect(),
tbl_all = tbl(con, original_query) |> collect(),
tbl_all2 = tbl(con, modified_query) |> collect() # works just as well
)
Проблема в том, что этот метод не работает, когда одному CTE требуется предыдущий. Здесь, если tbl_set нужно tbl_ver, то бежать q2 невозможно. Я постарался сделать пример максимально простым, чтобы CTE в нем не были зависимыми, но реальный запрос имеет зависимости между CTE.
если tbl_set требуется tbl_ver, то сначала CREATE TABLE tbl_ver; а затем вы можете переписать запрос tbl_set, чтобы использовать его. Думаю, я отредактирую свой ответ, чтобы показать, как вы изменили бы свой исходный запрос, чтобы использовать конкретные созданные tbl_set и tbl_ver, поскольку это зависит от них...
1) Вместо использования CTE создайте временные таблицы. Ниже мы немного изменили пример, чтобы создание tbl_ver зависело от tbl_set.
library(RSQLite)
con <- dbConnect(SQLite())
dbWriteTable(con, "iris", iris)
res <- dbExecute(con, "CREATE TEMPORARY TABLE tbl_set AS
SELECT * FROM iris WHERE Species = 'setosa' ")
res <- dbExecute(con, "CREATE TEMPORARY TABLE tbl_ver AS
SELECT * FROM iris WHERE Species = 'versicolor' AND
\"Sepal.Length\" > (SELECT avg(\"Sepal.Length\") FROM tbl_set)")
L <- list(
tbl_set = dbGetQuery(con, "SELECT * FROM tbl_set"),
tbl_ver = dbGetQuery(con, "SELECT * FROM tbl_ver"),
tbl_all = dbGetQuery(con, "SELECT * FROM tbl_set
UNION ALL SELECT * FROM tbl_ver")
)
dbDisconnect(con)
str(L)
предоставление
List of 3
$ tbl_set:'data.frame': 50 obs. of 5 variables:
..$ Sepal.Length: num [1:50] 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...
..$ Sepal.Width : num [1:50] 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...
..$ Petal.Length: num [1:50] 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...
..$ Petal.Width : num [1:50] 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...
..$ Species : chr [1:50] "setosa" "setosa" "setosa" "setosa" ...
$ tbl_ver:'data.frame': 47 obs. of 5 variables:
..$ Sepal.Length: num [1:47] 7 6.4 6.9 5.5 6.5 5.7 6.3 6.6 5.2 5.9 ...
..$ Sepal.Width : num [1:47] 3.2 3.2 3.1 2.3 2.8 2.8 3.3 2.9 2.7 3 ...
..$ Petal.Length: num [1:47] 4.7 4.5 4.9 4 4.6 4.5 4.7 4.6 3.9 4.2 ...
..$ Petal.Width : num [1:47] 1.4 1.5 1.5 1.3 1.5 1.3 1.6 1.3 1.4 1.5 ...
..$ Species : chr [1:47] "versicolor" "versicolor" "versicolor" "versicolor" ...
$ tbl_all:'data.frame': 97 obs. of 5 variables:
..$ Sepal.Length: num [1:97] 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...
..$ Sepal.Width : num [1:97] 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...
..$ Petal.Length: num [1:97] 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...
..$ Petal.Width : num [1:97] 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...
..$ Species : chr [1:97] "setosa" "setosa" "setosa" "setosa" ...
2) Если проблема заключается в извлечении CTE, тогда вводится SQL
оператор с CTE, извлекает их, генерирует и запускает операторы create temoprary table, а затем считывает их и исходный оператор SQL. (Чтобы извлечь CTE, мы считаем левые и правые круглые скобки и добавляем @ после внешних, чтобы позже можно было извлечь содержимое внешних скобок, следующих после имени и AS.)
library(gsubfn)
library(RSQLite)
sql <- "WITH
tbl_set AS (SELECT * FROM iris WHERE Species = 'setosa'),
tbl_ver AS (SELECT * FROM iris WHERE Species = 'versicolor' AND
\"Sepal.Length\" > (SELECT avg(\"Sepal.Length\") FROM tbl_set))
SELECT * FROM tbl_set
UNION ALL SELECT * FROM tbl_ver
"
# insert @ after outer parentheses
p <- proto(
pre = function(this) this$depth <- -1,
fun = function(this, x) {
if (x == "(") this$depth <- this$depth + 1
ret <- if (this$depth == 0) paste0(x, "@") else x
if (x == ")") this$depth <- this$depth - 1
ret
}
)
# extract CTEs
cte <- sql |>
chartr("\n", " ", x = _) |>
gsubfn("[\\(\\)]", p, x = _) |>
strapply("(([a-z_]+) +AS \\(@.*?\\)@)", perl = TRUE) |>
unlist() |>
trimws() |>
gsub("@", "", x = _)
# cte names
nms <- sub(" .*", "", cte)
# form SQL statements
sqls <- cte |>
sub("\\((.*)\\)", "\\1", x = _) |>
paste("CREATE TEMPORARY TABLE", ... = _) |>
gsub(".@", "", x = _) |>
setNames(nms)
# run statements to create temporary tables from cte's
library(RSQLite)
con <- dbConnect(SQLite())
dbWriteTable(con, "iris", iris)
for(s in sqls) dbExecute(con, s)
# get data
L <- c(lapply(nms, dbReadTable, conn = con),
list(tbl_all = dbGetQuery(con, sql)))
dbDisconnect(con)
+1 за редактирование примера, чтобы сделать его более актуальным, чем мой :-) и принятый ответ для ясности объяснения. Ключом к решению проблемы стало использование временных таблиц вместо CTE.
Вы можете использовать dbplyr для создания ссылок на удаленные фреймы данных, которые для большинства целей будут вести себя точно так же, как CTE. Проиллюстрирую это в заметке, которую написала здесь.
Следующее создает список, который вы ищете. Но мне интересно, действительно ли вам нужно обрабатывать свои фреймы данных?
library(dbplyr)
library(tidyverse)
# Set up the example database using the iris dataset ----
con <- DBI::dbConnect(duckdb::duckdb())
iris <- copy_to(con, iris)
tbl_set <- iris |> filter(Species == 'setosa')
tbl_ver <- iris |> filter(Species == 'versicolor')
tbl_all <-
tbl_set |>
union_all(tbl_ver)
tbl_all
#> # Source: SQL [?? x 5]
#> # Database: DuckDB v1.0.0 [root@Darwin 23.6.0:R 4.4.1/:memory:]
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> <dbl> <dbl> <dbl> <dbl> <fct>
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
#> 4 4.6 3.1 1.5 0.2 setosa
#> 5 5 3.6 1.4 0.2 setosa
#> 6 5.4 3.9 1.7 0.4 setosa
#> 7 4.6 3.4 1.4 0.3 setosa
#> 8 5 3.4 1.5 0.2 setosa
#> 9 4.4 2.9 1.4 0.2 setosa
#> 10 4.9 3.1 1.5 0.1 setosa
#> # ℹ more rows
dbs <- list(tbl_set, tbl_ver, tbl_all)
map(dbs, collect)
#> [[1]]
#> # A tibble: 50 × 5
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> <dbl> <dbl> <dbl> <dbl> <fct>
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
#> 4 4.6 3.1 1.5 0.2 setosa
#> 5 5 3.6 1.4 0.2 setosa
#> 6 5.4 3.9 1.7 0.4 setosa
#> 7 4.6 3.4 1.4 0.3 setosa
#> 8 5 3.4 1.5 0.2 setosa
#> 9 4.4 2.9 1.4 0.2 setosa
#> 10 4.9 3.1 1.5 0.1 setosa
#> # ℹ 40 more rows
#>
#> [[2]]
#> # A tibble: 50 × 5
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> <dbl> <dbl> <dbl> <dbl> <fct>
#> 1 7 3.2 4.7 1.4 versicolor
#> 2 6.4 3.2 4.5 1.5 versicolor
#> 3 6.9 3.1 4.9 1.5 versicolor
#> 4 5.5 2.3 4 1.3 versicolor
#> 5 6.5 2.8 4.6 1.5 versicolor
#> 6 5.7 2.8 4.5 1.3 versicolor
#> 7 6.3 3.3 4.7 1.6 versicolor
#> 8 4.9 2.4 3.3 1 versicolor
#> 9 6.6 2.9 4.6 1.3 versicolor
#> 10 5.2 2.7 3.9 1.4 versicolor
#> # ℹ 40 more rows
#>
#> [[3]]
#> # A tibble: 100 × 5
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> <dbl> <dbl> <dbl> <dbl> <fct>
#> 1 5.1 3.5 1.4 0.2 setosa
#> 2 4.9 3 1.4 0.2 setosa
#> 3 4.7 3.2 1.3 0.2 setosa
#> 4 4.6 3.1 1.5 0.2 setosa
#> 5 5 3.6 1.4 0.2 setosa
#> 6 5.4 3.9 1.7 0.4 setosa
#> 7 4.6 3.4 1.4 0.3 setosa
#> 8 5 3.4 1.5 0.2 setosa
#> 9 4.4 2.9 1.4 0.2 setosa
#> 10 4.9 3.1 1.5 0.1 setosa
#> # ℹ 90 more rows
Created on 2024-07-06 with reprex v2.1.0
просьба уточнить ; проблема в том, что вы знаете, как сделать это вручную, но хотите каким-то образом автоматизировать это? или вы застряли на извлечении подтаблицы даже вручную?