1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>"CREATE(AGGREGATE") manual page</TITLE>
</HEAD>
<BODY>
<A HREF="sql.html">SQL Reference Contents</A>
<H2><A NAME="sect0" HREF="#toc0">NAME </A></H2>
create aggregate - define a new aggregate
<H2><A NAME="sect1" HREF="#toc1">SYNOPSIS </A></H2>
<B>create aggregate
</B> agg-name [<B>as </B>] <BR>
<tt> </tt><tt> </tt><B>( </B>[<B>sfunc1 </B> <B>= </B> state-transition-function-1 <BR>
<tt> </tt><tt> </tt> , <B>basetype
</B> <B>= </B> data-type <BR>
<tt> </tt><tt> </tt> , <B>stype1 </B> <B>= </B> sfunc1-return-type] <BR>
<tt> </tt><tt> </tt> [<B>, </B> <B>sfunc2 </B> <B>= </B> state-transition-function-2
<BR>
<tt> </tt><tt> </tt> , <B>stype2 </B> <B>= </B> sfunc2-return-type] <BR>
<tt> </tt><tt> </tt> [<B>, </B> <B>finalfunc </B> <B>= </B> final-function]
<BR>
<tt> </tt><tt> </tt> [<B>, </B> <B>initcond1 </B> <B>= </B> initial-condition-1] <BR>
<tt> </tt><tt> </tt> [<B>, </B> <B>initcond2 </B> <B>= </B> initial-condition-2]<B>)
</B> <BR>
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION </A></H2>
An aggregate function can use up to three functions, two
<I>state transition</I> functions, X1 and X2: X1( internal-state1, next-data_item
) ---> next-internal-state1 <BR>
X2( internal-state2 ) ---> next-internal-state2 <BR>
and a
<B>final calculation</B> function, F: F(internal-state1, internal-state2) ---> aggregate-value
<BR>
These functions are required to have the following properties:
<DL>
<DT>The arguments
to state-transition-function-1 must be </DT>
<DD><B>(</B>stype1<B>,</B>basetype<B>)</B>, and its return
value must be stype1. </DD>
<DT>The argument and return value of state-transition-function-2
must be </DT>
<DD><B>stype2</B>. </DD>
<DT>The arguments to the final-calculation-function must be
</DT>
<DD><B>(</B>stype1<B>,</B>stype2<B>)</B>, and its return value must be a POSTGRES base type (not
necessarily the same as basetype. </DD>
<DT>The final-calculation-function should be
specified if and only if both </DT>
<DD>state-transition functions are specified.
</DD>
</DL>
<P>
Note that it is possible to specify aggregate functions that have varying
combinations of state and final functions. For example, the `count' aggregate
requires <B>sfunc2</B> (an incrementing function) but not <B>sfunc1</B> or <B>finalfunc</B>,
whereas the `sum' aggregate requires <B>sfunc1</B> (an addition function) but not
<B>sfunc2</B> or <B>finalfunc</B> and the `average' aggregate requires both of the above
state functions as well as a <B>finalfunc</B> (a division function) to produce
its answer. In any case, at least one state function must be defined,
and any <B>sfunc2</B> must have a corresponding <B>initcond2</B>. <P>
Aggregates also require
two initial conditions, one for each transition function. These are specified
and stored in the database as fields of type <I>text</I>.
<H2><A NAME="sect3" HREF="#toc3">EXAMPLE </A></H2>
This <I>avg</I> aggregate
consists of two state transition functions, a addition function and a
incrementing function. These modify the internal state of the aggregate
through a running sum and and the number of values seen so far. It accepts
a new employee salary, increments the count, and adds the new salary to
produce the next state. The state transition functions must be passed
correct initialization values. The final calculation then divides the sum
by the count to produce the final answer. -- <BR>
--Create an aggregate for int4
average <BR>
-- <BR>
create aggregate avg (sfunc1 = int4add, basetype = int4, <BR>
stype1 = int4, sfunc2 = int4inc, stype2 = int4, <BR>
finalfunc =
int4div, initcond1 = "0", initcond2 = "0") <BR>
<H2><A NAME="sect4" HREF="#toc4">SEE ALSO </A></H2>
create <A HREF="function.l.html">function(l)</A>
,
remove <A HREF="aggregate.l.html">aggregate(l)</A>
. <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">NAME</A></LI>
<LI><A NAME="toc1" HREF="#sect1">SYNOPSIS</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">EXAMPLE</A></LI>
<LI><A NAME="toc4" HREF="#sect4">SEE ALSO</A></LI>
</UL>
</BODY></HTML>
|
