如何识别重叠周期的无效订阅并标记用于审计？

测试数据 #

首先创建测试表并插入数据：

CREATE OR REPLACE TEMP TABLE customer_orders(
    customer_id VARCHAR
  , product_id VARCHAR
  , subscription_start_date DATE
  , subscription_end_date DATE
);

INSERT INTO customer_orders
VALUES
  ('customer_id_001', 'product_id_001', '2024-01-01', '2024-01-31'),
  ('customer_id_001', 'product_id_001', '2024-02-01', '2024-02-29'),
  ('customer_id_001', 'product_id_001', '2024-03-01', '2024-03-31'),
  ('customer_id_001', 'product_id_001', '2024-04-01', '2024-04-15'),
  ('customer_id_001', 'product_id_001', '2024-04-01', '2024-04-30');

SELECT * FROM customer_orders ORDER BY 1,2,3,4,5;

需求说明 #

在上述数据中，订阅日期为2024-04-01至2024-04-15的订单为无效订单，因为存在后续同用户同产品的订单，其订阅覆盖了整个4月。需要标记该记录用于审计，预期输出如下：

CUSTOMER_ID    PRODUCT_ID  SUBSCRIPTION_START_DATE SUBSCRIPTION_END_DATE COMMENTS 
customer_id_001     product_id_001  2024-01-01  2024-01-31 VALID ORDER
customer_id_001     product_id_001  2024-02-01  2024-02-29 VALID ORDER
customer_id_001     product_id_001  2024-03-01  2024-03-31 VALID ORDER
customer_id_001     product_id_001  2024-04-01  2024-04-15 INVALID ORDER
customer_id_001     product_id_001  2024-04-01  2024-04-30 VALID ORDER

现有SQL问题 #

以下是当前编写的SQL，但返回结果不符合预期：

SELECT   
    c1.customer_id 
  , c1.product_id 
  , c1.subscription_start_date 
  , c1.subscription_end_date  
  , CASE WHEN c1.subscription_start_date BETWEEN c2.subscription_start_date AND c2.subscription_end_date
           OR c1.subscription_end_date   BETWEEN c2.subscription_start_date AND c2.subscription_end_date
           OR c2.subscription_start_date BETWEEN c1.subscription_start_date AND c1.subscription_end_date
         THEN 'Duplicate Subscription'
         ELSE 'Valid Subscription'
    END AS comment     
FROM customer_orders c1
INNER JOIN customer_orders c2 
   ON c1.customer_id = c2.customer_id
  AND c1.product_id  = c2.product_id
GROUP BY ALL  
ORDER BY 1,2,3,4,5;

问题点：

• 自连接未排除自身记录，每条数据都会和自身匹配，干扰判断逻辑
• 仅判断订阅区间重叠，未区分是否存在完全覆盖当前订单且有效期更长的同用户同产品订单
• GROUP BY ALL的用法无法正确聚合判断结果，导致输出混乱

正确解决方案 #

方案一：关联子查询 #

针对每个用户的同产品订单，检查是否存在其他订单完全覆盖当前订单的订阅区间：

SELECT
    customer_id,
    product_id,
    subscription_start_date,
    subscription_end_date,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM customer_orders c2
            WHERE c2.customer_id = c1.customer_id
              AND c2.product_id = c1.product_id
              -- 排除自身记录
              NOT (c2.subscription_start_date = c1.subscription_start_date AND c2.subscription_end_date = c1.subscription_end_date)
              -- c2的订阅完全覆盖c1的订阅
              AND c2.subscription_start_date <= c1.subscription_start_date
              AND c2.subscription_end_date >= c1.subscription_end_date
        ) THEN 'INVALID ORDER'
        ELSE 'VALID ORDER'
    END AS COMMENTS
FROM customer_orders c1
ORDER BY customer_id, product_id, subscription_start_date, subscription_end_date;

方案二：窗口函数（按月份筛选最长订阅） #

如果需求是保留每个用户、产品、订阅月份中有效期最长的订单，其余标记为无效，可使用窗口函数实现：

SELECT
    customer_id,
    product_id,
    subscription_start_date,
    subscription_end_date,
    CASE
        WHEN subscription_end_date < MAX(subscription_end_date) OVER (PARTITION BY customer_id, product_id, DATE_TRUNC('month', subscription_start_date))
        THEN 'INVALID ORDER'
        ELSE 'VALID ORDER'
    END AS COMMENTS
FROM customer_orders c1
ORDER BY customer_id, product_id, subscription_start_date, subscription_end_date;

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