Custom Aggregates, Ordered-Set & Hypothetical-Set Aggregates

The finance team needs a geometric mean. The risk team needs a 95th percentile. The sales team wants a hypothetical rank without inserting a row. All three are built-in, or writable, directly in SQL.

A geometric mean is not a built-in PostgreSQL aggregate — but CREATE AGGREGATE genuinely lets any engineer add one directly in SQL, with just a state-transition function and a final function. This lab writes a real geomean(float8) aggregate and verifies it against a compounding-investment-returns calculation with a known, independently-computable correct answer — not just checking that it runs, but that it is right.\n\nTwo further aggregate classes are already built into PostgreSQL and are frequently unknown even to experienced engineers: ordered-set aggregates like percentile_cont() and percentile_disc(), which need an explicit WITHIN GROUP (ORDER BY ...) clause since their result genuinely depends on row order, not just row values; and hypothetical-set aggregates like rank() WITHIN GROUP, which compute what a value's rank would be without ever inserting it.

A Real, From-Scratch Aggregate

CREATE FUNCTION geomean_sfunc(state float8[], val float8) RETURNS float8[] AS
  $ SELECT ARRAY[state[1] + ln(val), state[2] + 1] $ LANGUAGE sql;
CREATE FUNCTION geomean_ffunc(state float8[]) RETURNS float8 AS
  $ SELECT exp(state[1] / state[2]) $ LANGUAGE sql;

CREATE AGGREGATE geomean(float8) (
  sfunc = geomean_sfunc, stype = float8[], finalfunc = geomean_ffunc, initcond = '{0,0}'
);

The state tracks a running sum of logarithms and a running count in a two-element array; the final function converts back with exp(sum/count) — the standard, real way to compute a geometric mean incrementally.

Verified Against a Known Answer

SELECT geomean(x) FROM (VALUES (1.10::float8),(1.20),(0.95)) AS t(x);  -- 1.078365153390936
SELECT (1.10*1.20*0.95)^(1.0/3.0);                                     -- 1.07836515339093589945

Both genuinely agree — the aggregate is not just running without error, it is computing the mathematically correct compounding return.

Ordered-Set Aggregates: percentile_cont vs percentile_disc

SELECT percentile_cont(ARRAY[0.25,0.5,0.75]) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;
-- {25.75,50.5,75.25}   <- interpolated between actual values
SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;
-- 95                    <- an actual value that exists in the data

percentile_cont() interpolates between the two nearest real values; percentile_disc() always returns an actual value that genuinely exists in the input — the real, meaningful difference between them.

Hypothetical-Set Aggregates: rank() Without Inserting Anything

SELECT rank(105) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;  -- 101

105 does not exist in the series at all — rank() genuinely computed what its rank would be if it were inserted, with zero actual modification to any data.

CREATE AGGREGATE (sfunc, stype, finalfunc)

sfunc is called once per input row, folding the new value into a running state of type stype; finalfunc (optional) transforms that final state into the aggregate's real result. This lab's geomean tracks a running [sum of logarithms, count] pair as its state, and finalfunc converts it back with exp(sum/count) — the standard incremental technique for a geometric mean, verified directly against a known answer.

Ordered-set aggregate (WITHIN GROUP ORDER BY)

A class of aggregate whose result genuinely depends on the order of its inputs, not just their values — percentile_cont() and percentile_disc() are the standard examples, requiring an explicit WITHIN GROUP (ORDER BY ...) clause rather than a plain argument list.

Hypothetical-set aggregate

A further variant of ordered-set aggregate that computes what a value's rank or percentile would be if it were added to the existing ordered set, without ever actually inserting it — rank() WITHIN GROUP is the standard example, confirmed directly in this lab to correctly compute the rank of a value that does not exist anywhere in the underlying data.

🛠️ Write a Real geomean() Aggregate

Write the state and final functions, then create a real, callable geomean(float8) aggregate.

psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION geomean_sfunc(state float8[], val float8) RETURNS float8[] AS 'SELECT ARRAY[state[1] + ln(val), state[2] + 1]' LANGUAGE sql; CREATE OR REPLACE FUNCTION geomean_ffunc(state float8[]) RETURNS float8 AS 'SELECT exp(state[1] / state[2])' LANGUAGE sql;"

psql -U postgres -d beer_db -c "CREATE AGGREGATE geomean(float8) (sfunc = geomean_sfunc, stype = float8[], finalfunc = geomean_ffunc, initcond = '{0,0}');"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE OR REPLACE FUNCTION geomean_sfunc(state float8[], val float8) RETURNS float8[] AS 'SELECT ARRAY[state[1] + ln(val), state[2] + 1]' LANGUAGE sql; CREATE OR REPLACE FUNCTION geomean_ffunc(state float8[]) RETURNS float8 AS 'SELECT exp(state[1] / state[2])' LANGUAGE sql;" SET CREATE FUNCTION CREATE FUNCTION student@lab:~$ psql -U postgres -d beer_db -c "CREATE AGGREGATE geomean(float8) (sfunc = geomean_sfunc, stype = float8[], finalfunc = geomean_ffunc, initcond = '{0,0}');" SET CREATE AGGREGATE

✅ Verify Against a Known Correct Answer

Call the new aggregate on a compounding-returns series and confirm it matches an independently-computed known answer.

psql -U postgres -d beer_db -c "SELECT geomean(x) FROM (VALUES (1.10::float8),(1.20),(0.95)) AS t(x);"

psql -U postgres -d beer_db -c "SELECT (1.10*1.20*0.95)^(1.0/3.0);"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT geomean(x) FROM (VALUES (1.10::float8),(1.20),(0.95)) AS t(x);" SET geomean ------------------- 1.078365153390936 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT (1.10*1.20*0.95)^(1.0/3.0);" SET ?column? ------------------------ 1.07836515339093589945 (1 row)

📊 Ordered-Set Aggregates: Interpolated vs Discrete

Compare percentile_cont() (interpolated) against percentile_disc() (an actual value) on the same data.

psql -U postgres -d beer_db -c "SELECT percentile_cont(ARRAY[0.25,0.5,0.75]) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;" -c "SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT percentile_cont(ARRAY[0.25,0.5,0.75]) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;" -c "SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;" SET percentile_cont -------------------- {25.75,50.5,75.25} (1 row) percentile_disc ----------------- 95 (1 row)

🎯 A Hypothetical Rank, With Zero Data Modified

Compute the rank a value would have if it were inserted, without inserting anything at all.

psql -U postgres -d beer_db -c "SELECT rank(105) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT rank(105) WITHIN GROUP (ORDER BY x) FROM generate_series(1,100) x;" SET rank ------ 101 (1 row)

Lab 3.7.3 complete. A real custom aggregate, verified correct, plus two built-in aggregate classes:\n\n\n Custom geomean() aggregate : ✅ written from scratch, genuinely callable\n Verified against known answer : ✅ matches independent calculation\n percentile_cont vs _disc : ✅ interpolated vs actual-value difference confirmed\n Hypothetical rank() : ✅ real rank, zero data modified\n

Enable JavaScript to run the live terminal and track your progress.