什么是 AsOf Join？

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

AsOf Join 旨在解决的问题之一是查找某个随时间变化的属性在特定时间点的值。这种用例非常常见，其名称也由此而来：

给我该属性在此时间点的值.

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

投资组合示例数据集

我们从一个具体示例开始。假设我们有一个包含股票价格和时间戳的表：

我们还有另一个表，其中包含在不同时间点的投资组合持仓>：

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

内联 AsOf Join

通过使用 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;

这会将该时间点的持仓值附加到每一行。

它实质上执行了一个通过查找价格表中附近值来定义的函数。另请注意，缺失的股票代码值没有匹配项，因此不会出现在输出中。

外联 AsOf Join

因为 AsOf 最多从右侧生成一个匹配项，所以左侧表不会因为连接而增大，但如果右侧缺少时间，它可能会缩小。要处理这种情况，您可以使用外联 AsOf Join：

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 Join

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

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

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

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

关于在 AsOf Join 中使用 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 Join 中使用的>=条件，两个表中的值可能不同。AsOf Join 旨在根据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关键字的默认行为引起的任何潜在混淆。

另请参阅

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