使用dbplyr在R中使用last()函数时出现错误结果/错误。

Question

我将尝试使用 r 的 dbplyr 运行 sql，并在使用 dbplyr 代码的 last() 函数时遇到了一个错误结果/错误。我不确定是我的操作有误还是实际上存在一个错误。 目标：使用 dbplyr 从表中获取每个员工的最后状态。

我已尝试使用以下代码来复制此问题。 1. 库

library(tidyverse)
library(lubridate)
library(dbplyr)
library(RSQLite)

2. 数据

df_emp_status <- data.frame(
  Emp_id = c(121,321,451,121,451,451,321,755),
  
  TimeStamp = c('29-07-2019 08:55:55','29-07-2019 09:02:55','29-07-2019 09:05:50',
               '29-07-2019 10:05:50','29-07-2019 10:07:50','29-07-2019 10:10:10',
               '29-07-2019 10:20:10','29-07-2019 11:00:00'),
  
  Status = c('IN','IN','IN','OUT','OUT','IN','OUT','IN')
          )

3. 数据库连接与数据

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
df_emp_status_sql <- copy_to(con, df_emp_status, overwrite = TRUE)

4. 目标：获取每个员工的最新状态

4.1 R 代码：

使用 summarise()

df_emp_status %>% 
  group_by(Emp_id) %>% 
  arrange(Emp_id,TimeStamp) %>% 
  summarise(last_status = last(Status))

# A tibble: 4 × 2
  Emp_id last_status
   <dbl> <chr>      
1    121 OUT        
2    321 OUT        
3    451 IN         
4    755 IN

使用 mutate

df_emp_status %>% 
  group_by(Emp_id) %>% 
  arrange(Emp_id,TimeStamp) %>% 
  mutate(last_status = last(Status))

# A tibble: 8 × 4
# Groups:   Emp_id [4]
  Emp_id TimeStamp           Status last_status
   <dbl> <dttm>              <chr>  <chr>      
1    121 2019-07-29 08:55:55 IN     OUT        
2    121 2019-07-29 10:05:50 OUT    OUT        
3    321 2019-07-29 09:02:55 IN     OUT        
4    321 2019-07-29 10:20:10 OUT    OUT        
5    451 2019-07-29 09:05:50 IN     IN         
6    451 2019-07-29 10:07:50 OUT    IN         
7    451 2019-07-29 10:10:10 IN     IN         
8    755 2019-07-29 11:00:00 IN     IN 

以上结果是正确的并且给了我期望的结果:

4.2 SQL / DBPLYR 复制（给出错误结果）

由于在使用 dbplyr 时无法在 summarise 中使用 last()，所以要使用 mutate() 来替代。

df_emp_status_sql %>% 
  group_by(Emp_id) %>% 
  arrange(Emp_id,TimeStamp) %>% 
  mutate(last_status = last(Status))

# Source:     SQL [8 x 4]
# Database:   sqlite 3.39.4 [:memory:]
# Groups:     Emp_id
# Ordered by: Emp_id, TimeStamp
  Emp_id  TimeStamp Status last_status
   <dbl>      <dbl> <chr>  <chr>      
1    121 1564390555 IN     IN         
2    121 1564394750 OUT    OUT        
3    321 1564390975 IN     IN         
4    321 1564395610 OUT    OUT        
5    451 1564391150 IN     IN         
6    451 1564394870 OUT    OUT        
7    451 1564395010 IN     IN         
8    755 1564398000 IN     IN 

上述结果是错误的

4.3 查询交叉检查

df_emp_status_sql %>% 
  group_by(Emp_id) %>% 
  arrange(Emp_id,TimeStamp) %>% 
  mutate(last_status = last(Status)) %>% 
  show_query()

<SQL>
SELECT
  *,
  LAST_VALUE(`Status`) OVER (PARTITION BY `Emp_id` ORDER BY `Emp_id`, `TimeStamp`) AS `last_status`
FROM `df_emp_status`
ORDER BY `Emp_id`, `TimeStamp`

我已经尝试单独运行上面的查询，但是结果也是错误的。

4.4 正确的查询 我能够从如何在sql中使用last_value()中找到适用于所需结果的正确查询。

dbGetQuery(con,'
  SELECT
  *,
  LAST_VALUE(`Status`) OVER (
                            PARTITION BY `Emp_id` 
                                ORDER BY `Emp_id`, `TimeStamp`
                                RANGE BETWEEN 
                                  UNBOUNDED PRECEDING AND 
                                  UNBOUNDED FOLLOWING) AS `last_status`
FROM `df_emp_status`
ORDER BY `Emp_id`, `TimeStamp`
')

  Emp_id  TimeStamp Status last_status
1    121 1564390555     IN         OUT
2    121 1564394750    OUT         OUT
3    321 1564390975     IN         OUT
4    321 1564395610    OUT         OUT
5    451 1564391150     IN          IN
6    451 1564394870    OUT          IN
7    451 1564395010     IN          IN
8    755 1564398000     IN          IN

5. Conclusion:

RANGE BETWEEN 
                                  UNBOUNDED PRECEDING AND 
                                  UNBOUNDED FOLLOWING

上述代码似乎在使用last_value时对于正确结果来说非常重要，因此dbplyr翻译也应该包括上述代码在其r到sql的翻译中，否则会得到错误的结果。

- ViSa

---

问题是什么来着？这似乎更像是某种博客文章。 - Martin Gal

我对 dbplyr 不熟悉，也不是 SQL 专业人士，不确定我的做法是否正确。因此，我正在寻找替代方法，以便能够找到正确的解决方案，从而可以自信地使用 dbplyr 作为 SQL 的替代品。 - ViSa

@ViSa，你在 https://github.com/tidyverse/dplyr/issues 上报告过这个错误了吗？因为我昨天遇到了同样的问题，看起来这很可能是翻译错误。 - lorenzolightsgdwarf

嗨@lorenzolightsgdwarf。不，我没有在github.com/tidyverse/dplyr/issues上报告这个问题。 - ViSa

好的！我做到了 https://github.com/tidyverse/dbplyr/issues/1214 - lorenzolightsgdwarf

@ViSa，这个错误已经在2.3.0版本中修复了。祝编码愉快！ - lorenzolightsgdwarf

Answer 1

我记不得在哪里看到的，但我记得看到有关PRECEDING和FOLLOWING默认设置的内容。这似乎是在使用dbplyr时隐含确定的，因此您可能无法直接设置它们。

一个适用于此应用程序的替代方法是使用lead。尝试类似以下的内容：

df_emp_status_sql %>% 
  group_by(Emp_id) %>% 
  arrange(TimeStamp) %>% 
  mutate(next_status = lead(Status)) %>%
  filter(is.na(next_status)) %>%
  select(-next_status)

使用lead函数为每个记录添加下一个状态。每个id的最后一条记录将具有缺失值（NA或NULL，具体取决于语言选择），因此您可以根据此值进行过滤。

- Simon.S.A.

---

谢谢Simon，这是一个非常聪明的方法来解决这个问题。 - ViSa