BigQuery优化：订单同时存在Paid与Cancelled状态时仅保留Cancelled行

要解决同一订单同时返回Paid和Cancelled状态行的问题，我们可以通过窗口函数标记订单/订单项是否存在取消状态，再过滤掉不需要的行。具体修改如下：

WITH combined_data AS (
  -- 保留原查询逻辑，生成所有订单数据
  SELECT *
  EXCEPT (main_category, sub_category, category_id, parent_category_id),
    MAX(main_category) AS Main_Category,
    MAX(sub_category) AS Sub_Category
  FROM (
    SELECT
      DISTINCT
      status.modified_date AS Modified_Date,
      orders.id AS Order_ID,
      items.id AS Item_ID,
      products.product_id AS Product_ID,
      products.name AS Product_Title,
      products.margin AS Margin,
      items.price AS Item_Price,
      orders.total_price AS Total_Price,
      "Cancelled" AS Status, -- Always set to "Cancelled"
      CAST(orders.created_date AS DATETIME) AS Order_Date,
      orders.order_user_id AS User_ID,
      orders.firstname AS First_Name,
      orders.lastname AS Last_Name,
      orders.telephone AS Telephone,
      orders.email AS Email,
      orders.website_id AS Website_ID,
      products.date_available AS Date_Live,
      vendor.vendor_id AS Vendor_ID,
      IFNULL(vencomp.store_page, vendor.vendor_name) AS Vendor_Name,
      CAST(vendor.created_date AS DATETIME) AS Vendor_Date,
      cat.category_id AS Category_ID,
      cat.parent_int AS Parent_Category_ID,
      CASE
        WHEN cat.lob_name = "shopping" THEN "שופינג"
        WHEN cat.lob_name = "local" THEN "לוקאל"
        WHEN cat.lob_name = "travel" THEN "תיירות"
      END AS LOB,
      agents.agent_name AS Agent_Name,
      ship.id AS Shipping_ID,
      ship.title AS Shipping_Method,
      CAST(ship.cost AS INT64) AS Shipping_Price,
      products.is_active AS Is_Active,
      EXTRACT(DATETIME FROM TIMESTAMP_MILLIS(items.expiration_date)) AS Voucher_Exp_Date,
      CASE
        WHEN vendor.approval_flag = 1 THEN "Active"
        ELSE "Not Active"
      END AS Vendor_Status,
      cat.sub_sub_category_title AS Sub2_Category,
      cat.sub_sub_sub_category_title AS Sub3_Category,
      items.voucher_id AS Voucher_ID,
      items.voucher_code AS Voucher_Code,
      items.security_code AS Security_Code,
      ref.id AS Refund_ID,
      ref.reason AS Refund_Reason,
      ref.method AS Refund_Method,
      ref.comment AS Refund_Comment,
      ref.amount AS Refund_Amount,
      ref.created_date AS Refund_Date,
      cat.main_category_title AS Main_Category,
      cat.sub_category_title AS Sub_Category,
      items.status AS Item_Status,
      CASE
        WHEN status.status = 'REFUNDED' THEN status.modified_date
        ELSE NULL
      END AS Cancel_Date
    FROM
      `gcommerce-m1-prod.m1_groo_prod_spurt.order` AS orders
    LEFT JOIN
      `gcommerce-m1-prod.m1_groo_prod_spurt.order_item` AS items
    ON
      orders.id = items.order_id
    LEFT JOIN
      `gcommerce-m1-prod.m1_groo_prod_spurt.order_item_status_log` AS status
    ON
      status.order_item_id = items.id
    LEFT JOIN
      `m1_groo_prod_spurt.product` AS products
    ON
      items.product_id = products.product_id
    LEFT JOIN
      `m1_groo_prod_spurt.product_to_category` AS catprod
    ON
      items.product_id = catprod.product_id
    LEFT JOIN
      `gcommerce-m1-prod.Groo_Bi_Reports.Categories Hierarchy` AS cat
    ON
      cat.category_id = catprod.category_id
    LEFT JOIN
      `m1_groo_prod_spurt.vendor` AS vendor
    ON
      vendor.vendor_id = orders.vendor_id
    LEFT JOIN
      `m1_groo_prod_spurt.vendor_company_information` AS vencomp
    ON
      vencomp.vendor_id = vendor.vendor_id
    LEFT JOIN
      `gcommerce-m1-prod.m1_groo_prod_spurt.shipping_methods` AS ship
    ON
      CAST(ship.id AS STRING) = orders.shipping_methods
    LEFT JOIN
      `Groo_Bi_Reports.Agent_Names` AS agents
    ON
      agents.Agent_ID = products.agent_name
    LEFT JOIN
      `gcommerce-m1-prod.m1_groo_prod_spurt.refund_details` AS ref
    ON
      ref.id = items.refund_id
    WHERE
      orders.migrated_id IS NULL

    UNION ALL

    SELECT DISTINCT *
    FROM `Groo_Bi_Reports.Unicorn_Main_View`
  )
  GROUP BY
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41
),
status_check AS (
  SELECT
    *,
    -- 标记当前订单项是否存在Cancelled状态（若需订单级别判断，将PARTITION BY改为仅Order_ID）
    MAX(CASE WHEN Status = 'Cancelled' THEN 1 ELSE 0 END) OVER (PARTITION BY Order_ID, Item_ID) AS has_cancelled
  FROM combined_data
)
-- 过滤规则：有取消记录则只保留取消行，无取消记录则保留所有行
SELECT * EXCEPT (has_cancelled)
FROM status_check
WHERE (has_cancelled = 1 AND Status = 'Cancelled') OR has_cancelled = 0;

关键修改说明 #

1. combined_data 子句：完全保留原查询的逻辑，确保原有数据处理流程不变。
2. status_check 子句：通过窗口函数给每个订单项添加has_cancelled标记，判断该订单项是否存在取消状态的记录。如果业务逻辑是订单级别的状态判断，可将PARTITION BY改为仅Order_ID。
3. 最终过滤条件：只保留两种有效行：
   • 订单项存在取消记录，且状态为Cancelled
   • 订单项无取消记录，保留原状态的行

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