当前位置: 首页 > news >正文

基于MySQL的企业专利数据高效查询与统计实现

背景

在进行产业链/产业评估工作时,我们需要对企业的专利进行评估,其中一个重要指标是统计企业每一年的专利数量。本文基于MySQL数据库,通过公司名称查询该公司每年的专利数,实现了高效的专利数据统计。

流程

流程图示例

项目流程概述如下:

目标:根据给定的企业名单,查询出每个企业每年的专利数量及专利得分。

任务分为两步:

  1. 构建专利申请人数据表;
  2. 利用Python查询并导出数据至Excel表。

难点与注意事项⚠️

  • 设计高效的专利申请人数据表,以便通过申请人名称快速查询专利信息。

  • 编写高效的企业专利SQL查询语句。

  • 确定专利得分:考虑到一项专利可能有多个申请人,根据申请人的位置来定义不同的得分公式。
    专利得分公式:  s c o r e = 1 p o s i t i o n \text{专利得分公式: } score = \frac{1}{position} 专利得分公式score=position1

构建专利申请人数据表

专利数据库相关的文章:3500多万家专利数据存入MySQL数据库。

由于专利数据包含超过3000万条记录,且每项专利可能有多位申请人,直接检索是否包含目标申请人效率较低。因此,我们构建了一个专利申请人表(patent_p),将每个申请人作为单独的记录,并对申请人字段建立索引,便于快速查询。表结构如下:

CREATE TABLE patent_p (id INT AUTO_INCREMENT PRIMARY KEY,applicant VARCHAR(255),publication_number VARCHAR(31),application_date DATE,publication_date DATE,grant_publication_date DATE,score DOUBLE
);

字段说明

  • 专利公开号:作为专利的唯一标识符,便于后续关联专利表。
  • 申请人:每条记录仅包含一个申请人,以便在此字段上建立索引,加速检索。
  • 日期字段:用于按照年份筛选专利数据。

注意:原始专利表中的申请人可能有多位,故在专利申请人表中将每个申请人独立存储,再对申请人字段建立索引,从而大幅提升检索效率。

处理申请人拆分的代码如下所示:

def filter_company(applicant):"""从原始的多个申请人,拆分成一个一个的申请人"""if applicant is None or not isinstance(applicant, str):return []split_pattern = r"[;;]"applicant = re.split(split_pattern, applicant)applicant = map(str.strip, applicant)return list(filter(lambda x: len(x) >= 4, applicant))

具体的数据导入代码:

import os
import re
import pymysql
import pandas as pd
from tqdm import tqdmPASSWORD = "数据库密码"
DATABASE = "数据库名"# 专利字段映射
Patent_Table_Column = {"申请人": "applicant","专利公开号": "publication_number","申请日": "application_date","申请公布日": "publication_date","授权公布日": "grant_publication_date",
}def filter_company(applicant):"""提取中文公司名称,并去除空格"""if applicant is None or not isinstance(applicant, str):return []split_pattern = r"[;;]"applicant = re.split(split_pattern, applicant)applicant = map(str.strip, applicant)return list(filter(lambda x: len(x) >= 4, applicant))def insert_sql_by_csv(file_name):df = pd.read_csv(file_name, low_memory=False)BATCH_SIZE = 3000table_column_en = list(Patent_Table_Column.values())# 连接到MySQL数据库connection = pymysql.connect(host="localhost",  # MySQL数据库的主机user="root",  # MySQL用户名password=PASSWORD,  # MySQL密码database=DATABASE,  # 你要插入数据的数据库charset="utf8mb4",cursorclass=pymysql.cursors.DictCursor,)try:with connection.cursor() as cursor:sql = f"""INSERT INTO patent_p ({", ".join(table_column_en)}, score) VALUES (%s, %s, %s, %s, %s, %s);""".strip()batch_data = []for _, row in tqdm(df.iterrows(), total=len(df)):d = {}applicants = []for zh_k, en_k in Patent_Table_Column.items():item = row[zh_k]if pd.isna(item):item = Noneif zh_k == "申请人":applicants = filter_company(item)else:d[en_k] = itemfor pos, applicant in enumerate(applicants):d["applicant"] = applicantd["score"] = 1 / (pos + 1)tmp_values = tuple([d[k] for k in table_column_en + ["score"]])batch_data.append(tmp_values)if len(batch_data) >= BATCH_SIZE:cursor.executemany(sql, batch_data)# 清空批次batch_data = []if batch_data:cursor.executemany(sql, batch_data)connection.commit()except Exception as e:print(f"插入数据时出现错误: {e}")connection.rollback()finally:connection.close()if __name__ == "__main__":folder = "/xxx/3571万专利申请全量数据1985-2022年/"print(f"文件总数: {len(os.listdir(folder))}")cnt = 0for file_name in os.listdir(folder):if file_name.endswith(".csv"):cnt += 1filename = os.path.join(folder, file_name)print(cnt, file_name)insert_sql_by_csv(filename)

