Database Reference
In-Depth Information
The key observation to make in the output is how the sum changes from row to row.
The
ORDER BY
clause means that the sum will be taken only from the beginning of the
partition to the current row, giving you a running total, where the location of the current
row in the list is dictated by the
ORDER BY
. For instance, if your row is in the fifth row
in the third partition, the sum will cover only the first five rows in the third partition.
We put an
ORDER BY left(tract_id,5), val
at the end of the query so you could
easily see the pattern, but keep in mind that the
ORDER BY
of the query is independent
of the
ORDER BY
in each
OVER
clause.
You can explicitly control the rows under consideration by adding a
RANGE
or
ROWS
clause:
ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING
.
PostgreSQL also supports window naming, which is useful if you have the same window
for each of your window columns.
Example 7-27
demonstrates how to name windows,
as well as how to use the
LEAD
and
LAG
window functions to show a record value before
and after for a given partition.
Example 7-27. Naming windows, demonstrating LEAD and LAG
SELECT
*
FROM
(
SELECT
ROW_NUMBER
()
OVER
(
wt
)
As
rnum
,
substring
(
tract_id
,
1
,
5
)
As
county_code
,
tract_id
,
LAG
(
tract_id
,
2
)
OVER
wt
As
tract_2_before
,
LEAD
(
tract_id
)
OVER
wt
As
tract_after
FROM
census
.
lu_tracts
WINDOW
wt
AS
(
PARTITION
BY
substring
(
tract_id
,
1
,
5
)
ORDER
BY
tract_id
)
)
As
x
WHERE
rnum
BETWEEN
2
and
3
AND
county_code
IN
(
'25007'
,
'25025'
)
ORDER
BY
county_code
,
rnum
;
rnum | county_code | tract_id | tract_2_before | tract_after
-----+-------------+-------------+----------------+-------------
2 | 25007 | 25007200200 | | 25007200300
3 | 25007 | 25007200300 | 25007200100 | 25007200400
2 | 25025 | 25025000201 | | 25025000202
3 | 25025 | 25025000202 | 25025000100 | 25025000301
Naming our window
wt
window.
Using our window name instead of retyping.
Both
LEAD
and
LAG
take an optional step argument that defines how many rows to skip
forward or backward; the step can be positive or negative.
LEAD
and
LAG
return
NULL
when trying to retrieve rows outside the window partition. This is a possibility that you
always have to account for.
