4 ms·
One of the hardest types of queries in a lot of DBs is the simple `min-by` or `max-by` queries - e.g. "find the most recent post for each user." Seems like Post
by NightMKoder 5y ago
One of the hardest types of queries in a lot of DBs is the simple `min-by` or `max-by` queries - e.g. "find the most recent post for each user." Seems like Postgres has a solution - `DISTINCT ON` - though personally I've always been a fan of how BigQuery does it: `ARRAY_AGG`. e.g.
SELECT user_id, ARRAY_AGG(STRUCT(post_id, text, timestamp) ORDER BY timestamp DESC LIMIT 1)[SAFE_OFFSET(0)].*
FROM posts
GROUP BY user_id
`DISTINCT ON` feels like a hack and doesn't cleanly fit into the SQL execution framework (e.g. you can't run a window function on the result without using subqueries). This feels cleaner, but I'm not actually aware of any other DBMS that supports `ARRAY_AGG` with `LIMIT`.
- mnahkies 5y agoI've generally used `PARTITION BY` to achieve the same effect in big query (similar to what was suggested in the article for postgres). Does the `ARRAY_AGG` approach offer any advantages?
- NightMKoder 5y agoI assume you mean `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) as row_number` with a `WHERE row_number = 1`. We ran into issues with memory usage (i.e. queries erroring because of too high memory usage). The `ARRAY_AGG` approach uses a simple heap, so it's as efficient as MIN/MAX because it can be computed in parallel. ROW_NUMBER however needs all the rows on one machine to number them properly. ARRAY_AGG combination is associative whereas ROW_NUMBER combination isn't.
- mnahkies 5y agoThat makes sense, I might have a play with the array agg approach. I'm especially curious if it has any impact on slot time consumed
- bbirk 5y agoI would use lateral join. Alternatively you can use window function instead of lateral join, buy from my experience lateral joins are usually faster for this kind of top-N queries in postgres. select user_table.user_id, tmp.post_id, tmp.text, tmp.timestamp from user_table left outer join lateral ( select post_id, text, timestamp from post where post.user_id = user_table.user_id order by timestamp limit 1 ) tmp on true order by user_id limit 30;
- zX41ZdbW 5y agoIn ClickHouse we have argMin/argMax aggregate functions. And also LIMIT BY. It looks like this: ... ORDER BY date DESC LIMIT 1 BY user_id
- zX41ZdbW 5y agoAnd we have groupArray aggregate function with limit. > I'm not actually aware of any other DBMS that supports `ARRAY_AGG` with `LIMIT`. So, put ClickHouse in you list :)