筛选符合特定条件的唯一Participant__c值的SQL查询方案

需求说明 #

数据表中Id为主键，存在多条Participant__c值相同但Id不同的记录。需要查询出唯一的Participant__c值，满足以下条件：

• 该Participant__c对应的记录中，Survey_Index_Number__c分别为2001、2003、2005
• 上述三条记录的Completion_Status__c均为Complete

提问者原本倾向使用分区逻辑但不知如何入手，自行编写了统计已完成问卷数量的查询，现给出优化及其他可行实现方案。

提问者自行编写的查询（统计完成数量） #

SELECT
    Participant__c as 'ContactId',
    COUNT(DISTINCT Survey_Index_Number__c) AS 'SurveyCountComplete'
FROM
    (SELECT
        Participant__c,
        Survey_Index_Number__c,
        Completion_Status__c
    FROM
        Contact_Salesforce c
    INNER JOIN
        Survey_Result__c_Salesforce sr on sr.Participant__c = c.Id
    WHERE
        sr.Completion_Status__c = 'Complete'
        AND Survey_Index_Number__c IN ('2001', '2003', '2005')) as CompletedSurveys
GROUP BY
    Participant__c

可行解决方案 #

方案1：优化现有查询（最直接） #

在现有查询基础上添加HAVING子句，筛选出完成指定3个问卷的用户，直接得到符合要求的Participant__c：

SELECT
    Participant__c as ContactId
FROM
    (SELECT
        sr.Participant__c,
        sr.Survey_Index_Number__c
    FROM
        Contact_Salesforce c
    INNER JOIN
        Survey_Result__c_Salesforce sr on sr.Participant__c = c.Id
    WHERE
        sr.Completion_Status__c = 'Complete'
        AND sr.Survey_Index_Number__c IN ('2001', '2003', '2005')) as CompletedSurveys
GROUP BY
    Participant__c
HAVING COUNT(DISTINCT Survey_Index_Number__c) = 3;

方案2：使用分区逻辑（窗口函数） #

通过窗口函数按Participant__c分区，统计每个用户完成的指定问卷数量，再筛选出数量为3的用户：

WITH SurveyCompletion AS (
    SELECT
        sr.Participant__c,
        COUNT(DISTINCT sr.Survey_Index_Number__c) OVER (PARTITION BY sr.Participant__c) AS TotalCompleted
    FROM
        Contact_Salesforce c
    INNER JOIN
        Survey_Result__c_Salesforce sr on sr.Participant__c = c.Id
    WHERE
        sr.Completion_Status__c = 'Complete'
        AND sr.Survey_Index_Number__c IN ('2001', '2003', '2005')
)
SELECT DISTINCT Participant__c AS ContactId
FROM SurveyCompletion
WHERE TotalCompleted = 3;

方案3：使用交集查询（逻辑直观） #

通过三次查询的交集，直接获取同时完成三个指定问卷的用户：

SELECT sr.Participant__c AS ContactId
FROM Survey_Result__c_Salesforce sr
JOIN Contact_Salesforce c ON sr.Participant__c = c.Id
WHERE sr.Completion_Status__c = 'Complete' AND sr.Survey_Index_Number__c = '2001'

INTERSECT

SELECT sr.Participant__c
FROM Survey_Result__c_Salesforce sr
JOIN Contact_Salesforce c ON sr.Participant__c = c.Id
WHERE sr.Completion_Status__c = 'Complete' AND sr.Survey_Index_Number__c = '2003'

INTERSECT

SELECT sr.Participant__c
FROM Survey_Result__c_Salesforce sr
JOIN Contact_Salesforce c ON sr.Participant__c = c.Id
WHERE sr.Completion_Status__c = 'Complete' AND sr.Survey_Index_Number__c = '2005';

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