AsOf Join
什么是 AsOf Join？
时间序列数据并不总是完美对齐。 时钟可能有轻微偏差，或因果之间存在延迟。 这会让两组有序数据的关联变得困难。 AsOf join 正是用来解决这类问题的工具。
AsOf join 的一个典型用途是： 在某个时间点查找一个变化属性当时的值。 这个场景非常常见，名称也由此而来：
给我这个属性在该时刻的值（as of this time）。
更一般地说，AsOf join 内置了一些常见的时序分析语义， 而这些语义用标准 SQL 实现通常会很繁琐且性能较差。
投资组合示例数据集
先看一个具体示例。
假设我们有一张带时间戳的股票 prices 表：
| ticker | when | price |
|---|---|---|
| APPL | 2001-01-01 00:00:00 | 1 |
| APPL | 2001-01-01 00:01:00 | 2 |
| APPL | 2001-01-01 00:02:00 | 3 |
| MSFT | 2001-01-01 00:00:00 | 1 |
| MSFT | 2001-01-01 00:01:00 | 2 |
| MSFT | 2001-01-01 00:02:00 | 3 |
| GOOG | 2001-01-01 00:00:00 | 1 |
| GOOG | 2001-01-01 00:01:00 | 2 |
| GOOG | 2001-01-01 00:02:00 | 3 |
还有另一张在不同时间点记录持仓的 holdings 表：
| ticker | when | shares |
|---|---|---|
| APPL | 2000-12-31 23:59:30 | 5.16 |
| APPL | 2001-01-01 00:00:30 | 2.94 |
| APPL | 2001-01-01 00:01:30 | 24.13 |
| GOOG | 2000-12-31 23:59:30 | 9.33 |
| GOOG | 2001-01-01 00:00:30 | 23.45 |
| GOOG | 2001-01-01 00:01:30 | 10.58 |
| DATA | 2000-12-31 23:59:30 | 6.65 |
| DATA | 2001-01-01 00:00:30 | 17.95 |
| DATA | 2001-01-01 00:01:30 | 18.37 |
将这些表加载到 Goose：
CREATE TABLE prices AS FROM 'https://pub.kumose.cc/goose/data/prices.csv';
CREATE TABLE holdings AS FROM 'https://pub.kumose.cc/goose/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;
这会将该时刻的持仓价值附加到每一行：
| ticker | when | value |
|---|---|---|
| APPL | 2001-01-01 00:00:30 | 2.94 |
| APPL | 2001-01-01 00:01:30 | 48.26 |
| GOOG | 2001-01-01 00:00:30 | 23.45 |
| GOOG | 2001-01-01 00:01:30 | 21.16 |
本质上，它是通过在 prices 表中查找相邻时间值来计算结果。
另外，缺失 ticker 匹配的行不会出现在输出中。
外连接 AsOf Join
由于 AsOf 从右表最多匹配一行， 左表行数不会因为 join 而增加， 但当右表缺少对应时间时，结果可能变少。 要处理这种情况，可以使用 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 的价格和值，而不是丢弃左表行。
| ticker | when | value |
|---|---|---|
| APPL | 2000-12-31 23:59:30 | |
| APPL | 2001-01-01 00:00:30 | 2.94 |
| APPL | 2001-01-01 00:01:30 | 48.26 |
| GOOG | 2000-12-31 23:59:30 | |
| GOOG | 2001-01-01 00:00:30 | 23.45 |
| GOOG | 2001-01-01 00:01:30 | 21.16 |
| DATA | 2000-12-31 23:59:30 | |
| DATA | 2001-01-01 00:00:30 | |
| DATA | 2001-01-01 00:01:30 |
在 AsOf Join 中使用 USING 关键字
到目前为止，我们一直显式写出 AsOf 条件。
当两表列名相同时，SQL 还提供了更简化的 join 条件语法。
该语法通过 USING 关键字列出需要做相等比较的字段。
AsOf 同样支持此语法，但有两个限制：
- 最后一个字段用于不等式比较
- 不等式固定为
>=（最常见场景）
这样首个查询可以写为：
SELECT ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");
ASOF Join 中 USING 的列选择说明
在 join 中使用 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 使用 >= 条件，两表值可能不同。
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 默认行为造成歧义。