如何将Informatica聚合器中未分组未聚合端口逻辑转换为BigQuery SQL

核心思路 #

Informatica聚合器中未纳入GROUP BY且未做聚合的列，会输出数据处理顺序中的最后值。在BigQuery中可以通过两种简洁方式实现该逻辑：利用ARRAY_AGG函数直接在GROUP BY中提取最后值，或通过窗口函数标记每组最后行后完成聚合。

方法1：使用ARRAY_AGG直接提取最后值（推荐） #

无需子查询，直接在GROUP BY语句中通过ARRAY_AGG指定排序规则，获取每组内的最后一个值。

示例SQL #

假设：

• 分组列为group_col1, group_col2
• 需聚合的列为agg_col（以SUM为例）
• 未分组且需取最后值的列为A, B
• 确定“最后顺序”的排序列为order_col（如加载时间戳、自增ID，对应Informatica的数据流入顺序）

SELECT
  group_col1,
  group_col2,
  SUM(agg_col) AS total_agg,
  -- 取每组按order_col降序排列后的最后一个A值
  ARRAY_AGG(A ORDER BY order_col DESC LIMIT 1)[OFFSET(0)] AS last_A,
  -- 取每组按order_col降序排列后的最后一个B值
  ARRAY_AGG(B ORDER BY order_col DESC LIMIT 1)[OFFSET(0)] AS last_B
FROM your_source_table
GROUP BY group_col1, group_col2;

关键说明 #

• ORDER BY order_col DESC：必须指定排序依据，否则BigQuery无法保证行顺序，结果会随机。
• [OFFSET(0)]：ARRAY_AGG返回单元素数组，通过该语法取出实际值。
• 默认保留NULL值：BigQuery的ARRAY_AGG默认遵循RESPECT NULLS，会保留最后行的NULL；若需忽略NULL，可添加IGNORE NULLS，如ARRAY_AGG(A IGNORE NULLS ORDER BY order_col DESC LIMIT 1)[OFFSET(0)]。

方法2：通过窗口函数标记最后行后聚合 #

先给每组内的行按指定顺序排名，过滤出每组最后一行，再结合聚合函数完成计算。

示例SQL #

WITH ranked_data AS (
  SELECT
    group_col1,
    group_col2,
    A,
    B,
    agg_col,
    -- 给每组内的行按order_col降序排名，最后一行标记为1
    ROW_NUMBER() OVER (
      PARTITION BY group_col1, group_col2
      ORDER BY order_col DESC
    ) AS rn
  FROM your_source_table
)
SELECT
  group_col1,
  group_col2,
  SUM(agg_col) AS total_agg,
  A AS last_A,
  B AS last_B
FROM ranked_data
WHERE rn = 1
GROUP BY group_col1, group_col2, A, B;

关键说明 #

• 每组仅保留最后一行，因此GROUP BY中加入A, B不会改变分组结果（同一组只有这一行的A、B值），聚合和取最后值可同时完成。
• 若存在多行同属“最后顺序”（如相同order_col值），ROW_NUMBER()会随机选一行；若需保留所有这类行，可改用RANK()或DENSE_RANK()，需匹配Informatica的实际行为调整。

注意事项 #

• 必须明确排序依据：Informatica的“最后值”依赖数据流入顺序，BigQuery无默认行顺序，必须指定对应的排序列（如时间戳、主键）才能精准复刻逻辑。
• 若原Informatica映射未指定排序规则，需确认业务逻辑是否允许随机取最后值，或补充合适的排序列。

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