使用any()使用dbplyr从SQLite数据库中获取数据

发布于 2025-01-17 01:23:05 字数 876 浏览 3 评论 0原文

我想通过使用 any() 函数结合 group_by 来从 R 中的本地 SQLite 数据库中获取数据，以过滤至少一行等于某个值的组健康）状况。最终学习 SQL 可能会有所帮助，但是，到目前为止，我设法使用 dbplyr 完成所有查询，我希望也有一个 dplyr 解决方案来解决这个问题。

db <- dbConnect(RSQLite::SQLite(), "test_db.sqlite")

test_table <- tibble(id = c(rep(1:3, each = 3)),
                     cond = c(rep("A", 8), "B"))

dbWriteTable(db, "table", test_table)

table <- tbl(db, "table")

当表已经在内存中时，我可以轻松地完成我想要的操作

test_table %>% 
  group_by(id) %>% 
  filter(any(cond == "B"))

，这给了我

     id cond 
  <int> <chr>
1     3 A    
2     3 A    
3     3 B

但是这不起作用：

table %>% 
  group_by(id) %>% 
  filter(any(cond == "B"))

它会导致以下错误：

error: no such function: any

是否有 dbplyr 解决方法？

原文

I want to fetch data from a local SQLite database in R by using the any() function in combination with group_by to filter groups where at least one row is equal to a certain condition. It would probably help to finally learn SQL, however, until now I managed to do all my queries using dbplyr and I hope there is a dplyr solution for this problem as well.

db <- dbConnect(RSQLite::SQLite(), "test_db.sqlite")

test_table <- tibble(id = c(rep(1:3, each = 3)),
                     cond = c(rep("A", 8), "B"))

dbWriteTable(db, "table", test_table)

table <- tbl(db, "table")

With the table already in memory I can accomplish what I want easily using

test_table %>% 
  group_by(id) %>% 
  filter(any(cond == "B"))

which gives me

     id cond 
  <int> <chr>
1     3 A    
2     3 A    
3     3 B

However this does not work:

table %>% 
  group_by(id) %>% 
  filter(any(cond == "B"))

It results in the following error:

error: no such function: any

Is there a dbplyr workaround?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

作死小能手 2025-01-24 01:23:05

这是一个有效的解决方案：

library(dplyr)
library(DBI)

db <- dbConnect(RSQLite::SQLite(), "test_db.sqlite")

test_table <- tibble(id = c(rep(1:3, each = 3)),
                     cond = c(rep("A", 8), "B"))
test_table
### A tibble: 9 × 2
##     id cond 
##  <int> <chr>
##1     1 A    
##2     1 A    
##3     1 A    
##4     2 A    
##5     2 A    
##6     2 A    
##7     3 A    
##8     3 A    
##9     3 B    


dbWriteTable(db, "table", test_table)

table <- tbl(db, "table")

table %>%
  group_by(id) %>%
  filter(sum(cond == "B") > 0)

## Source:   lazy query [?? x 2]
## Database: sqlite 3.38.0 [test_db.sqlite]
## Groups:   id
#     id cond 
#  <int> <chr>
#1     3 A    
#2     3 A    
#3     3 B

Here is a solution that works:

library(dplyr)
library(DBI)

db <- dbConnect(RSQLite::SQLite(), "test_db.sqlite")

test_table <- tibble(id = c(rep(1:3, each = 3)),
                     cond = c(rep("A", 8), "B"))
test_table
### A tibble: 9 × 2
##     id cond 
##  <int> <chr>
##1     1 A    
##2     1 A    
##3     1 A    
##4     2 A    
##5     2 A    
##6     2 A    
##7     3 A    
##8     3 A    
##9     3 B    


dbWriteTable(db, "table", test_table)

table <- tbl(db, "table")

table %>%
  group_by(id) %>%
  filter(sum(cond == "B") > 0)

## Source:   lazy query [?? x 2]
## Database: sqlite 3.38.0 [test_db.sqlite]
## Groups:   id
#     id cond 
#  <int> <chr>
#1     3 A    
#2     3 A    
#3     3 B

回复收藏 0 原文

~没有更多了~