Do the aggregation first. Then use the lead()
and lag()
window functions. Looks like you were aiming for something like this:
SELECT *
, lead(curr_count) OVER w AS next_count
, lag(curr_count) OVER w AS prev_count
FROM (
SELECT l.city, d.year, d.month, count(*) AS curr_count
FROM fact f
JOIN crime c ON c.crime_key = f.crime_key -- see below
JOIN date d ON d.date_key = f.date_key
JOIN location l ON l.location_key = f.location_key
GROUP BY l.city, d.year, d.month
) sub
WINDOW w AS (PARTITION BY city ORDER BY year, month)
ORDER BY city, year, month;
You can use a CTE or a subquery (like I did) to apply the logic in two steps. May be easier to understand at first. But you can add window functions over aggregate functions in the same query level. See:
So this is equivalent and shorter (probably same query plan / performance):
SELECT l.city, d.year, d.month
, count(*) AS curr_count
, lead(count(*)) OVER w AS next_count
, lag(count(*)) OVER w AS prev_count
FROM fact f
JOIN crime c USING (crime_key) -- see below
JOIN date d USING (date_key)
JOIN location l USING (location_key)
GROUP BY l.city, d.year, d.month
WINDOW w AS (PARTITION BY l.city ORDER BY d.year, d.month);
Use equivalent explicit join syntax. Much easier to read.
The USING
shorthand in the join clause of my second query is only legit if column names are unambiguous. Else stick to the explicit ON
syntax in the first query.
Why join to table crime
? Does the join eliminate or multiply rows? Else you can remove it completely.
Use table aliases to keep the noise low.
You had PARTITION BY location.city, date.year, date.month ORDER BY date.month
. But I suggest you really want PARTITION BY city ORDER BY year, month
to make the query work across year boundaries?
It would probably be more efficient to use a single column of type date
(4 bytes) instead of year
and month
. (But don't name it just "date".)
Note how I skipped ORDER BY city, year, month
in the second query. You normally get rows sorted that way anyway due to the window definition, but there are no guarantees without explicit ORDER BY
. And you did not specify that order is required. If so, you might want to be explicit about this and add ORDER BY
.