如何在Snowflake中新增列获取股票周首个交易日对应数值

需求说明 #

现有Snowflake股票交易数据表结构如下：

StockName    Date          Value
GOOG         10/12/2021     $100
GOOG         10/11/2021     $995 

需要新增value_weekstart列，返回对应股票所在周首个交易日的交易数值。规则为周首日默认取周一，若周一为节假日无交易，则自动顺延至当周第一个有交易的日期，预期输出效果如下：

StockName         date          Value        value_weekstart
GOOG              10/12/2021    $1000         $995
GOOG              10/11/2021    $995          $995

实现代码 #

使用Snowflake内置窗口函数即可实现，无需额外维护节假日表：

SELECT 
    StockName,
    "Date",
    Value,
    FIRST_VALUE(Value) OVER (
        PARTITION BY StockName, DATE_TRUNC('WEEK', "Date"::DATE) 
        ORDER BY "Date"::DATE ASC
    ) AS value_weekstart
FROM 你的股票数据表名;

逻辑说明 #

• DATE_TRUNC('WEEK', "Date"::DATE)：Snowflake中该函数默认将日期截断至当周周一，同属一个自然周的日期会得到相同的截断值，作为周分组的依据
• 窗口函数按股票代码、周截断值分组，组内按交易日期升序排序，FIRST_VALUE直接取组内第一条记录的Value，也就是该周第一个交易日的数值。天然适配节假日顺延规则：如果周一无交易，组内最小日期就是该周第一个交易日，不需要额外处理节假日逻辑

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