dbplyr中的sql_variant是否正常工作？

Let's take a look at the example in ?sql_variant:

我们来看看?sql_variant中的示例:

We define a new translator function for aggregated functions, expanded from the default one:

我们为聚合函数定义了一个新的转换函数,从默认函数扩展:

postgres_agg <- sql_translator(.parent = base_agg,
  cor = sql_prefix("corr"),
  cov = sql_prefix("covar_samp"),
  sd =  sql_prefix("stddev_samp"),
  var = sql_prefix("var_samp")
)

We then define a new variant, which is made from translation functions of the 3 different types (here 2):

然后我们定义一个新的变体,它由3种不同类型的翻译函数组成(这里是2):

postgres_var <- sql_variant(
  base_scalar,
  postgres_agg
)

translate_sql(cor(x, y), variant = postgres_var)
# <SQL> COR("x", "y")
translate_sql(sd(income / years), variant = postgres_var)
# <SQL> SD("income" / "years")

These don't look translated to me, shouldn't they be "CORR" and "STDDEV_SAMP" ?

这些看起来不会翻译给我,它们不应该是“CORR”和“STDDEV_SAMP”吗?

# Original comment:
# Any functions not explicitly listed in the converter will be translated
# to sql as is, so you don't need to convert all functions.
translate_sql(regr_intercept(y, x), variant = postgres_var)
# <SQL> REGR_INTERCEPT("y", "x")

This one behaves as expected, which is just like the other 2.

这个行为符合预期,就像其他2一样。

On the other hand default translated functions work, see:

另一方面,默认翻译函数有效,请参阅:

translate_sql(mean(x), variant = postgres_var)
#<SQL> avg("x") OVER ()

It's a bug right ? or am I missing something ?

这是一个错误吗?还是我错过了什么?

My goal is to create some variants for Oracle and use it in the following fashion,then for more complicated functions (example with SQLite to be reproducible):

我的目标是为Oracle创建一些变体并以下列方式使用它,然后用于更复杂的函数(例如,SQLite可重现):

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, cars, "cars")
con %>% tbl("cars") %>% summarize(dist = group_concat(dist)) # works as expected, as we're stealing the keyword from sqlite directly
sqlite_variant <- sql_variant(aggregate=sql_translator(.parent = base_agg,gpc = sql_prefix("group_concat")))
con %>% tbl("cars") %>% summarize(dist = gpc(dist)) # how do I make this work ?

EDIT:

One bounty later still no solution, I've cross posted the issue in the dplyr/dbplyr github page directly where I'm not sure if it has or will get attention, but in case I (or someone else) don't update this in time, check this url : https://github.com/tidyverse/dplyr/issues/3117

一个赏金后来仍然没有解决方案,我已经直接在dplyr / dbplyr github页面中发布了这个问题,我不确定它是否已经或将得到关注,但万一我(或其他人)不更新此及时,检查这个网址:https://github.com/tidyverse/dplyr/issues/3117

1 个解决方案

#1

This is what Hadley Wickham answered on provided github link:

这就是Hadley Wickham在提供的github链接上回答的问题:

translate_sql() doesn't have a variant argument any more

translate_sql()不再有变量参数

Indeed the variant argument is not documented, though the examples use it, I suppose it will be corrected for next version.

事实上,变量参数没有记录,尽管示例使用它,我想它将在下一版本中得到纠正。

Asked how to define custom SQL translations he had this to offer:

当被问及如何定义他提供的自定义SQL翻译时:

Have a look at http://dbplyr.tidyverse.org/articles/new-backend.html and http://dbplyr.tidyverse.org/articles/sql-translation.html

看看http://dbplyr.tidyverse.org/articles/new-backend.html和http://dbplyr.tidyverse.org/articles/sql-translation.html

I guess another option is to get the older version of dbplyr::sql_variant.

我想另一个选择是获取旧版本的dbplyr :: sql_variant。

#1