population & sample covariance, standard deviation Aggregate in PostgreSQL

最新推荐文章于 2021-04-20 16:09:03 发布
最新推荐文章于 2021-04-20 16:09:03 发布
阅读量912 收藏
点赞数
分类专栏： PostgreSQL源码分析 转载 文章标签： postgresql 数据库 函数

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

  • Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
  • Postgres-XL的项目发起人Mason Sharp
  • pgpool的作者石井达夫(Tatsuo Ishii)
  • PG-Strom的作者海外浩平(Kaigai Kohei)
  • Greenplum研发总监姚延栋
  • 周正中(德哥), PostgreSQL中国用户会创始人之一
  • 汪洋，平安科技数据库技术部经理
  • ……
 
  • 2015年度PG大象会报名地址：http://postgres2015.eventdove.com/
  • PostgreSQL中国社区： http://postgres.cn/
  • PostgreSQL专业1群： 3336901（已满）
  • PostgreSQL专业2群： 100910388
  • PostgreSQL专业3群： 150657323
PostgreSQL自带了一些常用的统计学聚合函数, 非常好用.
本文介绍一下方差和标准差的一些聚合函数.


总体方差 : population covariance
总体标准差 :  population standard deviation
样本方差 : sample covariance
样本标准差 : sample standard deviation
均值 :  mean

样本均值和样本方差的介绍 : 
均值介绍 : 

对方差, 标准差, 均值不了解的话, 建议参考网易公开课, 统计学.
浅显易懂.

