【PostgreSQL数据分析实战:从数据清洗到可视化全流程】5.3 相关性分析(PEARSON/SPEARMAN相关系数)
👉 点击关注不迷路
👉 点击关注不迷路
👉 点击关注不迷路
文章大纲
- 5.3 相关性分析(PEARSON/SPEARMAN相关系数)
- 5.3.1 相关性分析理论基础
- 5.3.1.1 相关系数定义与分类
- 5.3.1.2 Pearson相关系数( Pearson Correlation Coefficient)
- 数学定义
- 适用条件
- 5.3.1.3 Spearman相关系数(Spearman Rank Correlation Coefficient)
- 数学定义
- 适用条件
- 5.3.1.4 核心区别对比
- 5.3.2 PostgreSQL实战环境搭建
- 5.3.2.1 数据准备
- 5.3.2.2 自定义函数实现
- 1. Pearson相关系数计算函数
- 2. Spearman相关系数计算函数
- 5.3.3 实证分析:成绩与阅读时长的相关性
- 5.3.3.1 数据提取与预处理
- 5.3.3.2 Pearson相关分析
- 数学与语文成绩相关性
- 数学成绩与阅读时长相关性
- 5.3.3.3 Spearman相关分析
- 转换为秩次数据(以数学成绩为例)
- 计算Spearman相关系数
- 5.3.3.4 结果对比表
- 5.3.4 异常值影响分析
- 5.3.4.1 构造含异常值数据集
- 5.3.4.2 重新计算相关系数
- 5.3.5 最佳实践与注意事项
- 5.3.5.1 方法选择指南
- 5.3.5.2 PostgreSQL优化技巧
- 5.3.5.3 业务应用场景
- 5.3.6 总结与扩展
- 5.3.6.1 核心价值
- 5.3.6.2 后续实践建议
5.3 相关性分析(PEARSON/SPEARMAN相关系数)
在数据分析领域,相关性分析是探索变量间关系的核心技术。
- 通过量化变量间的关联程度,我们可以识别关键影响因素、验证研究假设并为建模提供依据。
- PostgreSQL作为强大的关系型数据库,不仅支持高效的数据存储,还能通过自定义函数和扩展实现复杂的统计分析。
- 本章将深入解析
Pearson和Spearman两种核心相关系数
的原理、适用场景及在PostgreSQL中的实战应用。
5.3.1 相关性分析理论基础
5.3.1.1 相关系数定义与分类
相关系数
是用于衡量两个变量线性(或单调)关联程度的统计量,取值范围为[-1, 1]。绝对值越接近1表示相关性越强
:
- 正值:正相关(变量同方向变化)
- 负值:负相关(变量反方向变化)
- 0值:无线性/单调相关关系
5.3.1.2 Pearson相关系数( Pearson Correlation Coefficient)
数学定义
适用于连续变量且服从正态分布的线性相关分析,计算公式为:
其中:
- x ˉ , y ˉ \bar{x}, \bar{y} xˉ,yˉ 为变量均值
- 分子为协方差,分母为标准差乘积
适用条件
-
- 变量为连续型数据
-
- 数据服从双变量正态分布
-
- 存在线性相关关系
-
- 无显著异常值
5.3.1.3 Spearman相关系数(Spearman Rank Correlation Coefficient)
数学定义
基于变量秩次计算的非参数统计量
,适用于有序数据或非正态分布的单调相关分析。计算步骤:
-
- 将原始数据转换为秩次( r x , r y r_x, r_y rx,ry)
-
- 计算秩次的Pearson相关系数:
其中 d i = r x i − r y i d_i = r_{x_i} - r_{y_i} di=rxi−ryi 为秩次差
- 计算秩次的Pearson相关系数:
适用条件
-
- 变量可为
有序分类、等级数据
- 变量可为
-
- 数据分布无特殊要求
-
- 考察单调相关关系(不一定线性)
-
对异常值不敏感
5.3.1.4 核心区别对比
特征 | Pearson相关系数 | Spearman相关系数 |
---|---|---|
数据类型 | 连续型(正态分布) | 有序/等级/连续型 |
关系类型 | 线性相关 | 单调相关 |
分布假设 | 需要正态分布 | 无分布假设 |
异常值影响 | 敏感 | 不敏感 |
数学基础 | 协方差 / 标准差 | 秩次差平方和 |
5.3.2 PostgreSQL实战环境搭建
5.3.2.1 数据准备
使用学生成绩数据集(包含数学/语文成绩及课外阅读时长),表结构定义:
CREATE TABLE student_scores (student_id SERIAL PRIMARY KEY,math_score INT,chinese_score INT,reading_hours FLOAT,create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 插入模拟数据(30条)
INSERT INTO student_scores (math_score, chinese_score, reading_hours)
VALUES
(85, 80, 5.5), (78, 75, 4.2), (92, 88, 6.3), (65, 70, 3.8), (75, 72, 4.5),
(88, 85, 5.8), (90, 92, 7.0), (70, 68, 3.5), (80, 82, 5.0), (60, 65, 3.0),
-- 省略中间数据...
(72, 78, 4.8), (83, 86, 6.0), (68, 73, 4.0), (95, 90, 7.5), (55, 60, 2.5);
5.3.2.2 自定义函数实现
1. Pearson相关系数计算函数
-- 修改函数定义以支持 integer[] 和 double precision[] 类型
CREATE OR REPLACE FUNCTION pearson_correlation(x integer[],y double precision[]
) RETURNS FLOAT AS $$
DECLAREn INTEGER := array_length(x, 1);total_x NUMERIC := 0;total_y NUMERIC := 0;covariance FLOAT := 0;var_x FLOAT := 0;var_y FLOAT := 0;mean_x FLOAT;mean_y FLOAT;
BEGIN-- 合并计算总和FOR i IN 1..n LOOPtotal_x := total_x + x[i];total_y := total_y + y[i];END LOOP;-- 计算平均值mean_x := total_x / n;mean_y := total_y / n;-- 再次循环计算协方差和方差FOR i IN 1..n LOOPcovariance := covariance + (x[i] - mean_x) * (y[i] - mean_y);var_x := var_x + (x[i] - mean_x) ^ 2;var_y := var_y + (y[i] - mean_y) ^ 2;END LOOP;-- 返回 Pearson 相关系数RETURN covariance / (sqrt(var_x) * sqrt(var_y));
END;
$$ LANGUAGE plpgsql;
2. Spearman相关系数计算函数
CREATE OR REPLACE FUNCTION spearman_correlation(x integer[],y double precision[]
) RETURNS FLOAT AS $$
DECLAREn INTEGER := array_length(x, 1);ranks_x integer[];ranks_y double precision[];d_sq FLOAT := 0;
BEGIN-- 计算秩次(处理相同值:平均秩次)ranks_x := array(SELECT rank() OVER (ORDER BY val) - count(*) OVER (PARTITION BY val) / 2 + 0.5FROM unnest(x) WITH ORDINALITY AS t(val, ord)ORDER BY ord);ranks_y := array(SELECT rank() OVER (ORDER BY val) - count(*) OVER (PARTITION BY val) / 2 + 0.5FROM unnest(y) WITH ORDINALITY AS t(val, ord)ORDER BY ord);FOR i IN 1..n LOOPd_sq := d_sq + (ranks_x[i] - ranks_y[i])^2;END LOOP;RETURN 1 - (6 * d_sq) / (n * (n^2 - 1));
END;
$$ LANGUAGE plpgsql;
5.3.3 实证分析:成绩与阅读时长的相关性
5.3.3.1 数据提取与预处理
-- 提取数值型变量并转换为数组WITH data AS (SELECT ARRAY_AGG(math_score ORDER BY student_id) AS math_scores,ARRAY_AGG(chinese_score ORDER BY student_id) AS chinese_scores,ARRAY_AGG(reading_hours ORDER BY student_id) AS reading_hoursFROM student_scores
)
SELECT * FROM data;
5.3.3.2 Pearson相关分析
数学与语文成绩相关性
WITH data AS (SELECT ARRAY_AGG(math_score ORDER BY student_id) AS math_scores,ARRAY_AGG(chinese_score ORDER BY student_id) AS chinese_scores,ARRAY_AGG(reading_hours ORDER BY student_id) AS reading_hoursFROM student_scores
)
SELECT pearson_correlation(math_scores, chinese_scores) AS pearson_r
FROM data;
pearson_r |
---|
0.892 |
- 结果解读:
- 相关系数0.892(强正相关)
- 说明
数学成绩与语文成绩存在显著线性关系
- 数据分布验证:
通过直方图和Shapiro-Wilk检验确认两变量近似正态分布
数学成绩与阅读时长相关性
WITH data AS (SELECT ARRAY_AGG(math_score ORDER BY student_id) AS math_scores,ARRAY_AGG(chinese_score ORDER BY student_id) AS chinese_scores,ARRAY_AGG(reading_hours ORDER BY student_id) AS reading_hoursFROM student_scores
)
SELECT pearson_correlation(math_scores, reading_hours) AS pearson_r
FROM data;
pearson_r |
---|
0.785 |
5.3.3.3 Spearman相关分析
转换为秩次数据(以数学成绩为例)
原始分数 | 秩次(平均处理) |
---|---|
55 | 1.0 |
60 | 2.0 |
65 | 3.5 |
68 | 5.0 |
… | … |
计算Spearman相关系数
WITH data AS (SELECT ARRAY_AGG(math_score ORDER BY student_id) AS math_scores,ARRAY_AGG(chinese_score ORDER BY student_id) AS chinese_scores,ARRAY_AGG(reading_hours ORDER BY student_id) AS reading_hoursFROM student_scores
)
SELECT spearman_correlation(math_scores, reading_hours) AS spearman_r
FROM data;
spearman_r |
---|
0.812 |
5.3.3.4 结果对比表
分析指标 | 数学-语文 | 数学-阅读时长 |
---|---|---|
Pearson相关系数 | 0.892 (p<0.01) | 0.785 (p<0.01) |
Spearman相关系数 | 0.885 (p<0.01) | 0.812 (p<0.01) |
显著性检验 | 高度显著 | 高度显著 |
- 关键发现:
-
- 语文与数学成绩存在极强线性相关(Pearson值接近0.9)
-
- 阅读时长与数学成绩的Spearman系数略高于Pearson值,说明存在轻微非线性单调关系
-
- 两种方法均显示正向显著相关,支持"阅读时长影响学业成绩"的假设
5.3.4 异常值影响分析
5.3.4.1 构造含异常值数据集
-- 添加异常数据(数学成绩150分,阅读时长20小时)
INSERT INTO student_scores (math_score, chinese_score, reading_hours)
VALUES (150, 140, 20.0);
5.3.4.2 重新计算相关系数
指标 | 正常数据 | 含异常值数据 | 变化率 |
---|---|---|---|
Pearson_r | 0.785 | 0.623 | -20.6% |
Spearman_r | 0.812 | 0.805 | -0.9% |
- 结论:
- Pearson系数受异常值影响显著下降
- Spearman系数保持稳定,验证其抗干扰特性
- 提示:
在数据质量存疑时应优先使用Spearman分析
5.3.5 最佳实践与注意事项
5.3.5.1 方法选择指南
-
- 数据类型:
- 连续正态数据 → Pearson
有序/等级数据 → Spearman
- 非正态连续数据 → Spearman(或Kendall)
-
- 关系形态:
- 线性关系验证:绘制散点图 + 残差分析
- 单调关系:检查变量变化趋势一致性
- 数据质量:
- 缺失值处理:删除案例或插值(建议n≥30)
异常值检测:Z-score法 / K-means聚类
5.3.5.2 PostgreSQL优化技巧
-
- 批量计算:使用
ARRAY_AGG进行向量化运算,避免逐行循环
- 批量计算:使用
-
- 索引优化:对参与计算的数值型字段创建BRIN索引
-
- 扩展使用:考虑安装
plpythonu
扩展,调用Python的scipy.stats
库实现更复杂计算
- 扩展使用:考虑安装
5.3.5.3 业务应用场景
- 教育领域:分析学科成绩相关性,优化课程设置
- 电商领域:用户停留时长与购买转化率的关联分析
- 金融领域:资产收益率的相关性建模(Spearman更适合非正态金融数据)
5.3.6 总结与扩展
5.3.6.1 核心价值
-
- Pearson相关系数:
量化线性关系强度,为回归建模提供依据
- Pearson相关系数:
-
- Spearman相关系数:
捕捉单调关联,适用于更广泛的数据类型
- Spearman相关系数:
-
- 数据库原生实现:避免数据导出,提升分析效率与安全性
5.3.6.2 后续实践建议
-
- 结合
WITH RECURSIVE
实现分组相关系数计算
- 结合
-
- 集成PostGIS进行空间数据的相关性分析
-
- 与后续章节的可视化模块结合,通过Tableau/Power BI展示相关矩阵
以上内容系统解析了两种相关系数的原理与PostgreSQL实现。
你可以告诉我是否需要补充特定案例细节,或对函数性能优化提出进一步需求,我会继续完善内容。
- 通过合理选择相关系数并利用PostgreSQL的自定义函数能力,分析师能够在
数据库层完成从数据清洗到统计分析的全流程操作
,显著提升数据分析的规范性与高效性
。- 实际应用中需始终结合业务场景与数据特征,选择最适合的分析工具,确保结论的可靠性与决策价值。
相关文章:

【PostgreSQL数据分析实战:从数据清洗到可视化全流程】5.3 相关性分析(PEARSON/SPEARMAN相关系数)
👉 点击关注不迷路 👉 点击关注不迷路 👉 点击关注不迷路 文章大纲 5.3 相关性分析(PEARSON/SPEARMAN相关系数)5.3.1 相关性分析理论基础5.3.1.1 相关系数定义与分类5.3.1.2 Pearson相关系数( Pearson Corr…...
Redis面试 实战贴 后面持续更新链接
redis是使用C语言写的。 面试问题列表: Redis支持哪些数据类型?各适用于什么场景? Redis为什么采用单线程模型?优势与瓶颈是什么? RDB和AOF持久化的区别?如何选择?混合持久化如何实现&#x…...
小程序滚动条隐藏(uniapp版本)
单独指定页面隐藏(找到对应的scroll-view) <style> /* 全局隐藏滚动条样式 */ ::-webkit-scrollbar { display: none; width: 0; height: 0; color: transparent; background: transparent; } /* 确保scroll-view组件也隐藏滚动条 */ …...

python基础:序列和索引-->Python的特殊属性
一.序列和索引 1.1 用索引检索字符串中的元素 # 正向递增 shelloworld for i in range (0,len(s)):# i是索引print(i,s[i],end\t\t) print(\n--------------------------) # 反向递减 for i in range (-10,0):print(i,s[i],end\t\t)print(\n--------------------------) print(…...

java反射(2)
package 反射;import java.lang.reflect.Constructor; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.Arrays;public class demo {public static void main(String[] args) throws Exception {// 通过类的全限定名获取对应的 Class 对象…...
C++核心概念全解析:从析构函数到运算符重载的深度指南
目录 前言一、构析函数1.1 概念1.2 语法格式 1.3 核心特性1.4 调用时机1.5 构造函数 vs 析构函数1.6 代码示例 二、this关键字2.1 基本概念2.2 核心特性2.3 使用场景2.3.1 区分成员与局部变量2.3.2 返回对象自身(链式调用)2.3.3 成员函数间传递当前对象2…...
如何巧妙解决 Too many connections 报错?
1. 背景 在日常的 MySQL 运维中,难免会出现参数设置不合理,导致 MySQL 在使用过程中出现各种各样的问题。 今天,我们就来讲解一下 MySQL 运维中一种常见的问题:最大连接数设置不合理,一旦到了业务高峰期就会出现连接…...

自由学习记录(58)
Why you were able to complete the SpringBoot MyBatisPlus task smoothly: Clear logic flow: Database → Entity → Service → Controller → API → JSON response. Errors are explicit, results are verifiable — you know what’s broken and what’s fixed. Sta…...
ES6 知识点整理
一、变量声明:var、let、const 的区别 作用域 var:函数作用域(函数内有效)。let/const:块级作用域({} 内有效,如 if、for)。 变量提升 var 会提升变量到作用域顶部(值为…...

《MATLAB实战训练营:从入门到工业级应用》高阶挑战篇-《5G通信速成:MATLAB毫米波信道建模仿真指南》
《MATLAB实战训练营:从入门到工业级应用》高阶挑战篇-5G通信速成:MATLAB毫米波信道建模仿真指南 🚀📡 大家好!今天我将带大家进入5G通信的奇妙世界,我们一起探索5G通信中最激动人心的部分之一——毫米波信…...

工程师 - 汽车分类
欧洲和中国按字母对汽车分类: **轴距**:简单来说,就是前轮中心点到后轮中心点之间的距离,也就是前轮轴和后轮轴之间的长度。根据轴距的大小,国际上通常把轿车分为以下几类(德国大众汽车习惯用A\B\C\D分类&a…...

57.[前端开发-前端工程化]Day04-webpack插件模式-搭建本地服务器
Webpack常见的插件和模式 1 认识插件Plugin 认识Plugin 2 CleanWebpackPlugin CleanWebpackPlugin 3 HtmlWebpackPlugin HtmlWebpackPlugin 生成index.html分析 自定义HTML模板 自定义模板数据填充 4 DefinePlugin DefinePlugin的介绍 DefinePlugin的使用 5 mode模式配置…...

K8S - 金丝雀发布实战 - Argo Rollouts 流量控制解析
一、金丝雀发布概述 1.1 什么是金丝雀发布? 金丝雀发布(Canary Release)是一种渐进式部署策略,通过逐步将生产流量从旧版本迁移至新版本,结合实时指标验证,在最小化风险的前提下完成版本迭代。其核心逻辑…...

Qt中数据结构使用自定义类————附带详细示例
文章目录 C对数据结构使用自定义类1 QMap使用自定义类1.1 使用自定义类做key1.2 使用自定义类做value 2 QSet使用自定义类 参考 C对数据结构使用自定义类 1 QMap使用自定义类 1.1 使用自定义类做key QMap<key,value>中数据存入时会对存入key值的数据进行比较ÿ…...

数据可视化与分析
数据可视化的目的是为了数据分析,而非仅仅是数据的图形化展示。 项目介绍 项目案例为电商双11美妆数据分析,分析品牌销售量、性价比等。 数据集包括更新日期、ID、title、品牌名、克数容量、价格、销售数量、评论数量、店名等信息。 1、数据初步了解…...
基于大模型预测的产钳助产分娩全方位研究报告
目录 一、引言 1.1 研究背景与意义 1.2 研究目的与方法 二、产钳助产分娩概述 2.1 产钳助产定义与历史 2.2 适用情况与临床意义 三、大模型预测原理与数据基础 3.1 大模型技术原理 3.2 数据收集与处理 3.3 模型训练与验证 四、术前预测与准备 4.1 大模型术前风险预…...
通过混合机器学习和 TOPSIS 实现智能手机身份验证的稳健行为生物识别框架
1. 简介 随着日常工作、个人生活和金融操作对智能手机的依赖性不断增强,对弹性安全身份验证系统的需求也日益增长。尽管 PIN 码、密码和静态生物识别等传统身份验证方法仍可为系统提供一定的安全级别,但事实证明,它们容易受到多种威胁,包括敏感数据泄露、网络钓鱼、盗窃和…...

旅游设备生产企业的痛点 质检系统在旅游设备生产企业的应用
在旅游设备制造行业,产品质量直接关系到用户体验与企业口碑。从景区缆车、观光车到水上娱乐设施,每一件设备的安全性与可靠性都需经过严苛检测。然而,传统质检模式常面临数据分散、流程不透明、合规风险高等痛点,难以满足旅游设备…...

使用ESPHome烧录固件到ESP32-C3并接入HomeAssistant
文章目录 一、安装ESPHome二、配置ESP32-C3控制灯1.主配置文件esp32c3-luat.yaml2.基础通用配置base.yaml3.密码文件secret.yaml4.围栏灯four_light.yaml5.彩灯rgb_light.yaml6.左右柱灯left_right_light.yaml 三、安装固件四、HomeAssistant配置ESPHome1.直接访问2.配置ESPHom…...
【漫话机器学习系列】237. TSS总平方和
深度理解 TSS(总平方和):公式、意义与应用 在机器学习与统计建模领域,评价模型好坏的重要指标之一就是方差与误差分析。其中,TSS(Total Sum of Squares,总平方和)扮演着非常关键的角…...

DeepSeek多尺度数据:无监督与原则性诊断方案全解析
DeepSeek 多尺度数据诊断方案的重要性 在当今的 IT 领域,数据如同石油,是驱动各类智能应用发展的核心资源。随着技术的飞速发展,数据的规模和复杂性呈爆炸式增长,多尺度数据处理成为了众多领域面临的关键挑战。以计算机视觉为例,在目标检测任务中,小目标可能只有几个像素…...

Spring Framework 6:虚拟线程支持与性能增强
文章目录 引言一、虚拟线程支持:并发模型的革命二、AOT编译与原生镜像优化三、响应式编程与可观测性增强四、HTTP接口客户端与声明式HTTP五、性能比较与实际应用总结 引言 Spring Framework 6作为Spring生态系统的基础框架,随着Java 21的正式发布&#…...
用Redisson实现库存扣减的方法
Redisson是一个在Redis基础上实现的Java客户端,提供了许多高级功能,包括分布式锁、计数器、集合等。使用Redisson实现库存扣减可以保证操作的原子性和高效性。本文将详细介绍如何使用Redisson实现一个简单的库存扣减功能。 一、初始化Redisson客户端 首…...
视频转GIF
视频转GIF 以下是一个使用 Python 将视频转换为 GIF 的脚本,使用了 imageio 和 opencv-python 库: import cv2 import imageio import numpy as np """将视频转换为GIF图参数:video_path -- 输入视频的路径gif_path -- 输出GIF的路径fp…...

一场静悄悄的革命:AI大模型如何重构中国产业版图?
一场静悄悄的革命:AI大模型如何重构中国产业版图? 当ChatGPT在2022年掀起全球AI热潮时,很少有人意识到,这场技术变革正在中国产业界掀起更深层次的革命。在浙江宁波,一个纺织企业老板打开"产业链智能创新平台",30秒内就获得了原料采购、设备升级、海外拓客的全…...
kotlin 02flow-sharedFlow 完整教程
一 sharedFlow是什么 SharedFlow 是 Kotlin 协程中 Flow 的一种 热流(Hot Flow),用于在多个订阅者之间 共享事件或数据流。它适合处理 一次性事件(如导航、弹窗、Toast、刷新通知等),而不是持续状态。 ✅ …...

CentOS网络之network和NetworkManager深度解析
文章目录 CentOS网络之network和NetworkManager深度解析1. CentOS网络服务发展历史1.1 传统network阶段(CentOS 5-6)1.2 过渡期(CentOS 7)1.3 新时代(CentOS 8) 2. network和NetworkManager的核心区别3. ne…...
【AI】模型与权重的基本概念
在 ModelScope 平台上,「模型」和「权重」的定义与工程实践紧密结合,理解它们的区别需要从实际的文件结构和加载逻辑入手。以下是一个典型 ModelScope 模型仓库的组成及其概念解析: 1. ModelScope 模型仓库的典型结构 以 deepseek-ai/deepse…...
设计模式-基础概念学习总结(继承、多态、虚方法、方法重写)
概念使用例子的方式介绍(继承,多态,虚方法,方法重写),实现代码python 1. 继承(Inheritance) 概念:子类继承父类的属性和方法,可以直接复用父类的代码&#…...
2025年小程序DDoS与CC攻击防御全指南:构建智能安全生态
2025年,小程序已成为企业数字化转型的核心载体,但随之而来的DDoS与CC攻击也愈发复杂化、智能化。攻击者利用AI伪造用户行为、劫持物联网设备发起T级流量冲击,甚至通过漏洞窃取敏感数据。如何在高并发业务场景下保障小程序的稳定与安全&#x…...