Обновите таблицу с помощью подзапроса в SQLite

Я хочу добавить столбец в свою таблицу, используя операторы ALTER TABLE и UPDATE, чтобы не воссоздавать полную таблицу.

При использовании подзапроса в моем выражении UPDATE я не получаю ожидаемого результата.

создавать воспроизводимые данные

library(dplyr)
library(dbplyr)
library(DBI)
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, iris[c(1,2,51),],"iris")

tbl(con,"iris")
# # Source:   table<iris> [?? x 5]
# # Database: sqlite 3.19.3 []
#   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.0          1.4         0.2     setosa
# 3          7.0         3.2          4.7         1.4 versicolor

создайте новый столбец в отдельной таблице

DBI::dbSendQuery(con, "CREATE TABLE new_table AS SELECT t2.new_col from
                 iris t1 inner join 
                 (SELECT Species, sum(`Sepal.Width`) as new_col FROM iris GROUP BY Species) t2
                 on t1.Species = t2.Species")

tbl(con,"new_table")
# # Source:   table<new_table> [?? x 1]
# # Database: sqlite 3.19.3 []
#   new_col
#     <dbl>
# 1     6.5
# 2     6.5
# 3     3.2

создать новый столбец в старой таблице

DBI::dbSendQuery(con, "ALTER TABLE iris ADD COLUMN new_col DOUBLE")

попробуйте подключить туда новый столбец из new_table

DBI::dbSendQuery(con, "UPDATE iris SET new_col = (SELECT new_col FROM new_table)")

tbl(con,"iris")
# # Source:   table<iris> [?? x 6]
# # Database: sqlite 3.19.3 []
#   Sepal.Length Sepal.Width Petal.Length Petal.Width    Species new_col
#          <dbl>       <dbl>        <dbl>       <dbl>      <chr>   <dbl>
# 1          5.1         3.5          1.4         0.2     setosa     6.5
# 2          4.9         3.0          1.4         0.2     setosa     6.5
# 3          7.0         3.2          4.7         1.4 versicolor     6.5

Как вы можете видеть, мой new_col содержит только значение 6.5, где я ожидал иметь 3.2 в последней строке. Как я могу это исправить ?


r sqlite dbplyr r-dbi rsqlite
person Moody_Mudskipper    schedule 18.06.2018    source источник
comment
Связано с этим вопросом: stackoverflow.com/questions/50766908/   -  person Moody_Mudskipper    schedule 18.06.2018
comment
Спасибо @Moody_Mudskipper за продвижение!   -  person Geet    schedule 19.06.2018

Ответы (1)


arrow_upward
6
arrow_downward

Строки в таблице базы данных SQL не имеют внутреннего порядка. Таким образом, вы не можете назначить «вектор» значений, как если бы вы делали это в R. Однако вы можете немного изменить свой запрос:

library(dplyr)
library(DBI)
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, iris[c(1,2,51),],"iris")

Создайте отдельную таблицу с агрегированными данными

DBI::dbSendQuery(con, "CREATE TABLE new_table AS 
                       SELECT Species, sum(`Sepal.Width`) as new_col FROM iris GROUP BY Species")

tbl(con,"new_table")
#> # Source:   table<new_table> [?? x 2]
#> # Database: sqlite 3.22.0 []
#>   Species    new_col
#>   <chr>        <dbl>
#> 1 setosa         6.5
#> 2 versicolor     3.2

Создайте новый столбец в старой таблице

DBI::dbSendQuery(con, "ALTER TABLE iris ADD COLUMN new_col DOUBLE")

Переместить данные в исходную таблицу с коррелированным подзапросом

DBI::dbSendQuery(con, "UPDATE iris SET new_col = (SELECT new_col FROM new_table t2
                               WHERE iris.Species = t2.Species)")

tbl(con,"iris")
#> # Source:   table<iris> [?? x 6]
#> # Database: sqlite 3.22.0 []
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species    new_col
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>        <dbl>
#> 1          5.1         3.5          1.4         0.2 setosa         6.5
#> 2          4.9         3            1.4         0.2 setosa         6.5
#> 3          7           3.2          4.7         1.4 versicolor     3.2

Если у вас есть несколько вычисляемых столбцов, вы можете использовать UPDATE ... SET (c1, c2, ...) = (...) следующим образом:

library(dplyr)
library(dbplyr)
library(DBI)
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, iris[c(1,2,51),],"iris")

