BigQuery 按指定日期与invoice_no唯一值汇总invoice_value的实现方法

当前SQL直接累加匹配时间范围的全量invoices_value，未对invoice_no做去重，同一张发票的多条冗余记录会被重复计算，导致总金额偏高。核心解决思路为：先对唯一invoice_no提取对应金额，再完成汇总计算。

实现方案 #

方案1：通用兼容方案（优先推荐） #

适配所有业务场景，无论同一invoice_no下的invoices_value是否存在差异，都能保证每个发票号仅被计算一次：

SELECT SUM(unique_invoice_value) AS INVOICES
FROM (
  -- 按invoice_no分组去重，每个发票仅保留一条有效金额
  SELECT 
    invoice_no,
    ANY_VALUE(invoices_value) AS unique_invoice_value -- 若同发票号存在金额不一致的情况，可根据业务规则替换为MAX/MIN等聚合逻辑
  FROM `my_data`
  WHERE invoices_value IS NOT NULL
    AND timestamp >= '2021-03-01'
    AND timestamp < '2021-03-02'
  GROUP BY invoice_no
)

方案2：简化实现方案 #

如果已确认所有重复的invoice_no对应的invoices_value完全一致，不存在金额差异，可通过字段去重后直接汇总，写法更简洁：

SELECT SUM(invoices_value) AS INVOICES
FROM (
  -- 以invoice_no为去重维度，每个发票仅保留一行
  SELECT DISTINCT invoice_no, invoices_value
  FROM `my_data`
  WHERE invoices_value IS NOT NULL
    AND timestamp >= '2021-03-01'
    AND timestamp < '2021-03-02'
)

注意事项 #

• 可先执行以下排查SQL，确认是否存在同一invoice_no对应多个金额的异常数据，避免取数偏差：

SELECT invoice_no, COUNT(DISTINCT invoices_value) AS diff_value_cnt
FROM `my_data`
WHERE invoices_value IS NOT NULL
  AND timestamp >= '2021-03-01'
  AND timestamp < '2021-03-02'
GROUP BY invoice_no
HAVING diff_value_cnt > 1

• 若timestamp字段为字符串类型而非TIMESTAMP类型，需先转换为时间类型再做范围筛选，避免时区转换导致的日期匹配错误。

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