Database Reference
In-Depth Information
jojo | {74,NULL,NULL,NULL,74,..} | {NULL,83,NULL,NULL,NULL,79,..}
jdoe | {75,NULL,NULL,NULL,78,..} | {NULL,72,NULL,NULL,NULL,72..}
robe | {68,NULL,NULL,NULL,77,..} | {NULL,83,NULL,NULL,NULL,85,..}
lhsu | {84,NULL,NULL,NULL,80,..} | {NULL,72,NULL,NULL,NULL,72,..}
(4 rows)
Observe that in
Example 7-21
we get a bunch of
NULL
s in our arrays. We could work
around this issue with some clever use of subselects, but most of those will be more
verbose and slower than the
FILTER
alternative shown in
Example 7-22
.
Example 7-22. FILTER used with array_agg
SELECT
student
,
array_agg
(
score
)
FILTER
(
WHERE
subject
=
'algebra'
)
As
algebra
,
array_agg
(
score
)
FILTER
(
WHERE
subject
=
'physics'
)
As
physics
FROM
test_scores
GROUP
BY
student
;
student | algebra | physics
---------+---------+---------
jojo | {74,74} | {83,79}
jdoe | {75,78} | {72,72}
robe | {68,77} | {83,85}
lhsu | {84,80} | {72,72}
The
FILTER
clause works for all aggregate functions, not just aggregate functions built
into PostgreSQL.
Window Functions
Window functions are a common ANSI SQL feature supported in PostgreSQL since
version 8.4. A window function has the prescience to see and use data beyond the current
row; hence the term
window
. A window defines which other rows need to be considered
in addition to the current row. Windows let you add aggregate information to each row
of your output where the aggregation involves other rows in the same window. Window
functions such as
row_number
and
rank
are useful for ordering your data in sophisticated
ways that use rows outside the selected results but within a window.
Without window functions, you'd have to resort to using joins and subqueries to poll
neighboring rows. On the surface, window functions violate the set-based principle of
SQL, but we mollify the purist by claiming that they are merely shorthand. You can find
more details and examples in
Window Functions
.
Example 7-23
gives you a quick start. Using a window function, we can obtain both the
detail data and the average value for all records with
fact_type_id
of 86 in one single
SELECT
. Note that the
WHERE
clause is always evaluated
before
the window function.
