Redshift中如何按用户完成问卷数量进行用户分群？

No worries at all—this is a common reporting requirement, and I’m glad to help you work through it! Here’s how to get the exact breakdown of users by number of completed questionnaires (1 to 7) in Redshift:

Step 1: Calculate User Completion Counts #

First, we'll compute how many unique questionnaires each user has completed. Then, we'll map those counts to the full range of 1-7 (including counts with zero users).

SQL Query #

WITH user_completions AS (
    -- Calculate how many questionnaires each user has completed
    SELECT 
        user_id,
        COUNT(DISTINCT questionnaire_id) AS completed_count
    FROM your_table_name  -- Replace with your actual table name
    GROUP BY user_id
),
completion_ranges AS (
    -- Generate the full range of 1 to 7 surveys
    SELECT 1 AS survey_count UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7
)
-- Join ranges to user counts, including zero values
SELECT 
    cr.survey_count AS completed_questionnaires,
    COUNT(uc.user_id) AS user_count
FROM completion_ranges cr
LEFT JOIN user_completions uc ON cr.survey_count = uc.completed_count
GROUP BY cr.survey_count
ORDER BY cr.survey_count;

Explanation: #

• user_completions: Groups by user and counts distinct questionnaires to avoid overcounting if there are duplicate entries for the same user/questionnaire pair. If your table guarantees one row per user/questionnaire, you can use COUNT(*) instead for better performance.
• completion_ranges: Creates a fixed list of numbers from 1 to 7. This ensures we include every possible count, even if no users fall into that category.
• Left Join: Ensures all 7 ranges are retained in the result, with user_count showing 0 for ranges with no matching users.

Optional: Get Output as a Single Formatted String #

If you want the output exactly like your example (a single string with all counts), you can use Redshift's LISTAGG function to concatenate the results:

WITH user_completions AS (
    SELECT 
        user_id,
        COUNT(DISTINCT questionnaire_id) AS completed_count
    FROM your_table_name
    GROUP BY user_id
),
completion_ranges AS (
    SELECT 1 AS survey_count UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7
),
count_breakdown AS (
    SELECT 
        cr.survey_count,
        COUNT(uc.user_id) AS user_count
    FROM completion_ranges cr
    LEFT JOIN user_completions uc ON cr.survey_count = uc.completed_count
    GROUP BY cr.survey_count
    ORDER BY cr.survey_count
)
SELECT LISTAGG(
    '完成' || survey_count || '份问卷的用户数：' || user_count, 
    '；'
) WITHIN GROUP (ORDER BY survey_count) AS formatted_result
FROM count_breakdown;

This will return a single row with the string format you provided in your example (adjusted to match your actual data).

Just remember to replace your_table_name and questionnaire_id with your actual table and column names!

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