使用Snowflake窗口函数按条件查询前一行及双证券的最新价格

原始数据定义 #

create or replace temporary table stock_trade_history (
   type varchar
  ,id varchar
  ,datetime TIMESTAMP_TZ
  ,price float );
insert into stock_trade_history values
    ('trade',1, '2020-01-06 09:00:01.290+09:00', 300), 
    ('trade',1, '2020-01-06 09:00:01.291+09:00', 301), 
    ('trade',1, '2020-01-06 09:00:45.297+09:00', 302),  
    ('trade',2, '2020-01-06 09:00:50.301+09:00', 1000),
    ('trade',1, '2020-01-06 09:01:01.318+09:00', 301),   
    ('trade',2, '2020-01-06 09:01:02.319+09:00', 1001),   
    ('trade',1, '2020-01-06 09:01:03.322+09:00', 300),   
    ('trade',1, '2020-01-06 09:01:04.346+09:00', 299), 
    ('trade',2, '2020-01-06 09:01:31.378+09:00', 999), 
    ('trade',2, '2020-01-06 09:01:40.381+09:00', 1000),  
    ('trade',1, '2020-01-06 09:01:41.382+09:00', 298), 
    ('trade',2, '2020-01-06 09:01:50.426+09:00', 1002); 

需求与问题 #

需要针对每一行数据，实时获取证券ID1和ID2的最新价格；同时要保证：当当前行ID为1时，能获取到此前最近的ID2价格，反之亦然。

原尝试的SQL通过PARTITION BY id拆分窗口，只能获取当前ID的最新价格，无法跨ID同步另一个证券的最新价：

select 
    *,
    first_value(price) over 
        (partition by id order by datetime desc rows between unbounded preceding and current row) as prev_price
from stock_trade_history order by datetime;

期望输出 #

|type | datetime           | id 1 price | id 2 price |
    ('trade', '2020-01-06 09:00:01.290+09:00', 300, NULL), 
    ('trade', '2020-01-06 09:00:01.291+09:00', 301, NULL), 
    ('trade', '2020-01-06 09:00:45.297+09:00', 302, NULL),  
    ('trade', '2020-01-06 09:00:50.301+09:00', 302, 1000),
    ('trade', '2020-01-06 09:01:01.318+09:00', 301, 1000),   
    ('trade', '2020-01-06 09:01:02.319+09:00', 301, 1001),   
    ('trade', '2020-01-06 09:01:03.322+09:00', 300, 1001),   
    ('trade', '2020-01-06 09:01:04.346+09:00', 299, 1001), 
    ('trade', '2020-01-06 09:01:31.378+09:00', 299, 999), 
    ('trade', '2020-01-06 09:01:40.381+09:00', 299, 1000),  
    ('trade', '2020-01-06 09:01:41.382+09:00', 298, 1000), 
    ('trade', '2020-01-06 09:01:50.426+09:00', 298, 1002); 

解决方案 #

使用全局时间排序的窗口，结合LAST_VALUE和IGNORE NULLS，分别过滤跟踪两个ID的最新价格：

SELECT 
    type,
    datetime,
    -- 获取当前时间点及之前ID=1的最新价格
    LAST_VALUE(CASE WHEN id = '1' THEN price END IGNORE NULLS) OVER (
        ORDER BY datetime 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS "id 1 price",
    -- 获取当前时间点及之前ID=2的最新价格
    LAST_VALUE(CASE WHEN id = '2' THEN price END IGNORE NULLS) OVER (
        ORDER BY datetime 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS "id 2 price"
FROM stock_trade_history
ORDER BY datetime;

逻辑说明 #

1. 不按ID分区，而是全局按交易时间排序，确保窗口包含所有历史数据到当前行
2. 用CASE语句过滤出对应ID的价格，非目标ID的行返回NULL
3. LAST_VALUE结合IGNORE NULLS会自动跳过NULL值，取到当前窗口内目标ID的最新价格
4. 窗口范围限定为UNBOUNDED PRECEDING AND CURRENT ROW，确保只取当前行及之前的历史数据，符合“最新价格”的时间要求

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