CODEWITHSUNDEEP
sqlpostgresql
Jonathan Petitcolas
Jonathan Petitcolas

Reputation: 4574

retrieve several values previous to several given dates

I got a values table such as:

id | user_id | value | date
---------------------------------
1  |      12 |    38 | 2014-04-05
2  |      15 |    19 | 2014-04-05
3  |      12 |    47 | 2014-04-08

I want to retrieve all values for given dates. However, if I don't have a value for one specific date, I want to get the previous available value. For instance, with the above dataset, if I query values for user 12 for dates 2014-04-07 and 2014-04-08, I need to retrieve 38 and 47.

I succeeded using two queries like:

SELECT *
FROM values
WHERE date <= $date
ORDER BY date DESC
LIMIT 1

However, it would require dates.length requests each time. So, I'm wondering if there is any more performant solution to retrieve all my values in a single request?

Upvotes: 1

Views: 26

Answers (1)

Patrick
Patrick

Reputation: 32179

In general, you would use a VALUES clause to specify multiple values in a single query.

If you have only occasional dates missing (and thus no big gaps in dates between rows for any particular user_id) then this would be an elegant solution:

SELECT dt, coalesce(value, lag(value) OVER (ORDER BY dt)) AS value
FROM (VALUES ('2014-04-07'::date), ('2014-04-08')) AS dates(dt)
LEFT JOIN "values" ON "date" = dt AND user_id = 12;

The lag() window function picks the previous value if the current row does not have a value.

If, on the other hand, there may be big gaps, you need to do some more work:

SELECT DISTINCT dt, first_value(value) OVER (ORDER BY diff) AS value
FROM (
  SELECT dt, value, dt - "date" AS diff
  FROM (VALUES ('2014-04-07'::date), ('2014-04-08')) AS dates(dt)
  CROSS JOIN "values"
  WHERE user_id = 12) sub;

In this case a CROSS JOIN is made for user_id = 12 and differences between the dates in the VALUES clause and the table rows computed, in a sub-query. So every row has a value for field value. In the main query the value with the smallest difference is selected using the first_value() window function. Note that ordering on diff and picking the first row would not work here because you want values for multiple dates returned.

Upvotes: 1

Related Questions