该表建成后的效果如下所示:
在这里插入图片描述

在数据插入完成后,再添加索引:
如果先添加索引再插入大量数据,速度会很慢;数据全部插入完成后,再添加索引速度会快很多。

使用以下SQL语句为 applicant 添加索引:

CREATE INDEX idx_applicant ON patent_p(applicant);

这条语句会在 patent_p 表的 applicant 列上创建一个索引 idx_applicant,从而提高在该列上进行查询的效率。若不添加索引,查询需要耗时7s左右,添加索引后,在毫秒级别就可以查出结果。

企业专利查询

在构建完企业信息数据库后,我们添加了公司的年度专利统计数据(2016年至2022年各年专利数量及总得分)。最终查询效果如下:

在这里插入图片描述

示例SQL查询语句:

SELECT applicant AS company_name, YEAR(application_date) AS year, COUNT(*) AS cnt, SUM(score) 
FROM patent_p 
WHERE applicant='深圳大学' 
GROUP BY YEAR(application_date);

查询结果如下所示:
在这里插入图片描述

查询结果解释

该查询语句的作用如下:

  1. select 子句

    • applicant as company_name:将applicant列重命名为company_name,表示公司名称。
    • YEAR(application_date) as year:提取application_date的年份,并将其命名为year
    • count(*) as cnt:计算每年提交的专利申请数量。
    • sum(score):计算该公司每年所有专利申请的得分总和。
  2. from 子句:从patent_p表中获取数据。

  3. where 子句:筛选出applicant字段值等于指定公司名称的记录。

  4. group by 子句:按application_date的年份分组,统计每年的数据。

该查询将返回指定公司每年专利申请数量(cnt)及年度专利得分(sum(score))。具体Python代码实现如下:

import os
import pandas as pd
import pymysql
# import argparsedatabase = "数据库名"
password = "数据库密码"connection = pymysql.connect(host="localhost",  # MySQL数据库的主机user="root",       # MySQL用户名password=password, # MySQL密码database=database, # 插入数据的数据库charset="utf8mb4",cursorclass=pymysql.cursors.DictCursor,
)columns = list(range(1985, 2024)) + ["专利件数", "专利得分"]def get_patent_statistics_by_name(name):if not name:return {}sql = f"""select applicant as company_name, YEAR(application_date) as year, count(*) as cnt, sum(score) from patent_p where applicant='{name}'group by YEAR(application_date);"""with connection.cursor() as cursor:data = cursor.execute(sql)data = cursor.fetchall()ans = {}cnt = 0score = 0for k in columns:ans[k] = Nonefor item in data:cnt += item.get("cnt", 0)score += item.get("sum(score)", 0)year = item.get("year", None)if year:ans[year] = item.get("cnt", 0)ans["专利得分"] = scoreans["专利件数"] = cntreturn pd.Series(ans)def add_patent_data(input_file, company_name_field="企业名称"):print("open", input_file)# 读取 CSV 文件df = pd.read_csv(input_file, low_memory=False)df[columns] = df[company_name_field].apply(get_patent_statistics_by_name)folder_path = os.path.dirname(input_file)output_file = os.path.basename(input_file).split(".")[0] + "_专利统计.xlsx"# 保存更新后的数据到 CSV 文件output_file = os.path.join(folder_path, output_file)df.to_excel(output_file, index=False)print(f"专利数据已成功添加到文件:{output_file}")if __name__ == "__main__":# parser = argparse.ArgumentParser(description="Add patent counts to industry.csv")# parser.add_argument("input_file", help="The input CSV file with industry data")# parser.add_argument(#     "-name", "--name", default="企业名称", help="The column name for company names"# )# args = parser.parse_args()# # 调用函数处理文件# add_patent_data(args.input_file, args.name)folder = "/.../pku_industry/csv_folder_test"for file in os.listdir(folder):if not file.endswith(".csv"):continuefile_name = os.path.join(folder, file)add_patent_data(file_name)connection.close()

