asof join
语法
aj(leftTable, rightTable, matchingCols, [rightMatchingCols])
参数
-
leftTable 和 rightTable 是连接的表。
-
matchingCols 是表示连接列的字符串标量或向量。
-
rightMatchingCols 是表示右表连接列的字符串标量或向量。当 leftTable和rightTable 至少有一个连接列不同时,必须指定 rightMatchingCols。返回结果中的连接列与左表的连接列名称相同。
详情
asof join和左连接函数十分相似,但有以下区别:
-
假设最后一个连接列为time,对于左表中某 time=t 的行:
-
如果右表中其它连接列都匹配的记录中有 time=t 的记录,则取之(若有多行,则取其中最后一行);
-
若没有time=t的记录,则取这些记录中在t之前的最近时间对应的行(若有多行,则取其中最后一行)。
-
-
如果只有1个连接列,则aj函数假定右表已按照连接列排过序。如果有多个连接列,则aj函数假定右表根据除最后一个连接列外的其他连接列定义分组,每个分组根据最后一个连接列排序。右表的其他连接列不需要排序。如果这些条件不符合,处理将与期望值不符。左表不需要排序。
asof join的最后一个连接列通常为时间类型,也可为整数类型,以及UUID或IPADDR类型。
若左表或右表为分区表时,用于分组的连接列(即除了最后一个连接列之外的所有连接列)必须包含全部分区字段。
注意:若 leftTable 不是分布式表,则其 rightTable 也不能是分布式表。
例子
t1 = table(2015.01.01+(0 31 59 90 120) as date, 1.2 7.8 4.6 5.1 9.5 as value)
t2 = table(2015.02.01+(0 15 89 89) as date, 1..4 as qty);
t1;
date | value |
---|---|
2015.01.01 | 1.2 |
2015.02.01 | 7.8 |
2015.03.01 | 4.6 |
2015.04.01 | 5.1 |
2015.05.01 | 9.5 |
t2;
date | qty |
---|---|
2015.02.01 | 1 |
2015.02.16 | 2 |
2015.05.01 | 3 |
2015.05.01 | 4 |
select * from lsj(t1, t2, `date);
date | value | qty |
---|---|---|
2015.01.01 | 1.2 | |
2015.02.01 | 7.8 | 1 |
2015.03.01 | 4.6 | |
2015.04.01 | 5.1 | |
2015.05.01 | 9.5 | 3 |
select * from aj(t1, t2, `date);
date | value | t2_date | qty |
---|---|---|---|
2015.01.01 | 1.2 | ||
2015.02.01 | 7.8 | 2015.02.01 | 1 |
2015.03.01 | 4.6 | 2015.02.16 | 2 |
2015.04.01 | 5.1 | 2015.02.16 | 2 |
2015.05.01 | 9.5 | 2015.05.01 | 4 |
select * from aj(t1, t2, `date) where t1.date>=2015.03.01;
date | value | t2_date | qty |
---|---|---|---|
2015.03.01 | 4.6 | 2015.02.16 | 2 |
2015.04.01 | 5.1 | 2015.02.16 | 2 |
2015.05.01 | 9.5 | 2015.05.01 | 4 |
asof连接的常用场景是在时间字段上作连接,用来获取最新信息。
假设有三张表,全部按照minute字段排过序。
minute = 09:30m 09:32m 09:33m 09:35m
price = 174.1 175.2 174.8 175.2
t1 = table(minute, price)
minute = 09:30m 09:31m 09:33m 09:34m
price = 29.2 28.9 29.3 30.1
t2 = table(minute, price)
minute =09:30m 09:31m 09:34m 09:36m
price = 51.2 52.4 51.9 52.8
t3 = table(minute, price);
t1;
minute | price |
---|---|
09:30m | 174.1 |
09:32m | 175.2 |
09:33m | 174.8 |
09:35m | 175.2 |
t2;
minute | price |
---|---|
09:30m | 29.2 |
09:31m | 28.9 |
09:33m | 29.3 |
09:34m | 30.1 |
t3;
minute | price |
---|---|
09:30m | 51.2 |
09:31m | 52.4 |
09:34m | 51.9 |
09:36m | 52.8 |
t2 = aj(t2, t3, `minute);
t2;
minute | price | t3_minute | t3_price |
---|---|---|---|
09:30m | 29.2 | 09:30m | 51.2 |
09:31m | 28.9 | 09:31m | 52.4 |
09:33m | 29.3 | 09:31m | 52.4 |
09:34m | 30.1 | 09:34m | 51.9 |
aj(t1, t2, `minute);
minute | price | t2_minute | t2_price | t3_minute | t3_price |
---|---|---|---|---|---|
09:30m | 174.1 | 09:30m | 29.2 | 09:30m | 51.2 |
09:32m | 175.2 | 09:31m | 28.9 | 09:31m | 52.4 |
09:33m | 174.8 | 09:33m | 29.3 | 09:31m | 52.4 |
09:35m | 175.2 | 09:34m | 30.1 | 09:34m | 51.9 |
注意,t2和t3在09:32m时间没有匹配记录,所以最近的09:31m时间的价格被选中。而t3的09:33m时的价格,以及t2和t3的09:35m处的价格也使用了最近的前一条记录用来替代缺失的记录。这个功能在需要生成特定时间点的数据时特别有用。比如说,一些信息于每周或者每月更新一次。在设计一个日交易策略时,可以使用那些更新不是很频繁的数据,通过asof连接来生成每日数据集。
asof最后一个连接列为uuid类型:
t1 = table(2015.01.01 2015.02.01 2015.03.01 2015.04.01 2015.05.01 as date, uuid(["5d212a78-cc48-e3b1-4235-b4d91473ee81", "5d212a78-cc48-e3b1-4235-b4d91473ee83", "5d212a78-cc48-e3b1-4235-b4d91473ee85", "5d212a78-cc48-e3b1-4235-b4d91473ee87", "5d212a78-cc48-e3b1-4235-b4d91473ee89"]) as uid)
t2 = table(2015.01.15 2015.01.20 2015.01.25 2015.03.01 as date,uuid(["5d212a78-cc48-e3b1-4235-b4d91473ee81", "5d212a78-cc48-e3b1-4235-b4d91473ee83", "5d212a78-cc48-e3b1-4235-b4d91473ee85", "5d212a78-cc48-e3b1-4235-b4d91473ee87"]) as uid)
select * from aj(t1, t2, `uid);
date | uid | t2_date | t2_uid |
---|---|---|---|
2015.01.01 | 5d212a78-cc48-e3b1-4235-b4d91473ee81 | 2015.01.15 | 5d212a78-cc48-e3b1-4235-b4d91473ee81 |
2015.02.01 | 5d212a78-cc48-e3b1-4235-b4d91473ee83 | 2015.01.20 | 5d212a78-cc48-e3b1-4235-b4d91473ee83 |
2015.03.01 | 5d212a78-cc48-e3b1-4235-b4d91473ee85 | 2015.01.25 | 5d212a78-cc48-e3b1-4235-b4d91473ee85 |
2015.04.01 | 5d212a78-cc48-e3b1-4235-b4d91473ee87 | 2015.03.01 | 5d212a78-cc48-e3b1-4235-b4d91473ee87 |
2015.05.01 | 5d212a78-cc48-e3b1-4235-b4d91473ee89 | 2015.03.01 | 5d212a78-cc48-e3b1-4235-b4d91473ee87 |