BigQuery SQL：基于多列最大值创建新列以获取客户最常购买的食品类型

确实，用CASE WHEN逐个对比列的方式在苹果类型较多时会特别繁琐，后续新增类型还要修改大量代码，非常不灵活。这里推荐用行转列+窗口函数的方案，不仅逻辑清晰，还具备很强的扩展性，完美适配你的需求。

核心思路 #

1. 宽表转窄表：把原本分散在多列的apple_type1~apple_type6转换成每行对应一个类型的结构，避免逐个列判断。
2. 排名筛选：用窗口函数给每个客户的各类型购买量排名，选出排名第一的类型（支持并列情况）。
3. 结果聚合：把并列的类型拼接成自然语言格式，同时处理全0购买量的特殊情况。

示例实现（以PostgreSQL为例） #

假设你的表名为customer_apple_purchases，可以用以下SQL实现：

WITH unpivoted AS (
  -- 把宽表转为窄表：每个客户的每个苹果类型对应一行
  SELECT 
    Cust_ID,
    -- 生成类型名称数组
    unnest(array['type1', 'type2', 'type3', 'type4', 'type5', 'type6']) AS apple_type,
    -- 生成对应类型的购买数量数组
    unnest(array[apple_type1, apple_type2, apple_type3, apple_type4, apple_type5, apple_type6]) AS buy_count
  FROM customer_apple_purchases
),
ranked AS (
  -- 给每个客户的苹果类型按购买量降序排名
  SELECT 
    Cust_ID,
    apple_type,
    buy_count,
    RANK() OVER (PARTITION BY Cust_ID ORDER BY buy_count DESC) AS rnk
  FROM unpivoted
)
-- 聚合结果，处理并列和全0情况
SELECT 
  Cust_ID,
  CASE 
    WHEN MAX(buy_count) = 0 THEN 'unknown'
    ELSE STRING_AGG(apple_type, ' and ') 
  END AS freq_apple_type_buy
FROM ranked
WHERE rnk = 1  -- 只保留排名第一的类型
GROUP BY Cust_ID
ORDER BY Cust_ID;

示例实现（以MySQL为例） #

MySQL没有unnest函数，我们用UNION ALL来完成行转列：

WITH unpivoted AS (
  -- 逐个列转为行
  SELECT Cust_ID, 'type1' AS apple_type, apple_type1 AS buy_count FROM customer_apple_purchases
  UNION ALL
  SELECT Cust_ID, 'type2' AS apple_type, apple_type2 AS buy_count FROM customer_apple_purchases
  UNION ALL
  SELECT Cust_ID, 'type3' AS apple_type, apple_type3 AS buy_count FROM customer_apple_purchases
  UNION ALL
  SELECT Cust_ID, 'type4' AS apple_type, apple_type4 AS buy_count FROM customer_apple_purchases
  UNION ALL
  SELECT Cust_ID, 'type5' AS apple_type, apple_type5 AS buy_count FROM customer_apple_purchases
  UNION ALL
  SELECT Cust_ID, 'type6' AS apple_type, apple_type6 AS buy_count FROM customer_apple_purchases
),
ranked AS (
  SELECT 
    Cust_ID,
    apple_type,
    buy_count,
    RANK() OVER (PARTITION BY Cust_ID ORDER BY buy_count DESC) AS rnk
  FROM unpivoted
)
SELECT 
  Cust_ID,
  CASE 
    WHEN MAX(buy_count) = 0 THEN 'unknown'
    ELSE GROUP_CONCAT(apple_type SEPARATOR ' and ') 
  END AS freq_apple_type_buy
FROM ranked
WHERE rnk = 1
GROUP BY Cust_ID
ORDER BY Cust_ID;

方案优势 #

• 扩展性强：如果后续新增apple_type7，只需要在转列部分添加对应的元素（PostgreSQL修改数组，MySQL新增一条UNION ALL语句），无需修改大量判断逻辑。
• 逻辑清晰：把复杂的列对比拆解成行处理，可读性和可维护性远高于一堆CASE WHEN。
• 自动处理并列：内置支持多个类型购买量相同的情况，直接拼接成“typeX and typeY”的格式。

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