玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 在当今社交媒体的时代,人们通过各种平台分享自己的生活、观点和情感。然而,对于平台管理员和品牌经营者来说,了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向,我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析,来构建一个社交媒体情感分析系统,系统将根据用户的发帖内容,自动判断其情感倾向是积极、消极还是中性,并将结果存储在数据库中。

业务场景

在当今社交媒体的时代,人们通过各种平台分享自己的生活、观点和情感。然而,对于平台管理员和品牌经营者来说,了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向,我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析,来构建一个社交媒体情感分析系统,系统将根据用户的发帖内容,自动判断其情感倾向是积极、消极还是中性,并将结果存储在数据库中。

数据准备

通过在kaggle上面找到豆瓣影评的数据集,里面包含了非常多的电影的中文和英文影评数据,非常适合用来实验和实践PG的pg_jieba分词插件的场景化分析。数据集链接如下:
https://www.kaggle.com/datasets/utmhikari/doubanmovieshortcomments

数据集元数据

kaggle上面的影评数据集字段介绍如下:

ID:the ID of the comment (start from 0)
Movie_Name_EN:the English name of the movie
Movie_Name_CN:the Chinese name of the movie
Crawl_Date:the date that the data are crawled
Number:the number of the comment
Username:the username of the account
Date:the date that the comment posted
Star:the star that users give to the movie (from 1 to 5, 5 grades)
Comment:the content of the comment
Like:the count of "like" on the comment
AI 代码解读

针对上述的影评数据集的字段信息,在PG数据库中创建对应的表结构如下,注意like是关键字,建议可以改为like_count,建表操作如下:

CREATE TABLE movie_comments (
    ID SERIAL PRIMARY KEY,
    Movie_Name_EN VARCHAR(255),
    Movie_Name_CN VARCHAR(255),
    Crawl_Date DATE,
    Number INTEGER,
    Username VARCHAR(255),
    Comment_riqi DATE,
    Star INTEGER,
    Comment TEXT,
    Like_Count INTEGER
);
AI 代码解读

数据导入

from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import csv

# Connect to the PostgreSQL database using SQLAlchemy
engine = create_engine('postgresql://XXXXXXXX:YYYYTTTT@pgm-ZZZZZZZZZZZ.pg.rds.aliyuncs.com:5432/demodb')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

# Define the MovieComments table schema
class MovieComments(Base):
    __tablename__ = 'movie_comments'
    id = Column(Integer, primary_key=True)
    movie_name_en = Column(String)
    movie_name_cn = Column(String)
    crawl_date = Column(DateTime)
    number = Column(Integer)
    username = Column(String)
    comment_riqi = Column(DateTime)
    star = Column(Integer)
    comment = Column(String)
    like_count = Column(Integer)

