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?
[ Parent
| on HN ]
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.