DBI::dbSendQuery(con, "CREATE TABLE aggs AS 
                       SELECT Species, 
                              SUM(`Sepal.Width`) AS sw_sum,
                              AVG(`Sepal.Width`) AS sw_avg 
                       FROM iris GROUP BY Species")
tbl(con,"aggs")
#> # Source:   table<aggs> [?? x 3]
#> # Database: sqlite 3.22.0 []
#>   Species    sw_sum sw_avg
#>   <chr>       <dbl>  <dbl>
#> 1 setosa        6.5   3.25
#> 2 versicolor    3.2   3.2

DBI::dbSendQuery(con, "ALTER TABLE iris ADD COLUMN sw_sum DOUBLE")
DBI::dbSendQuery(con, "ALTER TABLE iris ADD COLUMN sw_avg DOUBLE")

DBI::dbSendQuery(con, "UPDATE iris 
                       SET (sw_sum, sw_avg) = (SELECT sw_sum, sw_avg 
                           FROM aggs WHERE iris.Species = aggs.Species)")

tbl(con,"iris")
#> # Source:   table<iris> [?? x 7]
#> # Database: sqlite 3.22.0 []
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species  sw_sum sw_avg
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>     <dbl>  <dbl>
#> 1          5.1         3.5          1.4         0.2 setosa      6.5   3.25
#> 2          4.9         3            1.4         0.2 setosa      6.5   3.25
#> 3          7           3.2          4.7         1.4 versico…    3.2   3.2

Это также должно работать на Postgres, но, вероятно, не на SQL Server.

Собственно, промежуточная таблица в этом случае и не нужна:

library(dplyr)
library(dbplyr)
library(DBI)
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, iris[c(1,2,51),],"iris")

DBI::dbSendQuery(con, "ALTER TABLE iris ADD COLUMN sw_sum DOUBLE")
DBI::dbSendQuery(con, "ALTER TABLE iris ADD COLUMN sw_avg DOUBLE")

DBI::dbSendQuery(con, "UPDATE iris 
                       SET (sw_sum, sw_avg) = 
                              (SELECT sw_sum, sw_avg FROM 
                                    (SELECT Species, 
                                            SUM(`Sepal.Width`) AS sw_sum, 
                                            AVG(`Sepal.Width`) AS sw_avg 
                                     FROM iris GROUP BY Species) aggs 
                               WHERE iris.Species = aggs.Species)")

tbl(con,"iris")
#> # Source:   table<iris> [?? x 7]
#> # Database: sqlite 3.22.0 []
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species  sw_sum sw_avg
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>     <dbl>  <dbl>
#> 1          5.1         3.5          1.4         0.2 setosa      6.5   3.25
#> 2          4.9         3            1.4         0.2 setosa      6.5   3.25
#> 3          7           3.2          4.7         1.4 versico…    3.2   3.2

Однако промежуточная таблица может быть полезна и в других случаях. Например, когда он создается с использованием R, как в связанном вопросе.

person Ralf Stubner    schedule 18.06.2018
comment
Большое спасибо. Это стандартный SQL? (т.е. будет ли он устойчив к большинству/всем СУБД)? - person Moody_Mudskipper; 18.06.2018
comment
И если бы у меня было 2 вычисляемых столбца, мне пришлось бы повторять полный запрос для каждого или есть более компактный/эффективный способ? - person Moody_Mudskipper; 18.06.2018
comment
@Moody_Mudskipper Интересные вопросы. Я думаю, что это стандарт SQL и должен быть переносимым. Это становится сложнее с несколькими вычисляемыми столбцами. Postgres и SQL Server поддерживают немного разные UPDATE SET FROM JOIN. Postgres поддерживает UPDATE SET (c1, c2, ...) = (SELECT ...), который не поддерживается в SQL Server, хотя это также может быть стандартом SQL. AFAIK SQLite не поддерживает первый синтаксис, и я не пробовал последний синтаксис. Я также не пробовал это с MySQL, Oracle,... - person Ralf Stubner; 19.06.2018