IBM DB2 Access直通查询：筛选含过期服务且无有效服务的客户

需求 #

筛选出满足以下全部条件的客户及对应服务记录：

• 客户拥有已过期或15天内到期的服务
• 客户无任何未过期服务（包括服务结束日为9999-01-01的长期有效服务）

优化后的查询语句 #

WITH VALID_CUSTOMERS AS (
    -- 先筛选出：所有服务都已过期/即将过期，无有效服务的客户
    SELECT CONCAT(MAIN.CC, MAIN.CUSTOMER#) AS CUST_UNIQUE_ID
    FROM "SCHEMA.LIB".FILE5 AS MAIN
    WHERE MAIN.CUS#ID IN ('C', 'R')
      AND (MAIN.CANDAT < MAIN.STRDAT OR MAIN.DATE_SUSPENDED < MAIN.DATE_RESTARTED)
    -- 排除掉拥有有效服务的客户
    AND NOT EXISTS (
        SELECT 1
        FROM "SCHEMA.LIB".FILE1 AS X3
        JOIN "SCHEMA.LIB".FILE4 AS X4_1 
          ON CONCAT(X3.CC, X3.CUSTOMER#) = CONCAT(X4_1.CC, X4_1.CUSTOMER#)
        WHERE CONCAT(X3.CC, X3.CUSTOMER#) = CONCAT(MAIN.CC, MAIN.CUSTOMER#)
          AND X4_1.SERVICEID IS NOT NULL
          AND X4_1.QUANTITY IS NOT NULL
          AND X4_1.ROUTE NOT LIKE '%@%'
          AND X4_1.ROUTE NOT LIKE '%$%'
          AND X4_1.ROUTE IS NOT NULL
          AND SUBSTR(X4_1.ROUTE, 2, 1) IN ('1','2','3','4','5','6','7')
          -- 有效服务判定：结束日在15天后，或为长期服务
          AND (X3.SVENDDATE > CURRENT_DATE + 15 DAYS OR X3.SVENDDATE = DATE('9999-01-01'))
    )
)
-- 基于筛选后的客户，查询对应过期/即将过期的服务记录
SELECT
    MAIN.LOB,
    MAIN.CC,
    MAIN.CUSTOMER#,
    MAIN.CUSTOMER_NAME,
    X4_1.QUANTITY,
    X4_1.SERVICEID,
    X4_1.ROUTE,
    X3."SERVICE START",
    X3."SERVICE END",
    T1."SALES NAME",
    T2."SALES ID"
FROM "SCHEMA.LIB".FILE1 as X3
JOIN "SCHEMA.LIB".FILE4 AS X4_1 
  ON CONCAT(X3.CC, X3.CUSTOMER#) = CONCAT(X4_1.CC, X4_1.CUSTOMER#)
JOIN "SCHEMA.LIB".FILE5 AS MAIN 
  ON CONCAT(MAIN.CC, MAIN.CUSTOMER#) = CONCAT(X3.CC, X3.CUSTOMER#)
LEFT JOIN "SCHEMA.LIB".FILE2 AS T2 
  ON CONCAT(X3.CC, X3.CUSTOMER#) = CONCAT(T2.CC, T2.CUSTOMER#)
LEFT JOIN "SCHEMA.LIB".FILE3 AS T1 
  ON T1.EMPID = T2.EMPID
-- 只保留符合条件的客户
JOIN VALID_CUSTOMERS 
  ON CONCAT(MAIN.CC, MAIN.CUSTOMER#) = VALID_CUSTOMERS.CUST_UNIQUE_ID
WHERE
    X4_1.SERVICEID IS NOT NULL
    AND X4_1.QUANTITY IS NOT NULL
    AND X3.SVENDDATE <= CURRENT_DATE + 15 DAYS -- 过期/15天内到期
    AND X4_1.ROUTE NOT LIKE '%@%'
    AND X4_1.ROUTE NOT LIKE '%$%'
    AND X4_1.ROUTE IS NOT NULL
    AND SUBSTR(X4_1.ROUTE, 2, 1) IN ('1','2','3','4','5','6','7')
GROUP BY
    MAIN.LOB,
    MAIN.CC,
    MAIN.CUSTOMER#,
    MAIN.CUSTOMER_NAME,
    X4_1.QUANTITY,
    X4_1.SERVICEID,
    X4_1.ROUTE,
    X3."SERVICE START",
    X3."SERVICE END",
    T1."SALES NAME",
    T2."SALES ID"
-- LIMIT 100 -- 测试时保留，正式使用可移除

优化关键点 #

1. 先筛客户，再查服务：用CTEVALID_CUSTOMERS先排除所有拥有有效服务的客户，只保留目标客户集合，避免全表扫描大量无效数据
2. NOT EXISTS替代全表查询：通过NOT EXISTS检查客户是否存在有效服务，比直接查询所有长期服务更高效，因为一旦找到匹配的有效服务就停止检索
3. 减少关联层级：主查询只关联筛选后的客户，避免不必要的大表关联，提升查询速度
4. 保留原业务过滤条件：所有原查询中的业务规则（如ROUTE过滤、客户状态过滤）都完整保留，确保结果符合需求

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