R SQLite 在使用 COUNT 时将 char 转换为数字

Question

我正在通过 R 汇总 SQLite 数据库表，但是当我使用 COUNT() 时，它会将我的字符字段转换为数字，即如果我有观察值 0, 1, 2, n 我正在返回 0, 1, 2, 0。我已经在 Firefox 的 SQLite Manager 中尝试了查询，它按预期工作；所以我认为问题与 RSQLite 有关。

他是这个问题的一个工作示例:

install.packages("RSQLite")
library(RSQLite)

# Connect
db = dbConnect(SQLite(), "~/Desktop/test.sqlite")

# Make a table
dbSendQuery(db, "
   CREATE TABLE data
      (Year INT,
      Station TXT,
      Obs TXT)
")

# Generate dummy data
x = data.frame(Year=rep(1995:2004, 100),
    Station=rep(c("Bob", "Ringo", "Jarrett", "Heron"), 250),
    Obs=rep(c(0:3, "n"), 200))

# Write dummy data to db
dbWriteTable(db, "data", x, append=T, row.names=F)

# Get summary of data back
y = dbGetQuery(db, "
   SELECT Year, Station, Obs, COUNT(Obs) AS num FROM data
   GROUP BY Year, Station, Obs
")

我想知道:

1. 为什么要这样做？
2. 我该如何解决？

我正在使用:R 3.2.2、RStudio 0.99.489、Ubuntu 14.04、RSQLite 1.0.0 和 DBI 0.3.1

Answer 1

发生这种情况的原因是 Obs 列被声明为 TXT 类型，这不是内置类型。如果它被声明为 TEXT 类型，它就会按预期工作。

在 SQLite 中，列的类型只是一个提示，该列的每一行仍然可以有不同的类型。由于 TXT 不是已知类型，因此实际上会发生这种情况。在下面的示例中，txt 和 txt2 列在 CREATE 语句和 中声明为类型 TXT >TXT 不是 SQLite 中的内置类型(TEXT 是)。正如我们在 typeof(txt) 列下方的 y 输出中看到的，显示 txt 列的每一行的类型确实有不同的类型不同的行。 txt2 列也是如此，如 typeof(txt2) 列所示。当此类数据读回 R 时，R 使用第一个条目作为类型，因此具有第一个条目 0 且 ijnteger 类型的 txt 被读入 R 作为整数，并且txt2 的第一个条目为 n，文本类型被作为字符读入 R。这可以通过查看下面的 str(y) 输出看出。我们还注意到 text 列作为字符读入 R，因为 text 是 SQLite 中的已知数据类型。

library(RSQLite)
db <- dbConnect(SQLite(), ":memory:")

dbSendQuery(db, "CREATE TABLE d (txt TXT, txt2 TXT, text TEXT)")
x <- data.frame(txt = c(0, "n"), txt2 = c("n", 0), text2 = c(0, "n")) 
dbWriteTable(db, "d", x, append = TRUE, row.names = FALSE)

y <- dbGetQuery(db, 
         "select txt, typeof(txt), txt2, typeof(txt2), text, typeof(text) from d")

y
##  txt typeof(txt) txt2 typeof(txt2) text typeof(text)
## 1   0     integer    n         text    0         text
## 2   0        text    0      integer    n         text

str(y)
## 'data.frame':   2 obs. of  6 variables:
## $ txt         : int  0 0   <------ becomes int since 1st entry of txt is int
## $ typeof(txt) : chr  "integer" "text"
## $ txt2        : chr  "n" "0"  <---- chr since 1st entry is text
## $ typeof(txt2): chr  "text" "integer"
## $ text        : chr  "0" "n"  <--- chr since column declared as text in create
## $ typeof(text): chr  "text" "text"

有关详细信息，请参阅 sqlite documentation on datatypes .