如何在Snowflake中筛选同时回答主问题与对应子问题的调查数据

处理导入Snowflake数据库的调查数据，场景如下：

• 主问题（QUESTIONID='1'）为0-10分的满意度评分题
• 根据主问题评分，会展示不同子问题（1A："您最喜欢我们华夫饼的什么？"；1B："哪些方面可以改进？"），子问题可跳过不答
• 每个参与者有唯一RESPONSEID，三个问题各有唯一QUESTIONID

需求：仅保留同时回答了主问题和对应子问题的参与者数据，忽略仅回答主问题的记录。

当前SQL查询：

SELECT DISTINCT
    r.RESPONSEID,
    r.QUESTIONID,
    CASE
        WHEN r.SURVEY = q.SURVEY AND r.questionid = q.QUESTIONID THEN q.QUESTIONTEXT
        ELSE NULL
    END QTEXT,
    r.RESPONSE
FROM RESPONSES r
JOIN QUESTIONS q ON q.questionid = r.questionid
JOIN QUESTION_RESPONSE s ON s.response_id = r.responseid
WHERE r.SURVEY IN ('WaffleSurvey3000')
AND (q.QUESTIONID = '1' OR  q.QUESTIONID = '1A' OR q.QUESTIONID = '1B')
   AND QTEXT IS NOT NULL
ORDER BY RESPONSEID;

当前输出：

RESPONSEID   QUESTIONID     QTEXT                      RESPONSE
A            1              Between 0 and 10...         7
B            1              Between 0 and 10...         9
B            1A             What did you like...        Best Waffles EVER!
C            1              Between 0 and 10...         5
D            1              Between 0 and 10...         6
E            1              Between 0 and 10...         2
E            1B             What could be better...     Awful Waffles! Do better! SHAME

期望输出：

RESPONSEID   QUESTIONID     QTEXT                      RESPONSE
B            1              Between 0 and 10...         9
B            1A             What did you like...        Best Waffles EVER!
E            1              Between 0 and 10...         2
E            1B             What could be better...     Awful Waffles! Do better! SHAME

以下提供两种可行的修改方案，核心思路都是先筛选出符合条件的参与者ID，再过滤对应数据：

方案一：使用CTE（公共表表达式） #

WITH eligible_responses AS (
    SELECT RESPONSEID
    FROM RESPONSES
    WHERE SURVEY = 'WaffleSurvey3000'
      AND QUESTIONID IN ('1', '1A', '1B')
    GROUP BY RESPONSEID
    -- 筛选条件：必须有主问题1的回答，且至少有一个子问题回答
    HAVING SUM(CASE WHEN QUESTIONID = '1' THEN 1 ELSE 0 END) = 1
       AND SUM(CASE WHEN QUESTIONID IN ('1A', '1B') THEN 1 ELSE 0 END) >= 1
)
SELECT DISTINCT
    r.RESPONSEID,
    r.QUESTIONID,
    q.QUESTIONTEXT AS QTEXT,
    r.RESPONSE
FROM RESPONSES r
JOIN QUESTIONS q ON q.QUESTIONID = r.QUESTIONID
JOIN eligible_responses er ON er.RESPONSEID = r.RESPONSEID
WHERE r.SURVEY = 'WaffleSurvey3000'
  AND q.QUESTIONID IN ('1', '1A', '1B')
ORDER BY r.RESPONSEID;

说明 #

1. 用eligible_responses CTE筛选出符合要求的参与者ID：通过GROUP BY分组后，用HAVING子句确保每个ID同时存在主问题和至少一个子问题的回答
2. 原查询中的CASE语句可以删除，因为JOIN QUESTIONS q ON q.questionid = r.questionid已经保证了QUESTIONID匹配，直接取q.QUESTIONTEXT即可
3. 原查询中的QUESTION_RESPONSE表如果没有额外过滤需求，可以考虑去掉（从需求和数据来看，该表未起到筛选作用）

方案二：使用窗口函数 #

SELECT RESPONSEID, QUESTIONID, QTEXT, RESPONSE
FROM (
    SELECT DISTINCT
        r.RESPONSEID,
        r.QUESTIONID,
        q.QUESTIONTEXT AS QTEXT,
        r.RESPONSE,
        -- 统计当前参与者是否有主问题回答
        SUM(CASE WHEN r.QUESTIONID = '1' THEN 1 ELSE 0 END) OVER (PARTITION BY r.RESPONSEID) AS has_main_question,
        -- 统计当前参与者是否有子问题回答
        SUM(CASE WHEN r.QUESTIONID IN ('1A', '1B') THEN 1 ELSE 0 END) OVER (PARTITION BY r.RESPONSEID) AS has_sub_question
    FROM RESPONSES r
    JOIN QUESTIONS q ON q.QUESTIONID = r.QUESTIONID
    WHERE r.SURVEY = 'WaffleSurvey3000'
      AND q.QUESTIONID IN ('1', '1A', '1B')
) filtered_data
WHERE has_main_question = 1 AND has_sub_question >= 1
ORDER BY RESPONSEID;

说明 #

1. 内层查询通过窗口函数OVER (PARTITION BY r.RESPONSEID)，在每个参与者分组下统计主问题和子问题的回答数量
2. 外层查询过滤出同时有主问题和子问题回答的记录，无需额外关联表

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