News for hackers. Stuff that matters.
Re:Distinct on: a confusing, unique and useful feature in Po... by beckingz on Friday March 20, 2020 @01:50PM (#22639645)
This removes the self join needed to ask the query "(latest/first/biggest/smallest) record per (customer/user/entity)".

Nice!

Re:Distinct on: a confusing, unique and useful feature in Po... by Zecc on Friday March 20, 2020 @04:29PM (#22641333)
SQLite takes it one step further.

https://sqlite.org/quirks.html#aggregate_queries_can_contain...

> if an aggregate query contains a single min() or max() function, then the values of columns used in the output are taken from the row where the min() or max() value was achieved. If two or more rows have the same min() or max() value, then the columns values will be chosen arbitrarily from one of those rows.

Also here:

https://sqlite.org/lang_select.html#bareagg

> When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum. [...] There is still an ambiguity if two or more of the input rows have the same minimum or maximum value or if the query contains more than one min() and/or max() aggregate function.

Would have been nice if adding an ORDER BY would allow disambiguating.

Re:Distinct on: a confusing, unique and useful feature in Po... by caseyohara on Friday March 20, 2020 @02:56PM (#22640445)
I've been struggling with a similar query recently perhaps better suited for a time series DB. Can DISTINCT ON be used to ask the query "latest record per customer per day". For example, let's say we have a bunch of devices that send sensor readings arbitrarily throughout each day. Could DISTINCT ON be used to query the last reading of each day per device?
Re:Distinct on: a confusing, unique and useful feature in Po... by dagss on Friday March 20, 2020 @05:01PM (#22641623)
Also consider expressin this with a lateral join.

Scroll a bit down here for a similar example.

https://stackoverflow.com/questions/11472790/postgres-analog...

Re:Distinct on: a confusing, unique and useful feature in Po... by lenzm on Friday March 20, 2020 @03:05PM (#22640533)
Absolutely. You'll need an expression to get the date out of the timestamp, then you want rows that are distinct on customer_id & date order by timestamp desc.
Re:Distinct on: a confusing, unique and useful feature in Po... by claytonjy on Friday March 20, 2020 @03:23PM (#22640708)
I've been using Timescale DB, a postgres extension, to store our sensor-produced timeseries data and it handles this pretty well. DISTINCT ON seems to ruin the query plan there, by forcing sequential, non-index scans of each subtable (timescale breaks tables into time-buckets behind the scenes, so it can skip searching tables in time-filtered queries). I've had good performance with their "time_bucket" and "last" functions, though, and not having to use some NoSQL time series database has been really nice.
Re:Distinct on: a confusing, unique and useful feature in Po... by eyelidlessness on Friday March 20, 2020 @03:04PM (#22640526)
Yes, you can `DISTINCT ON` with a `DATE_TRUNC` expression which should get you what you want.