Blog / 2012 / October / 8

Simple Window Function

October 8, 2012

Here's a simple window function, used to work out a count of rows.

bands_band rows have a link to a regions_region row.

These two queries produce the same results. First, a simple count:

SELECT r.name, count(b.region_id)
FROM regions_region r
INNER JOIN bands_band b ON (r.id = b.region_id)
GROUP BY r.name
ORDER BY 2 desc

Next, an equivalent query using a window function:

SELECT DISTINCT ON (r.name) r.name, count(b.region_id) over (PARTITION BY b.region_id)
FROM regions_region r
INNER JOIN bands_band b ON b.region_id = r.id

If we want to order this query, we can use a subquery like this:

SELECT * FROM
(SELECT DISTINCT ON (r.name) r.name, count(b.region_id) over (PARTITION BY b.region_id)
FROM regions_region r
INNER JOIN bands_band b ON b.region_id = r.id) x
ORDER BY 2 desc

So what's the point of the window function?

Using window functions it's possible to aggregate over different things in different parts of the query. GROUP BY would refer to the entire query, here we can create a "window" specific to a given result column.

We can use it to produce a list of bands along with the number of bands that are also in that region:

SELECT b.name, count(*) over (PARTITION BY b.region_id)
FROM bands_band b

We can also easily expand this to include the region name:

SELECT b.name, r.name, count(*) over (PARTITION BY b.region_id)
FROM bands_band b
INNER JOIN regions_region r on (r.id = b.region_id)

Bands have a description of the national_grading level on them, held in b.national_grading. We can add this column to the results, along with a count of other bands with the same grading:

SELECT b.name, 
        r.name, 
        count(*) over (PARTITION BY b.region_id) as region_count, 
        b.national_grading, 
        count(*) over (PARTITION by b.national_grading) as grading_count
FROM bands_band b
INNER JOIN regions_region r on (r.id = b.region_id)

Tags: sql postgres window function