如何在BigQuery中为销售表每行匹配对应区间的描述？

根据你的需求，有两种常用的实现方式，分别适用于不同场景：

1. 动态匹配（适合lookup表会更新的场景） #

这种方式不需要硬编码阈值，后续更新lookup表即可自动适配，灵活性更高。核心思路是将lookup表的带逗号的limit转换为数值，然后通过交叉连接结合窗口函数找到每个销售额对应的最小匹配阈值：

WITH lookup_table AS (
  -- 将lookup表中的带逗号limit转为数值类型
  SELECT 
    CAST(REPLACE(limit, ',', '') AS INT64) AS limit_num,
    description
  FROM projectid.dataset.lookup
),
sales_cleaned AS (
  SELECT 
    total_sales,
    -- 如果total_sales是带$或逗号的字符串，先转为数值；如果本身是数值类型可省略这一步
    CAST(REPLACE(REPLACE(total_sales, '$', ''), ',', '') AS INT64) AS sales_num
  FROM projectid.dataset.sales
)
SELECT 
  total_sales,
  description
FROM sales_cleaned
CROSS JOIN lookup_table
-- 筛选出大于等于当前销售额的阈值
WHERE limit_num >= sales_num
-- 为每个销售额取最小的匹配阈值对应的描述
QUALIFY ROW_NUMBER() OVER (PARTITION BY total_sales ORDER BY limit_num ASC) = 1
ORDER BY total_sales;

2. 静态CASE WHEN（适合阈值固定的场景） #

如果lookup表的阈值不会变动，直接用CASE WHEN硬编码判断逻辑，执行效率更高：

SELECT
  total_sales,
  CASE
    WHEN CAST(REPLACE(REPLACE(total_sales, '$', ''), ',', '') AS INT64) <= 99 THEN 'tens'
    WHEN CAST(REPLACE(REPLACE(total_sales, '$', ''), ',', '') AS INT64) <= 999 THEN 'hundreds'
    WHEN CAST(REPLACE(REPLACE(total_sales, '$', ''), ',', '') AS INT64) <= 999999 THEN 'thousands'
    WHEN CAST(REPLACE(REPLACE(total_sales, '$', ''), ',', '') AS INT64) <= 999999999 THEN 'millions'
    WHEN CAST(REPLACE(REPLACE(total_sales, '$', ''), ',', '') AS INT64) <= 999999999999 THEN 'billions'
    ELSE 'trillions+' -- 可选：处理超过最大阈值的情况
  END AS description
FROM projectid.dataset.sales;

注意事项 #

• 如果你的total_sales字段本身就是数值类型（而非带$或逗号的字符串），可以去掉CAST(REPLACE(...) AS INT64)这部分转换逻辑，直接用total_sales进行判断。
• 第一种方式中使用的QUALIFY是BigQuery支持的语法，如果你用的是其他SQL引擎，可能需要改用子查询或CTE来实现类似逻辑。

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