Unica系统contacthistory表Oracle SQL查询：筛选特定状态客户ID

问题分析 #

你的原SQL存在逻辑错误：子查询返回了custid和contactdatetime两列，但NOT IN只能匹配单列，会导致语法或逻辑异常。核心需求是按客户ID+日期维度筛选：找出渠道为CRM、有状态2记录，且当日该客户无状态5记录的客户ID。

可行解决方案 #

方法一：LEFT JOIN 排除法

通过左连接同客户同日期的状态5记录，筛选连接失败的结果（即当日无状态5的记录）：

SELECT DISTINCT a.custid  -- 若需要完整的状态2记录，去掉DISTINCT改用a.*
FROM unica_campaign.ua_contacthistory_dlv a
LEFT JOIN unica_campaign.ua_contacthistory_dlv b
  ON a.custid = b.custid
  AND a.channel = b.channel
  AND DATE(a.contactdatetime) = DATE(b.contactdatetime)
  AND b.contactstatusid = '5'
WHERE a.channel = 'CRM'
  AND a.contactstatusid = '2'
  AND b.custid IS NULL;

方法二：NOT EXISTS 子查询

用子查询直接验证：同客户同日期不存在CRM渠道的状态5记录，逻辑更清晰：

SELECT DISTINCT a.custid  -- 若需要完整记录，改用a.*
FROM unica_campaign.ua_contacthistory_dlv a
WHERE a.channel = 'CRM'
  AND a.contactstatusid = '2'
  AND NOT EXISTS (
    SELECT 1
    FROM unica_campaign.ua_contacthistory_dlv b
    WHERE b.custid = a.custid
      AND b.channel = 'CRM'
      AND DATE(b.contactdatetime) = DATE(a.contactdatetime)
      AND b.contactstatusid = '5'
  );

方法三：窗口函数分组标记

如果需要查看更多维度数据，可按客户+日期分组，标记是否存在状态5记录：

WITH cust_daily_status AS (
  SELECT 
    *,
    MAX(CASE WHEN contactstatusid = '5' THEN 1 ELSE 0 END) OVER (PARTITION BY custid, DATE(contactdatetime)) AS has_contacted
  FROM unica_campaign.ua_contacthistory_dlv
  WHERE channel = 'CRM'
)
SELECT DISTINCT custid  -- 按需选择其他字段
FROM cust_daily_status
WHERE contactstatusid = '2'
  AND has_contacted = 0;

注：若数据库不支持DATE()函数，可替换为TRUNC(contactdatetime)或extract(year from contactdatetime)||'-'||extract(month from contactdatetime)||'-'||extract(day from contactdatetime)来匹配日期。

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