求含For Loop的Snowflake SQL/JS存储过程：获取各城市月度营收峰值日

针对需求——获取每个城市每月营收最高的日期及对应订单量，以下是两种带For Loop实现的存储过程，核心逻辑是先遍历城市-月份的唯一分组，再在每个分组内定位营收最大值对应的记录。

JavaScript 版本存储过程 #

CREATE OR REPLACE PROCEDURE GET_DAILY_TOP_REVENUE()
RETURNS TABLE(CITY VARCHAR, YEAR_MONTH VARCHAR, TOP_DATE DATE, REVENUE NUMBER, ORDERS NUMBER)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    // 创建临时表存储最终结果
    var createTmpTableSql = `
        CREATE OR REPLACE TEMPORARY TABLE TOP_REVENUE_RESULTS (
            CITY VARCHAR,
            YEAR_MONTH VARCHAR,
            TOP_DATE DATE,
            REVENUE NUMBER,
            ORDERS NUMBER
        )
    `;
    snowflake.execute({sqlText: createTmpTableSql});

    // 获取所有唯一的城市-月份组合，作为循环遍历的基础
    var cityMonthCursor = snowflake.execute({
        sqlText: `
            SELECT DISTINCT 
                CITY,
                TO_CHAR(DATE, 'YYYY-MM') AS YEAR_MONTH
            FROM YOUR_INPUT_TABLE_NAME
            ORDER BY CITY, YEAR_MONTH
        `
    });

    // 遍历每个城市-月份分组
    while (cityMonthCursor.next()) {
        var currentCity = cityMonthCursor.getColumnValue(1);
        var currentYearMonth = cityMonthCursor.getColumnValue(2);

        // 查询当前分组的最高营收值
        var maxRevenueCursor = snowflake.execute({
            sqlText: `
                SELECT MAX(REVENUE) AS MAX_REV
                FROM YOUR_INPUT_TABLE_NAME
                WHERE CITY = ? AND TO_CHAR(DATE, 'YYYY-MM') = ?
            `,
            binds: [currentCity, currentYearMonth]
        });

        if (maxRevenueCursor.next()) {
            var maxRev = maxRevenueCursor.getColumnValue(1);

            // 把当前分组中营收等于最大值的记录插入结果表
            var topRecordsCursor = snowflake.execute({
                sqlText: `
                    INSERT INTO TOP_REVENUE_RESULTS
                    SELECT 
                        CITY,
                        TO_CHAR(DATE, 'YYYY-MM') AS YEAR_MONTH,
                        DATE AS TOP_DATE,
                        REVENUE,
                        ORDERS
                    FROM YOUR_INPUT_TABLE_NAME
                    WHERE CITY = ? AND TO_CHAR(DATE, 'YYYY-MM') = ? AND REVENUE = ?
                `,
                binds: [currentCity, currentYearMonth, maxRev]
            });
        }
    }

    // 返回最终结果表
    return snowflake.execute({sqlText: "SELECT * FROM TOP_REVENUE_RESULTS"});
$$;

说明 #

• 替换YOUR_INPUT_TABLE_NAME为实际的订单数据表名
• 若同一城市同一月份存在多个日期营收相同且均为最大值，会返回所有符合条件的记录；如需仅保留最早/最晚日期，可在插入语句中添加QUALIFY ROW_NUMBER() OVER (PARTITION BY CITY, YEAR_MONTH ORDER BY DATE DESC) = 1筛选
• 执行方式：CALL GET_DAILY_TOP_REVENUE();

SQL 版本存储过程 #

CREATE OR REPLACE PROCEDURE GET_DAILY_TOP_REVENUE_SQL()
RETURNS TABLE(CITY VARCHAR, YEAR_MONTH VARCHAR, TOP_DATE DATE, REVENUE NUMBER, ORDERS NUMBER)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        -- 定义游标，获取所有城市-月份的唯一组合
        city_month_cursor CURSOR FOR
            SELECT DISTINCT 
                CITY,
                TO_CHAR(DATE, 'YYYY-MM') AS YEAR_MONTH
            FROM YOUR_INPUT_TABLE_NAME
            ORDER BY CITY, YEAR_MONTH;
        v_city VARCHAR;
        v_year_month VARCHAR;
        v_max_rev NUMBER;
    BEGIN
        -- 创建临时表存储结果
        CREATE OR REPLACE TEMPORARY TABLE TOP_REVENUE_RESULTS (
            CITY VARCHAR,
            YEAR_MONTH VARCHAR,
            TOP_DATE DATE,
            REVENUE NUMBER,
            ORDERS NUMBER
        );

        -- 遍历游标中的每个城市-月份分组
        FOR rec IN city_month_cursor DO
            v_city := rec.CITY;
            v_year_month := rec.YEAR_MONTH;

            -- 获取当前分组的最高营收值
            SELECT MAX(REVENUE) INTO v_max_rev
            FROM YOUR_INPUT_TABLE_NAME
            WHERE CITY = v_city AND TO_CHAR(DATE, 'YYYY-MM') = v_year_month;

            -- 插入对应记录到结果表
            INSERT INTO TOP_REVENUE_RESULTS
            SELECT 
                CITY,
                TO_CHAR(DATE, 'YYYY-MM') AS YEAR_MONTH,
                DATE AS TOP_DATE,
                REVENUE,
                ORDERS
            FROM YOUR_INPUT_TABLE_NAME
            WHERE CITY = v_city AND TO_CHAR(DATE, 'YYYY-MM') = v_year_month AND REVENUE = v_max_rev;
        END FOR;

        -- 返回最终结果
        RETURN TABLE(SELECT * FROM TOP_REVENUE_RESULTS);
    END;
$$;

说明 #

• 同样需要替换YOUR_INPUT_TABLE_NAME为实际表名
• 逻辑与JS版本一致，使用SQL原生的游标和FOR循环语法实现
• 执行方式：CALL GET_DAILY_TOP_REVENUE_SQL();

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