sql - 如何使用这种不寻常的匹配条件编写连接？

Question

我想“左连接”一个表，这样一个值不仅连接到一个匹配行，而且连接到任何后续的非匹配行，直到下一个匹配行。换句话说，我想用之前的非空值填充空值。

示例数据和期望的结果:

表x:

 id 
----
  1
  2
  3
  4
  5

表y:

 id | val 
----+-----
  1 | a
  4 | b

select x.id, y.val from x left join y on x.id=y.id order by x.id;的结果:

 id | val 
----+-----
  1 | a
  2 | 
  3 | 
  4 | b
  5 | 

期望的结果:

 id | val 
----+-----
  1 | a
  2 | a
  3 | a
  4 | b
  5 | b

Answer 1

指数

在 x.id 和 y.id 上创建索引 - 如果这些是您的主键，您可能已经有了。
多列索引也可能有所帮助，尤其是对于 index only scans。在 PG 9.2+ 中:

CREATE INDEX y_mult_idx ON y (id DESC, val)

但是，在我的测试中，这个索引一开始并没有被使用。必须将(否则毫无意义)val 添加到 ORDER BY 以说服查询规划器排序顺序匹配。请参阅查询3。

索引在这个综合设置中几乎没有什么不同。但是对于具有更多列的表，从表中检索 val 变得越来越昂贵，从而使“覆盖”索引更具吸引力。

查询

1) 简单

SELECT DISTINCT ON (x.id)
       x.id, y.val
FROM   x
JOIN   y ON y.id <= x.id
ORDER  BY x.id, y.id DESC;

SQL Fiddle.

在此相关答案中使用 DISTINCT 技术的更多解释:

• Select first row in each GROUP BY group?

我进行了一些测试，因为我怀疑第一个查询无法很好地扩展。小 table 很快，但大 table 就不好了。 Postgres 没有优化计划，而是从(有限的)交叉连接开始，成本为 O(N²)。

2) 快

这个查询仍然相当简单并且扩展性非常好:

SELECT x.id, y.val
FROM   x
JOIN  (SELECT *, lead(id, 1, 2147483647) OVER (ORDER BY id) AS next_id FROM y) y
       ON  x.id >= y.id
       AND x.id <  y.next_id
ORDER  BY 1;

窗口函数lead()是有帮助的。我利用提供默认值的选项来覆盖最后一行的极端情况:2147483647 是 biggest possible integer .适应您的数据类型。

3) 非常简单而且几乎一样快

SELECT x.id
     ,(SELECT val FROM y WHERE id <= x.id ORDER BY id DESC, <b>val</b> LIMIT 1) AS val
FROM   x;

通常，相关子查询 往往很慢。但是这个可以只从(覆盖)索引中选择一个值，而且非常简单，可以竞争。

额外的 ORDER BY 项 val(粗体强调)似乎毫无意义。但是添加它可以说服查询规划器使用上面的多列索引 y_mult_idx 是可以的，因为排序顺序匹配。注意

Index Only Scan using y_mult_idx ..

在 EXPLAIN 输出中。

测试用例

经过激烈的辩论和多次更新后，我收集了迄今为止发布的所有查询，并制作了一个测试用例以供快速概览。我只使用 1000 行，因此 SQLfiddle 不会因较慢的查询而超时。但是前 4 名(Erwin 2、Clodoaldo、a_horse、Erwin 3)在我所有的本地测试中呈线性增长。再次更新以包括我的最新添加，现在改进格式和按性能排序:

Big SQL Fiddle comparing performance.