Skip to content

Commit 1249cf8

Browse files
tglsfdctglsfdc
committed
SQL2003-standard statistical aggregates, by Sergey Koposov. I've added only
the float8 versions of the aggregates, which is all that the standard requires. Sergey's original patch also provided versions using numeric arithmetic, but given the size and slowness of the code, I doubt we ought to include those in core.
1 parent 0fd087a commit 1249cf8

File tree

12 files changed

+880
-103
lines changed

12 files changed

+880
-103
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 336 additions & 69 deletions
Large diffs are not rendered by default.

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

Lines changed: 369 additions & 13 deletions
Large diffs are not rendered by default.

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

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/utils/adt/int8.c,v 1.60 2006/03/05 15:58:42 momjian Exp $
10+
* $PostgreSQL: pgsql/src/backend/utils/adt/int8.c,v 1.61 2006/07/28 18:33:04 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -694,6 +694,28 @@ int8inc(PG_FUNCTION_ARGS)
694694
}
695695
}
696696

697+
/*
698+
* These functions are exactly like int8inc but are used for aggregates that
699+
* count only non-null values. Since the functions are declared strict,
700+
* the null checks happen before we ever get here, and all we need do is
701+
* increment the state value. We could actually make these pg_proc entries
702+
* point right at int8inc, but then the opr_sanity regression test would
703+
* complain about mismatched entries for a built-in function.
704+
*/
705+
706+
Datum
707+
int8inc_any(PG_FUNCTION_ARGS)
708+
{
709+
return int8inc(fcinfo);
710+
}
711+
712+
Datum
713+
int8inc_float8_float8(PG_FUNCTION_ARGS)
714+
{
715+
return int8inc(fcinfo);
716+
}
717+
718+
697719
Datum
698720
int8larger(PG_FUNCTION_ARGS)
699721
{

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.342 2006/07/27 19:52:06 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.343 2006/07/28 18:33:04 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200607271
56+
#define CATALOG_VERSION_NO 200607281
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.56 2006/07/27 19:52:06 tgl Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_aggregate.h,v 1.57 2006/07/28 18:33:04 tgl Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -192,6 +192,20 @@ DATA(insert ( 2157 float4_accum float8_stddev_samp 0 1022 "{0,0,0}" ));
192192
DATA(insert ( 2158 float8_accum float8_stddev_samp 0 1022 "{0,0,0}" ));
193193
DATA(insert ( 2159 numeric_accum numeric_stddev_samp 0 1231 "{0,0,0}" ));
194194

195+
/* SQL2003 binary regression aggregates */
196+
DATA(insert ( 2818 int8inc_float8_float8 - 0 20 "0" ));
197+
DATA(insert ( 2819 float8_regr_accum float8_regr_sxx 0 1022 "{0,0,0,0,0,0}" ));
198+
DATA(insert ( 2820 float8_regr_accum float8_regr_syy 0 1022 "{0,0,0,0,0,0}" ));
199+
DATA(insert ( 2821 float8_regr_accum float8_regr_sxy 0 1022 "{0,0,0,0,0,0}" ));
200+
DATA(insert ( 2822 float8_regr_accum float8_regr_avgx 0 1022 "{0,0,0,0,0,0}" ));
201+
DATA(insert ( 2823 float8_regr_accum float8_regr_avgy 0 1022 "{0,0,0,0,0,0}" ));
202+
DATA(insert ( 2824 float8_regr_accum float8_regr_r2 0 1022 "{0,0,0,0,0,0}" ));
203+
DATA(insert ( 2825 float8_regr_accum float8_regr_slope 0 1022 "{0,0,0,0,0,0}" ));
204+
DATA(insert ( 2826 float8_regr_accum float8_regr_intercept 0 1022 "{0,0,0,0,0,0}" ));
205+
DATA(insert ( 2827 float8_regr_accum float8_covar_pop 0 1022 "{0,0,0,0,0,0}" ));
206+
DATA(insert ( 2828 float8_regr_accum float8_covar_samp 0 1022 "{0,0,0,0,0,0}" ));
207+
DATA(insert ( 2829 float8_regr_accum float8_corr 0 1022 "{0,0,0,0,0,0}" ));
208+
195209
/* boolean-and and boolean-or */
196210
DATA(insert ( 2517 booland_statefunc - 0 16 _null_ ));
197211
DATA(insert ( 2518 boolor_statefunc - 0 16 _null_ ));

‎src/include/catalog/pg_proc.h

Lines changed: 42 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.418 2006/07/27 19:52:06 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.419 2006/07/28 18:33:04 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -1534,7 +1534,7 @@ DESCR("truncate interval to specified units");
15341534

15351535
DATA(insert OID = 1219 ( int8inc PGNSP PGUID 12 f f t f i 1 20 "20" _null_ _null_ _null_ int8inc - _null_ ));
15361536
DESCR("increment");
1537-
DATA(insert OID = 2804 ( int8inc_any PGNSP PGUID 12 f f t f i 2 20 "20 2276" _null_ _null_ _null_ int8inc - _null_ ));
1537+
DATA(insert OID = 2804 ( int8inc_any PGNSP PGUID 12 f f t f i 2 20 "20 2276" _null_ _null_ _null_ int8inc_any - _null_ ));
15381538
DESCR("increment, ignores second argument");
15391539
DATA(insert OID = 1230 ( int8abs PGNSP PGUID 12 f f t f i 1 20 "20" _null_ _null_ _null_ int8abs - _null_ ));
15401540
DESCR("absolute value");
@@ -2730,6 +2730,32 @@ DATA(insert OID = 1963 ( int4_avg_accum PGNSP PGUID 12 f f t f i 2 1016 "1016
27302730
DESCR("AVG(int4) transition function");
27312731
DATA(insert OID = 1964 ( int8_avg PGNSP PGUID 12 f f t f i 1 1700 "1016" _null_ _null_ _null_ int8_avg - _null_ ));
27322732
DESCR("AVG(int) aggregate final function");
2733+
DATA(insert OID = 2805 ( int8inc_float8_float8 PGNSP PGUID 12 f f t f i 3 20 "20 701 701" _null_ _null_ _null_ int8inc_float8_float8 - _null_ ));
2734+
DESCR("REGR_COUNT(double, double) transition function");
2735+
DATA(insert OID = 2806 ( float8_regr_accum PGNSP PGUID 12 f f t f i 3 1022 "1022 701 701" _null_ _null_ _null_ float8_regr_accum - _null_ ));
2736+
DESCR("REGR_...(double, double) transition function");
2737+
DATA(insert OID = 2807 ( float8_regr_sxx PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_sxx - _null_ ));
2738+
DESCR("REGR_SXX(double, double) aggregate final function");
2739+
DATA(insert OID = 2808 ( float8_regr_syy PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_syy - _null_ ));
2740+
DESCR("REGR_SYY(double, double) aggregate final function");
2741+
DATA(insert OID = 2809 ( float8_regr_sxy PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_sxy - _null_ ));
2742+
DESCR("REGR_SXY(double, double) aggregate final function");
2743+
DATA(insert OID = 2810 ( float8_regr_avgx PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_avgx - _null_ ));
2744+
DESCR("REGR_AVGX(double, double) aggregate final function");
2745+
DATA(insert OID = 2811 ( float8_regr_avgy PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_avgy - _null_ ));
2746+
DESCR("REGR_AVGY(double, double) aggregate final function");
2747+
DATA(insert OID = 2812 ( float8_regr_r2 PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_r2 - _null_ ));
2748+
DESCR("REGR_R2(double, double) aggregate final function");
2749+
DATA(insert OID = 2813 ( float8_regr_slope PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_slope - _null_ ));
2750+
DESCR("REGR_SLOPE(double, double) aggregate final function");
2751+
DATA(insert OID = 2814 ( float8_regr_intercept PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_regr_intercept - _null_ ));
2752+
DESCR("REGR_INTERCEPT(double, double) aggregate final function");
2753+
DATA(insert OID = 2815 ( float8_covar_pop PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_covar_pop - _null_ ));
2754+
DESCR("COVAR_POP(double, double) aggregate final function");
2755+
DATA(insert OID = 2816 ( float8_covar_samp PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_covar_samp - _null_ ));
2756+
DESCR("COVAR_SAMP(double, double) aggregate final function");
2757+
DATA(insert OID = 2817 ( float8_corr PGNSP PGUID 12 f f t f i 1 701 "1022" _null_ _null_ _null_ float8_corr - _null_ ));
2758+
DESCR("CORR(double, double) aggregate final function");
27332759

27342760
/* To ASCII conversion */
27352761
DATA(insert OID = 1845 ( to_ascii PGNSP PGUID 12 f f t f i 1 25 "25" _null_ _null_ _null_ to_ascii_default - _null_ ));
@@ -3196,6 +3222,20 @@ DATA(insert OID = 2157 ( stddev PGNSP PGUID 12 t f f f i 1 701 "700" _null_ _
31963222
DATA(insert OID = 2158 ( stddev PGNSP PGUID 12 t f f f i 1 701 "701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
31973223
DATA(insert OID = 2159 ( stddev PGNSP PGUID 12 t f f f i 1 1700 "1700" _null_ _null_ _null_ aggregate_dummy - _null_ ));
31983224

3225+
DATA(insert OID = 2818 ( regr_count PGNSP PGUID 12 t f f f i 2 20 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3226+
DATA(insert OID = 2819 ( regr_sxx PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3227+
DATA(insert OID = 2820 ( regr_syy PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3228+
DATA(insert OID = 2821 ( regr_sxy PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3229+
DATA(insert OID = 2822 ( regr_avgx PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3230+
DATA(insert OID = 2823 ( regr_avgy PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3231+
DATA(insert OID = 2824 ( regr_r2 PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3232+
DATA(insert OID = 2825 ( regr_slope PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3233+
DATA(insert OID = 2826 ( regr_intercept PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3234+
3235+
DATA(insert OID = 2827 ( covar_pop PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3236+
DATA(insert OID = 2828 ( covar_samp PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3237+
DATA(insert OID = 2829 ( corr PGNSP PGUID 12 t f f f i 2 701 "701 701" _null_ _null_ _null_ aggregate_dummy - _null_ ));
3238+
31993239
DATA(insert OID = 2160 ( text_pattern_lt PGNSP PGUID 12 f f t f i 2 16 "25 25" _null_ _null_ _null_ text_pattern_lt - _null_ ));
32003240
DATA(insert OID = 2161 ( text_pattern_le PGNSP PGUID 12 f f t f i 2 16 "25 25" _null_ _null_ _null_ text_pattern_le - _null_ ));
32013241
DATA(insert OID = 2162 ( text_pattern_eq PGNSP PGUID 12 f f t f i 2 16 "25 25" _null_ _null_ _null_ text_pattern_eq - _null_ ));

‎src/include/utils/builtins.h

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.280 2006/07/21 20:51:33 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.281 2006/07/28 18:33:04 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -348,6 +348,18 @@ extern Datum float8_var_pop(PG_FUNCTION_ARGS);
348348
extern Datum float8_var_samp(PG_FUNCTION_ARGS);
349349
extern Datum float8_stddev_pop(PG_FUNCTION_ARGS);
350350
extern Datum float8_stddev_samp(PG_FUNCTION_ARGS);
351+
extern Datum float8_regr_accum(PG_FUNCTION_ARGS);
352+
extern Datum float8_regr_sxx(PG_FUNCTION_ARGS);
353+
extern Datum float8_regr_syy(PG_FUNCTION_ARGS);
354+
extern Datum float8_regr_sxy(PG_FUNCTION_ARGS);
355+
extern Datum float8_regr_avgx(PG_FUNCTION_ARGS);
356+
extern Datum float8_regr_avgy(PG_FUNCTION_ARGS);
357+
extern Datum float8_covar_pop(PG_FUNCTION_ARGS);
358+
extern Datum float8_covar_samp(PG_FUNCTION_ARGS);
359+
extern Datum float8_corr(PG_FUNCTION_ARGS);
360+
extern Datum float8_regr_r2(PG_FUNCTION_ARGS);
361+
extern Datum float8_regr_slope(PG_FUNCTION_ARGS);
362+
extern Datum float8_regr_intercept(PG_FUNCTION_ARGS);
351363
extern Datum float48pl(PG_FUNCTION_ARGS);
352364
extern Datum float48mi(PG_FUNCTION_ARGS);
353365
extern Datum float48mul(PG_FUNCTION_ARGS);

‎src/include/utils/int8.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/int8.h,v 1.44 2006/03/05 15:59:07 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/int8.h,v 1.45 2006/07/28 18:33:04 tgl Exp $
1111
*
1212
* NOTES
1313
* These data types are supported on all 64-bit architectures, and may
@@ -74,6 +74,8 @@ extern Datum int8div(PG_FUNCTION_ARGS);
7474
extern Datum int8abs(PG_FUNCTION_ARGS);
7575
extern Datum int8mod(PG_FUNCTION_ARGS);
7676
extern Datum int8inc(PG_FUNCTION_ARGS);
77+
extern Datum int8inc_any(PG_FUNCTION_ARGS);
78+
extern Datum int8inc_float8_float8(PG_FUNCTION_ARGS);
7779
extern Datum int8larger(PG_FUNCTION_ARGS);
7880
extern Datum int8smaller(PG_FUNCTION_ARGS);
7981

‎src/test/regress/expected/aggregates.out

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -137,6 +137,61 @@ SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
137137
0 |
138138
(1 row)
139139

140+
-- SQL2003 binary aggregates
141+
SELECT regr_count(b, a) FROM aggtest;
142+
regr_count
143+
------------
144+
4
145+
(1 row)
146+
147+
SELECT regr_sxx(b, a) FROM aggtest;
148+
regr_sxx
149+
----------
150+
5099
151+
(1 row)
152+
153+
SELECT regr_syy(b, a) FROM aggtest;
154+
regr_syy
155+
------------------
156+
68756.2156939293
157+
(1 row)
158+
159+
SELECT regr_sxy(b, a) FROM aggtest;
160+
regr_sxy
161+
------------------
162+
2614.51582155004
163+
(1 row)
164+
165+
SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
166+
regr_avgx | regr_avgy
167+
-----------+------------------
168+
49.5 | 107.943152273074
169+
(1 row)
170+
171+
SELECT regr_r2(b, a) FROM aggtest;
172+
regr_r2
173+
--------------------
174+
0.0194977982031803
175+
(1 row)
176+
177+
SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
178+
regr_slope | regr_intercept
179+
-------------------+------------------
180+
0.512750700441271 | 82.5619926012309
181+
(1 row)
182+
183+
SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
184+
covar_pop | covar_samp
185+
-----------------+------------------
186+
653.62895538751 | 871.505273850014
187+
(1 row)
188+
189+
SELECT corr(b, a) FROM aggtest;
190+
corr
191+
-------------------
192+
0.139634516517873
193+
(1 row)
194+
140195
SELECT count(four) AS cnt_1000 FROM onek;
141196
cnt_1000
142197
----------

‎src/test/regress/expected/opr_sanity.out

Lines changed: 7 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -66,26 +66,25 @@ WHERE p1.oid != p2.oid AND
6666
-- of the same internal function (ie, matching prosrc fields). It's OK to
6767
-- have several entries with different pronames for the same internal function,
6868
-- but conflicts in the number of arguments and other critical items should
69-
-- be complained of.
70-
-- Ignore aggregates, since they all use "aggregate_dummy".
71-
-- As of 8.2, this finds int8inc and int8inc_any, which are OK.
69+
-- be complained of. (We don't check data types here; see next query.)
70+
-- Note: ignore aggregate functions here, since they all point to the same
71+
-- dummy built-in function.
7272
SELECT p1.oid, p1.proname, p2.oid, p2.proname
7373
FROM pg_proc AS p1, pg_proc AS p2
7474
WHERE p1.oid < p2.oid AND
7575
p1.prosrc = p2.prosrc AND
7676
p1.prolang = 12 AND p2.prolang = 12 AND
77-
p1.proisagg = false AND p2.proisagg = false AND
77+
(p1.proisagg = false OR p2.proisagg = false) AND
7878
(p1.prolang != p2.prolang OR
7979
p1.proisagg != p2.proisagg OR
8080
p1.prosecdef != p2.prosecdef OR
8181
p1.proisstrict != p2.proisstrict OR
8282
p1.proretset != p2.proretset OR
8383
p1.provolatile != p2.provolatile OR
8484
p1.pronargs != p2.pronargs);
85-
oid | proname | oid | proname
86-
------+---------+------+-------------
87-
1219 | int8inc | 2804 | int8inc_any
88-
(1 row)
85+
oid | proname | oid | proname
86+
-----+---------+-----+---------
87+
(0 rows)
8988

9089
-- Look for uses of different type OIDs in the argument/result type fields
9190
-- for different aliases of the same built-in function.

0 commit comments

Comments
 (0)