经过上述专利申请人表的构建流程,能够大幅提升企业专利信息的检索速度,为产业链分析提供强大的数据支持。

相关文章:

基于MySQL的企业专利数据高效查询与统计实现

背景 在进行产业链/产业评估工作时,我们需要对企业的专利进行评估,其中一个重要指标是统计企业每一年的专利数量。本文基于MySQL数据库,通过公司名称查询该公司每年的专利数,实现了高效的专利数据统计。 流程 项目流程概述如下&…...

热成像手机VS传统热成像仪:AORO A23为何更胜一筹?

热成像技术作为一种非接触式测温方法,广泛应用于石油化工巡检、电力巡检、应急救援、医疗、安防等“危、急、特”场景。提及热成像设备,人们往往会首先想到价格高昂、操作复杂且便携性有限的热成像仪。但是,随着技术的不断进步,市…...

Spring IoC——依赖注入

1. 依赖注入的介绍 DI,也就是依赖注入,在容器中建立的 bean (对象)与 bean 之间是有依赖关系的,如果直接把对象存在 IoC 容器中,那么就都是一个独立的对象,通过建立他们的依赖关系,…...

Linux 中,flock 对文件加锁

在Linux中,flock是一个用于对文件加锁的实用程序,它可以帮助协调多个进程对同一个文件的访问,避免出现数据不一致或冲突等问题。以下是对flock的详细介绍: 基本原理 flock通过在文件上设置锁来控制多个进程对该文件的并发访问。…...

CentOS下载ISO镜像的方法