PostgreSQL计算方差, 标准差的聚合函数如下 : 
stddev(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric historical alias for stddev_samp
stddev_pop(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric population standard deviation of the input values
stddev_samp(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric sample standard deviation of the input values
variance(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric historical alias for var_samp
var_pop(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric population variance of the input values (square of the population standard deviation)
var_samp(expression) smallint, int, bigint, real, double precision, or numeric double precision for floating-point arguments, otherwise numeric sample variance of the input values (square of the sample standard deviation)
其中stddev和variance是stddev_samp和var_samp的别名.
这些函数用于计算数据集的总体/样本 方差,总体/样本 标准差.
例如 : 
1,2,3,100 这组数据共4个值, 总体均值和样本均值分别为 : 
(1+2+3+100)/4 = 26.5
总体方差 : ((1-26.5)^2 +  (2-26.5)^2 +  (3-26.5)^2 +  (100-26.5)^2)/4 = 1801.25
样本方差 :  ((1-26.5)^2 +  (2-26.5)^2 +  (3-26.5)^2 +  (100-26.5)^2)/(4-1) = 2401.6666....
总体标准差 : 平方根( 总体方差 ) =  42.4411357058220109
样本标准差 :  平方根(样本 方差 ) =  49.0068022489395513
使用PostgreSQL计算如下 :  

  
  

   
   postgres=# select variance(id) from (values(1),(2),(3),(100)) as t(id);
  
  

  
  

   
          variance        
  
  

  
  

   
   -----------------------
  
  

  
  

   
    2401.6666666666666667
  
  

  
  

   
   (1 row)
  
  

  
  

   
   postgres=# select var_pop(id) from (values(1),(2),(3),(100)) as t(id);
  
  

  
  

   
           var_pop        
  
  

  
  

   
   -----------------------
  
  

  
  

   
    1801.2500000000000000
  
  

  
  

   
   (1 row)
  
  

  
  

   
   postgres=# select var_samp(id) from (values(1),(2),(3),(100)) as t(id);
  
  

  
  

   
          var_samp        
  
  

  
  

   
   -----------------------
  
  

  
  

   
    2401.6666666666666667
  
  

  
  

   
   (1 row)
  
  

  
  

   
   postgres=# select stddev(id) from (values(1),(2),(3),(100)) as t(id);
  
  

  
  

   
          stddev        
  
  

  
  

   
   ---------------------
  
  

  
  

   
    49.0068022489395513
  
  

  
  

   
   (1 row)
  
  

  
  

   
   postgres=# select stddev_pop(id) from (values(1),(2),(3),(100)) as t(id);
  
  

  
  

   
        stddev_pop      
  
  

  
  

   
   ---------------------
  
  

  
  

   
    42.4411357058220109
  
  

  
  

   
   (1 row)
  
  

  
  

   
   postgres=# select stddev_samp(id) from (values(1),(2),(3),(100)) as t(id);
  
  

  
  

   
        stddev_samp     
  
  

  
  

   
   ---------------------
  
  

  
  

   
    49.0068022489395513
  
  

  
  

   
   (1 row)

[参考]
1. src/backend/utils/adt/float.c 

  
  

   
   

    
    /*
   
   

   
   

    
     *              =========================
   
   

   
   

    
     *              FLOAT AGGREGATE OPERATORS
   
   

   
   

    
     *              =========================
   
   

   
   

    
     *
   
   

   
   

    
     *              float8_accum            - accumulate for AVG(), variance aggregates, etc.
   
   

   
   

    
     *              float4_accum            - same, but input data is float4
   
   

   
   

    
     *              float8_avg                      - produce final result for float AVG()
   
   

   
   

    
     *              float8_var_samp         - produce final result for float VAR_SAMP()
   
   

   
   

    
     *              float8_var_pop          - produce final result for float VAR_POP()
   
   

   
   

    
     *              float8_stddev_samp      - produce final result for float STDDEV_SAMP()
   
   

   
   

    
     *              float8_stddev_pop       - produce final result for float STDDEV_POP()
   
   

   
   

    
     *
   
   

   
   

    
     * The transition datatype for all these aggregates is a 3-element array
   
   

   
   

    
     * of float8, holding the values N, sum(X), sum(X*X) in that order.
   
   

   
   

    
     *
   
   

   
   

    
     * Note that we represent N as a float to avoid having to build a special
   
   

   
   

    
     * datatype.  Given a reasonable floating-point implementation, there should
   
   

   
   

    
     * be no accuracy loss unless N exceeds 2 ^ 52 or so (by which time the
   
   

   
   

    
     * user will have doubtless lost interest anyway...)
   
   

   
   

    
     */
   
   

  
  

  
  

   
   ..................
  
  

  
  

   
   

    
    Datum
   
   

   
   

    
    float8_var_pop(PG_FUNCTION_ARGS)
   
   

   
   

    
    {
   
   

   
   

    
            ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
   
   

   
   

    
            float8     *transvalues;
   
   

   
   

    
            float8          N,
   
   

   
   

    
                                    sumX,
   
   

   
   

    
                                    sumX2,
   
   

   
   

    
                                    numerator;
   
   

   
   

    
    

   
   

   
   

    
            transvalues = check_float8_array(transarray, "float8_var_pop", 3);
   
   

   
   

    
            N = transvalues[0];
   
   

   
   

    
            sumX = transvalues[1];
   
   

   
   

    
            sumX2 = transvalues[2];
   
   

   
   

    
    

   
   

   
   

    
            /* Population variance is undefined when N is 0, so return NULL */
   
   

   
   

    
            if (N == 0.0)
   
   

   
   

    
                    PG_RETURN_NULL();
   
   

   
   

    
    

   
   

   
   

    
            numerator = N * sumX2 - sumX * sumX;
   
   

   
   

    
            CHECKFLOATVAL(numerator, isinf(sumX2) || isinf(sumX), true);
   
   

   
   

    
    

   
   

   
   

    
            /* Watch out for roundoff error producing a negative numerator */
   
   

   
   

    
            if (numerator <= 0.0)
   
   

   
   

    
                    PG_RETURN_FLOAT8(0.0);
   
   

   
   

    
    

   
   

   
   

    
            PG_RETURN_FLOAT8(numerator / (N * N));
   
   

   
   

    
    }
   
   

   
   

    
    

   
   

   
   

    
    Datum
   
   

   
   

    
    float8_var_samp(PG_FUNCTION_ARGS)
   
   

   
   

    
    {
   
   

   
   

    
            ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
   
   

   
   

    
            float8     *transvalues;
   
   

   
   

    
            float8          N,
   
   

   
   

    
                                    sumX,
   
   

   
   

    
                                    sumX2,
   
   

   
   

    
                                    numerator;
   
   

  
  

  
  

   
   

    
    

   
   

   
   

    
            transvalues = check_float8_array(transarray, "float8_var_samp", 3);
   
   

   
   

    
            N = transvalues[0];
   
   

   
   

    
            sumX = transvalues[1];
   
   

   
   

    
            sumX2 = transvalues[2];
   
   

   
   

    
    

   
   

   
   

    
            /* Sample variance is undefined when N is 0 or 1, so return NULL */
   
   

   
   

    
            if (N <= 1.0)
   
   

   
   

    
                    PG_RETURN_NULL();
   
   

   
   

    
    

   
   

   
   

    
            numerator = N * sumX2 - sumX * sumX;
   
   

   
   

    
            CHECKFLOATVAL(numerator, isinf(sumX2) || isinf(sumX), true);
   
   

   
   

    
    

   
   

   
   

    
            /* Watch out for roundoff error producing a negative numerator */
   
   

   
   

    
            if (numerator <= 0.0)
   
   

   
   

    
                    PG_RETURN_FLOAT8(0.0);
   
   

   
   

    
    

   
   

   
   

    
            PG_RETURN_FLOAT8(numerator / (N * (N - 1.0)));
   
   

   
   

    
    }
   
   

   
   

    
    

   
   

   
   

    
    Datum
   
   

   
   

    
    float8_stddev_pop(PG_FUNCTION_ARGS)
   
   

   
   

    
    {
   
   

   
   

    
            ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
   
   

   
   

    
            float8     *transvalues;
   
   

   
   

    
            float8          N,
   
   

   
   

    
                                    sumX,
   
   

   
   

    
                                    sumX2,
   
   

   
   

    
                                    numerator;
   
   

   
   

    
    

   
   

   
   

    
            transvalues = check_float8_array(transarray, "float8_stddev_pop", 3);
   
   

   
   

    
            N = transvalues[0];
   
   

   
   

    
            sumX = transvalues[1];
   
   

   
   

    
            sumX2 = transvalues[2];
   
   

   
   

    
    

   
   

   
   

    
            /* Population stddev is undefined when N is 0, so return NULL */
   
   

   
   

    
            if (N == 0.0)
   
   

   
   

    
                    PG_RETURN_NULL();
   
   

  
  

  
  

   
   

    
    

   
   

   
   

    
            numerator = N * sumX2 - sumX * sumX;
   
   

   
   

    
            CHECKFLOATVAL(numerator, isinf(sumX2) || isinf(sumX), true);
   
   

   
   

    
    

   
   

   
   

    
            /* Watch out for roundoff error producing a negative numerator */
   
   

   
   

    
            if (numerator <= 0.0)
   
   

   
   

    
                    PG_RETURN_FLOAT8(0.0);
   
   

   
   

    
    

   
   

   
   

    
            PG_RETURN_FLOAT8(sqrt(numerator / (N * N)));
   
   

   
   

    
    }
   
   

   
   

    
    

   
   

   
   

    
    Datum
   
   

   
   

    
    float8_stddev_samp(PG_FUNCTION_ARGS)
   
   

   
   

    
    {
   
   

   
   

    
            ArrayType  *transarray = PG_GETARG_ARRAYTYPE_P(0);
   
   

   
   

    
            float8     *transvalues;
   
   

   
   

    
            float8          N,
   
   

   
   

    
                                    sumX,
   
   

   
   

    
                                    sumX2,
   
   

   
   

    
                                    numerator;
   
   

   
   

    
    

   
   

   
   

    
            transvalues = check_float8_array(transarray, "float8_stddev_samp", 3);
   
   

   
   

    
            N = transvalues[0];
   
   

   
   

    
            sumX = transvalues[1];
   
   

   
   

    
            sumX2 = transvalues[2];
   
   

   
   

    
    

   
   

   
   

    
            /* Sample stddev is undefined when N is 0 or 1, so return NULL */
   
   

   
   

    
            if (N <= 1.0)
   
   

   
   

    
                    PG_RETURN_NULL();
   
   

   
   

    
    

   
   

   
   

    
            numerator = N * sumX2 - sumX * sumX;
   
   

   
   

    
            CHECKFLOATVAL(numerator, isinf(sumX2) || isinf(sumX), true);
   
   

   
   

    
    

   
   

   
   

    
            /* Watch out for roundoff error producing a negative numerator */
   
   

   
   

    
            if (numerator <= 0.0)
   
   

   
   

    
                    PG_RETURN_FLOAT8(0.0);
   
   

   
   

    
    

   
   

   
   

    
            PG_RETURN_FLOAT8(sqrt(numerator / (N * (N - 1.0))));
   
   

  
  

  
  

   
   }
Postgresql中国用户会
关注
专栏目录
偏差、方差、标准差、协方差
weixin_42508236的博客
05-06 4241
目录1 期望值（Expectation）2 偏差（Bias）3 方差（Variance）3.1 总体方差（Population Variance）3.2 样本方差（Sample Variance）4 标准差（Deviation）4.1 总体标准差（Population Standard Deviation）4.2 样本标准差（Sample Standard Deviation）5 协方差（Covariance）5.1 协方差（Covariance）5.2 协方差矩阵（Covariance Matrix）5.
S1: Variance Standard Deviation Covariance
inner peace
01-23 997
参考这篇文章 参考
协方差、样本协方差、协方差矩阵、相关系数详解（python代码）
人工智能算法与工程实践
04-20 2万+
对于一个随机变量的分布特征，可以由均值、方差、标准差等进行描述。而对于两个随机变量的情况，有协方差和相关系数来描述两个随机变量的相互关系。 本文主要参考概率论与数理统计的教科书，整理了协方差、样本协方差、协方差矩阵、相关系数的概念解释和代码。 协方差（covariance） 协方差的概念来自概率论，实际应用中的样本协方差则与统计学概念有关。 协方差反应了随机变量X、YX、YX、Y之间“协同”变化的关系。也可以说，协方差在某种意义上给出了两个变量线性相关性的强度以及这些变量的尺度。
协方差与样本相关性
Little_Fire的博客
05-25 2772
方差（Variance）：用来度量随机变量和其数学期望（均值）之间的偏离程度。样本方差（Sample Variance）：每个样本值与全体样本值的平均数之差的平方值的平均数协方差（Covariance）：表示两个变量X、Y的总体误差。如果X、Y的变化趋势一致，cov(X,Y)&gt;0，两个变量正相关；若X、Y的变化趋势相反，cov(X,Y)&lt;0，两者负相关；若cov(X,Y)=0，X与Y线...
covariance 公式_必看！井下作业常用计算公式
weixin_39860583的博客
11-02 504
点击上方“钻机商圈”关注置顶二手机器、配件买卖，机器租赁、活源招聘信息，你想要的都在这里~钻集网APP手机版正式上线，赶快扫码下载吧！1注水泥塞施工1、水泥浆体积计算公式：式中：V——应配水泥浆的体积；LD——套管外径：mmd ——套管壁厚：mmh ——设计水泥塞厚度：mk ——附加系数(0.3—1.0)V——应配水泥浆的体积；LV0——每米套管内容积；LH1——注水泥塞时管柱尾深；m...
PostgreSQL 聚合函数讲解 - 3 总体|样本 方差, 标准方差
weixin_34326179的博客
12-14 1818
PostgreSQL自带了一些常用的统计学聚合函数, 非常好用. 本文介绍一下方差和标准差的一些聚合函数. 总体方差 : population covariance 总体标准差 : population standard deviation 样本方差 : sample covariance 样本标准差 : sample standard deviati...
Population Covariance和sample covariance有什么关系，什么时候改用哪个
06-11
Population covariance（总体协方差）和sample covariance（样本协方差）都是描述两个变量之间关系的统计量，但是它们的计算方式和使用场景略有不同。 Population covariance是针对整个总体进行计算的，它衡量的是...
Adaptive Adjustment of Noise Covariance in Kalman Filter
最新发布
10-16
Adaptive Adjustment of Noise Covariance in Kalman Filter
covariance
01-16
协方差是概率论和统计学中用于衡量两个随机变量之间线性关系的一个重要概念。在统计学中，协方差描述了当一个变量取值较高时，另一个变量取值是否倾向于较高，反之亦然。如果两个变量的正值（大值）和负值（小值）...
Evaluating covariance in prognostic and system health management applications
04-18
Evaluating covariance in prognostic and system health management applications；协方差评估
标准差(Standard Deviation) 和 标准误差(Standard Error)
热门推荐
zzminer的专栏
05-17 3万+
本文摘自 Streiner DL.Maintaining standards: differences between the standard deviation and standarderror, and when to use each. Can J Psychiatry 1996; 41: 498–502.   标准差(Standard Deviation)
numpy的nonzero()和logical_and/or/not() 方差、标准差和协方差三者之间
qq_40997551的博客
11-10 751
一、numpy的nonzero()和logical_and/or/not() np.nonzero(参数)： no.nonzero()函数是numpy中用于得到数组array中非零元素的位置（数组索引）的函数，很适合用来对数据下标的提取。着重需要强调的是nonzero函数中不仅可以放数值矩阵/行列，同样可以放布尔型（True、False）矩阵/行列，由于这个特性其适用范围更加的广泛和优秀，下面做一个简单的介绍和数据的实践。 只有df中非零元素才会有索引值，那些零值元素没有索引值； 返回的索引数值是一个二维的
covariance 公式_协方差（covariance）与相关系数（1）|统计学专题
weixin_39778582的博客
11-30 2342
1.三类关系趋势如下，在测量5个肝细胞gene x 转录本表达情况的基础上，同时也测量这5个肝细胞gene y转录本表达量。对来自同一细胞(sample)的两个数据进行配对，利用其在X轴(green)和Y轴(red)上的数据在二维平面组成一个新的点(蓝色的点)并用直线对其进行拟合。1)如果斜率(slope)为正，gene x与gene y在细胞中表达成正相关。gene x表达水平随gen...
Standard deviation
weixin_34273481的博客
12-16 1433
From Wikipedia, the free encyclopedia   Jump to: navigation, search In probability and statistics, the standard deviation of a probability distribution, random variable, or population or multis...
【概率论】4-6:协方差和相关性(Covariance and Correlation)
weixin_30681615的博客
09-22 716
title: 【概率论】4-6:协方差和相关性(Covariance and Correlation) categories: - Mathematic - Probability keywords: - Covariance - Correlation - Properties of Covariance and Correlation toc:...
数据分析数据挖掘的步骤
u010443559的博客
03-04 2897
数据挖掘主要包括以下几个步骤： 1、定义挖掘的目的 【目的】 清楚挖掘的目的是什么？ 想要达到的目的是什么？ 2、数据取样 【目的】抽取与挖掘目的相关的数据集 抽取数据标准 相关性 可靠性 完整性 有效性 衡量取样数据质量的标准 参考 ：P17 3、数据探索 【目的】保证数据的质量，为模型质量打下基础 数据探索主要包括 异常值分析 缺失值分析 相关性分析 周期性分析 可以从数据质量分析和数据特征分析两个角度对数据进行探索 数据质量分析 【目的】数据质量分析的主要任务是检查原始数据中是否存在脏数据；
用PostgreSQL了解一些统计学术语以及计算方法和表示方法 - 1
postgrechina的博客
10-14 975
1. 数学期望值, 平均值, mean http://zh.wikipedia.org/wiki/%E6%9C%9F%E6%9C%9B%E5%80%BC 表示: E[X], 即X变量的平均值. 也用miu表示 : μ=E[X] PostgreSQL中举例 :  postgres=# select avg(c1) from (values(null),(1)
Postgresql - Functions and Operators 函数和运算 - Aggregate
chuckchen1222的博客
08-06 355
聚合函数。包括了很多数学上的函数公式。非常方便。 Function Argument Type(s) Return Type Partial Mode Description array_agg(expression) any non-array type ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值