【postgresql 基础入门】聚合函数，通用型，统计分析型，多种多样的聚合函数满足数据的大数据的统计分析

聚合函数

​专栏内容：

• postgresql内核源码分析
• 手写数据库toadb
• 并发编程

个人主页：我的主页

管理社区：开源数据库

座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

文章目录

• 聚合函数
• 一、前言
• 二、概述
• 三、语法介绍
• 四、通用聚集函数
• • 平均值 avg
  • 最大值 max
  • 最小值 min
  • 求和 sum
  • 行数统计 count
  • 字符串连接 string_agg
  • 五、统计类聚集函数
  • • 平方和
    • 乘积和
    • 标准差
    • 标准方差
    • 六、总结
    • 七、结尾

      一、前言

      ---

      在数据库管理系统中，SQL（结构化查询语言）的聚集函数扮演着至关重要的角色。它们能够对一组值执行计算，并返回单个值，为数据分析与决策提供了极大的便利。聚集函数不仅简化了复杂的数据处理任务，还使得数据的统计与汇总变得高效且准确。

      常见的SQL聚集函数包括求和（SUM）、平均值（AVG）、最大值（MAX）、最小值（MIN）以及计数（COUNT）等。这些函数能够快速地处理大量数据，提取出关键信息，帮助用户更好地了解数据的分布与特征。

      随着大数据时代的到来，数据的规模与复杂性不断增加，SQL聚集函数的重要性也日益凸显。它们为数据科学家、分析师以及开发者提供了强大的工具，使得数据的挖掘与分析变得更加深入与精准。因此，深入了解和掌握SQL聚集函数的使用方法，对于提升数据处理能力和优化决策过程具有重要意义。

      二、概述

      ---

      聚集函数把输入的集合，通过计算得到一个单一值，在postgresql 中内建了许多聚集函数，满足常用的统计分析，主要分为以下几类：

      • 通用聚集函数
      • 统计性聚集函数
      • 有序集聚集函数
      • 假想集聚集

        在这里我们分享几个经常用到的通用聚集函数 avg, max,min,sum,count，和字符串连接，以及用于分析偏差的聚集函数。

        三、语法介绍

        ---

        一个聚集表达式表示在由一个查询选择的行上应用一个聚集函数。一个聚集函数将多个输入减少到一个单一输出值，例如对输入的求和或平均。一个聚集表达式的语法是下列之一：

        aggregate_name (column1 [ , … ] [ order_by_clause ] )

        aggregate_name (DISTINCT column1 [ , … ] [ order_by_clause ] )

        aggregate_name ( * )

        说明：

        • 这里aggregate_name是一个聚集函数名称，

        • column1是一个表的列名，一般聚集函数会对每一行进行计算，当指定distinct时，重复值只参与计算一次；

        • 可选order by 子句对指定列进行排序，当然对于max,min 来讲排序是没有意义的，而对于字符串连接来讲，可以指定结果的显示次序；

        • 当输入为 *时，所有列都会参与计算；

        • 大部分聚集函数忽略空输入，这样其中一个或多个表达式得到空值的行将被丢弃；

          下面来介绍几个常用的集聚函数。

          四、通用聚集函数

          ---

          这里列举一些常用的聚集函数。

          平均值 avg

          avg 计算指定列的算术平均值，它可以输入的类型，以及对应的输出类型有：

          • avg ( smallint ) → numeric
          • avg ( integer ) → numeric
          • avg ( bigint ) → numeric
          • avg ( numeric ) → numeric
          • avg ( real ) → double precision
          • avg ( double precision ) → double precision
          • avg ( interval ) → interval

            计算所有非空输入值的平均值(算术平均值)，可以看到avg内置的求平均函数，适配了各种数值类型；

            最大值 max

            max 计算非空输入值的最大值;

            • max ( type ) → type 与输入类型相同

              type 类型可以是任何数字、字符串、日期/时间或enum类型， 以及inet, interval, money, oid, pg_lsn,tid和任何这些类型的数组。

              最小值 min

              min 计算非空输入值的最小值；

              • min ( type ) → type 与输入类型相同

                type 类型可以是任何数字、字符串、日期/时间或enum类型， 以及inet, interval,money, oid, pg_lsn,tid和任何这些类型的数组。

                求和 sum

                sum 计算非空输入值的总和；它可以支持以下类型的输入，同时对应输出类型：

                • sum ( smallint ) → bigint
                • sum ( integer ) → bigint
                • sum ( bigint ) → numeric
                • sum ( numeric ) → numeric
                • sum ( real ) → real
                • sum ( double precision ) → double precision
                • sum ( interval ) → interval
                • sum ( money ) → money

                  与avg相比，这里多了一种money类型。

                  行数统计 count

                  count计算输入值不为空的输入行的数量, 有两种写法：

                  • count ( * ) → bigint
                  • count ( column1 ) → bigint

                    一种是*,它是计算所有行的数量；而另一种传入列名，这时只计算当前列的非空值的行数，如果当前列没有非空值，那么与前一种结果相同。

                    当然还有一种常见的写法count(1)，这里传入常量1，它的结果与第一种写法相同，计算所有行的行数量。

                    下面通过一个例子来看一下效果：

                    postgres=# select * from products ;
                     product_id | product_name |  price  | category
                    ------------+--------------+---------+----------
                              2 | shirt        |  202.40 | type2
                              3 | cake         |   37.80 | type4
                              5 | hat          |   88.40 | type2
                              6 | milk         |   19.80 | type4
                              1 | iphone       | 8999.01 | type5
                              7 | keyboard     |   92.01 | type5
                              4 | pencil       |    8.20 | type1
                              8 | desk         |  120.00 |
                    (8 rows)
                    postgres=# select count(1) from products ;
                     count
                    -------
                         8
                    (1 row)
                    postgres=# select count(category) from products ;
                     count
                    -------
                         7
                    (1 row)
                    postgres=# select count(distinct category) from products ;
                     count
                    -------
                         4
                    (1 row)
                    

                    这里使用列名category统计，就会排除空值行，当然还可以对列的值进行去重distinct, 之后统计实际类型的数量。

                    字符串连接 string_agg

                    string_agg连接非空输入值到字符串中，支持两种输入类型：

                    • string_agg ( value text, delimiter text ) → text
                    • string_agg ( value bytea, delimiter bytea ) → bytea

                      value是输入的内容，delimiter是分隔符，

                      第一个值之后的每个值前面都有相应的分隔符(delimiter)(如果它不为空);

                      postgres=# select string_agg(product_name,',') from products ;
                                         string_agg
                      -------------------------------------------------
                       shirt,cake,hat,milk,iphone,keyboard,pencil,desk
                      (1 row)
                      

                      可以看到，经过字符串连接之后，产品名之间按指定分隔符进行连接起来。

                      五、统计类聚集函数

                      ---

                      当做一些简单的统计分析时，可以使用统计学中的公式进行计算，这里只列了几个常用的，通过类似的命名可以查看postgresql帮助文档，已经内置了很多统计类型的聚集函数。

                      平方和

                      • regr_sxx ( Y double precision, X double precision ) → double precision

                        计算自变量的“平方和” ，计算公式为：sum(X²) - sum(X)²/N.

                        postgres=# select regr_sxx(product_id,price) from products ;
                             regr_sxx
                        -------------------
                         69613296.20214999
                        (1 row)
                        

                        • regr_syy ( Y double precision, X double precision ) → double precision

                          计算因变量的“平方和”，计算公式为： sum(Y²) - sum(Y)²/N.

                          postgres=# select regr_syy(product_id,price) from products ;
                           regr_syy
                          ----------
                                 42
                          (1 row)
                          

                          注意，这里的Y 是前面一个输入。

                          乘积和

                          • regr_sxy ( Y double precision, X double precision ) → double precision

                            计算独立变量乘以因变量的“交叉积和”， ，计算公式为：sum(X*Y) - sum(X) * sum(Y)/N.

                            标准差

                            计算输入值的样本标准差;

                            • stddev_samp ( numeric_type ) → double precision / numeric

                              在输入值为real 或 double precision类型时，输出值类型为 double precision，其它数值类型时为 numeric。

                              postgres=# select stddev_samp(price) from products ;
                                  stddev_samp
                              -------------------
                               3153.530814865639
                              (1 row)
                              

                              标准方差

                              计算输入值的样本方差(样本标准差的平方);

                              • var_samp ( numeric_type ) → double precision / numeric

                                在输入值为real 或 double precision类型时，输出值类型为 double precision，其它数值类型时为 numeric。

                                六、总结

                                ---

                                本文分享了通用类型和统计类型的常用聚集函数，它们经常被使用到，也是数据库中对数据使用的一些常见方式，还有很多类似的聚集函数，可以查看postgresql帮助手册。

                                七、结尾

                                ---

                                非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

                                作者邮箱：study@senllang.onaliyun.com

                                如有错误或者疏漏欢迎指出，互相学习。

                                注：未经同意，不得转载！