Median is Translated wrongly for MSSQL in summarize

[thothal]

Calculating the overall median does not yield valid SQL

library(dbplyr)
library(dplyr)
d <- lazy_frame(x = 1:10, con = simulate_mssql())
d %>% summarize(med = median(x, na.rm = TRUE))
# <SQL>
# SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) AS `med`
# FROM `df`

The proper SQL syntax would look like:

--- Set up
DROP TABLE IF EXISTS #temp;
WITH rcte (x) AS (
  SELECT 1 AS x
   UNION ALL
  SELECT x + 1
    FROM rcte
   WHERE x <= 100
) 
SELECT x
  INTO #temp
  FROM rcte;

--- SQL should look like this
SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER () AS med
  FROM #temp;

Thus instead of

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) AS `med`
  FROM `df`;

we would expect

SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) OVER() AS `med`
  FROM `df`;

[hadley]

Could you please link to the appropriate SQL server docs for this?

[thothal]

PERCENTILE_CONT

The OVER clause is not optional.

[hadley]

Ooops, looks like we already fixed this for quantile(), but we forgot about median():

https://github.com/tidyverse/dbplyr/blob/main/R/backend-mssql.R#L361