Database Reference
In-Depth Information
INITCOND
=
'{0,0}'
);
Let's take our
geom_mean
function for a test drive. In
Example 8-8
, we compute a heuristic
rating for racial diversity and list the top five most racially diverse counties in Massa‐
chusetts.
Example 8-8. Top five most racially diverse counties using geometric mean
SELECT
left
(
tract_id
,
5
)
As
county
,
geom_mean
(
val
)
As
div_county
FROM
census
.
vw_facts
WHERE
category
=
'Population'
AND
short_name
!=
'white_alone'
GROUP
BY
county
ORDER
BY
div_county
DESC
LIMIT
5
;
county | div_county
-------+---------------------
25025 | 85.1549046212833364
25013 | 79.5972921427888918
25017 | 74.7697097102419689
25021 | 73.8824162064128504
25027 | 73.5955049035237656
Let's put things into overdrive and try our new aggregate function as a window aggregate,
as shown in
Example 8-9
.
Example 8-9. Top five most racially diverse census tracts with averages
WITH
X
AS
(
SELECT
tract_id
,
left
(
tract_id
,
5
)
As
county
,
geom_mean
(
val
)
OVER
(
PARTITION
BY
tract_id
)
As
div_tract
,
ROW_NUMBER
()
OVER
(
PARTITION
BY
tract_id
)
As
rn
,
geom_mean
(
val
)
OVER
(
PARTITION
BY
left
(
tract_id
,
5
))
As
div_county
FROM
census
.
vw_facts
WHERE
category
=
'Population'
AND
short_name
!=
'white_alone'
)
SELECT
tract_id
,
county
,
div_tract
,
div_county
FROM
X
WHERE
rn
=
1
ORDER
BY
div_tract
DESC
,
div_county
DESC
LIMIT
5
;
tract_id | county | div_tract | div_county
------------+--------+----------------------+---------------------
25025160101 | 25025 | 302.6815688785928786 | 85.1549046212833364
25027731900 | 25027 | 265.6136902148147729 | 73.5955049035237656
25021416200 | 25021 | 261.9351057509603296 | 73.8824162064128504
25025130406 | 25025 | 260.3241378371627137 | 85.1549046212833364
25017342500 | 25017 | 257.4671462282508267 | 74.7697097102419689
