在BigQuery中按唯一ORDER_ID合并多行多列至单列

针对你的需求，可以通过BigQuery的字符串聚合函数实现，以下是具体SQL语句：

WITH sample_data AS (
  SELECT 'ZX' AS ORDER_ID, 3 AS NR_ORDERS, 'K' AS TYPE, 'M' AS MAKE, 'B' AS MODEL UNION ALL
  SELECT 'ZX' AS ORDER_ID, 2 AS NR_ORDERS, 'V' AS TYPE, 'J' AS MAKE, 'P' AS MODEL UNION ALL
  SELECT 'ZX' AS ORDER_ID, 1 AS NR_ORDERS, 'D' AS TYPE, 'G' AS MAKE, 'Y' AS MODEL UNION ALL
  SELECT 'AB' AS ORDER_ID, 2 AS NR_ORDERS, 'C' AS TYPE, 'T' AS MAKE, 'B' AS MODEL UNION ALL
  SELECT 'AB' AS ORDER_ID, 1 AS NR_ORDERS, 'G' AS TYPE, 'H' AS MAKE, 'B' AS MODEL
)
SELECT
  ORDER_ID,
  MAX(NR_ORDERS) AS NR_ORDERS,
  STRING_AGG(CONCAT(TYPE, ' ', MAKE, ' ', MODEL), ', ' ORDER BY NR_ORDERS DESC) AS JOINED_COLUMNS
FROM sample_data
GROUP BY ORDER_ID;

关键逻辑说明： #

• CONCAT(TYPE, ' ', MAKE, ' ', MODEL)：将每行的TYPE、MAKE、MODEL字段用空格拼接为单个字符串，例如把K、M、B组合成K M B
• STRING_AGG(..., ', ' ORDER BY NR_ORDERS DESC)：按ORDER_ID分组后，将组内的拼接字符串用, 分隔聚合，同时通过ORDER BY NR_ORDERS DESC保证顺序和示例一致
• MAX(NR_ORDERS)：提取每个ORDER_ID对应的最大NR_ORDERS值，匹配你期望的输出格式

执行上述语句后，即可得到你需要的结果：

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