求助：如何对Snowflake COPY_HISTORY数据按日期行转列

静态PIVOT（适合临时查询） #

如果仅需一次性查询最近10天的数据，可直接指定日期列，写法如下：

WITH daily_loads AS (
    SELECT
        table_schema_name,
        table_name,
        DATE_TRUNC('DAY', last_load_time) AS day,
        SUM(row_count) AS row_count
    FROM
        DEVDWH.SNOWFLAKE_ACCOUNT_USAGE.COPY_HISTORY
    WHERE
        table_schema_name IN ('SCHEMA1','SCHEMA2')
        AND table_catalog_name = 'DEVDWH'
        AND last_load_time >= CURRENT_DATE - 10
    GROUP BY
        1,2,3
)
SELECT
    table_schema_name,
    table_name,
    -- 替换为实际最近10天的日期，列名可自定义
    NVL("2024-05-20", 0) AS DAY_20240520,
    NVL("2024-05-21", 0) AS DAY_20240521,
    NVL("2024-05-22", 0) AS DAY_20240522,
    NVL("2024-05-23", 0) AS DAY_20240523,
    NVL("2024-05-24", 0) AS DAY_20240524,
    NVL("2024-05-25", 0) AS DAY_20240525,
    NVL("2024-05-26", 0) AS DAY_20240526,
    NVL("2024-05-27", 0) AS DAY_20240527,
    NVL("2024-05-28", 0) AS DAY_20240528,
    NVL("2024-05-29", 0) AS DAY_20240529
FROM daily_loads
PIVOT (
    SUM(row_count)
    FOR day IN (
        '2024-05-20'::DATE,
        '2024-05-21'::DATE,
        '2024-05-22'::DATE,
        '2024-05-23'::DATE,
        '2024-05-24'::DATE,
        '2024-05-25'::DATE,
        '2024-05-26'::DATE,
        '2024-05-27'::DATE,
        '2024-05-28'::DATE,
        '2024-05-29'::DATE
    )
) AS p
ORDER BY table_schema_name, table_name;

动态PIVOT（适合定时监控） #

如果需要自动适配最近10天的日期，无需手动修改SQL，可使用Snowflake动态SQL实现：

DECLARE
    start_date DATE := CURRENT_DATE - 10;
    pivot_cols STRING;
    select_cols STRING;
    sql_stmt STRING;
BEGIN
    -- 生成PIVOT所需的日期列表
    SELECT LISTAGG('''' || DATEADD(DAY, seq4(), start_date) || '''::DATE', ', ')
    INTO pivot_cols
    FROM TABLE(GENERATOR(ROWCOUNT => 10));

    -- 生成SELECT对应的列名，自动处理空值为0
    SELECT LISTAGG('NVL("' || DATEADD(DAY, seq4(), start_date) || '", 0) AS DAY_' || TO_CHAR(DATEADD(DAY, seq4(), start_date), 'YYYYMMDD'), ', ')
    INTO select_cols
    FROM TABLE(GENERATOR(ROWCOUNT => 10));

    -- 拼接完整SQL语句
    sql_stmt := '
        WITH daily_loads AS (
            SELECT
                table_schema_name,
                table_name,
                DATE_TRUNC(''DAY'', last_load_time) AS day,
                SUM(row_count) AS row_count
            FROM
                DEVDWH.SNOWFLAKE_ACCOUNT_USAGE.COPY_HISTORY
            WHERE
                table_schema_name IN (''SCHEMA1'',''SCHEMA2'')
                AND table_catalog_name = ''DEVDWH''
                AND last_load_time >= ''' || start_date || '''
            GROUP BY
                1,2,3
        )
        SELECT
            table_schema_name,
            table_name,
            ' || select_cols || '
        FROM daily_loads
        PIVOT (
            SUM(row_count)
            FOR day IN (' || pivot_cols || ')
        ) AS p
        ORDER BY table_schema_name, table_name;
    ';

    -- 执行动态SQL
    EXECUTE IMMEDIATE sql_stmt;
END;

关键说明 #

• 使用NVL将无数据日期的row_count转为0，避免结果出现NULL
• DATE_TRUNC后的day为DATE类型，PIVOT中的IN子句必须匹配DATE类型
• 动态SQL通过GENERATOR函数生成最近10天的日期，自动拼接列名和PIVOT参数

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