In SQL, an ordered-set aggregate closes its parentheses and then takes WITHIN GROUP (ORDER BY ...). DQL cannot parse anything after a function’s closing parenthesis, so in DQL the clause moves inside the call:
SQL
DQL
percentile_cont(0.5) WITHIN GROUP (ORDER BY e.value)
PERCENTILE_CONT(0.5 WITHIN GROUP ORDER BY e.value)
percentile_disc(0.9) WITHIN GROUP (ORDER BY e.value DESC)
PERCENTILE_DISC(0.9 WITHIN GROUP ORDER BY e.value DESC)
mode() WITHIN GROUP (ORDER BY e.status)
MODE(WITHIN GROUP ORDER BY e.status)
No comma between the fraction and WITHIN GROUP, and no parentheses around ORDER BY .... MODE takes no fraction, so its call starts straight with WITHIN GROUP.
ORDER BY takes exactly one item, optionally with ASC / DESC. PostgreSQL rejects more than one, and DQL does not accept a literal there.
The fraction is a literal, a parameter, or an expression over columns listed in GROUP BY. PostgreSQL rejects a fraction that reads an ungrouped column.
The results come from these rows of Entity, where value and the coordinates are double precision:
id
category
status
score
value
x1
y1
x2
y2
1
books
active
91
16
0
0
3
4
2
books
active
78
49
1
1
4
5
3
books
draft
85
120
0
0
0
2
4
music
draft
42
-5
2
2
2
2
Each -- → line under a query is one row of its result. A query without its own ORDER BY has its rows listed in sample-row order, or by group.
-- WIDTH_BUCKET: bucket number (1-based) for a value in a histogram with N equal-width bucketsSELECTWIDTH_BUCKET(e.score,0,100,10)asbucket,COUNT(e.id)ascountFROMEntityeGROUPBYbucketORDERBYbucket-- → ['bucket' => 5, 'count' => 1]-- ['bucket' => 8, 'count' => 1]-- ['bucket' => 9, 'count' => 1]-- ['bucket' => 10, 'count' => 1]-- POWER used for square root and Pythagorean distanceSELECTPOWER(e.value,0.5)assquare_rootFROMEntityeWHEREe.value>0-- → ['square_root' => 4.0]-- ['square_root' => 7.0]-- ['square_root' => 10.954451150103322]SELECTPOWER(POWER(e.x2-e.x1,2)+POWER(e.y2-e.y1,2),0.5)asdistanceFROMEntitye-- → ['distance' => 5.0]-- ['distance' => 5.0]-- ['distance' => 2.0]-- ['distance' => 0.0]-- Random reservoir sampling: WHERE filters ~10% of rows, ORDER BY shuffles themSELECTeFROMEntityeWHERERANDOM()<0.1ORDERBYRANDOM()-- DQL has no LIMIT: cap the rows with $query->setMaxResults(100)-- → each row with a one-in-ten chance, in random order-- RANDOM with boundsSELECTRANDOM(1,6)asrollFROMEntitye-- → a whole number from 1 to 6 for each row-- GREATEST/LEAST with aggregates - clamp aggregate results to a floor or ceilingSELECTe.category,GREATEST(MAX(e.value),0)asmax_non_negative,LEAST(MIN(e.value),100)asmin_cappedFROMEntityeGROUPBYe.category-- → ['category' => 'books', 'max_non_negative' => 120.0, 'min_capped' => 16.0]-- ['category' => 'music', 'max_non_negative' => 0.0, 'min_capped' => -5.0]-- Ordered-set aggregates: WITHIN GROUP ORDER BY goes inside the parentheses (see above)SELECTe.category,PERCENTILE_CONT(0.5WITHINGROUPORDERBYe.value)asmedian,PERCENTILE_DISC(0.9WITHINGROUPORDERBYe.value)astop_decile,MODE(WITHINGROUPORDERBYe.status)asmost_common_statusFROMEntityeGROUPBYe.category-- → ['category' => 'books', 'median' => 49.0, 'top_decile' => 120.0, 'most_common_status' => 'active']-- ['category' => 'music', 'median' => -5.0, 'top_decile' => -5.0, 'most_common_status' => 'draft']
Function Categories:
Mathematical functions
Basic Math: CEIL, FLOOR, ROUND, TRUNC for rounding operations
Power Functions: POWER, CBRT, EXP for exponential calculations
Logarithmic: LN, LOG for logarithmic operations
Trigonometric: DEGREES, RADIANS for angle conversions
Comparison: GREATEST, LEAST for finding extremes
Utility: SIGN, RANDOM, PI for various mathematical needs
Tips:
WIDTH_BUCKET numbers the equal-width bucket a value falls into, which is what a histogram groups by.
RANDOM() returns a value from 0 up to, but not including, 1. RANDOM(min, max) includes both bounds. With two string literals, PostgreSQL cannot pick the bounds’ type and fails with function random(unknown, unknown) is not unique: make at least one bound a field.
GREATEST and LEAST skip NULL arguments; they return NULL only when every argument is NULL.
