Skip to content

Instantly share code, notes, and snippets.

@dickoa
Created November 11, 2015 09:05
Show Gist options
  • Save dickoa/4d14f82e727250bd6f08 to your computer and use it in GitHub Desktop.
Save dickoa/4d14f82e727250bd6f08 to your computer and use it in GitHub Desktop.
Custom function in dplyr psql backend
library(dplyr)
library(RPostgres)
mydb <- src_postgres(dbname = "yourdb"
user = "user",
password = "password",
host = "localhost",
port = 5432L)
src_translate_env.tbl_postgres <- function (x)
{
sql_variant(base_scalar,
sql_translator(.parent = base_agg,
n = function() sql("count(*)"),
cor = sql_prefix("corr"),
cov = sql_prefix("covar_samp"),
sd = sql_prefix("stddev_samp"),
var = sql_prefix("var_samp"),
all = sql_prefix("bool_and"),
any = sql_prefix("bool_or"),
paste = function(x, collapse)
build_sql("string_agg(", x, ", ", collapse, ")"),
weighted.mean = sql_prefix("weighted_mean")),
base_win)
}
mytbl <- tbl(mydb, "tbl")
mytbl %>%
summarise(xw = weighted.mean(x, w))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment