如何在Snowflake中构建自定义排序逻辑并生成排序数值列？

核心思路 #

• 先通过窗口函数标记每个员工是否拥有Insights Manager或Executive Manager这两个高优先级角色
• 有高优先级角色的员工，所有行排序值固定为1；无高优先级角色的员工，按角色字母顺序分配排序值

实现代码 #

CREATE OR REPLACE VIEW employee_sorted_view AS
SELECT
    employee_id,
    role,
    -- 生成自定义排序值
    CASE
        WHEN has_priority_role = 1 THEN 1
        ELSE DENSE_RANK() OVER (PARTITION BY employee_id ORDER BY role ASC)
    END AS sort_order
FROM (
    SELECT
        employee_id,
        role,
        -- 标记当前员工是否持有高优先级角色
        MAX(CASE WHEN role IN ('Insights Manager', 'Executive Manager') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY employee_id) AS has_priority_role
    FROM your_employee_table -- 替换为你的实际表名
) sub_query;

代码说明 #

• 内层子查询：用MAX() OVER (PARTITION BY employee_id)全局判断每个员工是否有高优先级角色，只要有任一匹配角色，has_priority_role就为1
• 外层查询：
  • 若员工有高优先级角色，直接赋值排序值为1
  • 若无高优先级角色，用DENSE_RANK()按角色字母升序分配排序值（如果不需要处理重复角色，也可以替换为ROW_NUMBER()）

效果验证示例 #

假设原始数据：

生成的视图结果：

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