Snowflake中使用CTE实现指定列数据插入的正确语法咨询

完整实现语句 #

WITH aggregated_traffic AS (
    SELECT
        d_objects."OBJECT_GROUP" AS "d_objects.object_group",
        TO_CHAR(TO_DATE(d_dates."DAY"), 'YYYY-MM-DD') AS "d_dates.date_date",
        COALESCE(SUM(f_traffic."MEDIA_CONTENT_START"), 0) AS "f_traffic.sum_content_media_views",
        COALESCE(SUM(CASE WHEN d_platforms."PLATFORM" = 'Desktop' THEN f_traffic."MEDIA_START" ELSE NULL END), 0) AS media_views_web,
        COALESCE(SUM(CASE WHEN d_platforms."PLATFORM" = 'Mobile' THEN f_traffic."MEDIA_START" ELSE NULL END), 0) AS media_views_mobile,
        COALESCE(SUM(CASE WHEN d_platforms."PLATFORM" = 'App' THEN f_traffic."MEDIA_START" ELSE NULL END), 0) AS media_views_app
    FROM DATA_MART.F_TRAFFIC AS f_traffic
    INNER JOIN DATA_MART.D_DATES AS d_dates ON f_traffic."WH_DATE_ID" = d_dates."ID"
    INNER JOIN DATA_MART.D_OBJECTS AS d_objects ON f_traffic."D_OBJECTS_ID" = d_objects."ID"
    INNER JOIN DATA_MART.D_PLATFORMS AS d_platforms ON f_traffic."D_PLATFORMS_ID" = d_platforms."ID"
    LEFT JOIN DATA_MART.D_CONTENT_MEDIA AS d_content_media ON f_traffic."D_CONTENT_MEDIA_ID" = d_content_media."ID"
    WHERE 
        (f_traffic."MEDIA_TYPE" NOT IN ('video : vicki', 'audio', 'trailer') OR f_traffic."MEDIA_TYPE" IS NULL)
        AND d_dates."DAY" >= TO_DATE(DATEADD('day', -28, CURRENT_DATE()))
        AND d_dates."DAY" < TO_DATE(DATEADD('day', 28, DATEADD('day', -28, CURRENT_DATE())))
        AND d_objects."OBJECT_GROUP" = 'BILD'
        AND d_objects."OBJECT_NAME" IN ('BILD', 'SPORT BILD')
        AND d_content_media."ADOBE_CONTENT_TYPE" = 'video'
        AND (LOWER(d_content_media."TAXONOMY_LIST") NOT LIKE '%vicki%' OR d_content_media."TAXONOMY_LIST" IS NULL)
    GROUP BY TO_DATE(d_dates."DAY"), d_objects."OBJECT_GROUP"
)
INSERT INTO PROD_DWH.FOUNDRY_REPORTING.bild_daily_traffic (
    D_DATES, 
    SUM_CONTENT_MEDIA_VIEWS, 
    MEDIA_VIEWS_MOBILE, 
    MEDIA_VIEWS_APP, 
    REPORT_DATE, 
    REPORT_ID, 
    KPI_NAME
)
SELECT
    "d_dates.date_date",
    "f_traffic.sum_content_media_views",
    media_views_mobile,
    media_views_app,
    CURRENT_DATE(), -- 按实际业务需求替换为对应报告日期值
    'BILD_TRAFFIC_REPORT', -- 按实际业务需求替换为对应REPORT_ID值
    'DAILY_MEDIA_VIEWS' -- 按实际业务需求替换为对应KPI_NAME值
FROM aggregated_traffic
ORDER BY "d_dates.date_date" DESC;

关键说明 #

• CTE封装逻辑：通过WITH aggregated_traffic AS (...)把聚合查询封装成临时结果集，后续直接引用这个CTE名称即可，避免重复编写复杂的关联和过滤逻辑。
• 补充缺失列：目标表中的REPORT_DATE、REPORT_ID、KPI_NAME在原聚合查询中没有对应结果，需要根据业务规则补充固定值或动态计算值（示例中给出了默认值，需自行替换）。
• 条件简化：对原查询的WHERE条件做了简化，将多个<> 替换为NOT IN，移除冗余括号，不改变查询结果的同时提升可读性。

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