diff options
Diffstat (limited to 'doc/manual/xaggr.html')
| -rw-r--r-- | doc/manual/xaggr.html | 109 |
1 files changed, 109 insertions, 0 deletions
diff --git a/doc/manual/xaggr.html b/doc/manual/xaggr.html new file mode 100644 index 00000000000..28707221648 --- /dev/null +++ b/doc/manual/xaggr.html @@ -0,0 +1,109 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - EXTENDING SQL: AGGREGATES</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xoper.html">[ Previous ]</A> +<A HREF="xindex.html">[ Next ]</A> +</font> +<HR> +<H1>10. EXTENDING SQL: AGGREGATES</H1> +<HR> + Aggregates in POSTGRES are expressed in terms of state + transition functions. That is, an aggregate can be + defined in terms of state that is modified whenever an + instance is processed. Some state functions look at a + particular value in the instance when computing the new + state (<B>sfunc1</B> in the create aggregate syntax) while + others only keep track of their own internal state + (<B>sfunc2</B>). + If we define an aggregate that uses only <B>sfunc1</B>, we + define an aggregate that computes a running function of + the attribute values from each instance. "Sum" is an + example of this kind of aggregate. "Sum" starts at + zero and always adds the current instance's value to + its running total. We will use the <B>int4pl</B> that is + built into POSTGRES to perform this addition. + +<pre> CREATE AGGREGATE complex_sum ( + sfunc1 = complex_add, + basetype = complex, + stype1 = complex, + initcond1 = '(0,0)' + ); + + + SELECT complex_sum(a) FROM test_complex; + + + +------------+ + |complex_sum | + +------------+ + |(34,53.9) | + +------------+ +</pre> + + If we define only <B>sfunc2</B>, we are specifying an aggregate + that computes a running function that is independent of + the attribute values from each instance. + "Count" is the most common example of this kind of + aggregate. "Count" starts at zero and adds one to its + running total for each instance, ignoring the instance + value. Here, we use the built-in <B>int4inc</B> routine to do + the work for us. This routine increments (adds one to) + its argument. + +<pre> CREATE AGGREGATE my_count (sfunc2 = int4inc, -- add one + basetype = int4, stype2 = int4, + initcond2 = '0') + + SELECT my_count(*) as emp_count from EMP; + + + +----------+ + |emp_count | + +----------+ + |5 | + +----------+ +</pre> + + "Average" is an example of an aggregate that requires + both a function to compute the running sum and a function + to compute the running count. When all of the + instances have been processed, the final answer for the + aggregate is the running sum divided by the running + count. We use the <B>int4pl</B> and <B>int4inc</B> routines we used + before as well as the POSTGRES integer division + routine, <B>int4div</B>, to compute the division of the sum by + the count. + +<pre> CREATE AGGREGATE my_average (sfunc1 = int4pl, -- sum + basetype = int4, + stype1 = int4, + sfunc2 = int4inc, -- count + stype2 = int4, + finalfunc = int4div, -- division + initcond1 = '0', + initcond2 = '0') + + SELECT my_average(salary) as emp_average FROM EMP; + + + +------------+ + |emp_average | + +------------+ + |1640 | + +------------+ +</pre> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xoper.html">[ Previous ]</A> +<A HREF="xindex.html">[ Next ]</A> +</font> +</BODY> +</HTML> |