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

Commit1249cf8

Browse files
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 includethose in core.
1 parent0fd087a commit1249cf8

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+
returnint8inc(fcinfo);
710+
}
711+
712+
Datum
713+
int8inc_float8_float8(PG_FUNCTION_ARGS)
714+
{
715+
returnint8inc(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-
#defineCATALOG_VERSION_NO200607271
56+
#defineCATALOG_VERSION_NO200607281
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 ( 2157float4_accumfloat8_stddev_samp01022"{0,0,0}" ));
192192
DATA(insert (2158float8_accumfloat8_stddev_samp01022"{0,0,0}" ));
193193
DATA(insert (2159numeric_accumnumeric_stddev_samp01231"{0,0,0}" ));
194194

195+
/* SQL2003 binary regression aggregates */
196+
DATA(insert (2818int8inc_float8_float8-020"0" ));
197+
DATA(insert (2819float8_regr_accumfloat8_regr_sxx01022"{0,0,0,0,0,0}" ));
198+
DATA(insert (2820float8_regr_accumfloat8_regr_syy01022"{0,0,0,0,0,0}" ));
199+
DATA(insert (2821float8_regr_accumfloat8_regr_sxy01022"{0,0,0,0,0,0}" ));
200+
DATA(insert (2822float8_regr_accumfloat8_regr_avgx01022"{0,0,0,0,0,0}" ));
201+
DATA(insert (2823float8_regr_accumfloat8_regr_avgy01022"{0,0,0,0,0,0}" ));
202+
DATA(insert (2824float8_regr_accumfloat8_regr_r201022"{0,0,0,0,0,0}" ));
203+
DATA(insert (2825float8_regr_accumfloat8_regr_slope01022"{0,0,0,0,0,0}" ));
204+
DATA(insert (2826float8_regr_accumfloat8_regr_intercept01022"{0,0,0,0,0,0}" ));
205+
DATA(insert (2827float8_regr_accumfloat8_covar_pop01022"{0,0,0,0,0,0}" ));
206+
DATA(insert (2828float8_regr_accumfloat8_covar_samp01022"{0,0,0,0,0,0}" ));
207+
DATA(insert (2829float8_regr_accumfloat8_corr01022"{0,0,0,0,0,0}" ));
208+
195209
/* boolean-and and boolean-or */
196210
DATA(insert (2517booland_statefunc-016_null_ ));
197211
DATA(insert (2518boolor_statefunc-016_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(insertOID=1219 (int8incPGNSPPGUID12fftfi120"20"_null__null__null_int8inc-_null_ ));
15361536
DESCR("increment");
1537-
DATA(insertOID=2804 (int8inc_anyPGNSPPGUID12fftfi220"20 2276"_null__null__null_int8inc-_null_ ));
1537+
DATA(insertOID=2804 (int8inc_anyPGNSPPGUID12fftfi220"20 2276"_null__null__null_int8inc_any-_null_ ));
15381538
DESCR("increment, ignores second argument");
15391539
DATA(insertOID=1230 (int8absPGNSPPGUID12fftfi120"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(insertOID=1964 (int8_avgPGNSPPGUID12fftfi11700"1016"_null__null__null_int8_avg-_null_ ));
27322732
DESCR("AVG(int) aggregate final function");
2733+
DATA(insertOID=2805 (int8inc_float8_float8PGNSPPGUID12fftfi320"20 701 701"_null__null__null_int8inc_float8_float8-_null_ ));
2734+
DESCR("REGR_COUNT(double, double) transition function");
2735+
DATA(insertOID=2806 (float8_regr_accumPGNSPPGUID12fftfi31022"1022 701 701"_null__null__null_float8_regr_accum-_null_ ));
2736+
DESCR("REGR_...(double, double) transition function");
2737+
DATA(insertOID=2807 (float8_regr_sxxPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_sxx-_null_ ));
2738+
DESCR("REGR_SXX(double, double) aggregate final function");
2739+
DATA(insertOID=2808 (float8_regr_syyPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_syy-_null_ ));
2740+
DESCR("REGR_SYY(double, double) aggregate final function");
2741+
DATA(insertOID=2809 (float8_regr_sxyPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_sxy-_null_ ));
2742+
DESCR("REGR_SXY(double, double) aggregate final function");
2743+
DATA(insertOID=2810 (float8_regr_avgxPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_avgx-_null_ ));
2744+
DESCR("REGR_AVGX(double, double) aggregate final function");
2745+
DATA(insertOID=2811 (float8_regr_avgyPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_avgy-_null_ ));
2746+
DESCR("REGR_AVGY(double, double) aggregate final function");
2747+
DATA(insertOID=2812 (float8_regr_r2PGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_r2-_null_ ));
2748+
DESCR("REGR_R2(double, double) aggregate final function");
2749+
DATA(insertOID=2813 (float8_regr_slopePGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_slope-_null_ ));
2750+
DESCR("REGR_SLOPE(double, double) aggregate final function");
2751+
DATA(insertOID=2814 (float8_regr_interceptPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_regr_intercept-_null_ ));
2752+
DESCR("REGR_INTERCEPT(double, double) aggregate final function");
2753+
DATA(insertOID=2815 (float8_covar_popPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_covar_pop-_null_ ));
2754+
DESCR("COVAR_POP(double, double) aggregate final function");
2755+
DATA(insertOID=2816 (float8_covar_sampPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_covar_samp-_null_ ));
2756+
DESCR("COVAR_SAMP(double, double) aggregate final function");
2757+
DATA(insertOID=2817 (float8_corrPGNSPPGUID12fftfi1701"1022"_null__null__null_float8_corr-_null_ ));
2758+
DESCR("CORR(double, double) aggregate final function");
27332759

27342760
/* To ASCII conversion */
27352761
DATA(insertOID=1845 (to_asciiPGNSPPGUID12fftfi125"25"_null__null__null_to_ascii_default-_null_ ));
@@ -3196,6 +3222,20 @@ DATA(insert OID = 2157 ( stddevPGNSP PGUID 12 t f f f i 1 701 "700" _null_ _
31963222
DATA(insertOID=2158 (stddevPGNSPPGUID12tfffi1701"701"_null__null__null_aggregate_dummy-_null_ ));
31973223
DATA(insertOID=2159 (stddevPGNSPPGUID12tfffi11700"1700"_null__null__null_aggregate_dummy-_null_ ));
31983224

3225+
DATA(insertOID=2818 (regr_countPGNSPPGUID12tfffi220"701 701"_null__null__null_aggregate_dummy-_null_ ));
3226+
DATA(insertOID=2819 (regr_sxxPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3227+
DATA(insertOID=2820 (regr_syyPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3228+
DATA(insertOID=2821 (regr_sxyPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3229+
DATA(insertOID=2822 (regr_avgxPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3230+
DATA(insertOID=2823 (regr_avgyPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3231+
DATA(insertOID=2824 (regr_r2PGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3232+
DATA(insertOID=2825 (regr_slopePGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3233+
DATA(insertOID=2826 (regr_interceptPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3234+
3235+
DATA(insertOID=2827 (covar_popPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3236+
DATA(insertOID=2828 (covar_sampPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3237+
DATA(insertOID=2829 (corrPGNSPPGUID12tfffi2701"701 701"_null__null__null_aggregate_dummy-_null_ ));
3238+
31993239
DATA(insertOID=2160 (text_pattern_ltPGNSPPGUID12fftfi216"25 25"_null__null__null_text_pattern_lt-_null_ ));
32003240
DATA(insertOID=2161 (text_pattern_lePGNSPPGUID12fftfi216"25 25"_null__null__null_text_pattern_le-_null_ ));
32013241
DATA(insertOID=2162 (text_pattern_eqPGNSPPGUID12fftfi216"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
externDatumfloat8_var_samp(PG_FUNCTION_ARGS);
349349
externDatumfloat8_stddev_pop(PG_FUNCTION_ARGS);
350350
externDatumfloat8_stddev_samp(PG_FUNCTION_ARGS);
351+
externDatumfloat8_regr_accum(PG_FUNCTION_ARGS);
352+
externDatumfloat8_regr_sxx(PG_FUNCTION_ARGS);
353+
externDatumfloat8_regr_syy(PG_FUNCTION_ARGS);
354+
externDatumfloat8_regr_sxy(PG_FUNCTION_ARGS);
355+
externDatumfloat8_regr_avgx(PG_FUNCTION_ARGS);
356+
externDatumfloat8_regr_avgy(PG_FUNCTION_ARGS);
357+
externDatumfloat8_covar_pop(PG_FUNCTION_ARGS);
358+
externDatumfloat8_covar_samp(PG_FUNCTION_ARGS);
359+
externDatumfloat8_corr(PG_FUNCTION_ARGS);
360+
externDatumfloat8_regr_r2(PG_FUNCTION_ARGS);
361+
externDatumfloat8_regr_slope(PG_FUNCTION_ARGS);
362+
externDatumfloat8_regr_intercept(PG_FUNCTION_ARGS);
351363
externDatumfloat48pl(PG_FUNCTION_ARGS);
352364
externDatumfloat48mi(PG_FUNCTION_ARGS);
353365
externDatumfloat48mul(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
externDatumint8abs(PG_FUNCTION_ARGS);
7575
externDatumint8mod(PG_FUNCTION_ARGS);
7676
externDatumint8inc(PG_FUNCTION_ARGS);
77+
externDatumint8inc_any(PG_FUNCTION_ARGS);
78+
externDatumint8inc_float8_float8(PG_FUNCTION_ARGS);
7779
externDatumint8larger(PG_FUNCTION_ARGS);
7880
externDatumint8smaller(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 alluse "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 allpoint 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 = falseAND p2.proisagg = false AND
77+
(p1.proisagg = falseOR 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.

‎src/test/regress/sql/aggregates.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,17 @@ SELECT var_samp(b::numeric) FROM aggtest;
3939
SELECT var_pop(1.0), var_samp(2.0);
4040
SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
4141

42+
-- SQL2003 binary aggregates
43+
SELECT regr_count(b, a)FROM aggtest;
44+
SELECT regr_sxx(b, a)FROM aggtest;
45+
SELECT regr_syy(b, a)FROM aggtest;
46+
SELECT regr_sxy(b, a)FROM aggtest;
47+
SELECT regr_avgx(b, a), regr_avgy(b, a)FROM aggtest;
48+
SELECT regr_r2(b, a)FROM aggtest;
49+
SELECT regr_slope(b, a), regr_intercept(b, a)FROM aggtest;
50+
SELECT covar_pop(b, a), covar_samp(b, a)FROM aggtest;
51+
SELECT corr(b, a)FROM aggtest;
52+
4253
SELECTcount(four)AS cnt_1000FROM onek;
4354
SELECTcount(DISTINCT four)AS cnt_4FROM onek;
4455

‎src/test/regress/sql/opr_sanity.sql

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -68,17 +68,16 @@ WHERE p1.oid != p2.oid AND
6868
-- of the same internal function (ie, matching prosrc fields). It's OK to
6969
-- have several entries with different pronames for the same internal function,
7070
-- but conflicts in the number of arguments and other critical items should
71-
-- be complained of.
72-
-- Ignore aggregates, since they all use "aggregate_dummy".
73-
74-
-- As of 8.2, this finds int8inc and int8inc_any, which are OK.
71+
-- be complained of. (We don't check data types here; see next query.)
72+
-- Note: ignore aggregate functions here, since they all point to the same
73+
-- dummy built-in function.
7574

7675
SELECTp1.oid,p1.proname,p2.oid,p2.proname
7776
FROM pg_procAS p1, pg_procAS p2
7877
WHEREp1.oid<p2.oidAND
7978
p1.prosrc=p2.prosrcAND
8079
p1.prolang=12ANDp2.prolang=12AND
81-
p1.proisagg= falseANDp2.proisagg= falseAND
80+
(p1.proisagg= falseORp2.proisagg= false)AND
8281
(p1.prolang!=p2.prolangOR
8382
p1.proisagg!=p2.proisaggOR
8483
p1.prosecdef!=p2.prosecdefOR

0 commit comments

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