什么是 AsOf Join？

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

AsOf Join 的用途之一是查找特定时间点上某个变化属性的值。这种用例非常普遍，其名称也由此而来。

获取截止至此时的属性值.

更广泛地说，AsOf Join 涵盖了一些常见的时序分析语义，而在标准 SQL 中实现这些语义通常既繁琐又缓慢。

投资组合示例数据集

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

我们还有另一张包含不同时间点投资组合 holdings（持仓）的表

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

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

内联 AsOf Join (Inner AsOf Joins)

我们可以通过 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 Join (Outer AsOf Joins)

由于 AsOf 最多从右侧表产生一个匹配项，因此左侧表不会因为连接而增加行数，但如果右侧表存在时间缺失，左侧表可能会减少。为了处理这种情况，可以使用 outer（外联）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;

正如预期的那样，当没有对应的 ticker 或者时间早于价格记录的起始时间时，这会产生 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 列将仅出现一次，且均来自左表（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：模糊时间查找”。