Snowflake SQL：如何为col5非空值匹配col4中的前置最大值

原始数据表 #

col1   col2   col3   col4                       col5
aaa    bbb     -1    null                       2000-01-01 08:10:00.471
aaa    bbb     -1    null                       2000-01-01 08:09:55.678
aaa    bbb     -1    null                       2000-01-01 08:09:57.111
aaa    bbb     -1    null                       2000-01-01 08:11:15.564
aaa    bbb      0    2000-01-01 08:12:56.672    null
aaa    bbb      1    2000-01-01 08:09:00.897    null
aaa    bbb      2    2000-01-01 08:10:30.478    null

需求说明 #

为col5中的每个非空值，匹配col4中小于该值的最大时间值（前置值），预期结果如下：

预期结果集 #

col1   col2   col3   col4                       col5                      col6
aaa    bbb     -1    null                       2000-01-01 08:10:00.471   2000-01-01 08:09:00.897
aaa    bbb     -1    null                       2000-01-01 08:09:55.678   2000-01-01 08:09:00.897
aaa    bbb     -1    null                       2000-01-01 08:09:57.111   2000-01-01 08:09:00.897
aaa    bbb     -1    null                       2000-01-01 08:11:15.564   2000-01-01 08:10:30.478
aaa    bbb      0    2000-01-01 08:12:56.672    null                      null
aaa    bbb      1    2000-01-01 08:09:00.897    null                      null
aaa    bbb      2    2000-01-01 08:10:30.478    null                      null

解决方案 #

使用Snowflake支持的LATERAL横向连接可高效实现需求，SQL语句如下：

SELECT 
    t.*,
    matched.col4 AS col6
FROM your_table t
LEFT JOIN LATERAL (
    SELECT col4
    FROM your_table
    WHERE col4 IS NOT NULL 
      AND col4 < t.col5
    ORDER BY col4 DESC
    LIMIT 1
) matched ON t.col5 IS NOT NULL;

逻辑说明 #

1. 主查询遍历原始表的每一行
2. 针对col5非空的行，通过LATERAL子查询筛选出所有col4非空且时间小于当前col5的记录
3. 子查询按col4降序排序后取第一条，即为符合要求的最大前置时间值
4. 对于col5为空的行，LEFT JOIN后col6自动为null，完全匹配预期结果

性能优化建议 #

• 若col4字段有频繁的此类查询需求，建议为col4创建索引，提升子查询的匹配效率
• 若数据量极大，可先将col4非空的记录抽取到临时表中，再进行LATERAL连接，减少子查询的扫描范围

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