Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0ebf1cc

Browse files
author
Neil Conway
committed
Implement 4 new aggregate functions from SQL2003. Specifically: var_pop(),
var_samp(), stddev_pop(), and stddev_samp(). var_samp() and stddev_samp()are just renamings of the historical Postgres aggregates variance() andstddev() -- the latter names have been kept for backward compatibility.This patch includes updates for the documentation and regression tests.The catversion has been bumped.NB: SQL2003 requires that DISTINCT not be specified for any of theseaggregates. Per discussion on -patches, I have NOT implemented thisrestriction: if the user asks for stddev(DISTINCT x), presumably theyknow what they are doing.
1 parentab812ef commit0ebf1cc

File tree

9 files changed

+412
-128
lines changed

9 files changed

+412
-128
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 81 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.312 2006/03/1019:10:47 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.313 2006/03/1020:15:25 neilc Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -7914,6 +7914,46 @@ SELECT NULLIF(value, '(none)') ...
79147914
<type>double precision</type> for floating-point arguments,
79157915
otherwise <type>numeric</type>
79167916
</entry>
7917+
<entry>historical alias for <function>stddev_samp</function></entry>
7918+
</row>
7919+
7920+
<row>
7921+
<entry>
7922+
<indexterm>
7923+
<primary>standard deviation</primary>
7924+
<secondary>population</secondary>
7925+
</indexterm>
7926+
<function>stddev_pop(<replaceable class="parameter">expression</replaceable>)</function>
7927+
</entry>
7928+
<entry>
7929+
<type>smallint</type>, <type>int</type>,
7930+
<type>bigint</type>, <type>real</type>, <type>double
7931+
precision</type>, or <type>numeric</type>
7932+
</entry>
7933+
<entry>
7934+
<type>double precision</type> for floating-point arguments,
7935+
otherwise <type>numeric</type>
7936+
</entry>
7937+
<entry>population standard deviation of the input values</entry>
7938+
</row>
7939+
7940+
<row>
7941+
<entry>
7942+
<indexterm>
7943+
<primary>standard deviation</primary>
7944+
<secondary>sample</secondary>
7945+
</indexterm>
7946+
<function>stddev_samp(<replaceable class="parameter">expression</replaceable>)</function>
7947+
</entry>
7948+
<entry>
7949+
<type>smallint</type>, <type>int</type>,
7950+
<type>bigint</type>, <type>real</type>, <type>double
7951+
precision</type>, or <type>numeric</type>
7952+
</entry>
7953+
<entry>
7954+
<type>double precision</type> for floating-point arguments,
7955+
otherwise <type>numeric</type>
7956+
</entry>
79177957
<entry>sample standard deviation of the input values</entry>
79187958
</row>
79197959

@@ -7951,9 +7991,48 @@ SELECT NULLIF(value, '(none)') ...
79517991
<type>double precision</type> for floating-point arguments,
79527992
otherwise <type>numeric</type>
79537993
</entry>
7954-
<entry>sample variance of the input values (square of the sample standard deviation)</entry>
7994+
<entry>historical alias for <function>var_samp</function></entry>
79557995
</row>
79567996

7997+
<row>
7998+
<entry>
7999+
<indexterm>
8000+
<primary>variance</primary>
8001+
<secondary>population</secondary>
8002+
</indexterm>
8003+
<function>var_pop</function>(<replaceable class="parameter">expression</replaceable>)
8004+
</entry>
8005+
<entry>
8006+
<type>smallint</type>, <type>int</type>,
8007+
<type>bigint</type>, <type>real</type>, <type>double
8008+
precision</type>, or <type>numeric</type>
8009+
</entry>
8010+
<entry>
8011+
<type>double precision</type> for floating-point arguments,
8012+
otherwise <type>numeric</type>
8013+
</entry>
8014+
<entry>population variance of the input values (square of the population standard deviation)</entry>
8015+
</row>
8016+
8017+
<row>
8018+
<entry>
8019+
<indexterm>
8020+
<primary>variance</primary>
8021+
<secondary>sample</secondary>
8022+
</indexterm>
8023+
<function>var_samp</function>(<replaceable class="parameter">expression</replaceable>)
8024+
</entry>
8025+
<entry>
8026+
<type>smallint</type>, <type>int</type>,
8027+
<type>bigint</type>, <type>real</type>, <type>double
8028+
precision</type>, or <type>numeric</type>
8029+
</entry>
8030+
<entry>
8031+
<type>double precision</type> for floating-point arguments,
8032+
otherwise <type>numeric</type>
8033+
</entry>
8034+
<entry>sample variance of the input values (square of the sample standard deviation)</entry>
8035+
</row>
79578036
</tbody>
79588037
</tgroup>
79598038
</table>

‎src/backend/utils/adt/float.c

