如何在dbplyr查询的summarise函数中获取分组首行与末行

问题 #

本地dplyr中可以用head()在summarise里提取分组后的首行，但dbplyr无法将head()转换为SQL语句，执行时会报错no such function: head。需要用数据库兼容的方法实现分组后取首行和末行。

解决方案 #

1. 提取单个字段的首/末值：用first()和last() #

dbplyr原生支持first()和last()函数，它们会被转换为对应的SQL聚合逻辑，但必须先排序保证行顺序符合预期：

library(tidyverse)
library(DBI)
library(dbplyr)

# 初始化测试连接与表
con <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(con, "mtcars", mtcars)

# 分组取mpg字段的首行值
con %>%
  tbl("mtcars") %>%
  arrange(cyl, mpg) %>%  # 先按分组键cyl排序，再按目标字段mpg排序（可选，确保顺序）
  group_by(cyl) %>%
  summarise(first_mpg = first(mpg)) %>%
  collect()

# 分组取mpg字段的末行值
con %>%
  tbl("mtcars") %>%
  arrange(cyl, mpg) %>%
  group_by(cyl) %>%
  summarise(last_mpg = last(mpg)) %>%
  collect()

2. 提取整行的首/末数据：窗口函数+filter #

如果需要获取分组后首行/末行的完整数据，用row_number()窗口函数给分组内的行编号，再筛选目标行：

# 提取分组首行整行数据
con %>%
  tbl("mtcars") %>%
  arrange(cyl, mpg) %>%
  group_by(cyl) %>%
  mutate(row_num = row_number()) %>%
  filter(row_num == 1) %>%
  ungroup() %>%
  collect()

# 提取分组末行整行数据
con %>%
  tbl("mtcars") %>%
  arrange(cyl, desc(mpg)) %>%  # 倒序排序后，第一行即为原顺序的末行
  group_by(cyl) %>%
  mutate(row_num = row_number()) %>%
  filter(row_num == 1) %>%
  ungroup() %>%
  collect()

3. 简洁写法：用slice_head()/slice_tail()（dbplyr 1.0.0+） #

如果你的dbplyr版本在1.0.0及以上，可以直接用slice_head()和slice_tail()，语法更直观：

# 分组取首行
con %>%
  tbl("mtcars") %>%
  arrange(cyl, mpg) %>%
  group_by(cyl) %>%
  slice_head(n = 1) %>%
  ungroup() %>%
  collect()

# 分组取末行
con %>%
  tbl("mtcars") %>%
  arrange(cyl, mpg) %>%
  group_by(cyl) %>%
  slice_tail(n = 1) %>%
  ungroup() %>%
  collect()

重要提示 #

• 所有方法都必须先执行arrange()，数据库返回的行默认是无序的，不排序的话首末行结果完全不可靠。
• first()/last()适合只需要单个字段的场景，slice系列或窗口函数适合获取整行数据。

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