Snowflake中实现唯一左连接（Asof Join）的技术问询

你需要实现的是每个交易（trade）仅关联紧随其后的第一个报价（quote），其余报价不关联该交易，以此避免重复关联的问题。以下是具体解决方案：

问题重现 #

现有两张临时表trade和quotes，当前的关联逻辑会让多个quote关联同一个trade，导致第三行quote的price和volume出现非预期值。

建表及数据插入代码：

create or replace temporary table trade (
   type varchar
  ,id varchar
  ,datetime TIMESTAMP_TZ
  ,price float
  ,volume int);
insert into trade values
    ('trade',1, '2020-01-06 09:00:01.329+09:00', 300, 1), 
    ('trade',1, '2020-01-06 09:00:45.500+09:00', 301, 2);

create or replace temporary table quotes(
    type varchar
  , id varchar
  , datetime TIMESTAMP_TZ
  , ask float
  , bid float
);
insert into quotes values
    ('quote', 1, '2020-01-06 09:00:01.250+09:00', 300, 299), 
    ('quote', 1, '2020-01-06 09:00:01.399+09:00', 301, 299),
    ('quote', 1, '2020-01-06 09:00:01.421+09:00', 301, 300),    
    ('quote', 1, '2020-01-06 09:00:45.539+09:00', 302, 301);

解决方案 #

通过CTE先找到每个trade之后的第一个quote时间，再关联quotes表，仅保留匹配该时间的关联记录：

WITH next_quote_for_trade AS (
    SELECT 
        t.*,
        MIN(q.datetime) AS next_quote_datetime
    FROM trade t
    LEFT JOIN quotes q
        ON t.id = q.id AND q.datetime > t.datetime
    GROUP BY t.type, t.id, t.datetime, t.price, t.volume
)
SELECT 
    q.type, q.id, q.datetime, q.ask, q.bid,
    t.price, t.volume
FROM quotes q
LEFT JOIN next_quote_for_trade t
    ON q.id = t.id AND q.datetime = t.next_quote_datetime;

执行结果 #

TYPE    ID  DATETIME                        ASK BID PRICE   VOLUME
quote   1   2020-01-06 09:00:01.250 +0900   300 299 NULL    NULL
quote   1   2020-01-06 09:00:01.399 +0900   301 299 300     1
quote   1   2020-01-06 09:00:01.421 +0900   301 300 NULL    NULL
quote   1   2020-01-06 09:00:45.539 +0900   302 301 301     2

该结果满足你的需求：第三行quote的price和volume为NULL，仅第一个在trade之后的quote关联对应的交易记录。

内容的提问来源于stack exchange，提问作者MoneyBall