如何使用此异常匹配条件编写联接？

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-如果这些是您的主键，则可能已经有了。
多列索引也可能有所帮助，尤其是在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提琴。

DISTINCT在此相关答案中对该技术的更多说明：

• 在每个GROUP BY组中选择第一行？

我进行了一些测试，因为我怀疑第一个查询无法很好地扩展。小桌子很快，但是大桌子不好。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是可能的最大整数。适应您的数据类型。

3）非常简单，几乎一样快

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

通常， 相关的子查询 往往很慢。但这只不过可以从（覆盖）索引中选择一个值，否则就很容易竞争。

附加ORDER BY项val（粗体强调）似乎毫无意义。但是添加它可以使查询计划者确信可以y_mult_idx从上方使用多列索引，因为排序顺序是匹配的。注意

仅索引使用y_mult_idx进行扫描。

在EXPLAIN输出中。

测试用例

经过激烈的辩论和多次更新，我收集了迄今为止发布的所有查询，并提供了一个测试用例以进行快速概述。我只使用1000行，因此SQLfiddle不会因较慢的查询而超时。但是在我所有的本地测试中，前4位（Erwin
2，Clodoaldo，a_horse，Erwin 3）都是线性增长的。再次更新以包括我最新添加的内容，现在通过性能改进格式和顺序：

Big SQL Fiddle 比较性能。