什么是AsOf Join？

时间序列数据并不总是完美对齐的。 时钟可能略有偏差，或者原因和结果之间可能存在延迟。 这可能使得连接两组有序数据变得具有挑战性。 AsOf连接是解决这个问题及其他类似问题的工具。

AsOf连接用于解决的问题之一是在特定时间点找到变化属性的值。这个用例非常常见，以至于名称就来源于此：

给我这个时间点的属性值。

然而，更一般地说，AsOf连接体现了一些常见的时间分析语义，这些语义在标准SQL中实现起来可能既繁琐又缓慢。

投资组合示例数据集

让我们从一个具体的例子开始。 假设我们有一个带有时间戳的股票prices表：

我们有另一个表格，其中包含了不同时间点的投资组合holdings：

要将这些表加载到DuckDB，请运行：

CREATE TABLE prices AS FROM 'https://duckdb.org/data/prices.csv';
CREATE TABLE holdings AS FROM 'https://duckdb.org/data/holdings.csv';

内部AsOf连接

我们可以通过使用AsOf Join来找到持有时间戳之前的最新价格，从而计算每个持有在该时间点的价值：

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p
       ON h.ticker = p.ticker
      AND h.when >= p.when;

这将当时的持有价值附加到每一行：

它本质上执行了一个通过查找prices表中附近值来定义的函数。 还要注意，缺失的ticker值没有匹配项，并且不会出现在输出中。

外部AsOf连接

因为AsOf最多从右侧产生一个匹配， 所以左侧表不会因为连接而增长， 但如果右侧缺少时间，它可能会缩小。 为了处理这种情况，你可以使用一个外部 AsOf 连接：

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF LEFT JOIN prices p
            ON h.ticker = p.ticker
           AND h.when >= p.when
ORDER BY ALL;

正如你所料，当没有股票代码或时间在价格开始之前时，这将产生NULL价格和值，而不是删除左侧的行。

使用USING关键字的AsOf连接

到目前为止，我们已经明确指定了AsOf的条件， 但SQL也有一个简化的连接条件语法， 适用于两个表中列名相同的常见情况。 此语法使用USING关键字列出应比较相等的字段。 AsOf也支持此语法，但有两个限制：

• 最后一个字段是不等式
• 不等式是 >=（最常见的情况）

我们的第一个查询可以写成：

SELECT ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

关于在ASOF连接中使用USING进行列选择的说明

当你在连接中使用USING关键字时，USING子句中指定的列会在结果集中合并。这意味着如果你运行：

SELECT *
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

你将只获取到列 h.ticker, h.when, h.shares, p.price。列 ticker 和 when 只会出现一次，其中 ticker 和 when 来自左表（holdings）。

这种行为对于ticker列来说是正常的，因为两个表中的值是相同的。然而，对于when列，由于在AsOf连接中使用了>=条件，两个表中的值可能会有所不同。AsOf连接旨在根据when列，将左表（holdings）中的每一行与右表（prices）中最近的前一行进行匹配。

如果你想从两个表中检索when列以查看两个时间戳，你需要明确列出列，而不是依赖*，如下所示：

SELECT h.ticker, h.when AS holdings_when, p.when AS prices_when, h.shares, p.price
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

这确保您从两个表中获取完整的信息，避免由USING关键字的默认行为引起的任何潜在混淆。

See Also

有关实现细节，请参阅博客文章“DuckDB的AsOf连接：模糊时间查找”。