Database Reference
In-Depth Information
EXECUTE
var_sql
;
END
$$
;
Use
string_agg
to form a set of SQL statements as a single string of the form
INSERT INTO lu_fact_type(...) SELECT ... WHERE s01 ~ '[a-zA-Z]+';
Execute the SQL.
FILTER Clause for Aggregates
New in version 9.4 is the
FILTER
clause for aggregates, recently standardized in ANSI
SQL. This replaces the standard
CASE WHEN
clause for reducing the number of rows
included in an aggregation. For example, suppose you used
CASE WHEN
to break out
average test scores by student, as shown in
Example 7-19
.
Example 7-19. CASE WHEN used in AVG
SELECT
student
,
AVG
(
CASE
WHEN
subject
=
'algebra'
THEN
score
ELSE
NULL
END
)
As
algebra
,
AVG
(
CASE
WHEN
subject
=
'physics'
THEN
score
ELSE
NULL
END
)
As
physics
FROM
test_scores
GROUP
BY
student
;
The
FILTER
clause equivalent for
Example 7-19
is shown in
Example 7-20
.
Example 7-20. FILTER used with AVG aggregate
SELECT
student
,
AVG
(
score
)
FILTER
(
WHERE
subject
=
'algebra'
)
As
algebra
,
AVG
(
score
)
FILTER
(
WHERE
subject
=
'physics'
)
As
physics
FROM
test_scores
GROUP
BY
student
;
In the case of averages and sums and many other aggregates, the
CASE
and
FILTER
are
equivalent. The benefit is that
FILTER
is a little clearer in purpose and for large datasets
is faster. However, there are some aggregates—such as
array_agg
, which considers
NULL
s—where the
CASE
statement gives you extra
NULL
values you don't want. In
Example 7-21
we try to get the list of scores for each subject of interest for each student
using the
CASE .. WHEN..
approach.
Example 7-21. CASE WHEN used in array_agg
SELECT
student
,
array_agg
(
CASE
WHEN
subject
=
'algebra'
THEN
score
ELSE
NULL
END
)
As
algebra
,
array_agg
(
CASE
WHEN
subject
=
'physics'
THEN
score
ELSE
NULL
END
)
As
physics
FROM
test_scores
GROUP
BY
student
;
student | algebra | physics
---------+---------------------------+--------------------------------
