SQL查询需求：检测订单交易缺失及时间违规问题

目标1：检测超出对应时间范围的交易 #

通过提取交易记录的时间部分，与各类型规定的时间区间对比，筛选不符合规则的记录：

SELECT 
    CustID,
    timestamp,
    type,
    CAST(timestamp AS TIME) AS record_time,
    CASE type
        WHEN 'B' THEN '应在7:00-9:00'
        WHEN 'L' THEN '应在11:30-14:00'
        WHEN 'E' THEN '应在16:30-18:00'
        WHEN 'S' THEN '应在19:30-22:00'
    END AS required_time_range
FROM Orders
WHERE 
    (type = 'B' AND NOT (CAST(timestamp AS TIME) BETWEEN '07:00:00' AND '09:00:00'))
    OR (type = 'L' AND NOT (CAST(timestamp AS TIME) BETWEEN '11:30:00' AND '14:00:00'))
    OR (type = 'E' AND NOT (CAST(timestamp AS TIME) BETWEEN '16:30:00' AND '18:00:00'))
    OR (type = 'S' AND NOT (CAST(timestamp AS TIME) BETWEEN '19:30:00' AND '22:00:00'))

说明 #

• CAST(timestamp AS TIME)提取日期时间字段中的纯时间部分，简化区间对比逻辑
• 每个类型对应独立判断条件，精准筛选超时记录
• required_time_range字段直观展示该类型的合规时间范围，便于快速排查

目标2：检测任意日期缺少B/L/E/S某类交易 #

你原查询的问题是：LEFT JOIN后用WHERE sub.Count <4会过滤掉当天无任何交易的客户-日期组合（sub.Count会返回NULL）。以下是修正后的完整方案：

-- 生成所有客户与交易日期的完整组合
WITH customer_dates AS (
    SELECT 
        c.CustID,
        CAST(o.timestamp AS DATE) AS trade_date
    FROM Customers c
    CROSS JOIN (SELECT DISTINCT CAST(timestamp AS DATE) AS timestamp FROM Orders) o
),
-- 统计每个客户每天的各交易类型存在情况
type_counts AS (
    SELECT 
        CustID,
        CAST(timestamp AS DATE) AS trade_date,
        COUNT(DISTINCT CASE WHEN type = 'B' THEN type END) AS has_B,
        COUNT(DISTINCT CASE WHEN type = 'L' THEN type END) AS has_L,
        COUNT(DISTINCT CASE WHEN type = 'E' THEN type END) AS has_E,
        COUNT(DISTINCT CASE WHEN type = 'S' THEN type END) AS has_S
    FROM Orders
    WHERE type IN ('B','L','E','S')
    GROUP BY CustID, CAST(timestamp AS DATE)
)
-- 筛选缺少类型的客户-日期组合
SELECT 
    cd.CustID,
    cd.trade_date,
    CONCAT_WS(', ',
        CASE WHEN COALESCE(tc.has_B,0) = 0 THEN '缺少B类型' END,
        CASE WHEN COALESCE(tc.has_L,0) = 0 THEN '缺少L类型' END,
        CASE WHEN COALESCE(tc.has_E,0) = 0 THEN '缺少E类型' END,
        CASE WHEN COALESCE(tc.has_S,0) = 0 THEN '缺少S类型' END
    ) AS missing_types
FROM customer_dates cd
LEFT JOIN type_counts tc 
    ON cd.CustID = tc.CustID AND cd.trade_date = tc.trade_date
WHERE 
    COALESCE(tc.has_B,0) + COALESCE(tc.has_L,0) + COALESCE(tc.has_E,0) + COALESCE(tc.has_S,0) <4

说明 #

• customer_dates CTE生成所有客户与所有交易日期的笛卡尔积，确保不会遗漏任何客户的交易日
• type_counts CTE用条件统计标记每个客户每天是否存在对应类型交易，1代表存在，0代表不存在
• COALESCE处理NULL值（当天无该类型交易时返回NULL），最终筛选出类型总数不足4的记录，并明确显示缺少的类型

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