BigQuery拆分列统计子串频次及评论字段词频统计实现问询

Hey there! Since you're new to BigQuery, let's break down your two requirements step by step with simple, actionable queries. I'll keep things straightforward so you can follow along easily.

If your column contains delimited values (like comma-separated, semicolon-separated, etc.), BigQuery's SPLIT() and UNNEST() functions are your go-to tools here. Let's use a common comma-separated example:

SELECT
  TRIM(substring) AS cleaned_substring, -- Remove extra spaces around substrings
  COUNT(*) AS frequency
FROM
  `your_project.your_dataset.your_table`,
  UNNEST(SPLIT(delimited_column, ',')) AS substring -- Split column into array, then convert array elements to rows
WHERE
  delimited_column IS NOT NULL -- Skip empty rows to avoid skewed counts
GROUP BY
  cleaned_substring
ORDER BY
  frequency DESC;

Quick breakdown: #

• SPLIT(delimited_column, ','): Breaks your column's string into an array of substrings. Swap ',' with your actual delimiter (like ';' or '|') if needed.
• UNNEST(...): Turns the array into individual rows—this is critical for counting each substring separately.
• TRIM(...): Cleans up messy formatting (like leading/trailing spaces) that often comes with delimited data.
• COUNT(*): Tallies how many times each substring appears across all rows.

Analyzing comment words requires handling case sensitivity, punctuation, and clean splitting. Here's a robust starting query:

SELECT
  word,
  COUNT(*) AS frequency
FROM
  `your_project.your_dataset.your_table`,
  UNNEST(SPLIT(
    LOWER(REGEXP_REPLACE(comment_column, r'[^a-zA-Z0-9\s\']', '')), -- Clean the comment text
    ' '
  )) AS word
WHERE
  comment_column IS NOT NULL
  AND word != '' -- Skip empty strings from multiple spaces
GROUP BY
  word
ORDER BY
  frequency DESC;

Quick breakdown: #

• LOWER(...): Makes all text lowercase so "Great" and "great" are counted as the same word.
• REGEXP_REPLACE(...): Removes unwanted punctuation (adjust the regex r'[^a-zA-Z0-9\s\']' if you want to keep characters like apostrophes in "don't").
• SPLIT(..., ' '): Splits the cleaned comment into individual words using spaces as the separator.
• AND word != '': Filters out empty rows caused by consecutive spaces (e.g., "hello world" would split into ["hello", "", "", "world"] otherwise).

Bonus Tips for New BigQuery Users: #

• Always replace your_project.your_dataset.your_table, delimited_column, and comment_column with your actual project/dataset/table/column names.
• To focus on emotional or high-impact words, add a HAVING word IN ('love', 'frustration', 'easy') clause after GROUP BY.
• For large tables, add LIMIT 100 at the end while testing to avoid long query wait times.

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