SQL Advanced Aggregation

To be added

Table of contents

1. WITHIN GROUP

---

WITHIN GROUP

WITHIN GROUP is used to aggregate on ordered data.

This syntax simplifies some of the operations that would otherwise require workarounds with window functions.

Some examples with PERCENTILE_DISC and PERCENTILE_CONT (discrete and continuous percentiles):

WITH temp AS (SELECT GENERATE_SERIES(1, 100) AS val)
SELECT UNNEST(PERCENTILE_DISC(ARRAY [0.25, 0.5, 0.75, 1]) WITHIN GROUP ( ORDER BY val ))
FROM temp;
--- Result
--- 25, 50, 75, 100

WITH temp AS (SELECT GENERATE_SERIES(1, 100) AS val)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP ( ORDER BY val ) AS median
FROM temp;
-- Result
-- 50

UNNEST is used to convert an array into a set of rows.

Apart from PERCENTILE_DISC and PERCENTILE_CONT, WITHIN GROUP can be used with the preexisting window functions and also MODE.