granicus.if.org Git - postgresql/blob - src/test/regress/sql/aggregates.sql

   1 --
   2 -- AGGREGATES
   3 --
   4
   5 SELECT avg(four) AS avg_1 FROM onek;
   6
   7 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
   8
   9 -- In 7.1, avg(float4) is computed using float8 arithmetic.
  10 -- Round the result to 3 digits to avoid platform-specific results.
  11
  12 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
  13
  14 SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
  15
  16
  17 SELECT sum(four) AS sum_1500 FROM onek;
  18 SELECT sum(a) AS sum_198 FROM aggtest;
  19 SELECT sum(b) AS avg_431_773 FROM aggtest;
  20 SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
  21
  22 SELECT max(four) AS max_3 FROM onek;
  23 SELECT max(a) AS max_100 FROM aggtest;
  24 SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
  25 SELECT max(student.gpa) AS max_3_7 FROM student;
  26
  27 SELECT stddev_pop(b) FROM aggtest;
  28 SELECT stddev_samp(b) FROM aggtest;
  29 SELECT var_pop(b) FROM aggtest;
  30 SELECT var_samp(b) FROM aggtest;
  31
  32 SELECT stddev_pop(b::numeric) FROM aggtest;
  33 SELECT stddev_samp(b::numeric) FROM aggtest;
  34 SELECT var_pop(b::numeric) FROM aggtest;
  35 SELECT var_samp(b::numeric) FROM aggtest;
  36
  37 -- population variance is defined for a single tuple, sample variance
  38 -- is not
  39 SELECT var_pop(1.0), var_samp(2.0);
  40 SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
  41
  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
  53 SELECT count(four) AS cnt_1000 FROM onek;
  54 SELECT count(DISTINCT four) AS cnt_4 FROM onek;
  55
  56 select ten, count(*), sum(four) from onek
  57 group by ten order by ten;
  58
  59 select ten, count(four), sum(DISTINCT four) from onek
  60 group by ten order by ten;
  61
  62 -- user-defined aggregates
  63 SELECT newavg(four) AS avg_1 FROM onek;
  64 SELECT newsum(four) AS sum_1500 FROM onek;
  65 SELECT newcnt(four) AS cnt_1000 FROM onek;
  66 SELECT newcnt(*) AS cnt_1000 FROM onek;
  67 SELECT oldcnt(*) AS cnt_1000 FROM onek;
  68 SELECT sum2(q1,q2) FROM int8_tbl;
  69
  70 -- test for outer-level aggregates
  71
  72 -- this should work
  73 select ten, sum(distinct four) from onek a
  74 group by ten
  75 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
  76
  77 -- this should fail because subquery has an agg of its own in WHERE
  78 select ten, sum(distinct four) from onek a
  79 group by ten
  80 having exists (select 1 from onek b
  81                where sum(distinct a.four + b.four) = b.four);
  82
  83 --
  84 -- test for bitwise integer aggregates
  85 --
  86 CREATE TEMPORARY TABLE bitwise_test(
  87   i2 INT2,
  88   i4 INT4,
  89   i8 INT8,
  90   i INTEGER,
  91   x INT2,
  92   y BIT(4)
  93 );
  94
  95 -- empty case
  96 SELECT
  97   BIT_AND(i2) AS "?",
  98   BIT_OR(i4)  AS "?"
  99 FROM bitwise_test;
 100
 101 COPY bitwise_test FROM STDIN NULL 'null';
 102 1       1       1       1       1       B0101
 103 3       3       3       null    2       B0100
 104 7       7       7       3       4       B1100
 105 \.
 106
 107 SELECT
 108   BIT_AND(i2) AS "1",
 109   BIT_AND(i4) AS "1",
 110   BIT_AND(i8) AS "1",
 111   BIT_AND(i)  AS "?",
 112   BIT_AND(x)  AS "0",
 113   BIT_AND(y)  AS "0100",
 114
 115   BIT_OR(i2)  AS "7",
 116   BIT_OR(i4)  AS "7",
 117   BIT_OR(i8)  AS "7",
 118   BIT_OR(i)   AS "?",
 119   BIT_OR(x)   AS "7",
 120   BIT_OR(y)   AS "1101"
 121 FROM bitwise_test;
 122
 123 --
 124 -- test boolean aggregates
 125 --
 126 -- first test all possible transition and final states
 127
 128 SELECT
 129   -- boolean and transitions
 130   -- null because strict
 131   booland_statefunc(NULL, NULL)  IS NULL AS "t",
 132   booland_statefunc(TRUE, NULL)  IS NULL AS "t",
 133   booland_statefunc(FALSE, NULL) IS NULL AS "t",
 134   booland_statefunc(NULL, TRUE)  IS NULL AS "t",
 135   booland_statefunc(NULL, FALSE) IS NULL AS "t",
 136   -- and actual computations
 137   booland_statefunc(TRUE, TRUE) AS "t",
 138   NOT booland_statefunc(TRUE, FALSE) AS "t",
 139   NOT booland_statefunc(FALSE, TRUE) AS "t",
 140   NOT booland_statefunc(FALSE, FALSE) AS "t";
 141
 142 SELECT
 143   -- boolean or transitions
 144   -- null because strict
 145   boolor_statefunc(NULL, NULL)  IS NULL AS "t",
 146   boolor_statefunc(TRUE, NULL)  IS NULL AS "t",
 147   boolor_statefunc(FALSE, NULL) IS NULL AS "t",
 148   boolor_statefunc(NULL, TRUE)  IS NULL AS "t",
 149   boolor_statefunc(NULL, FALSE) IS NULL AS "t",
 150   -- actual computations
 151   boolor_statefunc(TRUE, TRUE) AS "t",
 152   boolor_statefunc(TRUE, FALSE) AS "t",
 153   boolor_statefunc(FALSE, TRUE) AS "t",
 154   NOT boolor_statefunc(FALSE, FALSE) AS "t";
 155
 156 CREATE TEMPORARY TABLE bool_test(
 157   b1 BOOL,
 158   b2 BOOL,
 159   b3 BOOL,
 160   b4 BOOL);
 161
 162 -- empty case
 163 SELECT
 164   BOOL_AND(b1)   AS "n",
 165   BOOL_OR(b3)    AS "n"
 166 FROM bool_test;
 167
 168 COPY bool_test FROM STDIN NULL 'null';
 169 TRUE    null    FALSE   null
 170 FALSE   TRUE    null    null
 171 null    TRUE    FALSE   null
 172 \.
 173
 174 SELECT
 175   BOOL_AND(b1)     AS "f",
 176   BOOL_AND(b2)     AS "t",
 177   BOOL_AND(b3)     AS "f",
 178   BOOL_AND(b4)     AS "n",
 179   BOOL_AND(NOT b2) AS "f",
 180   BOOL_AND(NOT b3) AS "t"
 181 FROM bool_test;
 182
 183 SELECT
 184   EVERY(b1)     AS "f",
 185   EVERY(b2)     AS "t",
 186   EVERY(b3)     AS "f",
 187   EVERY(b4)     AS "n",
 188   EVERY(NOT b2) AS "f",
 189   EVERY(NOT b3) AS "t"
 190 FROM bool_test;
 191
 192 SELECT
 193   BOOL_OR(b1)      AS "t",
 194   BOOL_OR(b2)      AS "t",
 195   BOOL_OR(b3)      AS "f",
 196   BOOL_OR(b4)      AS "n",
 197   BOOL_OR(NOT b2)  AS "f",
 198   BOOL_OR(NOT b3)  AS "t"
 199 FROM bool_test;
 200
 201 --
 202 -- Test several cases that should be optimized into indexscans instead of
 203 -- the generic aggregate implementation.  We can't actually verify that they
 204 -- are done as indexscans, but we can check that the results are correct.
 205 --
 206
 207 -- Basic cases
 208 select max(unique1) from tenk1;
 209 select max(unique1) from tenk1 where unique1 < 42;
 210 select max(unique1) from tenk1 where unique1 > 42;
 211 select max(unique1) from tenk1 where unique1 > 42000;
 212
 213 -- multi-column index (uses tenk1_thous_tenthous)
 214 select max(tenthous) from tenk1 where thousand = 33;
 215 select min(tenthous) from tenk1 where thousand = 33;
 216
 217 -- check parameter propagation into an indexscan subquery
 218 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
 219 from int4_tbl;