如何在dbplyr分组mutate时正确使用last()函数？

背景与需求 #

现有远程数据表定义如下：

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)
remote_data <- memdb_frame(
  grp = c(2, 2, 2, 1, 3, 1, 1),
  win = c("B", "C", "A", "B", "C", "A", "C"),
  id = c(1,3,5,7,2,4,6),
)

需求：按grp分组，按win排序后，为每组的每一行添加该组排序后的最后一个id（即last_id）。本地拉取数据后计算可得到预期结果：

# 本地计算的预期输出
remote_data %>% 
  collect() %>% 
  arrange(grp, win) %>% 
  group_by(grp) %>% 
  mutate(last_id = last(id)) %>% 
  ungroup()
#> # A tibble: 7 × 4
#>     grp win      id last_id
#>   <dbl> <chr> <dbl>   <dbl>
#> 1     1 A         4       6
#> 2     1 B         7       6
#> 3     1 C         6       6
#> 4     2 A         5       3
#> 5     2 B         1       3
#> 6     2 C         3       3
#> 7     3 C         2       2

但直接用dbplyr远程计算时，结果不符合预期：last_id仅取到当前行的id，而非分组最后一行。尝试window_order()调整排序也未解决问题。

解决方案 #

方法1：调整窗口框架范围 #

SQL中LAST_VALUE()默认的窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，导致仅能取到当前行之前的值。通过window_frame()将窗口范围扩展到整个分组即可解决：

remote_data %>%
  group_by(grp) %>%
  window_order(win) %>%
  window_frame(-Inf, Inf) %>% # 窗口覆盖分组内所有行
  mutate(last_id = last(id)) %>%
  ungroup() %>%
  arrange(grp, win)

对应的SQL查询会自动添加窗口范围：

SELECT
  *,
  LAST_VALUE(`id`) OVER (
    PARTITION BY `grp` 
    ORDER BY `win` 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS `last_id`
FROM `dbplyr_001`
ORDER BY `grp`, `win`

方法2：用first()配合倒序排序 #

如果不想调整窗口框架，可通过倒序排序后取first(id)，等价于原顺序的最后一个值：

remote_data %>%
  group_by(grp) %>%
  window_order(desc(win)) %>% # 按win倒序排列
  mutate(last_id = first(id)) %>% # 倒序后的第一个值即原顺序的最后一个
  ungroup() %>%
  arrange(grp, win)

此方法兼容性更好，部分数据库对窗口框架的支持可能有限，倒序取首值更通用。

方法3：分组聚合后JOIN #

先单独计算每组的最后一个id，再通过JOIN合并到原数据，逻辑更直观：

# 先获取每组排序后的最后一个id
last_id_map <- remote_data %>%
  group_by(grp) %>%
  arrange(win) %>%
  slice_tail(n = 1) %>%
  select(grp, last_id = id)

# 关联回原数据
remote_data %>%
  left_join(last_id_map, by = "grp") %>%
  arrange(grp, win)

验证结果 #

上述三种方法的远程计算结果均与本地拉取后的预期输出一致，可根据个人习惯或数据库特性选择合适方案。

内容的提问来源于stack exchange，提问作者moodymudskipper