sql - 检索几个给定日期之前的几个值

Question

我有一个 values 表，例如:

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

我想检索给定日期的所有值。但是，如果我没有某个特定日期的值，我想获取以前的可用值。例如，对于上述数据集，如果我查询用户 12 的日期 2014-04-07 和 2014-04-08 的值，我需要检索 38 和 47 .

我成功地使用了两个查询:

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

但是，每次都需要 dates.length 请求。所以，我想知道是否有任何更高效的解决方案可以在单个请求中检索我的所有值？

Answer 1

一般来说，您会使用 VALUES在单个查询中指定多个值的子句。

如果您只是偶尔缺少日期(因此任何特定 user_id 的行之间的日期没有太大差距)那么这将是一个优雅的解决方案:

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;

lag() window function如果当前行没有 value，则选择前一个 value。

另一方面，如果可能存在较大差距，您需要做更多的工作:

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;

在这种情况下，为 user_id = 12 创建了一个 CROSS JOIN 并且 VALUES 子句中的日期与计算的表行之间的差异, 在子查询中。所以每一行都有一个字段 value 的值。在主查询中，使用 first_value() 窗口函数选择差异最小的 value。请注意，在 diff 上排序并选择第一行在这里不起作用，因为您希望返回多个日期的值。