在Snowflake中将列名转为新列值的简化SQL实现咨询

样本数据 #

total_container_cnt | forty_ft_container_cnt | twenty_ft_container_cnt | fifty_three_ft_container_cnt

3                   |      NULL              |    3                    |   NULL
2                   |       2                |      NULL               |   NULL

预期输出 #

total_container_cnt | forty_ft_container_cnt | twenty_ft_container_cnt | fifty_three_ft_container_cnt | NEW_COLUMN

3                   |      NULL              |    3                    |   NULL      | 3*twenty_ft_container_cnt
2                   |       2                |      NULL               |   NULL      | 2* forty_ft_container_cnt

现有实现 #

我已经写出可行的SQL，但用到了多个CASE WHEN语句，想寻求更简洁的写法：

select temp.*,
       case when forty_ft_container_cnt is not null then concat('total_container_cnt','*','forty_ft_container_cnt')
       when twenty_ft_container_cnt is not null then concat('total_container_cnt','*','twenty_ft_container_cnt')
       when fifty_three_ft_container_cnt is not null then concat('total_container_cnt','*','fifty_three_ft_container_cnt')
 end as new_column

from temp

简洁方案 #

可以用两种更紧凑的写法替代多分支CASE WHEN：

方案1：利用ELT()+FIELD()组合 #

SELECT
    temp.*,
    CONCAT(
        total_container_cnt,
        '* ',
        ELT(
            FIELD(
                1,
                (forty_ft_container_cnt IS NOT NULL),
                (twenty_ft_container_cnt IS NOT NULL),
                (fifty_three_ft_container_cnt IS NOT NULL)
            ),
            'forty_ft_container_cnt',
            'twenty_ft_container_cnt',
            'fifty_three_ft_container_cnt'
        )
    ) AS NEW_COLUMN
FROM temp;

逻辑说明：

• FIELD(1, 条件1, 条件2, 条件3)返回第一个结果为1（条件为真）的位置索引，因为每行仅一个容器列非空，只会匹配到一个位置
• ELT(索引值, 字符串1, 字符串2, 字符串3)根据索引返回对应的列名字符串
• 最后用CONCAT()拼接数值与列名，得到预期格式

方案2：利用COALESCE()简化分支 #

SELECT
    temp.*,
    CONCAT(
        total_container_cnt,
        '* ',
        COALESCE(
            CASE WHEN forty_ft_container_cnt IS NOT NULL THEN 'forty_ft_container_cnt' END,
            CASE WHEN twenty_ft_container_cnt IS NOT NULL THEN 'twenty_ft_container_cnt' END,
            CASE WHEN fifty_three_ft_container_cnt IS NOT NULL THEN 'fifty_three_ft_container_cnt' END
        )
    ) AS NEW_COLUMN
FROM temp;

逻辑说明：
COALESCE()会依次取第一个非空的列名字符串，把多个CASE分支合并到一个函数中，结构更紧凑。

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