aggregates.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

--
-- AGGREGATES
--
SELECT avg(four) AS avg_1 FROM onek;
       avg_1        
--------------------
 1.5000000000000000
(1 row)

SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
       avg_32        
---------------------
 32.6666666666666667
(1 row)

-- In 7.1, avg(float4) is computed using float8 arithmetic.
-- Round the result to 3 digits to avoid platform-specific results.
SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
 avg_107_943 
-------------
     107.943
(1 row)

SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
 avg_3_4 
---------
     3.4
(1 row)

SELECT sum(four) AS sum_1500 FROM onek;
 sum_1500 
----------
     1500
(1 row)

SELECT sum(a) AS sum_198 FROM aggtest;
 sum_198 
---------
     198
(1 row)

SELECT sum(b) AS avg_431_773 FROM aggtest;
 avg_431_773 
-------------
     431.773
(1 row)

SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
 avg_6_8 
---------
     6.8
(1 row)

SELECT max(four) AS max_3 FROM onek;
 max_3 
-------
     3
(1 row)

SELECT max(a) AS max_100 FROM aggtest;
 max_100 
---------
     100
(1 row)

SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
 max_324_78 
------------
     324.78
(1 row)

SELECT max(student.gpa) AS max_3_7 FROM student;
 max_3_7 
---------
     3.7
(1 row)

SELECT count(four) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
(1 row)

SELECT count(DISTINCT four) AS cnt_4 FROM onek;
 cnt_4 
-------
     4
(1 row)

select ten, count(*), sum(four) from onek
group by ten order by ten;
 ten | count | sum 
-----+-------+-----
   0 |   100 | 100
   1 |   100 | 200
   2 |   100 | 100
   3 |   100 | 200
   4 |   100 | 100
   5 |   100 | 200
   6 |   100 | 100
   7 |   100 | 200
   8 |   100 | 100
   9 |   100 | 200
(10 rows)

select ten, count(four), sum(DISTINCT four) from onek
group by ten order by ten;
 ten | count | sum 
-----+-------+-----
   0 |   100 |   2
   1 |   100 |   4
   2 |   100 |   2
   3 |   100 |   4
   4 |   100 |   2
   5 |   100 |   4
   6 |   100 |   2
   7 |   100 |   4
   8 |   100 |   2
   9 |   100 |   4
(10 rows)

SELECT newavg(four) AS avg_1 FROM onek;
       avg_1        
--------------------
 1.5000000000000000
(1 row)

SELECT newsum(four) AS sum_1500 FROM onek;
 sum_1500 
----------
     1500
(1 row)

SELECT newcnt(four) AS cnt_1000 FROM onek;
 cnt_1000 
----------
     1000
(1 row)

-- test for outer-level aggregates
-- this should work
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
 ten | sum 
-----+-----
   0 |   2
   2 |   2
   4 |   2
   6 |   2
   8 |   2
(5 rows)

-- this should fail because subquery has an agg of its own in WHERE
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b
               where sum(distinct a.four + b.four) = b.four);
ERROR:  aggregates not allowed in WHERE clause
--
-- test for bitwise integer aggregates
--
CREATE TEMPORARY TABLE bitwise_test(
  i2 INT2,
  i4 INT4,
  i8 INT8,
  i INTEGER,
  x INT2,
  y BIT(4)
);
-- empty case
SELECT 
  BIT_AND(i2) AS "?",
  BIT_OR(i4)  AS "?"
FROM bitwise_test;
 ? | ? 
---+---
   |  
(1 row)

COPY bitwise_test FROM STDIN NULL 'null';
SELECT
  BIT_AND(i2) AS "1",
  BIT_AND(i4) AS "1",
  BIT_AND(i8) AS "1",
  BIT_AND(i)  AS "?",
  BIT_AND(x)  AS "0",
  BIT_AND(y)  AS "0100",
  BIT_OR(i2)  AS "7",
  BIT_OR(i4)  AS "7",
  BIT_OR(i8)  AS "7",
  BIT_OR(i)   AS "?",
  BIT_OR(x)   AS "7",
  BIT_OR(y)   AS "1101"
FROM bitwise_test;
 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101 
---+---+---+---+---+------+---+---+---+---+---+------
 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
(1 row)

