Snowflake技术需求：按门店、Campaign及Priority循环分组25条生成计算优先级

现有Snowflake查询通过ROW_NUMBER() OVER (PARTITION BY store)，基于PRIORITY列按门店计算出C_PRIORITY，运行正常。现需进一步优化：按CAMPAIGN_NAME和PRIORITY排序，以25条为一组循环取数——先取优先级最高的Campaign的前25条，再取下一Campaign的前25条，遍历所有Campaign后回到首个Campaign再取25条，循环至所有记录按门店生成唯一的序列顺序（新的Calculated Priority）。

当前查询代码 #

SELECT CONTACT_ID 
        ,LEAD_NUMBER
        ,LEAD_CREATION_DATE
        ,CUSTOMER_FIRST_NAME
        ,CUSTOMER_LAST_NAME 
        ,CAMPAIGN_NAME
        ,STORE
        ,MOBILE_NUMBER
        ,HOME_NUMBER
        ,WORK_NUMBER
        ,CELL_DO_NOT_CONTACT_FLAG
        ,HOME_DO_NOT_CONTACT_FLAG
        ,WORK_DO_NOT_CONTACT_FLAG
        ,PRIORITY
        ,ROW_NUMBER() OVER (PARTITION by store ORDER BY priority asc, campaign_name, store) AS C_PRIORITY 
        ,HISTORICAL_DATE
        
FROM stores_campaigns_all

WHERE historical_date = '2023-03-10'
AND store IS NOT NULL

核心需求 #

当前输出是按门店分区后，先按优先级升序、再按Campaign名称排序生成连续的C_PRIORITY；期望实现的是：按优先级从高到低（假设PRIORITY值越小优先级越高，可按需调整）对Campaign排序，每个Campaign内按顺序取25条为一批，循环遍历所有Campaign，最终给每个门店的所有记录生成唯一的循环序列编号。

要实现这种循环分批取数的排序逻辑，分两步处理即可：

1. 给每个门店内的每条记录，标记其在所属Campaign内的顺序，同时给每个门店的Campaign按优先级排好队；
2. 基于Campaign内的顺序计算批次号，再按批次+Campaign优先级+Campaign内顺序排序，生成最终的序列编号。

修改后的查询代码如下：

WITH ranked_data AS (
    SELECT 
        CONTACT_ID 
        ,LEAD_NUMBER
        ,LEAD_CREATION_DATE
        ,CUSTOMER_FIRST_NAME
        ,CUSTOMER_LAST_NAME 
        ,CAMPAIGN_NAME
        ,STORE
        ,MOBILE_NUMBER
        ,HOME_NUMBER
        ,WORK_NUMBER
        ,CELL_DO_NOT_CONTACT_FLAG
        ,HOME_DO_NOT_CONTACT_FLAG
        ,WORK_DO_NOT_CONTACT_FLAG
        ,PRIORITY
        ,HISTORICAL_DATE
        -- 给每个门店+Campaign下的记录编顺序号，标记它在所属Campaign里的位置
        ,ROW_NUMBER() OVER (PARTITION BY store, CAMPAIGN_NAME ORDER BY priority asc, CONTACT_ID) AS campaign_row_num
        -- 给每个门店的Campaign按优先级排等级，确保高优先级Campaign先被取数
        ,DENSE_RANK() OVER (PARTITION BY store ORDER BY priority asc, CAMPAIGN_NAME) AS campaign_priority_rank
    FROM stores_campaigns_all
    WHERE historical_date = '2023-03-10'
      AND store IS NOT NULL
)
SELECT 
    * EXCLUDE (campaign_row_num, campaign_priority_rank)
    -- 按批次号、Campaign优先级、Campaign内顺序排序，生成最终的循环序列编号
    ,ROW_NUMBER() OVER (PARTITION BY store ORDER BY CEIL(campaign_row_num / 25), campaign_priority_rank, campaign_row_num) AS CALCULATED_PRIORITY
FROM ranked_data
ORDER BY store, CALCULATED_PRIORITY;

逻辑说明 #

• campaign_row_num：给每个门店里的同一个Campaign下的记录编顺序号，比如某Campaign有100条，这个字段就是1到100；
• campaign_priority_rank：给每个门店里的Campaign按优先级排等级，优先级最高的Campaign排1，次高的排2，以此类推；
• CEIL(campaign_row_num / 25)：把每个Campaign里的记录按25条一批分组，1-25条是批次1，26-50条是批次2；
• 最终的CALCULATED_PRIORITY：先按批次号走，批次1里先取所有高优先级Campaign的前25条，再取次高的前25条，等所有Campaign的批次1取完后，再取批次2的所有Campaign的25条，以此类推，最后给这些记录按顺序编唯一的序号。

如果你的PRIORITY字段是值越大优先级越高，只需把代码里的ORDER BY priority asc改成ORDER BY priority desc即可。

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