4 ms·

This removes the self join needed to ask the query "(latest/first/biggest/smallest) record per (customer/user/entity)". Nice!

caseyohara 7y ago
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?
- eyelidlessness 7y ago
  Yes, you can `DISTINCT ON` with a `DATE_TRUNC` expression which should get you what you want.
- lenzm 7y ago
  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.
- claytonjy 7y ago
  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.
- dagss 7y ago
  Also consider expressin this with a lateral join. Scroll a bit down here for a similar example. https://stackoverflow.com/questions/11472790/postgres-analogue-to-cross-apply-in-sql-server https://stackoverflow.com/questions/11472790/postgres-analog...
Zecc 7y ago
SQLite takes it one step further. https://sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause 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 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.

caseyohara 7y ago

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?

eyelidlessness 7y ago
Yes, you can `DISTINCT ON` with a `DATE_TRUNC` expression which should get you what you want.
lenzm 7y ago
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.
claytonjy 7y ago
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.
dagss 7y ago
Also consider expressin this with a lateral join. Scroll a bit down here for a similar example. https://stackoverflow.com/questions/11472790/postgres-analogue-to-cross-apply-in-sql-server https://stackoverflow.com/questions/11472790/postgres-analog...

Zecc 7y ago

SQLite takes it one step further. https://sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause 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 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.