如何将PostgreSQL代码转换为Snowflake？请求协助转换示例代码

核心差异点说明 #

• JSON字段提取：PostgreSQL的->>操作符在Snowflake中可用冒号语法（:）简化，或使用GET_PATH函数
• 时区转换：Snowflake用CONVERT_TIMEZONE函数替代PostgreSQL的链式at time zone语法
• 日期运算：Snowflake推荐用DATEADD函数替代interval减法，语法更直观

转换后的完整Snowflake代码 #

WITH sitel_users AS (
    SELECT
        user_id
    FROM validation_rep vr
    WHERE TRUE
        -- Snowflake中提取JSON布尔值的简化写法
        AND vr.feature_flags:is_bogota::BOOLEAN IS TRUE
), call_grade AS (
    SELECT
        aoc.user_id
        -- 将UTC时间转换为美中时区并转为日期格式
        , CONVERT_TIMEZONE('UTC', 'US/CENTRAL', aocg.created_at)::DATE AS call_grade_date
        , AVG(aocg.score) AS call_grade_avg
    FROM asterisk_outbound_call_grade aocg
    JOIN asterisk_outbound_call aoc 
        ON aocg.asterisk_outbound_call_id = aoc.id
    WHERE TRUE
        AND aocg.deleted_at IS NULL
        -- 计算7天前的美中时区时间阈值
        AND aocg.created_at >= DATEADD(day, -7, CONVERT_TIMEZONE('US/CENTRAL', CURRENT_TIMESTAMP()))
        AND EXISTS(SELECT 1 FROM sitel_users su WHERE aoc.user_id = su.user_id)
    GROUP BY 1, 2
)
-- 可根据需求添加CTE的后续使用逻辑，原代码未完成
SELECT * FROM call_grade;

补充说明 #

1. 若feature_flags是VARCHAR类型而非Snowflake的VARIANT类型，需先转换：PARSE_JSON(vr.feature_flags):is_bogota::BOOLEAN
2. 若created_at本身已是美中时区的时间戳，可简化时区转换为aocg.created_at::DATE
3. CURRENT_DATE() AT TIME ZONE 'US/CENTRAL'可替代CONVERT_TIMEZONE('US/CENTRAL', CURRENT_TIMESTAMP())，效果一致

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