Lines changed: 68 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.121 2006/03/0515:58:41 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.122 2006/03/10 20:15:25 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1861,11 +1861,13 @@ setseed(PG_FUNCTION_ARGS)
18611861
*FLOAT AGGREGATE OPERATORS
18621862
*=========================
18631863
*
1864-
*float8_accum- accumulate for AVG(), STDDEV(), etc
1865-
*float4_accum- same, but input data is float4
1866-
*float8_avg- produce final result for float AVG()
1867-
*float8_variance - produce final result for float VARIANCE()
1868-
*float8_stddev- produce final result for float STDDEV()
1864+
*float8_accum- accumulate for AVG(), variance aggregates, etc.
1865+
*float4_accum- same, but input data is float4
1866+
*float8_avg- produce final result for float AVG()
1867+
*float8_var_samp- produce final result for float VAR_SAMP()
1868+
*float8_var_pop- produce final result for float VAR_POP()
1869+
*float8_stddev_samp- produce final result for float STDDEV_SAMP()
1870+
*float8_stddev_pop- produce final result for float STDDEV_POP()
18691871
*
18701872
* The transition datatype for all these aggregates is a 3-element array
18711873
* of float8, holding the values N, sum(X), sum(X*X) in that order.
@@ -2015,7 +2017,7 @@ float8_avg(PG_FUNCTION_ARGS)
20152017
}
20162018

20172019
Datum
2018-
float8_variance(PG_FUNCTION_ARGS)
2020+
float8_var_pop(PG_FUNCTION_ARGS)
20192021
{
20202022
ArrayType*transarray=PG_GETARG_ARRAYTYPE_P(0);
20212023
float8*transvalues;
@@ -2024,7 +2026,35 @@ float8_variance(PG_FUNCTION_ARGS)
20242026
sumX2,
20252027
numerator;
20262028

2027-
transvalues=check_float8_array(transarray,"float8_variance");
2029+
transvalues=check_float8_array(transarray,"float8_var_pop");
2030+
N=transvalues[0];
2031+
sumX=transvalues[1];
2032+
sumX2=transvalues[2];
2033+
2034+
/* Population variance is undefined when N is 0, so return NULL */
2035+
if (N==0.0)
2036+
PG_RETURN_NULL();
2037+
2038+
numerator=N*sumX2-sumX*sumX;
2039+
2040+
/* Watch out for roundoff error producing a negative numerator */
2041+
if (numerator <=0.0)
2042+
PG_RETURN_FLOAT8(0.0);
2043+
2044+
PG_RETURN_FLOAT8(numerator / (N*N));
2045+
}
2046+
2047+
Datum
2048+
float8_var_samp(PG_FUNCTION_ARGS)
2049+
{
2050+
ArrayType*transarray=PG_GETARG_ARRAYTYPE_P(0);
2051+
float8*transvalues;
2052+
float8N,
2053+
sumX,
2054+
sumX2,
2055+
numerator;
2056+
2057+
transvalues=check_float8_array(transarray,"float8_var_samp");
20282058
N=transvalues[0];
20292059
sumX=transvalues[1];
20302060
sumX2=transvalues[2];
@@ -2043,7 +2073,35 @@ float8_variance(PG_FUNCTION_ARGS)
20432073
}
20442074

20452075
Datum
2046-
float8_stddev(PG_FUNCTION_ARGS)
2076+
float8_stddev_pop(PG_FUNCTION_ARGS)
2077+
{
2078+
ArrayType*transarray=PG_GETARG_ARRAYTYPE_P(0);
2079+
float8*transvalues;
2080+
float8N,
2081+
sumX,
2082+
sumX2,
2083+
numerator;
2084+
2085+
transvalues=check_float8_array(transarray,"float8_stddev_pop");
2086+
N=transvalues[0];
2087+
sumX=transvalues[1];
2088+
sumX2=transvalues[2];
2089+
2090+
/* Population stddev is undefined when N is 0, so return NULL */
2091+
if (N==0.0)
2092+
PG_RETURN_NULL();
2093+
2094+
numerator=N*sumX2-sumX*sumX;
2095+
2096+
/* Watch out for roundoff error producing a negative numerator */
2097+
if (numerator <=0.0)
2098+
PG_RETURN_FLOAT8(0.0);
2099+
2100+
PG_RETURN_FLOAT8(sqrt(numerator / (N*N)));
2101+
}
2102+
2103+
Datum
2104+
float8_stddev_samp(PG_FUNCTION_ARGS)
20472105
{
20482106
ArrayType*transarray=PG_GETARG_ARRAYTYPE_P(0);
20492107
float8*transvalues;
@@ -2052,7 +2110,7 @@ float8_stddev(PG_FUNCTION_ARGS)
20522110
sumX2,
20532111
numerator;
20542112

2055-
transvalues=check_float8_array(transarray,"float8_stddev");
2113+
transvalues=check_float8_array(transarray,"float8_stddev_samp");
20562114
N=transvalues[0];
20572115
sumX=transvalues[1];
20582116
sumX2=transvalues[2];

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp