I really like SQL. It is so compact. So nice results can be achieved with relatively small amount of code. I know, I know … It can be complex puzzle when use with long subqueries or other “modern” SQL features. While I am not SQL master I have such problems too.
But recently I learned new SQL feature. Or if you prefer I can call it ‘trick’. I wanted to count my records by value in one of the fields grouped in series by date. I wanted to get result table as follows:
My first approach to this problem was
SELECT created_at::date, SUM(CASE WHEN previous_state='a' THEN 1 ELSE 0 END) AS state_a, SUM(CASE WHEN previous_state='b' THEN 1 ELSE 0 END) AS state_b, SUM(CASE WHEN previous_state='c' THEN 1 ELSE 0 END) AS state_c FROM undos GROUP BY 1 ORDER BY 1 DESC
I got the result I expected, so work done. No! I could not resist to
find a way to get rid of this not so pretty
sum-case-when-then-else-end combo. I would lie if I told that I found
solution fast. But it is not story about searching solution
… Sorry. Anyway it was not fascinating story. Long story short: try
to look for
pivoting rows in postgres.
Proper solution you can find below.
SELECT created_at::date, COUNT(*) FILTER (WHERE previous_state = 'a') AS state_a, COUNT(*) FILTER (WHERE previous_state = 'b') AS state_b, COUNT(*) FILTER (WHERE previous_state = 'c') AS state_c FROM undos GROUP BY 1 ORDER BY 1 DESC;
FILTER is the right tool here.
FILTER is extending
aggregating function with extra condition that make
COUNT works only
with records for which
WHERE predicate is true. It is good at
pivoting rows to columns and probably couple more abut which I have no
idea. In case of performance query plan is the same for both
FILTER is part of SQL:2003
standard — so it is not
new, cool stuff. One the other hand
FILTER does not have big
adoption. As far as I know only
Postgresql has adopted it in version
9.4 released in 2014.
I encourage every programmer to learn SQL and try to use it for generating some reports. I believe that SQL skill will make better from any of you. Maybe some 2018 resolution?