Iterative approach to writing SQL in Postgres
Let’s look at building a somewhat complex Postgres SQL query step-by-step.
This query uses some cool features like generate_series, common table
expressions (“with” queries), and several date functions.
This is a reporting query on an internal, open source app to track guests in the office and help plan catering I wrote and maintained while I was still at Heroku. Being on the Heroku Postgres team for the last five years has somewhat warped my sensibilities; the entire application is mostly just 4-5 SQL queries wrapped up in Sinatra.
Background
In the beginning, the schema was pretty simple. There was only a
single table, guests:
create table guests (
id uuid primary key default uuid_generate_v4(),
guest_name text not null ,
herokai_name text not null ,
lunch boolean default false ,
nda boolean default false ,
notify_hipchat boolean default false ,
notify_gchat boolean default false ,
notify_sms text ,
notes text ,
created_at timestamptz default now() ,
visiting_range daterange not null
);
create index visiting_range_gist on guests using gist(visiting_range);
There isn’t anything particularly advanced going on there, except perhaps the
daterange column type. Range Types were added in the 2012 release of
Postgres 9.2. Sometimes guests visit for several consecutive days, and the
ability to express that in a single column is convenient. The type also
supports a special kind of index, GiST. We use it for extremely fast
lookups based on if the range includes a given date, though it has other uses.
Iteration 1: the beginning
The first thing we want to know is how many people come to the office each
month, and how many come for lunch. We have the boolean if they were signed
up for lunch, and we have the range they visited on, but we can’t just
group by the range.
Instead we start building a table of dates starting from the first day of the
month the system went into production until a few months in the future.
generate_series is what’s called a set returning function, and it does
exactly what we need.
The use of ::interval here is worth pointing out. Postgres is powerful enough
to deal with the varying amount of days in months and apply that statement
correctly, so it will always be the last day of whichever month is three months
from now.
select *
from generate_series(
'2013-09-01',
date_trunc('month', now()) + '3 months - 1 day'::interval,
'1 day') as v;
v
------------------------
2013-09-01 00:00:00-07
2013-09-02 00:00:00-07
2013-09-03 00:00:00-07
...
2015-11-30 00:00:00-08
(821 rows)
Next, we add a left outer join on the real guests table. We use an outer
join because otherwise a normal join would exclude days with zero guests. The
<@ operator means ‘contained by’, so for each day, we’re joining in all the
guests whose visiting range contains that day.
select *
from generate_series(
'2013-09-01',
date_trunc('month', now()) + '3 months - 1 day'::interval,
'1 day') as v
left outer join guests on v::date <@ visiting_range;
What we have at this point is at least one row per day, and one row for each day a guest is visiting. With that, we can do group by the day start counting the things we’re looking for.
count(expression) gives the number of rows where expression is not null.
This works well for the total number of guests, because the outer join gives us
nulls when there aren’t any guests. It’s a little tricker for the boolean
lunch column, since false values still get counted. Fortunately Postgres
gives us nullif which, well, does what it says.
select
v::date as visiting_on,
count(visiting_range) as total,
count(nullif(lunch, false)) as lunch
from generate_series(
'2013-09-01',
date_trunc('month', now()) + '3 months - 1 day'::interval,
'1 day') as v
left outer join guests on v::date <@ visiting_range
group by 1
order by 1;
visiting_on | total | lunch
-------------+-------+-------
...
2013-09-23 | 3 | 1
2013-09-24 | 0 | 0
2013-09-25 | 1 | 1
2013-09-26 | 0 | 0
2013-09-27 | 0 | 0
...
We’re almost there. What we need next is to group again by month, and add up all the values. There are a few ways to do this, but I think “with” queries are best because they’re much easier to work with, and understand again in the future. In fact, this query is over a year and a half old, but is still pretty easy to understand.
We wrap the previous query inside the CTE, and then group by
date_trunc('month', visiting_on) which makes it so all dates in the same
month are the same. Also, it’s nice that the Month format to to_char pads
shorter month names with spaces so all the years line up.
with counts_by_day as (
select
v::date as visiting_on,
count(visiting_range) as total,
count(nullif(lunch, false)) as lunch
from generate_series(
'2013-09-01',
date_trunc('month', now()) + '3 months - 1 day'::interval,
'1 day') as v
left outer join guests on v::date <@ visiting_range
group by 1
)
select
to_char(date_trunc('month', visiting_on), 'Month YYYY') as month,
sum(total) as total,
sum(lunch) as lunch
from counts_by_day
group by date_trunc('month', visiting_on)
order by date_trunc('month', visiting_on) asc;
month | total | lunch
----------------+-------+-------
September 2013 | 4 | 2
October 2013 | 116 | 61
November 2013 | 117 | 76
...
And there you have it. We’ve solved what we set out to do. Up next is dealing with an oversight in the query and adding more information as the system grows.
Iteration 2: the bug
What we had seemed to work well, but there was one oversight. It turned out that sometimes a visit would span a weekend, and because this query joins in every day of visit, weekends were being counted.
We need to find some way to exclude Saturday and Sunday. I don’t know a way to do this off the top of my head, but a good place to look is the Date/Time Functions and Operators page.
There we find that EXTRACT has a dow option that gives “the day of the week
as Sunday (0) to Saturday (6)”. That looks pretty good, but looking a bit more
we find isodow which goes from “Monday (1) to Sunday (7)”. Even better, now
the two things we need to exclude are next to each other.
And with that, we can add a single line to the counts_by_day CTE:
left outer join guests on v::date <@ visiting_range
+ where extract(isodow from v) < 6 -- only Monday-Friday
group by 1
Iteration 3: new features
Some time later the system grew the ability to track which guests actually check in and when. That table looks like this:
create table checkins (
id uuid primary key default uuid_generate_v4(),
guest_id uuid not null,
created_at timestamptz not null default now()
);
create index on checkins(guest_id);
This allows each day a guest comes out of their range to be accounted for
individually, just by adding a row. As an aside, this is the second time we’ve
seen a table with a timestamptz column. Always use timestamptz. Look at
your database right now and change any timestamp columns to timestamptz.
Now, we already know how many people were supposed to show up each month, but how does that compare to those who actually do?
We need to join in the checkins not only on the guest_id but also when the
day parts match up. date_trunc('day', date) will do that for us. Again we
want a left outer join.
Counting is a little tricker than before. Total checkins isn’t so bad, just
count on the id column since it will be nil on no checkin due to the outer
join. But for checkins and lunch, we need to do a bit more work. lunch and
checkins.id is not null will only be true when lunch is true and when the
join matched. All other cases will either be false or null, and we use
nullif again to make those falses become null so they’re not counted.
The change to the outer query is pretty simple, just two new sums in the select list.
with counts_by_day as (
select
v::date as visiting_on,
count(visiting_range) as total,
count(nullif(lunch, false)) as lunch,
+ count(checkins.id) as checkins,
+ count(nullif(lunch and checkins.id is not null, false) ) as checklunch
from generate_series(
'2013-09-01',
date_trunc('month', now()) + '3 months - 1 day'::interval,
'1 day') as v
left outer join guests on v::date <@ visiting_range
+ left outer join checkins on date_trunc('day', v::date) = date_trunc('day', checkins.created_at) and guests.id=checkins.guest_id
where extract(isodow from v) < 6
group by 1
)
select
to_char(date_trunc('month', visiting_on), 'Month YYYY') as month,
sum(total) as total,
sum(lunch) as lunch,
+ sum(checkins) as checkins,
+ sum(checkinslunch) as checkinslunch
from counts_by_day
group by date_trunc('month', visiting_on)
order by date_trunc('month', visiting_on) asc;
And there we go. While this query can seem a bit daunting at first, seeing it put together step-by-step makes it easier to understand. I hope some of these ideas are useful the next time you have to write a query.