pgsql聚合函数解析 · PHP知识总结

[TOC] # **通用聚合函数** <table border="0"> <tbody> <tr> <td>函数</td> <td>参数类型</td> <td>返回类型</td> <td>描述</td> <td>示例</td> <td>结果</td> </tr> <tr> <td>array_agg(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>任意非数组类型</td> <td>参数类型的数组</td> <td>将入参包括NULL连接成一个数组</td> <td>select array_agg(id) from tbl_test;</td> <td>{1,2,3}</td> </tr> <tr> <td>array_agg(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>任意数组类型</td> <td>入参数据类型</td> <td> 将入参数组连接成更高维度的数组，输入的数组必须是相同的维度，且不允许是空或NULL </td> <td>select array_agg(array['b','c','a']);</td> <td> {{b,c,a}}</td> </tr> <tr> <td>avg(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> smallint, int, bigint, real, double precision, numeric, or interval</td> <td>整形返回numeric，浮点型返回double precision，其他和入参类型相同</td> <td>平均值</td> <td>select avg(id) from tbl_test;</td> <td>2.0000000000000000</td> </tr> <tr> <td>bit_and(<tt class="REPLACEABLE c3">expression</tt>)</td> <td><tt class="TYPE">smallint</tt>, <tt class="TYPE">int</tt>, <tt class="TYPE">bigint</tt>, or <tt class="TYPE">bit</tt></td> <td>和入参类型相同</td> <td>所有非NULL输入值的按位与，如果全为NULL则返回NULL</td> <td>select bit_and(id) from tbl_test;</td> <td>0</td> </tr> <tr> <td>bit_or(<tt class="REPLACEABLE c3">expression</tt>)</td> <td><tt class="TYPE">smallint</tt>, <tt class="TYPE">int</tt>, <tt class="TYPE">bigint</tt>, or <tt class="TYPE">bit</tt></td> <td>和入参类型相同</td> <td>所有非NULL输入值的按位或，如果全为NULL则返回NULL</td> <td>select bit_or(id) from tbl_test;</td> <td>3</td> </tr> <tr> <td>bool_and(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>bool</td> <td>bool</td> <td>如果输入全是true则返回true，否则为false</td> <td>select bool_or(id::bool) from tbl_test;</td> <td>t</td> </tr> <tr> <td>bool_or(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>bool</td> <td>bool</td> <td>如果输入至少一个true，则返回true，否则返回false</td> <td>select bool_or((id-1)::bool) from tbl_test;</td> <td>t</td> </tr> <tr> <td>count(*)</td> <td> </td> <td>bigint</td> <td>输入行数</td> <td>select count(*) from tbl_test;</td> <td>3</td> </tr> <tr> <td>count(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>any</td> <td>bigint</td> <td>输入行中非NULL的行数</td> <td>select count(id) from tbl_test;</td> <td>3</td> </tr> <tr> <td>every(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>bool</td> <td>bool</td> <td>功能同bool_and</td> <td> </td> <td> </td> </tr> <tr> <td>json_agg(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>any</td> <td>json</td> <td>将输入聚合成一个json数组</td> <td>select json_agg(id) from tbl_test;</td> <td>[1, 2, 3]</td> </tr> <tr> <td>jsonb_agg(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>any</td> <td>jsonb</td> <td>将输入聚合成一个json数组</td> <td>select jsonb_agg(id) from tbl_test;</td> <td>[1, 2, 3]</td> </tr> <tr> <td>json_object_agg(<tt class="REPLACEABLE c3">name</tt>,<tt class="REPLACEABLE c3">value</tt>)</td> <td>(any, any)</td> <td>json</td> <td>将输入组成一个key/value对的json对象</td> <td>select json_object_agg('a','one');</td> <td>{ "a" : "one" }</td> </tr> <tr> <td>jsonb_object_agg(<tt class="REPLACEABLE c3">name</tt>,<tt class="REPLACEABLE c3">value</tt>)</td> <td>(any, any)</td> <td>jsonb</td> <td>将输入组成一个key/value对的json对象</td> <td>select jsonb_object_agg('a','one');</td> <td>{"a": "one"}</td> </tr> <tr> <td>max(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> </td> <td> </td> <td>输入最大值</td> <td> select max(id) from tbl_test; </td> <td>3</td> </tr> <tr> <td>min(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> </td> <td> </td> <td>输入最小值</td> <td>select min(id) from tbl_test;</td> <td>1</td> </tr> <tr> <td>string_agg(<tt class="REPLACEABLE c3">expression</tt>,<tt class="REPLACEABLE c3">delimiter</tt>)</td> <td>(<tt class="TYPE">text</tt>, <tt class="TYPE">text</tt>) or (<tt class="TYPE">bytea</tt>, <tt class="TYPE">bytea</tt>)</td> <td>同参数类型</td> <td>将输入使用delimiter连接成一个text</td> <td>select string_agg(name,',') from tbl_test;</td> <td> 张三,李四,王五</td> </tr> <tr> <td>sum(<tt class="REPLACEABLE c3">expression</tt>)</td> <td><tt class="TYPE">smallint</tt>, <tt class="TYPE">int</tt>, <tt class="TYPE">bigint</tt>, <tt class="TYPE">real</tt>, <tt class="TYPE">double precision</tt>, <tt class="TYPE">numeric</tt>, <tt class="TYPE">interval</tt>, or <tt class="TYPE">money</tt></td> <td> </td> <td>输入和</td> <td>select sum(id) from tbl_test;</td> <td>6</td> </tr> <tr> <td>xmlagg(<tt class="REPLACEABLE c3">expression</tt>)</td> <td>xml</td> <td>xml</td> <td> </td> <td>请参考xml类型及其函数</td> <td> </td> </tr> </tbody> </table> # **统计聚合函数** <table border="0"> <tbody> <tr> <td>函数</td> <td>参数类型</td> <td>返回类型</td> <td>描述</td> <td>示例</td> <td>结果</td> </tr> <tr> <td>corr(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>相关系数</td> <td>select corr(id,id) from tbl_test;</td> <td>1</td> </tr> <tr> <td>covar_pop(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>总体协方差</td> <td>select covar_pop(id,id) from tbl_test;</td> <td>0.666666666666667</td> </tr> <tr> <td>covar_samp(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>样本协方差</td> <td>select covar_samp(id,id1) from tbl_test;</td> <td>0</td> </tr> <tr> <td>regr_avgx(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>自变量平均值(sum(X)/N)</td> <td>select regr_avgx(id,id1) from tbl_test;</td> <td>1</td> </tr> <tr> <td>regr_avgy(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>因变量平均值(sum(Y)/N)</td> <td>select regr_avgy(id,id1) from tbl_test;</td> <td>2</td> </tr> <tr> <td>regr_count(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>bigint</td> <td>两个参数都不为NULL的行数</td> <td> select regr_count(id,id1) from tbl_test;</td> <td>3</td> </tr> <tr> <td>regr_intercept(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的Y轴截距</td> <td>select regr_intercept(id,id) from tbl_test;</td> <td>0</td> </tr> <tr> <td>regr_r2(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>相关系数平方</td> <td>select regr_r2(id,id) from tbl_test;</td> <td>1</td> </tr> <tr> <td>regr_slope(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td>根据所有输入点(X,Y)利用最小二乘法计算一个线性方程式。然后返回该直线的斜率</td> <td>select regr_slope(id,id) from tbl_test;</td> <td>1</td> </tr> <tr> <td>regr_sxx(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td><tt class="LITERAL">sum(<tt class="REPLACEABLE c3">X</tt>^2) - sum(<tt class="REPLACEABLE c3">X</tt>)^2/<tt class="REPLACEABLE c3">N</tt></tt> </td> <td>select regr_sxx(id,id) from tbl_test;</td> <td>2</td> </tr> <tr> <td>regr_sxy(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td><tt class="LITERAL">sum(<tt class="REPLACEABLE c3">X</tt>*<tt class="REPLACEABLE c3">Y</tt>) - sum(<tt class="REPLACEABLE c3">X</tt>) * sum(<tt class="REPLACEABLE c3">Y</tt>)/<tt class="REPLACEABLE c3">N</tt></tt> </td> <td>select regr_sxy(id,id) from tbl_test;</td> <td>2</td> </tr> <tr> <td>regr_syy(<tt class="REPLACEABLE c3">Y</tt>, <tt class="REPLACEABLE c3">X</tt>)</td> <td>double precision</td> <td>double precision</td> <td><tt class="LITERAL">sum(<tt class="REPLACEABLE c3">Y</tt>^2) - sum(<tt class="REPLACEABLE c3">Y</tt>)^2/<tt class="REPLACEABLE c3">N</tt></tt> </td> <td>select regr_syy(id,id) from tbl_test;</td> <td>2</td> </tr> <tr> <td>stddev(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> smallint, int, bigint, real, double precision, or numeric </td> <td> double precision for floating-point arguments, otherwise numeric </td> <td>同stddev_samp</td> <td> </td> <td> </td> </tr> <tr> <td>stddev_pop(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> smallint, int, bigint, real, double precision, or numeric </td> <td> double precision for floating-point arguments, otherwise numeric </td> <td>总体标准差</td> <td>select stddev_pop(id) from tbl_test;</td> <td>0.81649658092772603273</td> </tr> <tr> <td>stddev_samp(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> smallint, int, bigint, real, double precision, or numeric </td> <td> double precision for floating-point arguments, otherwise numeric </td> <td>样本标准差</td> <td>select stddev_samp(id) from tbl_test;</td> <td>1.00000000000000000000</td> </tr> <tr> <td><code class="FUNCTION">variance</code>(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> smallint, int, bigint, real, double precision, or numeric </td> <td> double precision for floating-point arguments, otherwise numeric </td> <td>同var_samp</td> <td> </td> <td> </td> </tr> <tr> <td><code class="FUNCTION">var_pop</code>(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> smallint, int, bigint, real, double precision, or numeric </td> <td> double precision for floating-point arguments, otherwise numeric </td> <td>总体方差</td> <td>select var_pop(id) from tbl_test;</td> <td>0.66666666666666666667</td> </tr> <tr> <td><code class="FUNCTION">var_samp</code>(<tt class="REPLACEABLE c3">expression</tt>)</td> <td> smallint, int, bigint, real, double precision, or numeric </td> <td> double precision for floating-point arguments, otherwise numeric </td> <td>样本方差</td> <td>select var_samp(id) from tbl_test;</td> <td>1.00000000000000000000</td> </tr> </tbody> # **顺序集聚合函数** <table border="0"> <tbody> <tr> <td>函数</td> <td>直接参数类型</td> <td>聚合参数类型</td> <td>返回类型</td> <td>描述</td> <td>示例</td> <td>结果</td> </tr> <tr> <td>mode() WITHIN GROUP (ORDER BY<tt class="REPLACEABLE c3">sort_expression</tt>)</td> <td> </td> <td>任意可排序类型</td> <td>同排序类型</td> <td> 返回最频繁的输入值（如果有 多个同样频繁的结果，则返回第一个） </td> <td>select mode() within group (order by id) from tbl_test;</td> <td>2</td> </tr> <tr> <td>percentile_cont(<tt class="REPLACEABLE c3">fraction</tt>) WITHIN GROUP (ORDER BY <tt class="REPLACEABLE c3">sort_expression</tt>)</td> <td>double precision</td> <td><tt class="TYPE">double precision</tt>or <tt class="TYPE">interval</tt></td> <td>同排序类型</td> <td>continuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed</td> <td>select percentile_cont(0.25) WITHIN GROUP (ORDER BY id) from tbl_test;</td> <td>1.75</td> </tr> <tr> <td>percentile_cont(<tt class="REPLACEABLE c3">fractions</tt>) WITHIN GROUP (ORDER BY <tt class="REPLACEABLE c3">sort_expression</tt>)</td> <td>double precision[]</td> <td><tt class="TYPE">double precision</tt>or <tt class="TYPE">interval</tt></td> <td>array of sort expression's type</td> <td>multiple continuous percentile: returns an array of results matching the shape of the <tt class="LITERAL">fractions</tt>parameter, with each non-null element replaced by the value corresponding to that percentile</td> <td> </td> <td> </td> </tr> <tr> <td>percentile_disc(<tt class="REPLACEABLE c3">fraction</tt>) WITHIN GROUP (ORDER BY <tt class="REPLACEABLE c3">sort_expression</tt>)</td> <td>double precision</td> <td>any sortable type</td> <td>same as sort expression</td> <td>discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction</td> <td> </td> <td> </td> </tr> <tr> <td>percentile_disc(<tt class="REPLACEABLE c3">fractions</tt>) WITHIN GROUP (ORDER BY <tt class="REPLACEABLE c3">sort_expression</tt>)</td> <td>double precision[]</td> <td>any sortable type</td> <td>array of sort expression's type</td> <td>multiple discrete percentile: returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the input value corresponding to that percentile</td> <td> </td> <td> </td> </tr> </tbody> </table> </table>