如何统计BigQuery列中单词出现频率并支持指定单词黑名单过滤

方案1：BigQuery 原生SQL实现（推荐） #

无需导出数据，直接在BigQuery内完成计算，适合大数据量场景。
实现逻辑为：文本清洗→拆分单词→黑名单过滤→分组统计排序，示例代码如下：

-- 自定义配置项：修改为你的实际表、列信息，以及黑名单列表
WITH config AS (
  SELECT 
    ARRAY['the', 'and', 'of', 'a', 'to', 'in', 'is', 'it', 'for', 'on'] AS black_list, -- 黑名单可自定义
    100 AS top_n -- 返回高频词数量
),
split_words AS (
  -- 文本清洗：转小写、去除特殊符号，拆分字符串为单词数组
  SELECT
    REGEXP_EXTRACT_ALL(
      LOWER(REGEXP_REPLACE(你的文本列名, r'[^a-zA-Z0-9\u4e00-\u9fa5\s]', '')), -- 正则同时支持中英文字符
      r'[a-zA-Z0-9\u4e00-\u9fa5]+'
    ) AS words
  FROM
    `你的项目ID.你的数据集.你的表名`
),
flatten_valid_words AS (
  -- 展开单词数组为单行，过滤黑名单、空值
  SELECT word
  FROM split_words, UNNEST(words) AS word
  CROSS JOIN config
  WHERE 
    word != ''
    AND word NOT IN UNNEST(config.black_list)
)
-- 统计频次排序
SELECT
  word,
  COUNT(*) AS frequency
FROM flatten_valid_words
GROUP BY word
ORDER BY frequency DESC
LIMIT (SELECT top_n FROM config)

• 黑名单调整：直接修改config块中black_list数组的元素即可，支持中英文单词
• 中文适配：上述正则已默认支持中文，不需要额外调整
• 自定义清洗规则：修改REGEXP_REPLACE的正则规则，即可调整要保留/过滤的字符类型

方案2：Python实现 #

适合需要复杂分词逻辑（如中文分词、词性过滤）的场景，需要先拉取BigQuery数据到本地处理。

依赖安装 #

pip install google-cloud-bigquery jieba collections

示例代码 #

from google.cloud import bigquery
from collections import Counter
import jieba
import re

# 配置项
PROJECT_ID = "你的项目ID"
TABLE_PATH = "你的数据集.你的表名"
TEXT_COLUMN = "你的文本列名"
BLACK_LIST = {"的", "了", "和", "是", "the", "and", "of", "a"} # 自定义黑名单
TOP_N = 100
MIN_WORD_LEN = 2 # 过滤掉长度小于该值的单词

# 1. 从BigQuery拉取文本数据
client = bigquery.Client(project=PROJECT_ID)
query = f"SELECT {TEXT_COLUMN} FROM `{PROJECT_ID}.{TABLE_PATH}`"
text_list = [row[0] for row in client.query(query) if row[0]]

all_valid_words = []
for text in text_list:
    # 英文场景用正则拆分
    # words = re.findall(r'\w+', text.lower())
    # 中文场景用jieba分词
    words = jieba.lcut(text)
    # 过滤黑名单和短词
    valid_words = [
        word.strip().lower() for word in words 
        if word.strip().lower() not in BLACK_LIST 
        and len(word.strip()) >= MIN_WORD_LEN
    ]
    all_valid_words.extend(valid_words)

# 统计高频词
top_words = Counter(all_valid_words).most_common(TOP_N)

# 打印结果
for word, freq in top_words:
    print(f"{word}: {freq}")

• 中英文切换：注释/切换对应分词逻辑即可
• 黑名单可动态扩展，直接往BLACK_LIST集合中添加元素即可

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