BigQuery产品描述列分析：高频词、后置词及危险刀具筛选

安全阿岳

2026-7-6

BigQuery 刀具产品数据处理方案

1. 统计产品描述列中出现频率最高的词汇

通过拆分文本、清理无效字符后分组统计高频词：

WITH tokenized_descriptions AS (
  SELECT
    LOWER(REGEXP_EXTRACT(word, r'[a-zA-Z]+')) AS cleaned_word
  FROM
    `your-project.your-dataset.your-table`,
    UNNEST(SPLIT(REGEXP_REPLACE(product_description, r'[^\w\s]', ''), ' ')) AS word
  WHERE
    cleaned_word IS NOT NULL AND cleaned_word != ''
)
SELECT
  cleaned_word,
  COUNT(*) AS frequency
FROM
  tokenized_descriptions
GROUP BY
  cleaned_word
ORDER BY
  frequency DESC
LIMIT 10;

核心步骤：去除标点符号→拆分描述为单词数组→转小写并提取纯字母词汇→分组统计频率并排序

2. 找出紧随“Knife”之后的高频词汇

利用正则匹配提取"Knife"后的紧邻单词，再统计出现频率：

WITH word_pairs AS (
  SELECT
    REGEXP_EXTRACT_ALL(LOWER(product_description), r'knife\s+([a-zA-Z]+)') AS following_words
  FROM
    `your-project.your-dataset.your-table`
  WHERE
    REGEXP_CONTAINS(product_description, r'\bKnife\b', 'i')
)
SELECT
  word,
  COUNT(*) AS frequency
FROM
  word_pairs,
  UNNEST(following_words) AS word
GROUP BY
  word
ORDER BY
  frequency DESC
LIMIT 10;

关键逻辑：用正则knife\s+([a-zA-Z]+)匹配"knife"后的第一个单词（不区分大小写），提取后展开统计

3. 筛选锋利危险刀具的产品描述

通过正则匹配目标特征并排除指定非危险类别：

SELECT
  product_id,
  product_description
FROM
  `your-project.your-dataset.your-table`
WHERE
  REGEXP_CONTAINS(product_description, r'\b(sharp|dangerous|blade|cutting)\b', 'i')
  AND NOT REGEXP_CONTAINS(product_description, r'\b(Halloween knife|Knife Block|Knife Tray|Knife Organizer)\b', 'i')
  AND REGEXP_CONTAINS(product_description, r'\bKnife\b', 'i');