0

I have two columns in the database:

Value | Date
---------------------
1.3   | 1410374280000

Value is a float and Date is an epoch integer in milliseconds

I am trying to optimize this query as this returns a few thousand rows.

SELECT * FROM data WHERE date >= [some_time_in_the_past]

Since I am rendering a chart on the front end, I need far fewer data points. So I am truncating the date.

SELECT data.date / 5000 * 5000 AS truncated, data.price FROM data
WHERE date >= [some_time_in_the_past]

This above truncates the date to every 5 seconds. So now I want to SELECT DISTINCT on it.

However:

SELECT DISTINCT truncated, price

This will SELECT DISTINCT on both the truncated_date as well as the price. Is there a way to only SELECT DISTINCT on the truncated date and get the price column too.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
samol
  • 18,950
  • 32
  • 88
  • 127
  • First you show a table with a column named `Value`, then you continue with a column named `price`? Also `"get the price column too"` doesn't define what you want. Obviously, you want to fold sets of rows with the same truncated timestamp into one. You have to define *which* price to pick from the set. First, last, avg, random, arbitrary pick? – Erwin Brandstetter Sep 23 '14 at 04:09

3 Answers3

0

distinct on

select distinct on (truncated) *
from (
    select data.date / 5000 * 5000 as truncated, data.price
    from data
    where date >= [some_time_in_the_past]
) s
order by truncated, price desc

If you want the lowest price change from price desc to price asc

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
0

Maybe a possible solution is to group by the truncated date column and calculate the average of the price column:

SELECT truncated,
       AVG(price)
  FROM data
 GROUP
    BY truncated
DirkNM
  • 2,614
  • 15
  • 21
0

To pick an arbitrary price with DISTINCT ON for each set of identical truncated (which fits your non-existing definition): 

SELECT DISTINCT ON (1)
       (date / 5000) * 5000 AS truncated, price
FROM   data
WHERE  date >= [some_time_in_the_past]
ORDER  BY 1;

Add more ORDER BY expressions to pick something more specific, like @Clodoaldo already provided.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228