Objectives
• pg_jieba概述
• pg_jieba安装
• pg_jieba使用技巧与应用场景
pg_jieba概述
• pg_jieba 是一个为 PostgreSQL 数据库设计的中文全文搜索扩展。它基于 cppjieba 项目，提供了强大的中文分词功能，使得 PostgreSQL 能够高效地处理中文文本数据。无论是企业级应用还是个人项目，pg_jieba 都为数据库带来显著的性能提升和功能扩展。
• g_jieba 的核心技术基于 cppjieba，这是一个用 C++ 实现的中文分词库。pg_jieba 通过将 cppjieba 集成到 PostgreSQL 中，实现了对中文文本的全文搜索支持。
Pg_jieba技术特点
• C++11 支持：pg_jieba 需要 C++11 编译器支持，确保了代码的现代化和高性能。
• PostgreSQL 扩展：作为 PostgreSQL 的扩展，pg_jieba 能够无缝集成到现有的数据库环境中，无需对数据库架构进行重大调整。
• 多种分词模式：支持多种分词模式，包括 MP(最大概率法)、HMM(隐马尔可夫模型)和混合模式，满足不同场景的需求。
pg_jieba应用场景
pg_jieba 适用于多种应用场景，特别是那些需要处理大量中文文本数据的场景：
• 企业级应用：如客户关系管理系统(CRM)、内容管理系统(CMS)等，需要对中文文档进行高效搜索和分析。
• 搜索引擎：为搜索引擎提供中文分词支持，提升搜索结果的准确性和用户体验。
• 数据分析：在数据分析和挖掘中，中文分词是基础步骤，pg_jieba 能够帮助快速处理和分析中文数据。
pg_jieba项目特点
pg_jieba 适用于多种应用场景，特别是那些需要处理大量中文文本数据的场景：
• 高效分词：基于 cppjieba 的高效分词算法，确保了中文文本处理的速度和准确性。
• 易于集成：作为 PostgreSQL 的扩展，pg_jieba 安装简便，配置灵活，能够快速集成到现有系统中。
• 灵活配置：支持用户自定义词典，可以根据具体需求调整分词效果。
• 跨平台支持：经过在 CentOS 和 MacOS 上的测试，pg_jieba 具有良好的跨平台兼容性。
pg_jieba安装
1、下载
git clone --depth=1 https://github.com/jaiminpan/pg_jieba
2、初始化子项目
cd pg_jieba
git submodule update --init --recursive
3、编译与安装
cmake -DCMAKE_PREFIX_PATH=/usr/local/pg-12.2
make
make install
4、安装插件
create extension pg_jieba ;
pg_jieba加载
• 在postgresql.conf中添加如下内容
• shared_preload_libraries = 'pg_jieba'
pg_jieba搜索模式概述
pg_jieba插件提供了以下可选的文本搜索配置：
• jiebacfg：精确模式，将文本精确切分，不存在冗余的词语。
• jiebaqry：全模式，将文本中所有可以匹配的词语都扫描出来，可能存在冗余的词语。
pg_jieba搜索模式一
• 精确模式配置-- jiebacfg
• SELECT * FROM to_tsvector('jiebacfg', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造');
pg_jieba搜索模式二
• 全模式配置-- jiebaqry
• SELECT * FROM to_tsvector('jiebaqry', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造');
应用案例
• 业务场景
• 在当今社交媒体的时代，人们通过各种平台分享自己的生活、观点和情感。然而，对于平台管理员和品牌经营者来说，了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向，我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析，来构建一个社交媒体情感分析系统，系统将根据用户的发帖内容，自动判断其情感倾向是积极、消极还是中性，并将结果存储在数据库中。
数据集特点
• 数据集元数据
kaggle上面的影评数据集字段介绍如下：
建表
• 创建movie_comments 表
CREATE TABLE movie_comments (
id SERIAL PRIMARY KEY, #必须要有主键
“userId” INTEGER, #导入时列区别大小写
"movieId" INTEGER, #导入时列区别大小写
Star INTEGER,
Crawl_Date INTEGER,
Comment TEXT,
Like_Count INTEGER
);
导入数据脚本
• 编辑load-data.py脚本-1
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base
import csv
# Connect to the PostgreSQL database using SQLAlchemy
engine = create_engine('postgresql://localhost:5432/postgres')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
• 编辑load-data.py脚本-2
# Define the MovieComments table schema
class MovieComments(Base):
__tablename__ = 'movie_comments'
id = Column(Integer,primary_key=True)
userId = Column(Integer)
movieId = Column(Integer)
star = Column(Integer)
crawl_date = Column(Integer)
comment = Column(String)
like_count = Column(Integer)
• 编辑load-data.py脚本-3
# Open the CSV file and parse the data
with open('/soft/ratings.csv', 'r') as csvfile:
csvreader = csv.reader(csvfile)
next(csvreader) # Skip the header row
count = 0
for row in csvreader:
# Extract the data from the row
userId = int(row[0])
movieId = int(row[1])
star = int(row[2])
crawl_date = int(row[3])
comment = row[4]
like_count = int(row[5])
• 编辑load-data.py脚本-4
# Create a new MovieComments object with the extracted data and add it to the session
movie_comment = MovieComments(userId=userId, movieId=movieId, star=star, crawl_date=crawl_date,comment=comment,
like_count=like_count)
session.add(movie_comment)
count+=1
if count % 100 == 0:
# Commit the changes to the database
session.commit()
session.commit()
# Close the database connection
session.close()
engine.dispose()
查看分词效果
• 可以使用pg_jieba的to_tsvector函数来对评论进行分词，例如，以下的SQL查询会返回每个评论的分词结果
SELECT id,"movieId" , to_tsvector('jiebacfg', comment) as words
FROM movie_comments
limit 10;
词频统计
• 可以对分词结果进行统计分析。例如，以下的SQL查询会返回每个词出现的次数
SELECT word, count(*) as frequency
FROM (
SELECT unnest(tsvector_to_array(words)) as word
FROM (
SELECT to_tsvector('jiebacfg', comment) as words
FROM movie_comments
) sub1
) sub2
GROUP BY word
ORDER BY frequency DESC limit 10;
上面的查询首先使用tsvector_to_array函数将每个评论的分词结果转化为一个数组，然后使用unnest函数将这些数组转化为一列，最后对这一列进行分组和计数。
• 可以对分词结果进行统计分析。例如，以下的SQL查询会返回每个词出现的次数
分析特定电影的影评
• 如果只对某部电影的评论感兴趣，可以添加一个WHERE子句来限制分析的范围。例如，以下的查询会返回电影"复仇者联盟"的评论中每个词出现的次数
SELECT word, count(*) as frequency
FROM (
SELECT unnest(tsvector_to_array(words)) as word
FROM (
SELECT to_tsvector('jiebacfg', comment) as words
FROM movie_comments
WHERE "movieId" = 2
) sub1
) sub2
GROUP BY word
ORDER BY frequency DESC
LIMIT 10;
• 如果只对某部电影的评论感兴趣，可以添加一个WHERE子句来限制分析的范围。例如，以下的查询会返回电影"复仇者联盟"的评论中每个词出现的次数
分析高评分和低评分差异
• 可以比较高评分和低评分评论中常用词的差异。例如，以下的查询会返回评分高于4的评论和评分低于2的评论中每个词出现的次数
上面的SQL查询首先使用string_to_array函数将每个评论拆分成单词数组。然后使用unnest函数将数组展开为单独的单词行。接下来将每个单词转换为小写，并过滤掉长度小于2的单词。最后，使用CASE语句在高评和低评中计算单词出现的次数，并使用GROUP BY将单词分组在一起。HAVING子句保证只返回同时出现在高评和低评中的单词。查询结果按高评计数、低评计数和单词的字母顺序排序。
• 可以比较高评分和低评分评论中常用词的差异。例如，以下的查询会返回评分高于4的评论和评分低于2的评论中每个词出现的次数
分析分词的共现频率
• 可以分析两个词同时出现在同一评论中的频率。例如，以下的查询会返回"电影"和"好看"同时出现在同一评论中的次数
注：@@是PostgreSQL中的全文搜索运算符，它用于检查tsvector是否匹配给定的tsquery。 tsvector是文档的全文索引，而tsquery是用于搜索文档的查询。
其他分析场景
• 统计每部电影的评论数量并按照数量从高到低排序
SELECT "movieId", COUNT(*) AS Comment_Count
FROM movie_comments
GROUP BY "movieId"
ORDER BY Comment_Count DESC;
• 找出所有评分为5星且点赞数大于100的评论
SELECT *
FROM movie_comments
WHERE Star = 5 AND Like_Count > 100;
• 统计每个用户的评论数量并按照数量从高到低排序
SELECT "userId", COUNT(*) AS Comment_Count
FROM movie_comments
GROUP BY "userId"
ORDER BY Comment_Count DESC;
• 找出某部电影中评分为3星及以下的评论并按照点赞数从高到低排序
SELECT *
FROM movie_comments
WHERE "movieId" = '2' AND Star <= 3
ORDER BY Like_Count DESC;
