超500万用户规模下SQL/Pandas实现指定单词列表在用户描述列的匹配数量统计优化方案

兄弟，500万+用户的规模用嵌套循环肯定扛不住，下面给你两种高效的实现方案，优先推荐SQL，毕竟数据库原生处理大数据的效率比Python循环高太多：

方案一：SQL原生实现 #

数据库引擎对大数据的JOIN和分组统计做了深度优化，完全不需要把数据拉到Python里处理，直接在库内完成计算。

适用于PostgreSQL的写法 #

利用unnest把单词列表转成临时表，再通过ILIKE做不区分大小写的匹配，最后分组统计不同单词的数量：

WITH words_list AS (
    SELECT unnest(ARRAY['python', 'css', 'html', ...]) AS word
)
SELECT 
    u.username,
    u.description,
    COUNT(DISTINCT w.word) AS total
FROM users u
LEFT JOIN words_list w ON u.description ILIKE '%' || w.word || '%'
GROUP BY u.username, u.description;

适用于MySQL的写法 #

MySQL没有unnest函数，先创建临时表存储单词列表，再做关联统计：

-- 创建临时表并插入单词列表
CREATE TEMPORARY TABLE words_list (word VARCHAR(255));
INSERT INTO words_list VALUES ('python'), ('css'), ('html'), ...;

-- 统计每个用户的匹配单词数
SELECT 
    u.username,
    u.description,
    COUNT(DISTINCT w.word) AS total
FROM users u
LEFT JOIN words_list w ON u.description LIKE CONCAT('%', w.word, '%')
GROUP BY u.username, u.description;

注意：如果需要精确匹配完整单词（比如避免把"pythonista"识别成"python"），可以在匹配条件里加上单词边界，比如PostgreSQL用ILIKE '%\m' || w.word || '\M%'，MySQL用REGEXP CONCAT('[[:<:]]', w.word, '[[:>:]]')

方案二：Python Pandas高效实现 #

如果必须用Pandas处理，一定要用向量化操作替代嵌套循环，Pandas的底层是C实现的，效率比Python级循环高几个数量级。

方法一：正则匹配+去重统计 #

import pandas as pd
import re

# 假设已经从数据库加载用户数据到DataFrame
df = pd.read_sql("SELECT username, description FROM users", your_database_connection)
words = ['python', 'css', 'html', ...]

# 构建正则模式，用|分隔所有单词，加上\b实现完整单词匹配（可选）
pattern = '|'.join(rf'\b{re.escape(word)}\b' for word in words)

# 对每个描述提取所有匹配的单词，去重后统计数量
df['total'] = df['description'].str.findall(pattern, flags=re.IGNORECASE).apply(lambda x: len(set(x)))

# 处理空描述的情况，填充0
df['total'] = df['total'].fillna(0)

方法二：布尔矩阵求和 #

通过生成布尔矩阵，每行代表一个用户，每列代表一个单词是否匹配，最后按行求和得到总数：

import pandas as pd
import re

df = pd.read_sql("SELECT username, description FROM users", your_database_connection)
words = ['python', 'css', 'html', ...]

# 生成每个单词的匹配布尔列
match_matrix = pd.DataFrame({
    word: df['description'].str.contains(
        rf'\b{re.escape(word)}\b', 
        flags=re.IGNORECASE, 
        na=False  # 空描述直接视为不匹配
    )
    for word in words
})

# 每行求和就是该用户匹配的不同单词数
df['total'] = match_matrix.sum(axis=1)

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