Since my times using Crystal Reports (around 2005) I've been used to "a group by flattens all your columns into one value". It was something inescapable (unless big hacks)
But lately, with complex types in postgres, and smarter aggregation functions, I've grown to create nested structures in group bys when I want to retain information about individual records inside the group.
It probably violates a few normalization rules, but who cares?
So here's it:
select country,
count(*),
jsonb_pretty(jsonb_agg((id, status, deleted_at) order by deleted_at desc)) as statuses
from customer
group by country having count(*)>1
order by count desc;
I think it's super cool to be able to generate a result with all the nested information of the integrants of each group, and even more, being able to order by, inside the groups!I imagine there's a lot of cool stuff you can do later with UNNEST-style functions, that would allow us to recreate the registers as tables, to do further queries.
Or maybe this is just another hack, like the ones I was writing in 2005. But anyhow, they are very useful for data explorations.
Happy hacking
Entradas