步骤 1:访问CentOS官方网站 首先,打开浏览器,输入CentOS的官方网站地址:Download 在网站上找到ISO镜像的下载链接,通常位于“Downloads”或类似的页面上。 选择所需的CentOS版本和架构(如x86_64&#xf…...

Node.js 入门指南:从零开始构建全栈应用

​🌈个人主页:前端青山 🔥系列专栏:node.js篇 🔖人终将被年少不可得之物困其一生 依旧青山,本期给大家带来node.js篇专栏内容:node.js-入门指南:从零开始构建全栈应用 前言 大家好,我是青山。作…...

MYSQL 真实高并发下的死锁

https://pan.baidu.com/s/1nM3VQdbkNZhnK-wWboEYxA?pwdvwu6 下面是风控更新语句 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-08-04 01:00:10 140188779017984 *** (1) TRANSACTION: TRANSACTION 895271870, ACTIVE 0 sec starting …...

Zookeeper 简介 | 特点 | 数据存储

1、简介 zk就是一个分布式文件系统,不过存储数据的量极小。 1. zookeeper是一个为分布式应用程序提供的一个分布式开源协调服务框架。是Google的Chubby的一个开源实现,是Hadoop和Hbase的重要组件。主要用于解决分布式集群中应用系统的一致性问题。 2. 提…...

设计模式之结构型模式---装饰器模式

目录 1.概述2.类图3.应用场景及优缺点3.1 应用场景3.2 优缺点3.2.1 优点3.2.2 缺点 4.实现4.1 案例类图4.2 代码实现4.2.1 定义抽象构建角色4.2.2 定义具体构建角色4.2.3 定义抽象装饰器角色4.2.4 定义具体装饰角色4.2.5 装饰器模式的使用 1.概述 装饰器模式是指在不改变现有对…...

Android Pair

Pair在Android中是一种轻量级的工具类,并不是严格意义上的数据结构。 数据结构是一组有组织的方式来存储和管理数据的方式,如数组、链表、栈、队列、树、图等,它们有自己的特性和操作规则。而Pair更像是一个简单的封装,用于在需要…...

华为荣耀曲面屏手机下面空白部分设置颜色的方法

荣耀部分机型下面有一块空白区域&#xff0c;如下图红框部分 设置这部分的颜色需要在themes.xml里面设置navigationBarColor属性 <item name"android:navigationBarColor">android:color/white</item>...

《C#语法一篇通》,有20万字,需8MB字节,宜48小时阅读,没准会继续完善

本文摘录了C#语法的主要内容&#xff0c;接近20万字。 所有鸡汤的味道都等于马尿&#xff01; 如果你相信任何所谓的鸡汤文章&#xff0c;智商堪忧。 计算机语言没有”好不好“之说&#xff0c;骗子才会告诉你哪个语言好&#xff0c;学好任何一本基础语言&#xff08;C&#…...

嵌入式硬件工程师的职业发展规划

嵌入式硬件工程师可以按照以下阶段进行职业发展规划&#xff1a; 1. **初级阶段&#xff08;1-3 年&#xff09; ** - **技术学习与积累**&#xff1a; **电路基础强化**&#xff1a; 深入学习模拟电路和数字电路知识&#xff0c;能够熟练分析和设计基本的电路&#xff0c;…...

QT for android 问题总结(QT 5.15.2)

1.配置好的sdk&#xff0c;显示设置失败 Android SDK Command-line Tools run. Android Platform-Tools installed. Command-line Tools (latest) 版本过高导致报错 &#xff0c;下载一个低版本的latest &#xff0c;替换掉之前latest中的文件。即可&#xff0c;latest 路径如…...

PyTorch实战-手写数字识别-MLP模型

1 需求 包懂&#xff0c;40分钟掌握PyTorch深度学习框架&#xff0c;对应神经网络算法理论逐行讲解用PyTorch实现图像分类代码_哔哩哔哩_bilibili 10分钟入门神经网络 PyTorch 手写数字识别_哔哩哔哩_bilibili pytorch tutorial: PyTorch 手写数字识别 教程代码 从零设计并训…...

(附项目源码)Java开发语言,基于Java的高校实验室教学管理系统的设计与开发 50,计算机毕设程序开发+文案(LW+PPT)

摘 要 随着高校实验室教学与管理的复杂性增加&#xff0c;传统的手动管理系统已经无法满足日益增长的需求。实验室教学不仅涉及到学生的教学安排和管理&#xff0c;还需要对实验设备、实验材料、实验室资源等进行有效的调配和管理。而目前实验室教学管理的各项工作&#xff0c;…...

【日常问题排查小技巧-连载】

线上服务CPU飙高排查 先执行 top&#xff0c;找到CPU占用比较高的进程 id&#xff0c;&#xff08;比如 21448&#xff09; jstack 进程 id > show.txt&#xff08;jstack 21448 > show.txt&#xff09; 找到进程中CPU占用比较高的线程&#xff0c;线程 id 转换为 16 进…...

elastic search查找字段的方法

一,比如:elastic search 查找id为“ien9292voewew”的方法 此id为主键id,意思就是唯一id,在ES中是_id, 在 Elasticsearch 中,如果你想要查找特定 ID 的文档,可以使用 _get API。以下是如何通过 RESTful 请求或使用 Python 客户端来查找 ID 为 ien9292voewew 的文档的方…...

MATLAB下的四个模型的IMM例程(CV、CT左转、CT右转、CA四个模型),附下载链接

基于IMM算法的目标跟踪。利用卡尔曼滤波和多模型融合技术&#xff0c;能够在含噪声的环境中提高估计精度&#xff0c;带图像输出 文章目录 概述源代码运行结果代码结构与功能1. 初始化2. 仿真参数设置3. 模型参数设置4. 生成量测数据5. IMM算法初始化6. IMM迭代7. 绘图8. 辅助函…...

无人机之中继通信技术篇

一、定义与原理 无人机中继通信技术是指通过无人机搭载中继设备&#xff0c;将信号从一个地点传输到另一个地点&#xff0c;从而延长通信距离并保持较好的通信质量。其原理类似于传统的中继通信&#xff0c;即在两个终端站之间设置若干中继站&#xff0c;中继站将前站送来的信号…...

业务系统对接大模型的基础方案:架构设计与关键步骤

业务系统对接大模型&#xff1a;架构设计与关键步骤 在当今数字化转型的浪潮中&#xff0c;大语言模型&#xff08;LLM&#xff09;已成为企业提升业务效率和创新能力的关键技术之一。将大模型集成到业务系统中&#xff0c;不仅可以优化用户体验&#xff0c;还能为业务决策提供…...

PHP和Node.js哪个更爽?

先说结论&#xff0c;rust完胜。 php&#xff1a;laravel&#xff0c;swoole&#xff0c;webman&#xff0c;最开始在苏宁的时候写了几年php&#xff0c;当时觉得php真的是世界上最好的语言&#xff0c;因为当初活在舒适圈里&#xff0c;不愿意跳出来&#xff0c;就好比当初活在…...

阿里云ACP云计算备考笔记 (5)——弹性伸缩

目录 第一章 概述 第二章 弹性伸缩简介 1、弹性伸缩 2、垂直伸缩 3、优势 4、应用场景 ① 无规律的业务量波动 ② 有规律的业务量波动 ③ 无明显业务量波动 ④ 混合型业务 ⑤ 消息通知 ⑥ 生命周期挂钩 ⑦ 自定义方式 ⑧ 滚的升级 5、使用限制 第三章 主要定义 …...

Python爬虫(二):爬虫完整流程

爬虫完整流程详解&#xff08;7大核心步骤实战技巧&#xff09; 一、爬虫完整工作流程 以下是爬虫开发的完整流程&#xff0c;我将结合具体技术点和实战经验展开说明&#xff1a; 1. 目标分析与前期准备 网站技术分析&#xff1a; 使用浏览器开发者工具&#xff08;F12&…...

数据链路层的主要功能是什么

数据链路层&#xff08;OSI模型第2层&#xff09;的核心功能是在相邻网络节点&#xff08;如交换机、主机&#xff09;间提供可靠的数据帧传输服务&#xff0c;主要职责包括&#xff1a; &#x1f511; 核心功能详解&#xff1a; 帧封装与解封装 封装&#xff1a; 将网络层下发…...

C++中string流知识详解和示例

一、概览与类体系 C 提供三种基于内存字符串的流&#xff0c;定义在 <sstream> 中&#xff1a; std::istringstream&#xff1a;输入流&#xff0c;从已有字符串中读取并解析。std::ostringstream&#xff1a;输出流&#xff0c;向内部缓冲区写入内容&#xff0c;最终取…...

大语言模型(LLM)中的KV缓存压缩与动态稀疏注意力机制设计

随着大语言模型&#xff08;LLM&#xff09;参数规模的增长&#xff0c;推理阶段的内存占用和计算复杂度成为核心挑战。传统注意力机制的计算复杂度随序列长度呈二次方增长&#xff0c;而KV缓存的内存消耗可能高达数十GB&#xff08;例如Llama2-7B处理100K token时需50GB内存&a…...

AI病理诊断七剑下天山,医疗未来触手可及

一、病理诊断困局&#xff1a;刀尖上的医学艺术 1.1 金标准背后的隐痛 病理诊断被誉为"诊断的诊断"&#xff0c;医生需通过显微镜观察组织切片&#xff0c;在细胞迷宫中捕捉癌变信号。某省病理质控报告显示&#xff0c;基层医院误诊率达12%-15%&#xff0c;专家会诊…...

JS设计模式(4):观察者模式

JS设计模式(4):观察者模式 一、引入 在开发中&#xff0c;我们经常会遇到这样的场景&#xff1a;一个对象的状态变化需要自动通知其他对象&#xff0c;比如&#xff1a; 电商平台中&#xff0c;商品库存变化时需要通知所有订阅该商品的用户&#xff1b;新闻网站中&#xff0…...

人工智能(大型语言模型 LLMs)对不同学科的影响以及由此产生的新学习方式

今天是关于AI如何在教学中增强学生的学习体验&#xff0c;我把重要信息标红了。人文学科的价值被低估了 ⬇️ 转型与必要性 人工智能正在深刻地改变教育&#xff0c;这并非炒作&#xff0c;而是已经发生的巨大变革。教育机构和教育者不能忽视它&#xff0c;试图简单地禁止学生使…...