使用sparklyr的n_distinct按条件统计Spark DataFrame去重值遇问题

原代码中appl[appl_y == 'y']这种本地data.frame的索引写法在Spark DataFrame环境下不适用，Spark的分布式计算模型不支持这种行级索引过滤，导致n_distinct实际统计了所有appl的去重值，而非符合条件的子集，最终计数异常。

正确写法一：使用ifelse过滤后统计 #

通过ifelse将不符合条件的appl值替换为NA，n_distinct会自动忽略NA，仅统计符合条件的去重值：

library(sparklyr)
library(dplyr)

df <- data.frame(
  appl = c("Apple", "Microsoft", "Google", "Amazon", "Facebook", "Samsung", "IBM"),
  appl_y = c("y", "n", "y", "n", "y", "n", "y"),
  manu = c("USA", "USA", "USA", "China", "USA", "South Korea", "USA"),
  alternate_flag = c("y", "n", "y", "y", "n", "y", "n")
)

# 连接Spark
sc <- spark_connect(master = "local")
# 转为Spark DataFrame
df_spark <- copy_to(sc, df, "df_spark")

# 分组统计符合条件的去重值
result <- df_spark %>%
  group_by(manu) %>%
  summarize(
    num_appl_y = n_distinct(ifelse(appl_y == 'y', appl, NA)),
    num_appl_flag = n_distinct(ifelse(alternate_flag == 'y', appl, NA))
  )

# 查看结果
collect(result)

正确写法二：先过滤再合并统计结果 #

如果需要更清晰的分步逻辑，可以先分别过滤符合条件的数据，统计后再合并结果：

library(sparklyr)
library(dplyr)

df <- data.frame(
  appl = c("Apple", "Microsoft", "Google", "Amazon", "Facebook", "Samsung", "IBM"),
  appl_y = c("y", "n", "y", "n", "y", "n", "y"),
  manu = c("USA", "USA", "USA", "China", "USA", "South Korea", "USA"),
  alternate_flag = c("y", "n", "y", "y", "n", "y", "n")
)

sc <- spark_connect(master = "local")
df_spark <- copy_to(sc, df, "df_spark")

# 统计appl_y='y'的分组去重数
appl_y_stats <- df_spark %>%
  filter(appl_y == 'y') %>%
  group_by(manu) %>%
  summarize(num_appl_y = n_distinct(appl))

# 统计alternate_flag='y'的分组去重数
flag_stats <- df_spark %>%
  filter(alternate_flag == 'y') %>%
  group_by(manu) %>%
  summarize(num_appl_flag = n_distinct(appl))

# 合并结果，将无符合条件的组的计数设为0
result <- appl_y_stats %>%
  full_join(flag_stats, by = "manu") %>%
  mutate(
    num_appl_y = replace(num_appl_y, is.na(num_appl_y), 0),
    num_appl_flag = replace(num_appl_flag, is.na(num_appl_flag), 0)
  )

collect(result)

两种写法都能得到正确结果：

• USA的num_appl_y为4（Apple、Google、Facebook、IBM），num_appl_flag为3（Apple、Google、Amazon）
• China的num_appl_y为0，num_appl_flag为1（Amazon）
• South Korea的num_appl_y为0，num_appl_flag为1（Samsung）

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