基于条件提取UID首次获取交付日期及属性的SQL实现问题

问题背景 #

需从示例表中为每个UID提取首次获取交付日期及对应属性，排除Status为cancelled的记录，且需遵循特定规则。

示例表结构及数据 #

CREATE TABLE orders (
  UID VARCHAR(10),
  GID VARCHAR(10),
  OID VARCHAR(10),
  Status VARCHAR(20),
  Delivery_Date DATE,
  BU VARCHAR(10),
  FB VARCHAR(10),
  OC VARCHAR(10),
  Device VARCHAR(10)
);

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U1', 'PO', '1234', 'free', '2022-12-31', 'EC', 'EC', 'App/Web', 'App');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U1', 'PO', 'PO', 'delivered', '2023-01-01', 'PH', 'E-PH', 'App/Web', 'App');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U1', NULL, '2345', 'paid', '2023-01-05', 'EC', 'EC', 'App/Web', 'Web');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U1', 'P1', '4567', 'delivered', '2023-01-05', 'LB', 'LB', 'B2B', 'Web');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U2', 'P2', 'P2', 'cancelled', '2023-01-05', 'LB', 'CW-LB', 'Offline', 'COT');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U2', 'P3', '9678', 'free', '2023-01-10', 'EC', 'EC', 'App/Web', 'App');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U2', 'P3', 'P3', 'delivered', '2023-01-10', 'PH', 'CW-PH', 'TELE', 'App');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U3', 'P5', '3456', 'Paid', '2023-01-11', 'EC', 'EC', 'App/Web', 'Web');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U3', 'P5', 'P5', 'delivered', '2023-01-12', 'LB', 'PSP-LB', 'Offline', 'Web');

INSERT INTO orders (UID, GID, OID, Status, Delivery_Date, BU, FB, OC, Device)
VALUES ('U4', 'P4', 'P4', 'delivered', '2023-01-12', 'PH', 'PSP-PH', 'B2B', 'App');

尝试过的错误查询 #

SELECT 
    t1.UID AS UID,
    t2.BU AS Acquired_BU,
    t2.Delivery_Date AS Acquired_Delivery_Date,
    t2.FB AS Acquired_FB,
    t2.OC AS Acquired_OC,
    t2.Device as Acquired_Device
FROM
    (SELECT 
        UID, GID, MAX(Delivery_Date) over (partition by UID, GID) AS max_date
    FROM 
        orders
    WHERE 
        Status = 'delivered' 
    GROUP BY 
        UID) t1
JOIN
    orders t2 ON t1.UID = t2.UID AND t1.GID = t2.GID AND t1.max_date = t2.Delivery_Date

查询规则 #

• 排除所有Status = 'cancelled'的记录
• 规则1：若UID存在多条GID记录，且其中最小交付日期的记录是BU=EC且Status='free'，则取该UID的次小交付日期对应的记录
• 规则2：若UID的BU=EC记录Status='Paid'，且无Status='free'的记录，取该UID所有记录中的最小交付日期对应的记录
• 规则3：若UID无BU=EC的记录，且所有记录Status不在('free','Paid')中，取该UID所有记录中的最小交付日期对应的记录

预期输出 #

正确解决方案 #

思路 #

1. 先过滤掉Status='cancelled'的无效记录；
2. 为每个UID的有效记录按Delivery_Date升序排序并生成序号；
3. 标记每个UID是否触发规则1（即最小日期的记录是EC+free）；
4. 根据规则触发结果，选择对应序号的记录：触发规则1取第2条，否则取第1条。

完整SQL查询 #

WITH filtered_orders AS (
    -- 过滤取消记录，为每个UID的记录按交付日期排序并标记规则1触发条件
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY UID ORDER BY Delivery_Date ASC) AS rn,
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY UID ORDER BY Delivery_Date ASC) = 1 
                 AND BU = 'EC' AND Status = 'free'
            THEN 1 
            ELSE 0 
        END AS is_rule1_trigger
    FROM orders
    WHERE Status != 'cancelled'
),
uid_rule_check AS (
    -- 汇总每个UID是否触发规则1
    SELECT 
        UID,
        MAX(is_rule1_trigger) AS trigger_rule1
    FROM filtered_orders
    GROUP BY UID
)
SELECT 
    fo.UID,
    fo.Delivery_Date AS "Acquired Delivery Date",
    fo.BU AS "Acquired BU",
    fo.FB AS "Acquired FB",
    fo.OC AS "Acquired OC",
    fo.Device AS "Acquired Device"
FROM filtered_orders fo
JOIN uid_rule_check urc ON fo.UID = urc.UID
WHERE 
    (urc.trigger_rule1 = 1 AND fo.rn = 2)
    OR (urc.trigger_rule1 = 0 AND fo.rn = 1)
ORDER BY fo.UID;

说明 #

• filtered_orders：完成无效记录过滤，同时生成排序序号和规则1触发标记；
• uid_rule_check：统一判断每个UID是否需要触发规则1；
• 最终查询根据规则触发状态筛选对应记录，完全匹配预期输出要求。

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