如何用SQL统计客户调研表指定列的单词出现次数？

Hey there! Since you're new to SQL, let's walk through exactly how to count word frequencies from your survey comments column—this is the first step to building your word cloud. I’ll break this down into simple, actionable steps with code examples for the most common SQL databases.

We need to accomplish three key things:

• Split each survey comment into individual words
• Clean the words (remove punctuation, normalize case, filter out useless "stopwords" like "the" or "and")
• Count how many times each cleaned word appears across all comments

Pick the example matching your database, and swap out CustomerSurveys (your table name) and SurveyComments (your comment column name) with your actual names.

For SQL Server (2016+) #

SQL Server has a built-in STRING_SPLIT function that simplifies splitting words. Here’s the full query to count word frequencies:

SELECT 
    CleanWord,
    COUNT(*) AS WordCount
FROM (
    -- Split comments into words and clean them up
    SELECT 
        LOWER(TRIM(REPLACE(REPLACE(REPLACE(value, '.', ''), ',', ''), '!', ''))) AS CleanWord
    FROM 
        CustomerSurveys -- Replace with your table name
    CROSS APPLY 
        STRING_SPLIT(SurveyComments, ' ') -- Replace SurveyComments with your column name
    WHERE 
        -- Filter out empty strings after cleaning
        LOWER(TRIM(REPLACE(REPLACE(REPLACE(value, '.', ''), ',', ''), '!', ''))) <> ''
        -- Filter out common stopwords (add/remove as needed)
        AND LOWER(TRIM(REPLACE(REPLACE(REPLACE(value, '.', ''), ',', ''), '!', ''))) NOT IN ('the', 'and', 'is', 'are', 'a', 'an', 'for', 'of', 'to')
) AS CleanWords
GROUP BY 
    CleanWord
ORDER BY 
    WordCount DESC; -- Sort by most frequent words first

For MySQL #

MySQL doesn’t have a built-in split function, so we use a numbers table trick to split words. Adjust the numbers in the CROSS JOIN section if your comments have more than 10 words:

SELECT 
    CleanWord,
    COUNT(*) AS WordCount
FROM (
    SELECT 
        LOWER(TRIM(REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(s.SurveyComments, ' ', n.n), ' ', -1), '[^a-zA-Z0-9]', ''))) AS CleanWord
    FROM 
        CustomerSurveys s -- Replace with your table name
    CROSS JOIN 
        -- Creates a list of numbers to split words (add more if needed)
        (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
         UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) n
    WHERE 
        -- Only process rows where the number matches the word position
        n.n <= LENGTH(s.SurveyComments) - LENGTH(REPLACE(s.SurveyComments, ' ', '')) + 1
        -- Filter empty strings and stopwords
        AND LOWER(TRIM(REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(s.SurveyComments, ' ', n.n), ' ', -1), '[^a-zA-Z0-9]', ''))) <> ''
        AND LOWER(TRIM(REGEXP_REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(s.SurveyComments, ' ', n.n), ' ', -1), '[^a-zA-Z0-9]', ''))) NOT IN ('the', 'and', 'is', 'are', 'a', 'an', 'for', 'of', 'to')
) AS CleanWords
GROUP BY 
    CleanWord
ORDER BY 
    WordCount DESC;

For PostgreSQL #

PostgreSQL uses UNNEST and STRING_TO_ARRAY to split strings into words:

SELECT 
    CleanWord,
    COUNT(*) AS WordCount
FROM (
    SELECT 
        LOWER(TRIM(REGEXP_REPLACE(word, '[^a-zA-Z0-9]', '', 'g'))) AS CleanWord
    FROM 
        CustomerSurveys, -- Replace with your table name
        UNNEST(STRING_TO_ARRAY(SurveyComments, ' ')) AS word -- Replace SurveyComments with your column name
    WHERE 
        -- Filter empty strings and stopwords
        LOWER(TRIM(REGEXP_REPLACE(word, '[^a-zA-Z0-9]', '', 'g'))) <> ''
        AND LOWER(TRIM(REGEXP_REPLACE(word, '[^a-zA-Z0-9]', '', 'g'))) NOT IN ('the', 'and', 'is', 'are', 'a', 'an', 'for', 'of', 'to')
) AS CleanWords
GROUP BY 
    CleanWord
ORDER BY 
    WordCount DESC;

• Add more punctuation: If your comments have question marks, semicolons, or other symbols, add them to the REPLACE functions (SQL Server) or update the regex pattern (e.g., [^a-zA-Z0-9] to exclude more symbols).
• Expand stopwords: Add more common words that don’t add value to your word cloud (like "this", "that", "it").
• Handle multiple spaces: If comments have double spaces, add a step to replace them with single spaces first (e.g., REPLACE(SurveyComments, ' ', ' ') in SQL Server).

Once you run this query, you’ll get a list of words with their counts—perfect for feeding into a word cloud tool!

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