BigQuery中处理快照视图订单状态变更导致的重复行问题

1. 视图层面过滤：只保留每个订单/商品的最新状态行 #

直接修改快照视图的SQL逻辑，确保每个订单（或订单+商品维度）仅返回最新的记录，从源头上避免重复统计。用窗口函数ROW_NUMBER()按目标维度分组，按更新时间倒序排序后取首行：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY update_time DESC) AS rn
    FROM order_snapshot
) t
WHERE rn = 1;

2. 数据写入层面优化：更新而非新增（业务允许时） #

如果不需要留存完整的状态变更历史，直接修改状态变更逻辑：当订单状态从2变更为3时，更新原有行的状态及相关字段，而非新增行。示例SQL：

UPDATE order_snapshot 
SET status = 3, update_time = NOW()
WHERE order_id = ? AND product_id = ?;

这种方式从根源上杜绝重复行，适合无需审计状态轨迹的场景。

3. 统计逻辑修正：按最新状态聚合计算 #

若必须保留所有历史快照行（比如需要审计），则在计算总价时先聚合出每个订单商品的最新状态行，再求和统计：

SELECT 
    order_id,
    SUM(CASE WHEN rn = 1 THEN price * quantity ELSE 0 END) AS actual_total_price
FROM (
    SELECT 
        order_id,
        product_id,
        price,
        quantity,
        ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY update_time DESC) AS rn
    FROM order_snapshot
) t
GROUP BY order_id;

这种方法兼顾历史数据留存与统计准确性。

4. 新增状态生效标记：标记旧状态为无效 #

在快照表中添加is_active字段，新增新状态行时，将同一订单商品的旧状态行标记为0（无效），统计时仅取is_active = 1的行：

• 状态变更执行语句：

-- 标记旧行无效
UPDATE order_snapshot SET is_active = 0 WHERE order_id = ? AND product_id = ?;
-- 插入新状态行
INSERT INTO order_snapshot (order_id, product_id, status, price, quantity, is_active, update_time)
VALUES (?, ?, 3, ?, ?, 1, NOW());

• 总价统计语句：

SELECT SUM(price * quantity) AS actual_total_price FROM order_snapshot WHERE order_id = ? AND is_active = 1;

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