在Snowflake中如何为数字添加M（百万）、B（十亿）后缀

核心实现方案 #

通过CASE表达式判断数值量级，结合数值运算与字符串拼接，就能快速实现需求格式：

带小数的格式化（如1.5M、2.3B） #

SELECT 
    original_number,
    CASE
        WHEN original_number >= 1000000000 THEN CONCAT(ROUND(original_number / 1000000000, 1), 'B')
        WHEN original_number >= 1000000 THEN CONCAT(ROUND(original_number / 1000000, 1), 'M')
        ELSE CAST(original_number AS VARCHAR)
    END AS formatted_number
FROM your_table;

整数格式（如10M、5B） #

如果不需要小数位，用FLOOR取整或直接转换为整数类型：

SELECT 
    original_number,
    CASE
        WHEN original_number >= 1000000000 THEN CONCAT(CAST(FLOOR(original_number / 1000000000) AS VARCHAR), 'B')
        WHEN original_number >= 1000000 THEN CONCAT(CAST(FLOOR(original_number / 1000000) AS VARCHAR), 'M')
        ELSE CAST(original_number AS VARCHAR)
    END AS formatted_number
FROM your_table;

支持负数的场景 #

若需要处理负数值，用ABS()判断量级即可：

SELECT 
    original_number,
    CASE
        WHEN ABS(original_number) >= 1000000000 THEN CONCAT(ROUND(original_number / 1000000000, 1), 'B')
        WHEN ABS(original_number) >= 1000000 THEN CONCAT(ROUND(original_number / 1000000, 1), 'M')
        ELSE CAST(original_number AS VARCHAR)
    END AS formatted_number
FROM your_table;

内容的提问来源于stack exchange，提问作者Suresh Kumar Desu