如何使用BigQuery合并同一用户的现金与非现金交易行？

核心思路 #

针对同一用户的现金/非现金交易记录，通过按用户+交易类型分组，结合聚合函数筛选出两年的真实交易金额（排除随机赋值的100），最终将多条记录合并为单行。

示例SQL代码 #

假设你的表名为your_dataset.transaction_table，关键字段包括user_id（用户ID）、transaction_type（交易类型：cash/non-cash）、amount_2020（2020年金额）、amount_2021（2021年金额），其余20余列为用户或交易类型的关联属性：

SELECT
  user_id,
  transaction_type,
  -- 提取2020年真实金额（排除值为100的随机赋值）
  MAX(CASE WHEN amount_2020 != 100 THEN amount_2020 END) AS real_amount_2020,
  -- 提取2021年真实金额（排除值为100的随机赋值）
  MAX(CASE WHEN amount_2021 != 100 THEN amount_2021 END) AS real_amount_2021,
  -- 处理其余20余列：若字段为用户/交易类型的固定属性，用MAX或FIRST_VALUE保留唯一值
  MAX(other_column1) AS other_column1,
  FIRST_VALUE(other_column2) OVER (PARTITION BY user_id, transaction_type ORDER BY transaction_date) AS other_column2,
  -- 其他字段依此类推
FROM
  `your_dataset.transaction_table`
GROUP BY
  user_id,
  transaction_type

关键说明 #

1. 分组逻辑：按user_id和transaction_type分组，确保同一用户的同类型交易被合并。
2. 真实金额提取：利用MAX()聚合函数配合CASE WHEN，筛选出非100的真实金额——同一分组内只有一条记录的对应年份是真实值，另一条是100，MAX()会自动保留真实值（若真实金额可能等于100，需结合交易日期判断，见下方补充）。
3. 其余字段处理：对于用户或交易类型的固定属性（如用户姓名、交易类型描述等），使用MAX()或FIRST_VALUE()保留唯一值；若字段随交易变化，可根据业务需求选择合适的聚合方式。

补充优化（若真实金额可能等于100） #

如果存在真实交易金额恰好为100的情况，需结合transaction_date判断年份，确保提取对应年份的真实值：

SELECT
  user_id,
  transaction_type,
  MAX(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2020 THEN amount_2020 END) AS real_amount_2020,
  MAX(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2021 THEN amount_2021 END) AS real_amount_2021,
  -- 其余字段处理同上
FROM
  `your_dataset.transaction_table`
GROUP BY
  user_id,
  transaction_type

内容的提问来源于stack exchange，提问作者mp.kaur