PostgreSQL can provide high performance summaries over multi-million record tables, and supports some great SQL sugar to make it concise and readable, in particular aggregate filtering, a feature unique to PostgreSQL and SQLite.
A huge amount of reporting is about generating percentages: for a particular condition, what is a value relative to a baseline.
Some Sample Data
Here's a quick "sales table" with three categories ("a" and "b" and "c") and one million random values between 0 and 10:
CREATE TABLE sales AS SELECT a, b, CASE WHEN random() < 0.4 THEN 'bird' ELSE 'bee' END AS c, 10 * random() AS value FROM generate_series(1,1000) a, generate_series(1,1000) b;
The Olden Days
In the bad-old-days, generating a percentage might involve adding in a subquery to generate the total before calculating the percentage. To "find the % of value where c is 'bee'":
SELECT 100.0 * sum(value) / (SELECT sum(value) AS total FROM sales) AS bee_pct FROM sales WHERE c = 'bee'
This is all very nice, but what if I also want to calculate the percentage of sales with an "a" value > 900?
Suddenly I'm running two queries, or perhaps building a CTE like this:
WITH total AS ( SELECT sum(value) AS total FROM sales ), bee AS ( SELECT sum(value) AS bee FROM sales WHERE c = 'bee' ), a900 AS ( SELECT sum(value) AS a900 FROM sales WHERE a > 900 ) SELECT 100.0 * bee / total AS bee_pct, 100.0 * a900 / total AS a900_pct FROM total, bee, a900;
Yuck! That's ugly! Also, it scans the table three times. Is there another way? Sure, there's always another way, but it's not necessarily any nicer.
SELECT 100.0 * sum(CASE WHEN c = 'bee' THEN value ELSE 0.0 END) / sum(value) AS bee_pct, 100.0 * sum(CASE WHEN a > 900 THEN value ELSE 0.0 END) / sum(value) AS a900_pct FROM sales;
but sometimes people forget that aggregate functions are also window functions,
so they can accept the same controls as more exciting window functions like
SELECT 100.0 * sum(value) FILTER (WHERE c = 'bee') / sum(value) AS bee_pct, 100.0 * sum(value) FILTER (WHERE a > 900) / sum(value) AS a900_pct FROM sales;
This is so much clearer than the other alternatives, and it runs faster than them too!
With modern PostgreSQL, this single scan of the table
will be parallelized.
Even better, you can use any aggregate function at all with a filter condition,
which is not really possible with the
SELECT stddev(value) FILTER (WHERE c = 'bee') AS bee_stddev, stddev(value) FILTER (WHERE a > 900) AS a900_stddev FROM sales;
Fish in your Data Lake
For simple reporting and data analysis in a data lake, there's nothing quite as nice as a good wide materialized view that gathers all the columns of interest into a single flat table, and the liberal application of aggregate filters.
Aggregate filters can even be combined with standard
GROUP clauses to get a
quick break down of statistics within groups.
SELECT b / 100 AS b_div_100, stddev(value) FILTER (WHERE c = 'bee') AS bee_stddev, stddev(value) FILTER (WHERE a > 900) AS a900_stddev FROM sales GROUP BY 1;
- When building up analytical queries, think about what you can extract in one pass through the table, using aggregate filters to strip out just the information you want.
- When building up an analytical lake, consider materializing interesting columns into a query view and using aggregate filters to explore the contents.
August 24, 2021 •More by this author