Snowflake中SQL实现每行找1秒时间偏移的首行价格差问题

我有一张包含timestamp、price列的表，需要判断每条记录对应的1秒前的最近价格，并计算当前价格与该价格的差值。其中timestamp采用Snowflake的TIMESTAMP_TZ格式，示例值为2022-12-12 9:30:00.0+01:00。

用Python循环实现的逻辑伪代码如下：

# pseudocode in python
price_diff = {}
for i in range(len(table)):
  current_row = table[i]
  for j in range(i, -1, -1):
     prev_row = table[j]
     if current_row.datetime - prev_row.datetime >= 1 second:
        price_diff[current_row.index] = current_row.price - prev_row.price
        break

我尝试的SQL语句如下：

SELECT table1.datetime, table1.price, 
(
  SELECT table2.price
  FROM mytable table2
  WHERE
  timediff(second, table1.datetime, table2.datetime) > 1 
  ORDER BY
  table2.datetime
  DESC
  LIMIT 1
) AS price_tm1,
(table1.price - price_tm1) AS price_diff
FROM
mytable table1

但执行时报错：SQL compilation error: Unsupported subquery type cannot be evaluated，需要解决方案。

Snowflake不支持SELECT列表中这种关联子查询的写法，改用CROSS JOIN LATERAL可以实现需求，这种方式能正确关联主表每条记录，找到符合时间条件的最近前一条记录：

SELECT 
    t1.timestamp,
    t1.price,
    t2.price AS price_tm1,
    t1.price - t2.price AS price_diff
FROM mytable t1
CROSS JOIN LATERAL (
    SELECT price
    FROM mytable t2
    WHERE t2.timestamp <= DATEADD(second, -1, t1.timestamp)
    ORDER BY t2.timestamp DESC
    LIMIT 1
) t2;

逻辑说明 #

1. 主表t1的每条记录，通过CROSS JOIN LATERAL关联子查询t2
2. 子查询筛选出时间早于当前记录1秒及以上的所有记录（t2.timestamp <= DATEADD(second, -1, t1.timestamp)）
3. 按时间倒序排序后取第一条，即为当前记录之前最近的符合1秒间隔的价格
4. 最后计算价格差值price_diff

如果部分记录不存在符合条件的1秒前记录，price_tm1会返回NULL，此时price_diff也会为NULL。若需要处理这种情况，可用COALESCE设置默认值：

SELECT 
    t1.timestamp,
    t1.price,
    COALESCE(t2.price, t1.price) AS price_tm1,
    t1.price - COALESCE(t2.price, t1.price) AS price_diff
FROM mytable t1
CROSS JOIN LATERAL (
    SELECT price
    FROM mytable t2
    WHERE t2.timestamp <= DATEADD(second, -1, t1.timestamp)
    ORDER BY t2.timestamp DESC
    LIMIT 1
) t2;

另外，建议给timestamp列建立索引，提升大数据量下的查询性能。

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