--
-- test boolean aggregates
--
-- first test all possible transition and final states
SELECT
  -- boolean and transitions
  -- null because strict
  booland_statefunc(NULL, NULL)  IS NULL AS "t",
  booland_statefunc(TRUE, NULL)  IS NULL AS "t",
  booland_statefunc(FALSE, NULL) IS NULL AS "t",
  booland_statefunc(NULL, TRUE)  IS NULL AS "t",
  booland_statefunc(NULL, FALSE) IS NULL AS "t",
  -- and actual computations
  booland_statefunc(TRUE, TRUE) AS "t",
  NOT booland_statefunc(TRUE, FALSE) AS "t",
  NOT booland_statefunc(FALSE, TRUE) AS "t",
  NOT booland_statefunc(FALSE, FALSE) AS "t";
 t | t | t | t | t | t | t | t | t 
---+---+---+---+---+---+---+---+---
 t | t | t | t | t | t | t | t | t
(1 row)

SELECT
  -- boolean or transitions
  -- null because strict
  boolor_statefunc(NULL, NULL)  IS NULL AS "t",
  boolor_statefunc(TRUE, NULL)  IS NULL AS "t",
  boolor_statefunc(FALSE, NULL) IS NULL AS "t",
  boolor_statefunc(NULL, TRUE)  IS NULL AS "t",
  boolor_statefunc(NULL, FALSE) IS NULL AS "t",
  -- actual computations
  boolor_statefunc(TRUE, TRUE) AS "t",
  boolor_statefunc(TRUE, FALSE) AS "t",
  boolor_statefunc(FALSE, TRUE) AS "t",
  NOT boolor_statefunc(FALSE, FALSE) AS "t";
 t | t | t | t | t | t | t | t | t 
---+---+---+---+---+---+---+---+---
 t | t | t | t | t | t | t | t | t
(1 row)

CREATE TEMPORARY TABLE bool_test(  
  b1 BOOL,
  b2 BOOL,
  b3 BOOL,
  b4 BOOL);
-- empty case
SELECT
  BOOL_AND(b1)   AS "n",
  BOOL_OR(b3)    AS "n"
FROM bool_test;
 n | n 
---+---
   | 
(1 row)

COPY bool_test FROM STDIN NULL 'null';
SELECT
  BOOL_AND(b1)     AS "f",
  BOOL_AND(b2)     AS "t",
  BOOL_AND(b3)     AS "f",
  BOOL_AND(b4)     AS "n",
  BOOL_AND(NOT b2) AS "f",
  BOOL_AND(NOT b3) AS "t"
FROM bool_test;
 f | t | f | n | f | t 
---+---+---+---+---+---
 f | t | f |   | f | t
(1 row)

SELECT
  EVERY(b1)     AS "f",
  EVERY(b2)     AS "t",
  EVERY(b3)     AS "f",
  EVERY(b4)     AS "n",
  EVERY(NOT b2) AS "f",
  EVERY(NOT b3) AS "t"
FROM bool_test;
 f | t | f | n | f | t 
---+---+---+---+---+---
 f | t | f |   | f | t
(1 row)

SELECT
  BOOL_OR(b1)      AS "t",
  BOOL_OR(b2)      AS "t",
  BOOL_OR(b3)      AS "f",
  BOOL_OR(b4)      AS "n",
  BOOL_OR(NOT b2)  AS "f",
  BOOL_OR(NOT b3)  AS "t"
FROM bool_test;
 t | t | f | n | f | t 
---+---+---+---+---+---
 t | t | f |   | f | t
(1 row)

--
-- Test several cases that should be optimized into indexscans instead of
-- the generic aggregate implementation.  We can't actually verify that they
-- are done as indexscans, but we can check that the results are correct.
--
-- Basic cases
select max(unique1) from tenk1;
 max  
------
 9999
(1 row)

select max(unique1) from tenk1 where unique1 < 42;
 max 
-----
  41
(1 row)

select max(unique1) from tenk1 where unique1 > 42;
 max  
------
 9999
(1 row)

select max(unique1) from tenk1 where unique1 > 42000;
 max 
-----
    
(1 row)

-- multi-column index (uses tenk1_thous_tenthous)
select max(tenthous) from tenk1 where thousand = 33;
 max  
------
 9033
(1 row)

select min(tenthous) from tenk1 where thousand = 33;
 min 
-----
  33
(1 row)

-- check parameter propagation into an indexscan subquery
select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
from int4_tbl;
     f1      | gt 
-------------+----
           0 |  1
      123456 |   
     -123456 |  0
  2147483647 |   
 -2147483647 |  0
(5 rows)