唯一值的数量 [英] number of unique values sparklyr
问题描述
以下示例描述了如何在不使用 dplyr 和 sparklyr 聚合行的情况下计算不同值的数量.
the following example describes how you can't calculate the number of distinct values without aggregating the rows using dplyr with sparklyr.
是否有不破坏命令链的解决方法?
is there a work around that doesn't break the chain of commands?
更一般地说,您如何在 sparklyr 数据帧上使用 sql 之类的窗口函数.
more generally, how can you use sql like window functions on sparklyr data frames.
## generating a data set
set.seed(.328)
df <- data.frame(
ids = floor(runif(10, 1, 10)),
cats = sample(letters[1:3], 10, replace = TRUE),
vals = rnorm(10)
)
## copying to Spark
df.spark <- copy_to(sc, df, "df_spark", overwrite = TRUE)
# Source: table<df_spark> [?? x 3]
# Database: spark_connection
# ids cats vals
# <dbl> <chr> <dbl>
# 9 a 0.7635935
# 3 a -0.7990092
# 4 a -1.1476570
# 6 c -0.2894616
# 9 b -0.2992151
# 2 c -0.4115108
# 9 b 0.2522234
# 9 c -0.8919211
# 6 c 0.4356833
# 6 b -1.2375384
# # ... with more rows
# using the regular dataframe
df %>% mutate(n_ids = n_distinct(ids))
# ids cats vals n_ids
# 9 a 0.7635935 5
# 3 a -0.7990092 5
# 4 a -1.1476570 5
# 6 c -0.2894616 5
# 9 b -0.2992151 5
# 2 c -0.4115108 5
# 9 b 0.2522234 5
# 9 c -0.8919211 5
# 6 c 0.4356833 5
# 6 b -1.2375384 5
# using the sparklyr data frame
df.spark %>% mutate(n_ids = n_distinct(ids))
Error: Window function `distinct()` is not supported by this database
推荐答案
这里最好的方法是单独计算计数,使用 count
∘ distinct
:
The best approach here is to compute counts separately, either with count
∘ distinct
:
n_ids <- df.spark %>%
select(ids) %>% distinct() %>% count() %>% collect() %>%
unlist %>% as.vector
df.spark %>% mutate(n_ids = n_ids)
或approx_count_distinct
:
n_ids_approx <- df.spark %>%
select(ids) %>% summarise(approx_count_distinct(ids)) %>% collect() %>%
unlist %>% as.vector
df.spark %>% mutate(n_ids = n_ids_approx)
有点冗长,但是dplyr
使用的窗口函数方法无论如何都是死胡同,如果你想使用全局无界框架.
It is a bit verbose, but window function approach used by dplyr
is a dead end anyway, if you want to use global unbounded frame.
如果您想要确切的结果,您还可以:
If you want exact results you can also:
df.spark %>%
spark_dataframe() %>%
invoke("selectExpr", list("COUNT(DISTINCT ids) as cnt_unique_ids")) %>%
sdf_register()
这篇关于唯一值的数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!