SQL-запрос в R с использованием нескольких операторов AND на основе значений в кадре данных

Я мог сделать это в прошлом, используя sprintf() для создания инструкции sql на основе списка выбранных пользователем идентификаторов. Но теперь я хочу сделать оператор sql на основе выбранного пользователем списка нескольких полей из нескольких таблиц, и я в недоумении.

У меня есть база данных с несколькими таблицами:

TBL1
Date  Program  Name      Type    height  width
5/22  E7       Square    angle   5       5
5/22  H9       Circle    smooth  4       4    
9/9   E7       Circle    smooth  7       7   
10/10 R8       Triangle  angle   10      5  

TBL2
Date  Program  Name      Value1  Value2
5/22  E7       Square    5       2.4
5/22  H9       Circle    10      43
9/9   E7       Circle    3.2     9
10/10 R8       Triangle  999     1

TBL3
Type    1  2  3
angle   a  g  h
smooth  b  c  d

И у меня есть кадр данных строк, которые я хочу получить из базы данных

df
Date  Program  Name
5/22  E7       Square
9/9   E7       Circle
10/10 R8       Triangle

Мне нужно динамически генерировать оператор SQL для сбора данных, которые мне нужны. Поэтому мне нужно будет получить дату, программу, имя, тип, значение1 и значение2 для каждой даты, программы и имени в моем файле df.

Я пытался выяснить, может ли sqlinterpolate справиться с этим, но похоже, что не может?

Я бы объединил TBL1 и TBL3 WHERE Type = Type, а затем присоединил бы VALUES из TBL2 только для строк, где Date, Program и Name соответствовали моему df.

Желаемый результат возврата sql:

Date  Program  Name      Type    1   2   3  Value1  Value2
5/22  E7       Square    angle   a   g   h  5       2.4
9/9   E7       Circle    smooth  b   c   d  3.2     9
10/10 R8       Triangle  angle   a   g   h  999     1

Мысли?


r rmysql r-dbi
person Steve    schedule 03.04.2020    source источник
comment
Вы на самом деле не объяснили логику, с помощью которой мы получаем из ваших трех начальных кадров данных желаемый результат.   -  person Tim Biegeleisen    schedule 03.04.2020
comment
@TimBiegeleisen Я не знаю, как сделать это намного яснее? Я могу сделать один оператор sql, который объединяет три таблицы на основе одной строки из моего фрейма данных. Я просто не уверен, как создать динамический оператор, который может сделать это для нескольких строк.   -  person Steve    schedule 03.04.2020
comment
@DavidRanzolin, к сожалению, нет. Я прочитал ответ, где они написали временную таблицу и выполнили соединение с базой данных. Это было бы отличным решением, если бы я мог его использовать.   -  person Steve    schedule 03.04.2020

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете разбить df на строки, создать запрос для каждой из них, свернуть запросы с помощью «UNION ALL\n», а затем получить:

library(RSQLite)
library(DBI)
library(tidyverse)
library(glue)
#> 
#> Attaching package: 'glue'
#> The following object is masked from 'package:dplyr':
#> 
#>     collapse

t1 <- tibble(date = c("5/22", "5/22", "9/9", "10/10"),
             program = c("E7", "H9", "E7", "R8"),
             name = c("Square", "Circle", "Circle", "Triangle"),
             type = c("angle", "smooth", "smooth", "angle"),
             height = c(5, 4, 7, 10),
             width = c(5, 4, 5, 5))

t2 <- tibble(date = c("5/22", "5/22", "9/9", "10/10"),
             program = c("E7", "H9", "E7", "R8"),
             name = c("Square", "Circle", "Circle", "Triangle"),
             val1 = c(5, 10, 3.2, 999),
             val2 = c(2.4, 43, 9, 1))

t3 <- tibble(type = c("angle", "smooth"),
             one = c("a", "b"),
             two = c("g", "c"),
             three = c("h", "d"))

df <- tibble(date = c("5/22", "9/9", "10/10"),
             program = c("E7", "E7", "R8"),
             name = c("Square", "Circle", "Triangle"))

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "tab1", t1)
dbWriteTable(con, "tab2", t2)
dbWriteTable(con, "tab3", t3)

queries <- df %>% 
  split(seq_len(nrow(.))) %>% 
  map(~{
    d <- .x$date
    p <- .x$program
    n <- .x$name
    glue('SELECT t1.date, 
                      t1.program, 
                      t1.name,
                      t1.type,
                      t2.val1,
                      t2.val2,
                      t3.one,
                      t3.two,
                      t3.three
               FROM tab1 t1
               LEFT JOIN tab2 t2 ON t1.date = t2.date AND t1.program = t2.program
               LEFT JOIN tab3 t3 ON t1.type = t3.type
               WHERE t1.date = "{d}"
               AND t1.program = "{p}"
               AND t1.name = "{n}"')
  })
q <- paste(queries, collapse = "UNION ALL\n")
dbGetQuery(con, q)
#>    date program     name   type  val1 val2 one two three
#> 1  5/22      E7   Square  angle   5.0  2.4   a   g     h
#> 2   9/9      E7   Circle smooth   3.2  9.0   b   c     d
#> 3 10/10      R8 Triangle  angle 999.0  1.0   a   g     h

Создано 3 апреля 2020 г. с помощью пакета reprex (v0.3.0)

person David Ranzolin    schedule 03.04.2020
comment
Вы, сэр, мой герой. Большое спасибо. Узнал и о новом пакете! Раньше клеем не пользовался. - Я тоже не знал о UNION ALL. - person Steve; 03.04.2020