# Open the CSV file and parse the data
with open('DMSC.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
        id = int(row[0])
        movie_name_en = row[1]
        movie_name_cn = row[2]
        crawl_date = row[3]
        number = int(row[4])
        username = row[5]
        comment_riqi = row[6]
        star = int(row[7])
        comment = row[8]
        like_count = int(row[9])

        # Create a new MovieComments object with the extracted data and add it to the session
        movie_comment = MovieComments(id=id, movie_name_en=movie_name_en, movie_name_cn=movie_name_cn, crawl_date=crawl_date, number=number, username=username, comment_riqi=comment_riqi, star=star, 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()
AI 代码解读

自定义词典

导入数据之后,写入自定义词典,将电影的中文名和英文名写入词典表,这样大大的提高分词的准确度,同时也对后续的分析提供了更有价值的数据和信息,如下:

INSERT INTO JIEBA_USER_DICT(word, dict_name, weight) 
SELECT TMP.Movie_Name_CN, 0, 100
FROM
 (
     SELECT DISTINCT Movie_Name_CN as Movie_Name_CN
     FROM movie_comments
) AS TMP;


INSERT INTO JIEBA_USER_DICT(word, dict_name, weight) 
SELECT TMP.Movie_Name_EN, 0, 100
FROM
 (
     SELECT DISTINCT Movie_Name_EN as Movie_Name_EN
     FROM movie_comments
) AS TMP;

INSERT INTO jieba_user_dict VALUES ('钢铁侠',0,100);
AI 代码解读

分析场景示例

查看分词效果

可以使用pg_jieba的to_tsvector函数来对评论进行分词.例如,以下的SQL查询会返回每个评论的分词结果,如下:

SELECT id, movie_name_cn, to_tsvector('jiebacfg', comment) as words 
FROM movie_comments 
limit 10;
AI 代码解读

进行词频统计

可以对分词结果进行统计分析。例如,以下的SQL查询会返回每个词出现的次数,如下:

demodb=> SELECT word, count(*) as frequency
demodb-> FROM (
demodb(>     SELECT unnest(tsvector_to_array(words)) as word
demodb(>     FROM (
demodb(>         SELECT to_tsvector('jiebacfg', comment) as words
demodb(>         FROM movie_comments
demodb(>     ) sub1
demodb(> ) sub2
demodb-> GROUP BY word
demodb-> ORDER BY frequency DESC limit 10;
 word | frequency
------+-----------
      |   2124991
 电影 |    303655
 剧情 |    191414
 没有 |    161814
 不错 |    155734|    131681
 觉得 |    131395
 好看 |    130803
 喜欢 |    126598
 一个 |    118641
(10 行记录)
AI 代码解读

上面的查询首先使用tsvector_to_array函数将每个评论的分词结果转化为一个数组,然后使用unnest函数将这些数组转化为一列,最后对这一列进行分组和计数。

分析特定电影的影评

如果只对某部电影的评论感兴趣,可以添加一个WHERE子句来限制分析的范围。例如,以下的查询会返回电影"肖申克的救赎"的评论中每个词出现的次数,如下:

demodb=> SELECT word, count(*) as frequency
demodb-> FROM (
demodb(>     SELECT unnest(tsvector_to_array(words)) as word
demodb(>     FROM (
demodb(>         SELECT to_tsvector('jiebacfg', comment) as words
demodb(>         FROM movie_comments
demodb(>         WHERE movie_name_cn like '%复仇者联盟%'
demodb(>     ) sub1
demodb(> ) sub2
demodb-> GROUP BY word
demodb-> ORDER BY frequency DESC
demodb-> LIMIT 10;
  word  | frequency
--------+-----------
        |    132433
 电影   |     13480
 英雄   |     12421
 绿巨人 |     11514
 剧情   |     10530
 钢铁   |      8662
 没有   |      8459|      7911
 好看   |      7727|      7200
(10 行记录)
AI 代码解读

分析高评分和低评分差异

可以比较高评分和低评分评论中常用词的差异。例如,以下的查询会返回评分高于4的评论和评分低于2的评论中每个词出现的次数,如下:

SELECT word, count(*) as frequency, 'high' as rating
FROM (
    SELECT unnest(tsvector_to_array(words)) as word
    FROM (
        SELECT to_tsvector('jiebacfg', comment) as words
        FROM movie_comments
        WHERE star > 4
    ) sub1
) sub2
GROUP BY word
UNION ALL
SELECT word, count(*) as frequency, 'low' as rating
FROM (
    SELECT unnest(tsvector_to_array(words)) as word
    FROM (
        SELECT to_tsvector('jiebacfg', comment) as words
        FROM movie_comments
        WHERE star < 2
    ) sub1
) sub2
GROUP BY word;
AI 代码解读

也可以通过下面的SQL来实现,如下:

SELECT word, SUM(CASE WHEN star > 4 THEN 1 ELSE 0 END) AS high_score_count, SUM(CASE WHEN star < 2 THEN 1 ELSE 0 END) AS low_score_count
FROM (
SELECT word, star
FROM (
SELECT unnest(string_to_array(Comment, ' ')) AS word, star
FROM movie_comments
WHERE star > 4 OR star < 2
) AS words
WHERE length(word) > 1
) AS filtered_words
GROUP BY word
HAVING SUM(CASE WHEN star > 4 THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN star < 2 THEN 1 ELSE 0 END) > 0
ORDER BY high_score_count DESC, low_score_count DESC, word ASC;
AI 代码解读

上面的SQL查询首先使用string_to_array函数将每个评论拆分成单词数组。然后使用unnest函数将数组展开为单独的单词行。接下来将每个单词转换为小写,并过滤掉长度小于2的单词。最后,使用CASE语句在高评和低评中计算单词出现的次数,并使用GROUP BY将单词分组在一起。HAVING子句保证只返回同时出现在高评和低评中的单词。查询结果按高评计数、低评计数和单词的字母顺序排序。

分析分词的共现频率

可以分析两个词同时出现在同一评论中的频率。例如,以下的查询会返回"电影"和"好看"同时出现在同一评论中的次数,如下:

SELECT count(*) as cooccurrence
FROM (
    SELECT to_tsvector('jiebacfg', comment) as words
    FROM movie_comments
) sub
WHERE words @@ to_tsquery('jiebacfg', '电影 & 好看');

SELECT COUNT(DISTINCT Movie_Name_CN) AS Movie_Count
FROM movie_comments
WHERE to_tsvector('jieba', Comment) @@ to_tsquery('jieba', '电影 & 好看');
AI 代码解读

@@是PostgreSQL中的全文搜索运算符,它用于检查tsvector是否匹配给定的tsquery。 tsvector是文档的全文索引,而tsquery是用于搜索文档的查询。
to_tsvector('jieba',Comment)将“Comment”字段转换为tsvector,使用了“jieba”词典,使其能够使用pg_jieba插件进行中文分词。
to_tsquery('jieba','电影&好看')将“电影”和“好看”连接为一个查询,并使用“jieba”词典将其转换为tsquery。
@@运算符检查to_tsvector('jieba',Comment)是否与to_tsquery('jieba','电影&好看')匹配。 如果它们匹配,则返回true,否则返回false。

其他分析场景

  1. 统计每部电影的评论数量并按照数量从高到低排序。

    SELECT Movie_Name_CN, COUNT(*) AS Comment_Count
    FROM movie_comments
    GROUP BY Movie_Name_CN
    ORDER BY Comment_Count DESC;
    
    AI 代码解读
  2. 找出所有评分为5星且点赞数大于100的评论。

    SELECT *
    FROM movie_comments
    WHERE Star = 5 AND Like_Count > 100;
    
    AI 代码解读
  3. 统计每个用户的评论数量并按照数量从高到低排序。

    SELECT Username, COUNT(*) AS Comment_Count
    FROM movie_comments
    GROUP BY Username
    ORDER BY Comment_Count DESC;
    
    AI 代码解读
  4. 找出某部电影中评分为3星及以下的评论并按照点赞数从高到低排序。

    SELECT *
    FROM movie_comments
    WHERE Movie_Name_CN = '西游降魔篇' AND Star <= 3
    ORDER BY Like_Count DESC;
    
    AI 代码解读
  5. 统计每个月的评论数量并按照时间顺序排序。

    SELECT DATE_TRUNC('month', Crawl_Date) AS Month, COUNT(*) AS Comment_Count
    FROM movie_comments
    GROUP BY Month
    ORDER BY Month ASC;
    
    AI 代码解读

注意事项

  1. 使用pg_jieba插件前,需要将pg_jieba加入到shared_preload_libraries参数中。
    您可以使用RDS PostgreSQL参数设置功能,为shared_preload_libraries参数添加pg_jieba。具体操作,请参见设置实例参数。特别注意修改参数后,要点击提交按钮,否则修改不生效,不生效的情况下报错,如下:
    image.png

  2. 关于RDS PG数据库中的jieba_load_user_dict函数说明,针对不同的RDS PG的版本,该函数的参数不同,如下:

    1)1.1.0 适用于10~13
    2)1.2.0 适用于14/15

    select jieba_load_user_dict(参数1, 参数2)中
    参数1,表示加载自定义词典的词典序号
    参数2,表示是否加载默认词典,0表示加载默认词典,1表示不加载默认词典

  3. 查看pg_jieba插件的详细信息,如下:

    demodb=> \dx+ pg_jieba;
    Objects in extension "pg_jieba"
    Object Description                         
    function jieba_end(internal)
    function jieba_gettoken(internal,internal,internal)
    function jieba_gettoken_with_position(internal,internal,internal)
    function jieba_lextype(internal)
    function jieba_load_user_dict(integer,integer)
    function jieba_query_start(internal,integer)
    function jieba_start(internal,integer)
    table jieba_user_dict
    text search configuration jiebacfg
    text search configuration jiebacfg_pos
    text search configuration jiebaqry
    text search dictionary jieba_stem
    text search parser jieba
    text search parser jieba_position
    text search parser jiebaqry
    type word_type
    (16 rows)
    
    AI 代码解读
  4. 查看jieba分词的词性表,如下:

    demodb=>  select * from ts_token_type('jiebaqry');
    tokid | alias |         description         
    -------+-------+-----------------------------
      1 | nz    | other proper noun
      2 | n     | noun
      3 | m     | numeral
      4 | i     | idiom
      5 | l     | temporary idiom
      6 | d     | adverb
      7 | s     | space
      8 | t     | time
      9 | mq    | numeral-classifier compound
    
    AI 代码解读
  5. tsvector_to_array函数用法
    tsvector_to_array是PostgreSQL的一个函数,用于将tsvector类型的文本转换为由单词和位置组成的数组。tsvector是PostgreSQL的内置全文搜索类型,用于存储预处理的文本,包括单词、位置和权重。tsvector_to_array函数将tsvector文本分解为单词数组,每个单词都带有一个位置列表,该位置列表指示该单词在文本中出现的位置。例如,tsvector_to_array('a:1 b:2 c:1 d:4')将返回'{"a:1","b:2","c:1","d:4"}',其中每个元素代表一个单词和其位置列表。位置列表是一个整数数组,其中的每个元素都表示单词在文本中的一个位置。在全文搜索查询中,tsvector_to_array函数通常与unnest函数结合使用,以便在单词级别上分析tsvector文本。
    通常,与unnest函数一起使用,将tsvector转换为单独的单词行。下面是一个使用tsvector_to_array和unnest函数的示例查询,它将一个包含多个tsvector的列拆分为单独的单词行:
    SELECT movie_name_cn, word
    FROM (
    SELECT movie_name_cn, unnest(tsvector_to_array(to_tsvector('jieba', comment))) AS word
    FROM movie_comments
    ) AS words
    WHERE length(word) > 1
    ORDER BY movie_name_cn, word;
    
    AI 代码解读
    在这个查询中,首先使用to_tsvector函数将comment列中的文本转换为tsvector。然后使用tsvector_to_array函数将tsvector转换为由单词和位置列表组成的数组。最后,使用unnest函数将数组展开为单独的单词行。为了过滤掉长度小于2的单词,添加了一个WHERE子句。查询结果按电影名称和单词排序。

参考链接

pg_jiaba代码
RDS PG中文分词pg_jieba插件

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
1
1
0
1
分享
相关文章
华为数据库openGauss与PostgreSQL使用对比
华为openGauss数据库基于PostgreSQL内核演进,进行了多项增强。密码认证从MD5升级为SHA256;字符串存储中,char(n)、varchar(n)的n由字符改为字节,新增nvarchar2(n)表示字符,最大存储100MB;且将空字符&#39;&#39;统一转换为null,提升了数据处理的一致性和安全性。
76 12
【PG锦囊】阿里云 RDS PostgreSQL 版插件—AI 插件(rds_ai)
本文介绍了AI 插件(rds_ai)的核心优势、适用场景等,帮助您更好地了解 rds_ai 插件。想了解更多 RDS 插件信息和讨论交流,欢迎加入 RDS PG 插件用户专项服务群(103525002795)
瑶池数据库微课堂|PolarDB/RDS+ADB Zero-ETL:一种免费、易用、高效的数据同步方式
瑶池数据库微课堂介绍阿里云PolarDB/RDS与ADB的Zero-ETL功能,实现免费、易用、高效的数据同步。内容涵盖OLTP与OLAP的区别、传统ETL存在的问题及Zero-ETL的优势(零成本、高效同步),并演示了从RDS MySQL到AnalyticDB MySQL的具体操作步骤。未来将优化和迭代此功能,提供更好的用户体验。
体验领礼啦!体验自建数据库迁移到阿里云数据库RDS,领取桌面置物架!
「技术解决方案【Cloud Up 挑战赛】」上线!本方案介绍如何将自建数据库平滑迁移至云数据库RDS,解决业务增长带来的运维难题。通过使用RDS MySQL,您可获得稳定、可靠和安全的企业级数据库服务,专注于核心业务发展。完成任务即可领取桌面置物架,每个工作日限量50个,先到先得。
自建数据库迁移到云数据库RDS
本次课程由阿里云数据库团队的凡珂分享,主题为自建数据库迁移至云数据库RDS MySQL版。课程分为四部分:1) 传统数据库部署方案及痛点;2) 选择云数据库RDS MySQL的原因;3) 数据库迁移方案和产品选型;4) 线上活动与权益。通过对比自建数据库的局限性,介绍了RDS MySQL在可靠性、安全性、性价比等方面的优势,并详细讲解了使用DTS(数据传输服务)进行平滑迁移的步骤。此外,还提供了多种优惠活动信息,帮助用户降低成本并享受云数据库带来的便利。
104 6
市场领先者MySQL的挑战者:PostgreSQL的崛起
PostgreSQL(简称PG)是世界上最先进的开源对象关系型数据库,起源于1986年的加州大学伯克利分校POSTGRES项目。它以其丰富的功能、强大的扩展性和数据完整性著称,支持复杂数据类型、MVCC、全文检索和地理空间数据处理等特性。尽管市场份额略低于MySQL,但PG在全球范围内广泛应用,受到Google、AWS、Microsoft等知名公司支持。常用的客户端工具包括PgAdmin、Navicat和DBeaver。
76 4
体验自建数据库迁移到云数据库RDS,领取桌面置物架!
「技术解决方案【Cloud Up 挑战赛】」正式开启!本方案旨在帮助用户将自建数据库平滑迁移至阿里云RDS MySQL,享受稳定、高效、安全的数据库服务,助力业务快速发展。完成指定任务即可赢取桌面置物架等奖励,限量供应,先到先得。活动时间:2024年12月3日至12月31日16点。
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
94 42
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
48 25

相关产品

  • 云数据库 RDS
  • 云数据库 RDS PostgreSQL 版
  • 云数据库 RDS MySQL 版
  • AI助理

    你好,我是AI助理

    可以解答问题、推荐解决方案等