Hi there, I'm trying to generate an SQL query with multiple window function orderings using dbplyr.
I was trying to do this using multiple calls to the window_order function.
The default behaviour seems to be that ordering is additive.
Is there anyway to get window_order to overwrite previous orderings rather than add to them?
library(dplyr)
library(dbplyr)
df <- memdb_frame(x = 1:10, y = runif(10))
df %>%
window_order(x) %>%
mutate(z1 = cumsum(y)) %>%
window_order(desc(x)) %>%
mutate(z2 = cumsum(y)) %>%
show_query()
#> <SQL>
#> SELECT `x`, `y`, `z1`, SUM(`y`) OVER (ORDER BY `x`, `x` DESC ROWS UNBOUNDED PRECEDING) AS `z2`
#> FROM (SELECT `x`, `y`, SUM(`y`) OVER (ORDER BY `x` ROWS UNBOUNDED PRECEDING) AS `z1`
#> FROM `dbplyr_001`)
Is it possible to get to
#> <SQL>
#> SELECT `x`, `y`, `z1`, SUM(`y`) OVER (ORDER BY `x` DESC ROWS UNBOUNDED PRECEDING) AS `z2`
#> FROM (SELECT `x`, `y`, SUM(`y`) OVER (ORDER BY `x` ROWS UNBOUNDED PRECEDING) AS `z1`
#> FROM `dbplyr_001`)
i.e. for z1 I want ORDER BY x and for z2 I want ORDER BY x DESC (rather than ORDER BY x, x DESC)
Many thanks,
Robbie