5

I've got a big table (~9M rows) and want to group the rows on a field containing the year. So far that's pretty easy:

// greatly simplified:
SELECT count(*), year FROM dataset GROUP BY year ORDER BY 2;

We defined some irregular time periods spanning multiple years:

<1945, 1946-1964, 1965-1974, 1975-1991, 1992-2005 and >2005

I've got no clue on how to group these results in the group by clause. I could make subquery's for every time period.

SELECT
  ( SELECT count(*) FROM dataset WHERE year <= 1945 AND ...... ) AS pre1945,
  ( ....) AS period2,
  ....
FROM dataset

But that feels not right and I'm wondering if it was possible to let Postgresql do it. Especially because the query is a strong simplification of the real query: it has multiple conditions, amongst them a ST_within clause spanning four tables. So choosing the subquery-approach results in a bloated query.

Is there a better way to create this result?

2 Answers 2

8

Use conditional counting:

select count(case when year <= 1945 then 1 end) as pre1945,
       count(case when year between 1946 and 1964 then 1 end) as period2,
       count(case when year between 1965 and 1974 then 1 end) as period3,
       ...
from ...
where ...;

This works because count() ignores null values and the case statement returns a null for values outside of the range it tests for (an else null is implicit).

With the upcoming 9.4 version you can re-write this as

select count(*) filter (where year <= 1945) as pre1945,
       count(*) filter (where year between 1946 and 1964) as period2,
       count(*) filter (where year between 1965 and 1974) as period3,
       ...
from ...
where ...;
5
  • I never knew I could use conditions within count(). It works as expected, although the query itself is very slow. Even with an index on year.
    – stUrb
    Commented Dec 16, 2014 at 9:28
  • 1
    @stUrb: An index is not going to help as long as you are processing the whole table anyway - unless the index is substantially smaller than the table itself and an index-only scan is possible. Commented Dec 16, 2014 at 10:07
  • Hmm thats a pitty. So there is no way to speed it up?
    – stUrb
    Commented Dec 16, 2014 at 10:08
  • @stUrb. How could anybody say without knowing any relevant details? I suggest you start a new question providing all relevant details. Be sure to read the tag info for [postgresql-performance] first. Commented Dec 16, 2014 at 10:12
  • Ghehe I reread my comment and thought, how is anyone going to answer that :) Sorry about that.
    – stUrb
    Commented Dec 16, 2014 at 10:15
8

If you want the result as rows in instead of as columns as in @a_horse's answer then create the year ranges in a CTE and join the table to it

with years(year_range) as ( values
    (int4range(1900, 1945, '[]')),
    (int4range(1946, 1964, '[]')),
    (int4range(1965, 1974, '[]')),
    (int4range(1975, 1991, '[]')),
    (int4range(1992, 2005, '[]')),
    (int4range(2005, 2014, '[]'))
)
select year_range, count(*)
from
    dataset d
    left join
    years y on d.year <@ y.year_range
group by 1 
order by 1

http://www.postgresql.org/docs/current/static/rangetypes.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.