Snowflake中如何在GROUP BY语句中正确解析FIRST_VALUE聚合函数

问题原因 #

Snowflake中不存在first()聚合函数，你直接将first_value窗口函数与普通聚合函数混用，且未指定窗口分区范围，导致SQL编译报错——窗口函数的计算结果不属于GROUP BY的聚合列范畴，无法直接和聚合函数并列出现在SELECT子句中。

正确改写代码 #

我们可以通过子查询先处理需要first逻辑的字段，再在外层完成聚合计算，具体代码如下：

WITH pre_processed AS (
    SELECT 
        account_code,
        date,
        box_revenue_recognition_amount,
        box_flg,
        box_sku_quantity,
        box_revenue_recognition_refund_amount,
        box_discount_amount,
        box_shipping_amount,
        box_cogs,
        invoice_number,
        order_number,
        box_refund_date,
        -- 按聚合维度分区，取order_season_rank=1的第一条对应字段值
        FIRST_VALUE(CASE WHEN order_season_rank = 1 THEN box_type END) 
            OVER (PARTITION BY account_code, date ORDER BY order_season_rank) AS box_type,
        FIRST_VALUE(CASE WHEN order_season_rank = 1 THEN box_order_season END) 
            OVER (PARTITION BY account_code, date ORDER BY order_season_rank) AS box_order_season,
        FIRST_VALUE(CASE WHEN order_season_rank = 1 THEN box_product_name END) 
            OVER (PARTITION BY account_code, date ORDER BY order_season_rank) AS box_product_name,
        FIRST_VALUE(CASE WHEN order_season_rank = 1 THEN box_coupon_code END) 
            OVER (PARTITION BY account_code, date ORDER BY order_season_rank) AS box_coupon_code,
        FIRST_VALUE(CASE WHEN order_season_rank = 1 THEN revenue_recognition_reason END) 
            OVER (PARTITION BY account_code, date ORDER BY order_season_rank) AS revenue_recognition_reason
    FROM dedupe_sub_user_day
)
SELECT 
    account_code,
    date,
    SUM(box_revenue_recognition_amount) AS box_revenue_recognition_amount,
    SUM(CASE WHEN box_flg = 1 THEN box_sku_quantity END) AS box_sku_quantity,
    SUM(box_revenue_recognition_refund_amount) AS box_revenue_recognition_refund_amount,
    SUM(box_discount_amount) AS box_discount_amount,
    SUM(box_shipping_amount) AS box_shipping_amount,
    SUM(box_cogs) AS box_cogs,
    MAX(invoice_number) AS invoice_number,
    MAX(order_number) AS order_number,
    MIN(box_refund_date) AS box_refund_date,
    -- 同分区内该字段值一致，用MAX/MIN均可获取唯一值
    MAX(box_type) AS box_type,
    MAX(box_order_season) AS box_order_season,
    MAX(box_product_name) AS box_product_name,
    MAX(box_coupon_code) AS box_coupon_code,
    MAX(revenue_recognition_reason) AS revenue_recognition_reason
FROM pre_processed
GROUP BY account_code, date;

关键说明 #

• 子查询中通过PARTITION BY account_code, date将数据按聚合维度拆分，确保first_value仅在当前分组内筛选符合条件的记录。
• 外层聚合时，由于同一分组内first_value得到的字段值完全一致，使用MAX()或MIN()就能稳定获取该值，规避窗口函数与聚合函数的语法冲突。
• 若每个分组内order_season_rank = 1的记录有多条，可以在ORDER BY后添加主键等唯一字段，保证取到固定的第一条记录。

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