BigQuery聚合筛选：获取达标企业的第三张发票日期与金额总和

现有一张发票数据表：

注：index字段通过RANK() OVER (PARTITION BY company ORDER BY date_sent) as index生成

需求：筛选出发票数量≥3的企业，返回企业名称、前3张发票的金额总和以及第三张发票的发送日期。预期结果如下：

现有BigQuery代码仅能返回企业名称和前3张发票金额总和，无法获取第三张发票的发送日期：

select company,
  (select sum(amount) from grouped_invs.amount_sum_3 amount) as amount_sum_3,
from (
  select company,
    array_agg(invoices.amount order by invoices.index limit 3) amount_sum_3,
  from `data` invoices
  group by invoices.company
  having count(*) >= 3
) grouped_invs

提供两种可直接运行的BigQuery实现方案：

方案一：聚合时同时保留金额与日期数组 #

通过两次ARRAY_AGG分别存储前3张发票的金额和日期，再从数组中提取目标数据：

SELECT 
  company,
  -- 数组索引从0开始，第三张发票对应偏移量2
  (SELECT date_sent FROM UNNEST(grouped_invs.date_list) WITH OFFSET pos WHERE pos = 2) AS date_3rd,
  (SELECT SUM(amount) FROM UNNEST(grouped_invs.amount_list)) AS amount_sum_3
FROM (
  SELECT 
    company,
    ARRAY_AGG(amount ORDER BY index LIMIT 3) AS amount_list,
    ARRAY_AGG(date_sent ORDER BY index LIMIT 3) AS date_list
  FROM `data` invoices
  GROUP BY company
  HAVING COUNT(*) >= 3
) grouped_invs

方案二：先筛选前3条记录再聚合 #

先计算每个企业的发票总数和前3张的累计金额，直接筛选目标记录：

WITH ranked_invoices AS (
  SELECT 
    company,
    date_sent,
    amount,
    index,
    -- 计算前N张发票的累计金额
    SUM(amount) OVER (PARTITION BY company ORDER BY index ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum,
    -- 统计企业总发票数
    COUNT(*) OVER (PARTITION BY company) AS total_invoices
  FROM `data`
)
SELECT 
  company,
  date_sent AS date_3rd,
  running_sum AS amount_sum_3
FROM ranked_invoices
WHERE index = 3 AND total_invoices >= 3

方案说明 #

• 方案一适合需要保留更多前N条明细的场景，通过数组灵活提取所需字段；
• 方案二逻辑更直观，通过窗口函数一步完成累计计算和总数统计，直接过滤出目标结果。

内容的提问来源于stack exchange，提问作者Tom Hamilton Stubber