This patch implements some new aggregate functions defined by SQL2003:
stddev_pop(), stddev_samp(), var_pop(), and var_samp(). stddev_samp()
and var_samp() are identical to the existing stddev() and variance()
aggregates, so I've made the latter aliases for the former.
I noticed that SQL2003 does not allow DISTINCT to be specified for these
aggregate functions. I can't really see the rationale for this
restriction, and it would be fairly ugly to implement as far as I can
tell. Thoughts?
-Neil
============================================================
*** doc/src/sgml/func.sgml 8890d46febb8fdd59275cc8499b74abdfdf2877a
--- doc/src/sgml/func.sgml 2cc3ec6fb179bd88c7c26f269a09026f23fb0e2e
***************
*** 7917,7922 ****
--- 7917,7962 ----
<type>double precision</type> for floating-point arguments,
otherwise <type>numeric</type>
</entry>
+ <entry>historical alias for <function>stddev_samp</function></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>standard deviation</primary>
+ <secondary>population</secondary>
+ </indexterm>
+ <function>stddev_pop(<replaceable class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ <type>smallint</type>, <type>int</type>,
+ <type>bigint</type>, <type>real</type>, <type>double
+ precision</type>, or <type>numeric</type>
+ </entry>
+ <entry>
+ <type>double precision</type> for floating-point arguments,
+ otherwise <type>numeric</type>
+ </entry>
+ <entry>population standard deviation of the input values</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>standard deviation</primary>
+ <secondary>sample</secondary>
+ </indexterm>
+ <function>stddev_samp(<replaceable class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ <type>smallint</type>, <type>int</type>,
+ <type>bigint</type>, <type>real</type>, <type>double
+ precision</type>, or <type>numeric</type>
+ </entry>
+ <entry>
+ <type>double precision</type> for floating-point arguments,
+ otherwise <type>numeric</type>
+ </entry>
<entry>sample standard deviation of the input values</entry>
</row>
***************
*** 7954,7962 ****
<type>double precision</type> for floating-point arguments,
otherwise <type>numeric</type>
</entry>
! <entry>sample variance of the input values (square of the sample standard deviation)</entry>
</row>
</tbody>
</tgroup>
</table>
--- 7994,8041 ----
<type>double precision</type> for floating-point arguments,
otherwise <type>numeric</type>
</entry>
! <entry>historical alias for <function>var_samp</function></entry>
</row>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>variance</primary>
+ <secondary>population</secondary>
+ </indexterm>
+ <function>var_pop</function>(<replaceable class="parameter">expression</replaceable>)
+ </entry>
+ <entry>
+ <type>smallint</type>, <type>int</type>,
+ <type>bigint</type>, <type>real</type>, <type>double
+ precision</type>, or <type>numeric</type>
+ </entry>
+ <entry>
+ <type>double precision</type> for floating-point arguments,
+ otherwise <type>numeric</type>
+ </entry>
+ <entry>population variance of the input values (square of the population standard deviation)</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>variance</primary>
+ <secondary>sample</secondary>
+ </indexterm>
+ <function>var_samp</function>(<replaceable class="parameter">expression</replaceable>)
+ </entry>
+ <entry>
+ <type>smallint</type>, <type>int</type>,
+ <type>bigint</type>, <type>real</type>, <type>double
+ precision</type>, or <type>numeric</type>
+ </entry>
+ <entry>
+ <type>double precision</type> for floating-point arguments,
+ otherwise <type>numeric</type>
+ </entry>
+ <entry>sample variance of the input values (square of the sample standard deviation)</entry>
+ </row>
</tbody>
</tgroup>
</table>
============================================================
*** src/backend/utils/adt/float.c b5507f45806d6724997c6c0e89bbdb497526d75b
--- src/backend/utils/adt/float.c e4cb057539f9adebd9adbbdaeff84fbe77ea5c5b
***************
*** 1861,1871 ****
* FLOAT AGGREGATE OPERATORS
* =========================
*
! * float8_accum - accumulate for AVG(), STDDEV(), etc
! * float4_accum - same, but input data is float4
! * float8_avg - produce final result for float AVG()
! * float8_variance - produce final result for float VARIANCE()
! * float8_stddev - produce final result for float STDDEV()
*
* 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.
--- 1861,1873 ----
* FLOAT AGGREGATE OPERATORS
* =========================
*
! * float8_accum - accumulate for AVG(), STDDEV(), 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.
***************
*** 2015,2021 ****
}
Datum
! float8_variance(PG_FUNCTION_ARGS)
{
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
float8 *transvalues;
--- 2017,2023 ----
}
Datum
! float8_var_pop(PG_FUNCTION_ARGS)
{
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
float8 *transvalues;
***************
*** 2024,2034 ****
sumX2,
numerator;
! transvalues = check_float8_array(transarray, "float8_variance");
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();
--- 2026,2064 ----
sumX2,
numerator;
! transvalues = check_float8_array(transarray, "float8_var_pop");
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;
+
+ /* 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");
+ 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();
***************
*** 2043,2049 ****
}
Datum
! float8_stddev(PG_FUNCTION_ARGS)
{
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
float8 *transvalues;
--- 2073,2079 ----
}
Datum
! float8_stddev_pop(PG_FUNCTION_ARGS)
{
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
float8 *transvalues;
***************
*** 2052,2062 ****
sumX2,
numerator;
! transvalues = check_float8_array(transarray, "float8_stddev");
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();
--- 2082,2120 ----
sumX2,
numerator;
! transvalues = check_float8_array(transarray, "float8_stddev_pop");
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;
+
+ /* 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");
+ 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();
============================================================
*** src/backend/utils/adt/numeric.c f6199e8fdf71f23c3c24803502606941e1776d35
--- src/backend/utils/adt/numeric.c c27a8857cee9a8eccaa96cc669693c4dc1de6bff
***************
*** 2181,2190 ****
NumericGetDatum(N)));
}
! Datum
! numeric_variance(PG_FUNCTION_ARGS)
{
- ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
Datum *transdatums;
int ndatums;
Numeric N,
--- 2181,2191 ----
NumericGetDatum(N)));
}
! static Numeric
! numeric_stddev_internal(ArrayType *transarray,
! bool variance, bool sample,
! bool *is_null)
{
Datum *transdatums;
int ndatums;
Numeric N,
***************
*** 2195,2202 ****
--- 2196,2206 ----
vsumX,
vsumX2,
vNminus1;
+ NumericVar *comp;
int rscale;
+ *is_null = false;
+
/* We assume the input is array of numeric */
deconstruct_array(transarray,
NUMERICOID, -1, false, 'i',
***************
*** 2208,2223 ****
sumX2 = DatumGetNumeric(transdatums[2]);
if (NUMERIC_IS_NAN(N) || NUMERIC_IS_NAN(sumX) || NUMERIC_IS_NAN(sumX2))
! PG_RETURN_NUMERIC(make_result(&const_nan));
- /* Sample variance is undefined when N is 0 or 1, so return NULL */
init_var(&vN);
set_var_from_num(N, &vN);
! if (cmp_var(&vN, &const_one) <= 0)
{
free_var(&vN);
! PG_RETURN_NULL();
}
init_var(&vNminus1);
--- 2212,2237 ----
sumX2 = DatumGetNumeric(transdatums[2]);
if (NUMERIC_IS_NAN(N) || NUMERIC_IS_NAN(sumX) || NUMERIC_IS_NAN(sumX2))
! return make_result(&const_nan);
init_var(&vN);
set_var_from_num(N, &vN);
! /*
! * Sample stddev and variance are undefined with N <= 1;
! * population stddev is undefined when N == 0. In both cases
! * return NULL.
! */
! if (sample)
! comp = &const_one;
! else
! comp = &const_zero;
!
! if (cmp_var(&vN, comp) <= 0)
{
free_var(&vN);
! *is_null = true;
! return NULL;
}
init_var(&vNminus1);
***************
*** 2245,2250 ****
--- 2259,2266 ----
mul_var(&vN, &vNminus1, &vNminus1, 0); /* N * (N - 1) */
rscale = select_div_scale(&vsumX2, &vNminus1);
div_var(&vsumX2, &vNminus1, &vsumX, rscale, true); /* variance */
+ if (!variance)
+ sqrt_var(&vsumX, &vsumX, rscale); /* stddev */
res = make_result(&vsumX);
}
***************
*** 2254,2277 ****
free_var(&vsumX);
free_var(&vsumX2);
! PG_RETURN_NUMERIC(res);
}
Datum
! numeric_stddev(PG_FUNCTION_ARGS)
{
! ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
! Datum *transdatums;
! int ndatums;
! Numeric N,
! sumX,
! sumX2,
! res;
! NumericVar vN,
! vsumX,
! vsumX2,
! vNminus1;
! int rscale;
/* We assume the input is array of numeric */
deconstruct_array(transarray,
--- 2270,2283 ----
free_var(&vsumX);
free_var(&vsumX2);
! return res;
}
Datum
! numeric_var_samp(PG_FUNCTION_ARGS)
{
! Numeric res;
! bool is_null;
res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
true, true, &is_null);
***************
*** 2273,2287 ****
vNminus1;
int rscale;
! /* We assume the input is array of numeric */
! deconstruct_array(transarray,
! NUMERICOID, -1, false, 'i',
! &transdatums, NULL, &ndatums);
! if (ndatums != 3)
! elog(ERROR, "expected 3-element numeric array");
! N = DatumGetNumeric(transdatums[0]);
! sumX = DatumGetNumeric(transdatums[1]);
! sumX2 = DatumGetNumeric(transdatums[2]);
if (NUMERIC_IS_NAN(N) || NUMERIC_IS_NAN(sumX) || NUMERIC_IS_NAN(sumX2))
PG_RETURN_NUMERIC(make_result(&const_nan));
--- 2279,2286 ----
vNminus1;
int rscale;
! res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
! true, true, &is_null);
if (is_null)
PG_RETURN_NULL();
***************
*** 2283,2321 ****
sumX = DatumGetNumeric(transdatums[1]);
sumX2 = DatumGetNumeric(transdatums[2]);
! if (NUMERIC_IS_NAN(N) || NUMERIC_IS_NAN(sumX) || NUMERIC_IS_NAN(sumX2))
! PG_RETURN_NUMERIC(make_result(&const_nan));
! /* Sample stddev is undefined when N is 0 or 1, so return NULL */
! init_var(&vN);
! set_var_from_num(N, &vN);
! if (cmp_var(&vN, &const_one) <= 0)
! {
! free_var(&vN);
PG_RETURN_NULL();
! }
! init_var(&vNminus1);
! sub_var(&vN, &const_one, &vNminus1);
! init_var(&vsumX);
! set_var_from_num(sumX, &vsumX);
! init_var(&vsumX2);
! set_var_from_num(sumX2, &vsumX2);
! /* compute rscale for mul_var calls */
! rscale = vsumX.dscale * 2;
!
! mul_var(&vsumX, &vsumX, &vsumX, rscale); /* vsumX = sumX * sumX */
! mul_var(&vN, &vsumX2, &vsumX2, rscale); /* vsumX2 = N * sumX2 */
! sub_var(&vsumX2, &vsumX, &vsumX2); /* N * sumX2 - sumX * sumX */
!
! if (cmp_var(&vsumX2, &const_zero) <= 0)
! {
! /* Watch out for roundoff error producing a negative numerator */
! res = make_result(&const_zero);
! }
else
{
mul_var(&vN, &vNminus1, &vNminus1, 0); /* N * (N - 1) */
--- 2282,2319 ----
sumX = DatumGetNumeric(transdatums[1]);
sumX2 = DatumGetNumeric(transdatums[2]);
! if (is_null)
! PG_RETURN_NULL();
! else
! PG_RETURN_NUMERIC(res);
! }
! Datum
! numeric_stddev_samp(PG_FUNCTION_ARGS)
! {
! Numeric res;
! bool is_null;
! res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
! false, true, &is_null);
!
! if (is_null)
PG_RETURN_NULL();
! else
! PG_RETURN_NUMERIC(res);
! }
! Datum
! numeric_var_pop(PG_FUNCTION_ARGS)
! {
! Numeric res;
! bool is_null;
! res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
! true, false, &is_null);
! if (is_null)
! PG_RETURN_NULL();
else
PG_RETURN_NUMERIC(res);
}
***************
*** 2317,2340 ****
res = make_result(&const_zero);
}
else
! {
! mul_var(&vN, &vNminus1, &vNminus1, 0); /* N * (N - 1) */
! rscale = select_div_scale(&vsumX2, &vNminus1);
! div_var(&vsumX2, &vNminus1, &vsumX, rscale, true); /* variance */
! sqrt_var(&vsumX, &vsumX, rscale); /* stddev */
! res = make_result(&vsumX);
! }
! free_var(&vN);
! free_var(&vNminus1);
! free_var(&vsumX);
! free_var(&vsumX2);
! PG_RETURN_NUMERIC(res);
}
-
/*
* SUM transition functions for integer datatypes.
*
--- 2315,2338 ----
res = make_result(&const_zero);
}
else
! PG_RETURN_NUMERIC(res);
! }
! Datum
! numeric_stddev_pop(PG_FUNCTION_ARGS)
! {
! Numeric res;
! bool is_null;
! res = numeric_stddev_internal(PG_GETARG_ARRAYTYPE_P(0),
! false, false, &is_null);
! if (is_null)
! PG_RETURN_NULL();
! else
! PG_RETURN_NUMERIC(res);
}
/*
* SUM transition functions for integer datatypes.
*
============================================================
*** src/include/catalog/pg_aggregate.h 7b9a2056b482d164756ecd3723d70582f7a4a0ab
--- src/include/catalog/pg_aggregate.h a9cdb6980c64d53d49e670cd63325ac1ac2c2519
***************
*** 144,156 ****
*/
DATA(insert ( 2147 int8inc - 0 20 0 ));
! /* variance */
! DATA(insert ( 2148 int8_accum numeric_variance 0 1231 "{0,0,0}" ));
! DATA(insert ( 2149 int4_accum numeric_variance 0 1231 "{0,0,0}" ));
! DATA(insert ( 2150 int2_accum numeric_variance 0 1231 "{0,0,0}" ));
! DATA(insert ( 2151 float4_accum float8_variance 0 1022 "{0,0,0}" ));
! DATA(insert ( 2152 float8_accum float8_variance 0 1022 "{0,0,0}" ));
! DATA(insert ( 2153 numeric_accum numeric_variance 0 1231 "{0,0,0}" ));
/* stddev */
DATA(insert ( 2154 int8_accum numeric_stddev 0 1231 "{0,0,0}" ));
--- 144,156 ----
*/
DATA(insert ( 2147 int8inc - 0 20 0 ));
! /* var_pop */
! DATA(insert ( 2718 int8_accum numeric_var_pop 0 1231 "{0,0,0}" ));
! DATA(insert ( 2719 int4_accum numeric_var_pop 0 1231 "{0,0,0}" ));
! DATA(insert ( 2720 int2_accum numeric_var_pop 0 1231 "{0,0,0}" ));
! DATA(insert ( 2721 float4_accum float8_var_pop 0 1022 "{0,0,0}" ));
! DATA(insert ( 2722 float8_accum float8_var_pop 0 1022 "{0,0,0}" ));
! DATA(insert ( 2723 numeric_accum numeric_var_pop 0 1231 "{0,0,0}" ));
/* var_samp */
DATA(insert ( 2641 int8_accum numeric_var_samp 0 1231 "{0,0,0}" ));
***************
*** 152,164 ****
DATA(insert ( 2152 float8_accum float8_variance 0 1022 "{0,0,0}" ));
DATA(insert ( 2153 numeric_accum numeric_variance 0 1231 "{0,0,0}" ));
! /* stddev */
! DATA(insert ( 2154 int8_accum numeric_stddev 0 1231 "{0,0,0}" ));
! DATA(insert ( 2155 int4_accum numeric_stddev 0 1231 "{0,0,0}" ));
! DATA(insert ( 2156 int2_accum numeric_stddev 0 1231 "{0,0,0}" ));
! DATA(insert ( 2157 float4_accum float8_stddev 0 1022 "{0,0,0}" ));
! DATA(insert ( 2158 float8_accum float8_stddev 0 1022 "{0,0,0}" ));
! DATA(insert ( 2159 numeric_accum numeric_stddev 0 1231 "{0,0,0}" ));
/* boolean-and and boolean-or */
DATA(insert ( 2517 booland_statefunc - 0 16 _null_ ));
--- 152,164 ----
DATA(insert ( 2152 float8_accum float8_variance 0 1022 "{0,0,0}" ));
DATA(insert ( 2153 numeric_accum numeric_variance 0 1231 "{0,0,0}" ));
! /* var_samp */
! DATA(insert ( 2641 int8_accum numeric_var_samp 0 1231 "{0,0,0}" ));
! DATA(insert ( 2642 int4_accum numeric_var_samp 0 1231 "{0,0,0}" ));
! DATA(insert ( 2643 int2_accum numeric_var_samp 0 1231 "{0,0,0}" ));
! DATA(insert ( 2644 float4_accum float8_var_samp 0 1022 "{0,0,0}" ));
! DATA(insert ( 2645 float8_accum float8_var_samp 0 1022 "{0,0,0}" ));
! DATA(insert ( 2646 numeric_accum numeric_var_samp 0 1231 "{0,0,0}" ));
/* variance: historical Postgres syntax for var_samp */
DATA(insert ( 2148 int8_accum numeric_var_samp 0 1231 "{0,0,0}" ));
***************
*** 160,165 ****
--- 160,197 ----
DATA(insert ( 2158 float8_accum float8_stddev 0 1022 "{0,0,0}" ));
DATA(insert ( 2159 numeric_accum numeric_stddev 0 1231 "{0,0,0}" ));
+ /* variance: historical Postgres syntax for var_samp */
+ DATA(insert ( 2148 int8_accum numeric_var_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2149 int4_accum numeric_var_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2150 int2_accum numeric_var_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2151 float4_accum float8_var_samp 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2152 float8_accum float8_var_samp 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2153 numeric_accum numeric_var_samp 0 1231 "{0,0,0}" ));
+
+ /* stddev_pop */
+ DATA(insert ( 2724 int8_accum numeric_stddev_pop 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2725 int4_accum numeric_stddev_pop 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2726 int2_accum numeric_stddev_pop 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2727 float4_accum float8_stddev_pop 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2728 float8_accum float8_stddev_pop 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2729 numeric_accum numeric_stddev_pop 0 1231 "{0,0,0}" ));
+
+ /* stddev_samp */
+ DATA(insert ( 2712 int8_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2713 int4_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2714 int2_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2715 float4_accum float8_stddev_samp 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2716 float8_accum float8_stddev_samp 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2717 numeric_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+
+ /* stddev: historical Postgres syntax for stddev_samp */
+ DATA(insert ( 2154 int8_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2155 int4_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2156 int2_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+ DATA(insert ( 2157 float4_accum float8_stddev_samp 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2158 float8_accum float8_stddev_samp 0 1022 "{0,0,0}" ));
+ DATA(insert ( 2159 numeric_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
+
/* boolean-and and boolean-or */
DATA(insert ( 2517 booland_statefunc - 0 16 _null_ ));
DATA(insert ( 2518 boolor_statefunc - 0 16 _null_ ));
============================================================
*** src/include/catalog/pg_proc.h e576157d0fbb6de0a8a8f7f79b5111dc1681b6be
--- src/include/catalog/pg_proc.h 0d28a3f3af6e6c14bf12ebdc0964a1c2ee1f9b56
***************
*** 2668,2677 ****
/* Aggregate-related functions */
DATA(insert OID = 1830 ( float8_avg PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_avg - _null_ ));
DESCR("AVG aggregate final function");
! DATA(insert OID = 1831 ( float8_variance PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_variance - _null_ ));
! DESCR("VARIANCE aggregate final function");
! DATA(insert OID = 1832 ( float8_stddev PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_stddev - _null_ ));
! DESCR("STDDEV aggregate final function");
DATA(insert OID = 1833 ( numeric_accum PGNSP PGUID 12 f f t f i 2 1231 "1231 1700" _null_ _null_ _null_ numeric_accum - _null_ ));
DESCR("aggregate transition function");
DATA(insert OID = 1834 ( int2_accum PGNSP PGUID 12 f f t f i 2 1231 "1231 21" _null_ _null_ _null_ int2_accum - _null_ ));
--- 2668,2681 ----
/* Aggregate-related functions */
DATA(insert OID = 1830 ( float8_avg PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_avg - _null_ ));
DESCR("AVG aggregate final function");
! DATA(insert OID = 2512 ( float8_var_pop PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_var_pop - _null_ ));
! DESCR("VAR_POP aggregate final function");
! DATA(insert OID = 1831 ( float8_var_samp PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_var_samp - _null_ ));
! DESCR("VAR_SAMP aggregate final function");
! DATA(insert OID = 2513 ( float8_stddev_pop PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_stddev_pop - _null_ ));
! DESCR("STDDEV_POP aggregate final function");
! DATA(insert OID = 1832 ( float8_stddev_samp PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_stddev_samp - _null_ ));
! DESCR("STDDEV_SAMP aggregate final function");
DATA(insert OID = 1833 ( numeric_accum PGNSP PGUID 12 f f t f i 2 1231 "1231 1700" _null_ _null_ _null_ numeric_accum - _null_ ));
DESCR("aggregate transition function");
DATA(insert OID = 1834 ( int2_accum PGNSP PGUID 12 f f t f i 2 1231 "1231 21" _null_ _null_ _null_ int2_accum - _null_ ));
***************
*** 2682,2691 ****
DESCR("aggregate transition function");
DATA(insert OID = 1837 ( numeric_avg PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_avg - _null_ ));
DESCR("AVG aggregate final function");
! DATA(insert OID = 1838 ( numeric_variance PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_variance - _null_ ));
! DESCR("VARIANCE aggregate final function");
! DATA(insert OID = 1839 ( numeric_stddev PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_stddev - _null_ ));
! DESCR("STDDEV aggregate final function");
DATA(insert OID = 1840 ( int2_sum PGNSP PGUID 12 f f f f i 2 20 "20 21" _null_ _null_ _null_ int2_sum - _null_ ));
DESCR("SUM(int2) transition function");
DATA(insert OID = 1841 ( int4_sum PGNSP PGUID 12 f f f f i 2 20 "20 23" _null_ _null_ _null_ int4_sum - _null_ ));
--- 2686,2699 ----
DESCR("aggregate transition function");
DATA(insert OID = 1837 ( numeric_avg PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_avg - _null_ ));
DESCR("AVG aggregate final function");
! DATA(insert OID = 2514 ( numeric_var_pop PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_var_pop - _null_ ));
! DESCR("VAR_POP aggregate final function");
! DATA(insert OID = 1838 ( numeric_var_samp PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_var_samp - _null_ ));
! DESCR("VAR_SAMP aggregate final function");
! DATA(insert OID = 2596 ( numeric_stddev_pop PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_stddev_pop - _null_ ));
! DESCR("STDDEV_POP aggregate final function");
! DATA(insert OID = 1839 ( numeric_stddev_samp PGNSP PGUID 12 f f t f i 1 1700 "1231" _null_ _null_ _null_ numeric_stddev_samp - _null_ ));
! DESCR("STDDEV_SAMP aggregate final function");
DATA(insert OID = 1840 ( int2_sum PGNSP PGUID 12 f f f f i 2 20 "20 21" _null_ _null_ _null_ int2_sum - _null_ ));
DESCR("SUM(int2) transition function");
DATA(insert OID = 1841 ( int4_sum PGNSP PGUID 12 f f f f i 2 20 "20 23" _null_ _null_ _null_ int4_sum - _null_ ));
***************
*** 3115,3120 ****
--- 3123,3142 ----
DATA(insert OID = 2147 ( count PGNSP PGUID 12 t f f f i 1 20 "2276" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2718 ( var_pop PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2719 ( var_pop PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2720 ( var_pop PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2721 ( var_pop PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2722 ( var_pop PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2723 ( var_pop PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
+ DATA(insert OID = 2641 ( var_samp PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2642 ( var_samp PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2643 ( var_samp PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2644 ( var_samp PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2645 ( var_samp PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2646 ( var_samp PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
DATA(insert OID = 2148 ( variance PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ ));
DATA(insert OID = 2149 ( variance PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_ aggregate_dummy - _null_ ));
DATA(insert OID = 2150 ( variance PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_ aggregate_dummy - _null_ ));
***************
*** 3122,3127 ****
--- 3144,3163 ----
DATA(insert OID = 2152 ( variance PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
DATA(insert OID = 2153 ( variance PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2724 ( stddev_pop PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2725 ( stddev_pop PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2726 ( stddev_pop PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2727 ( stddev_pop PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2728 ( stddev_pop PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2729 ( stddev_pop PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
+ DATA(insert OID = 2712 ( stddev_samp PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2713 ( stddev_samp PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2714 ( stddev_samp PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2715 ( stddev_samp PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2716 ( stddev_samp PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+ DATA(insert OID = 2717 ( stddev_samp PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
+
DATA(insert OID = 2154 ( stddev PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ ));
DATA(insert OID = 2155 ( stddev PGNSP PGUID 12 t f f f i 1 1700 "23" _null_ _null_ _null_ aggregate_dummy - _null_ ));
DATA(insert OID = 2156 ( stddev PGNSP PGUID 12 t f f f i 1 1700 "21" _null_ _null_ _null_ aggregate_dummy - _null_ ));
============================================================
*** src/include/utils/builtins.h 2a0eacd496e8e8d8e562446804bf52edcd63e59e
--- src/include/utils/builtins.h e3df8fc6e6781c1348f1b61ec2dc552d8a2a47cd
***************
*** 340,347 ****
extern Datum float8_accum(PG_FUNCTION_ARGS);
extern Datum float4_accum(PG_FUNCTION_ARGS);
extern Datum float8_avg(PG_FUNCTION_ARGS);
! extern Datum float8_variance(PG_FUNCTION_ARGS);
! extern Datum float8_stddev(PG_FUNCTION_ARGS);
extern Datum float48pl(PG_FUNCTION_ARGS);
extern Datum float48mi(PG_FUNCTION_ARGS);
extern Datum float48mul(PG_FUNCTION_ARGS);
--- 340,349 ----
extern Datum float8_accum(PG_FUNCTION_ARGS);
extern Datum float4_accum(PG_FUNCTION_ARGS);
extern Datum float8_avg(PG_FUNCTION_ARGS);
! extern Datum float8_var_pop(PG_FUNCTION_ARGS);
! extern Datum float8_var_samp(PG_FUNCTION_ARGS);
! extern Datum float8_stddev_pop(PG_FUNCTION_ARGS);
! extern Datum float8_stddev_samp(PG_FUNCTION_ARGS);
extern Datum float48pl(PG_FUNCTION_ARGS);
extern Datum float48mi(PG_FUNCTION_ARGS);
extern Datum float48mul(PG_FUNCTION_ARGS);
***************
*** 814,821 ****
extern Datum int4_accum(PG_FUNCTION_ARGS);
extern Datum int8_accum(PG_FUNCTION_ARGS);
extern Datum numeric_avg(PG_FUNCTION_ARGS);
! extern Datum numeric_variance(PG_FUNCTION_ARGS);
! extern Datum numeric_stddev(PG_FUNCTION_ARGS);
extern Datum int2_sum(PG_FUNCTION_ARGS);
extern Datum int4_sum(PG_FUNCTION_ARGS);
extern Datum int8_sum(PG_FUNCTION_ARGS);
--- 816,825 ----
extern Datum int4_accum(PG_FUNCTION_ARGS);
extern Datum int8_accum(PG_FUNCTION_ARGS);
extern Datum numeric_avg(PG_FUNCTION_ARGS);
! extern Datum numeric_var_pop(PG_FUNCTION_ARGS);
! extern Datum numeric_var_samp(PG_FUNCTION_ARGS);
! extern Datum numeric_stddev_pop(PG_FUNCTION_ARGS);
! extern Datum numeric_stddev_samp(PG_FUNCTION_ARGS);
extern Datum int2_sum(PG_FUNCTION_ARGS);
extern Datum int4_sum(PG_FUNCTION_ARGS);
extern Datum int8_sum(PG_FUNCTION_ARGS);
============================================================
*** src/test/regress/expected/aggregates.out 49254a979d248732f0f7da0d6c38265690f7abbf
--- src/test/regress/expected/aggregates.out 05c77c93137f0f894df62f7ad1c0ca1ad986395f
***************
*** 75,80 ****
--- 75,142 ----
3.7
(1 row)
+ SELECT stddev_pop(b) FROM aggtest;
+ stddev_pop
+ -----------------
+ 131.10703231895
+ (1 row)
+
+ SELECT stddev_samp(b) FROM aggtest;
+ stddev_samp
+ ------------------
+ 151.389360803998
+ (1 row)
+
+ SELECT var_pop(b) FROM aggtest;
+ var_pop
+ ------------------
+ 17189.0539234823
+ (1 row)
+
+ SELECT var_samp(b) FROM aggtest;
+ var_samp
+ ------------------
+ 22918.7385646431
+ (1 row)
+
+ SELECT stddev_pop(b::numeric) FROM aggtest;
+ stddev_pop
+ ------------------
+ 151.389361431288
+ (1 row)
+
+ SELECT stddev_samp(b::numeric) FROM aggtest;
+ stddev_samp
+ ------------------
+ 151.389361431288
+ (1 row)
+
+ SELECT var_pop(b::numeric) FROM aggtest;
+ var_pop
+ --------------------
+ 22918.738754573025
+ (1 row)
+
+ SELECT var_samp(b::numeric) FROM aggtest;
+ var_samp
+ --------------------
+ 22918.738754573025
+ (1 row)
+
+ -- population variance is defined for a single tuple, sample variance
+ -- is not
+ SELECT var_pop(1.0), var_samp(2.0);
+ var_pop | var_samp
+ ---------+----------
+ 0 |
+ (1 row)
+
+ SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
+ stddev_pop | stddev_samp
+ ------------+-------------
+ 0 |
+ (1 row)
+
SELECT count(four) AS cnt_1000 FROM onek;
cnt_1000
----------
============================================================
*** src/test/regress/sql/aggregates.sql 88309f6da922847511fcf72f2855181da94d8029
--- src/test/regress/sql/aggregates.sql f77f6a6df401bf254fc9126ddb01eaa4b9487853
***************
*** 15,39 ****
SELECT sum(four) AS sum_1500 FROM onek;
-
SELECT sum(a) AS sum_198 FROM aggtest;
-
SELECT sum(b) AS avg_431_773 FROM aggtest;
-
SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
-
SELECT max(four) AS max_3 FROM onek;
-
SELECT max(a) AS max_100 FROM aggtest;
-
SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
! SELECT max(student.gpa) AS max_3_7 FROM student;
! SELECT count(four) AS cnt_1000 FROM onek;
SELECT count(DISTINCT four) AS cnt_4 FROM onek;
select ten, count(*), sum(four) from onek
--- 15,45 ----
SELECT sum(four) AS sum_1500 FROM onek;
SELECT sum(a) AS sum_198 FROM aggtest;
SELECT sum(b) AS avg_431_773 FROM aggtest;
SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
SELECT max(four) AS max_3 FROM onek;
SELECT max(a) AS max_100 FROM aggtest;
SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
+ SELECT max(student.gpa) AS max_3_7 FROM student;
! SELECT stddev_pop(b) FROM aggtest;
! SELECT stddev_samp(b) FROM aggtest;
! SELECT var_pop(b) FROM aggtest;
! SELECT var_samp(b) FROM aggtest;
+ SELECT stddev_pop(b::numeric) FROM aggtest;
+ SELECT stddev_samp(b::numeric) FROM aggtest;
+ SELECT var_pop(b::numeric) FROM aggtest;
+ SELECT var_samp(b::numeric) FROM aggtest;
! -- population variance is defined for a single tuple, sample variance
! -- is not
! SELECT var_pop(1.0), var_samp(2.0);
! SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
+ SELECT count(four) AS cnt_1000 FROM onek;
SELECT count(DISTINCT four) AS cnt_4 FROM onek;
select ten, count(*), sum(four) from onek
***************
*** 44,52 ****
SELECT newavg(four) AS avg_1 FROM onek;
-
SELECT newsum(four) AS sum_1500 FROM onek;
-
SELECT newcnt(four) AS cnt_1000 FROM onek;
--- 50,56 ----
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
