R Postgres后端实现类似unique/distinct功能的聚合方法

问题原因 #

unique()是R原生的去重函数，不属于标准SQL聚合函数，dbplyr无法直接将其转换为可在PostgreSQL端执行的SQL语句；而distinct()是用于整表/整行去重的操作，不能在summarise()中作为聚合函数使用，因此你的原代码无法得到预期结果。

可行方案 #

方案1：分组后目标列仅存在1个唯一值的场景 #

如果你确认要聚合的列（示例中的a）在分组后仅包含1个唯一值，直接使用MAX()或MIN()聚合函数即可，二者均能返回该唯一值，且是标准SQL函数，dbplyr可直接转换：

library(tidyverse)
library(dbplyr)

dbplyr::memdb_frame(a=c(2,2,2), b=c(2,3,4)) %>%
  summarise(
    aggregatedSum = sum(b), 
    aggregatedUnique = max(a)
  )

执行结果和你先collect()再聚合的输出完全一致。

方案2：分组后目标列存在多个唯一值的场景 #

如果目标列分组后可能有多个不同值，你可以通过sql()函数直接调用PostgreSQL原生的ARRAY_AGG(DISTINCT 列名)聚合函数，将所有唯一值聚合为数组存储在结果中：

dbplyr::memdb_frame(a=c(2,2,3), b=c(2,3,4)) %>%
  summarise(
    aggregatedSum = sum(b), 
    aggregatedUnique = sql("ARRAY_AGG(DISTINCT a)")
  )

如果后续需要仅取第一个唯一值，也可以调整SQL片段为sql("ARRAY_AGG(DISTINCT a)[1]")，直接在数据库端返回单个值。

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