<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="besteffortunion.html" title="Best Effort Union" />
<link rel="next" href="sqloptimizer.html" title="Virtuoso SQL Optimization" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>8. SQL Reference</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="aggregates" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="besteffortunion.html" title="Best Effort Union">Prev</a> | <a class="link" href="sqloptimizer.html" title="Virtuoso SQL Optimization">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div class="selected">
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
<div>
<a href="#aggcreateaggrstmt1" title="Create Aggregate Statement">Create Aggregate Statement</a>
<a href="#aggdropaggrstmt" title="Drop Aggregate Statement">Drop Aggregate Statement</a>
<a href="#createaggrsam" title="Examples of User-Defined Aggregates">Examples of User-Defined Aggregates</a>
</div>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="aggregates" />
<h2>8.27. Standard and User-Defined Aggregate Functions</h2>
<p>In addition to whole set of standard SQL aggregate functions,
Virtuoso provides a method to create application-specific aggregate
functions to create complex data structures inside SQL queries.</p>
<p>
The most evident way to calculate some value that depend on number of rows of a table is to
write a stored procedure that opens a cursor, then fetches row after row in a loop and
repeatedly modifies some intermediate values ("accumulators") inside the loop.
When the fetch operation signals that there are no more data, a final result is calculated
from values of "accumulators"</p>
<p>
E.g. to find an average of all values in a table's column COL, one may open a cursor to fetch
values from the COL, then set two "accumulators" TOTAL and CNT to zero, then
fetch row after row adding 1 to CNT and adding current value of COL to TOTAL.
At the end of loop, an error should be signalled if CNT is zero, otherwise the result is
TOTAL divided by CNT.</p>
<p>
Obviously, a competent programmer will use built-in AVG() aggregate function inside a
single SELECT statement: the code is much more readable and
the use of aggregate reduces the overhead in filling in result-set and fetching from it.</p>
<p>
More important advantage of aggregate functions is the ability to process many groups of
records in parallel, performing one table scan instead of many. SQL optimizer may use
sophisticated heuristics to find the fastest way of doing a complex query with aggregates
but it cannot optimize the code of a stored procedure.</p>
<a name="aggcreateaggrstmt1" />
<h3>8.27.1. Create Aggregate Statement</h3>
<div>
<pre class="programlisting">
CREATE AGGREGATE aggregate_name (parameter, parameter ...) [ RETURNS data_type ]
FROM
init_procedure,
acc_procedure,
final_function
[, merge_procedure ]
parameter : parameter_type name data_type
parameter_type : IN | INOUT
</pre>
</div>
<p>
The create aggregate statement forces the SQL compiler to treat any call of
the declared aggregate_name as special routine similar to one used for built-in
aggregate functions, so the call produces a single value for resultset instead of
a separate value for every row of the resultset.</p>
<p>
Every occurrence of the aggregate_name in a select statement
creates an invisible "accumulator" column in resultset.
The value of "accumulator" is calculated by init_procedure,
then every row of data causes a call of acc_procedure that may modify
the "accumulator" then the call of final_function should
get data from "accumulator" and return the total result.</p>
<p>
The init_procedure must have one inout parameter and return nothing.
It should set the parameter to the required initial value of the "accumulator".
(The SQL query compiler will use the declared data type of the parameter as
the data type of the "accumulator" so no casting problem may occur)
equal to
</p>
<p>
The acc_procedure must have at least one inout parameter, one or more parameters of any sort and must return nothing.
It should get the previously calculated value of the "accumulator"
from its first parameter, calculate a new value and set the result back to the first parameter.
</p>
<p>
The final_function must have one in or inout parameter and return a value.
It should get the value of the "accumulator" from its parameter
and return the calculated result.
</p>
<p>
The optional merge_procedure must have two inout parameters that
accept values of two "accumulators".
If defined, it may be used by SQL optimizer to split the table scan into
two or more scans through parts of the table. When every 'children' scan
iterates some part of the table, a separate "accumulators"
is used for every part. merge_procedure gets two accumulators and
adds the content of the second accumulator to the content of the first.
If merge_procedure is not defined, no such tricky optimization is used.
This optimization is never tried if the database resides on less than four disks,
if the number of CPUs is less than four and if the query contains
any call of user-defined aggregate with no merge_procedure defined;
thus you will probably never need to write such a function.
</p>
<p>
The declaration of aggregate is persistent.
If create aggregate statement is successful the text is stored into the SYS_PROCEDURES table.
This table is read at startup so user-defined aggregates are thus always available for use
and need be defined only once.
</p>
<br />
<a name="aggdropaggrstmt" />
<h3>8.27.2. Drop Aggregate Statement</h3>
<div>
<pre class="programlisting">
DROP AGGREGATE aggregate_name
</pre>
</div>
<p>
The drop aggregate statement removes the declaration of aggregate_name from SYS_PROCEDURES table
but does not drop or change any declarations of functions previously listed in create aggregate
statement.
</p>
<p>
The syntax of the 'call' operator is the same for 'plain' and 'aggregate' functions.
If an aggregate name is used in the text of stored procedure before the aggregate is
actually created then it is compiled as plain function call and causes run-time errors.
In order to prevent such confusion, keyword AGGREGATE may be placed right before
the name of aggregate function.
</p>
<br />
<a name="createaggrsam" />
<h3>8.27.3. Examples of User-Defined Aggregates</h3>
<a name="createaggruri" />
<h4>8.27.3.1. Aggregate for Composing URIs</h4>
<p>
Consider a table USER_ATTRIBUTES that contains configuration data for users of some remote web-service.
Every user have a number of parameters that should be passed to that service as a part of
URI when HTTP PUT is used.
Every row of USER_ATTRIBUTES contain a name and a value of some parameter for some type of users.
</p>
<div>
<pre class="programlisting">
create table USER_ATTRIBUTES (
ID integer not null primary key,
ATTR_NAME varchar,
USER_TYPE char(4),
ATTR_VALUE varchar );
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (1, 'key1', 't1', 'val1');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (2, 'key3', 't1', 'val3');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (3, 'key4', 't1', 'val4');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (4, 'key5', 't2', 'val5');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (5, 'key6', 't2', 'val6');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (6, 'key2', 't1', 'val2');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (7, 'key8', 't3', 'val8');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (8, 'key7', 't2', 'val7');
insert into USER_ATTRIBUTES (ID, ATTR_NAME, USER_TYPE, ATTR_VALUE) values (9, 'key9', 't4', 'val9');
</pre>
</div>
<p>
The application contains number of places where the resulting URI for a user should be
calculated. The URI is either an empty string (if no attributes are set) or
a string that starts from '?' char and consists of tokens like "attribute_name=attribute_value"
delimited by "&" character.
</p>
<div>
<pre class="programlisting">
create function ATTR_URI_INIT (inout _env any)
{
_env := string_output(); -- The "accumulator" is a string session. Initially it is empty.
}
create function ATTR_URI_ACC (
inout _env any, -- The first parameter is used for passing "accumulator" value.
in _attrname varchar, -- Second parameter gets the value passed by first parameter of aggregate call.
in _attrvalue varchar ) -- Third parameter gets the value passed by second parameter of aggregate call.
{
if (_attrname is null) -- Attributes with NULL names should not affect the result.
return;
-- The non-NULL name of attribute is appended to "accumulator"
http ('&', _env);
http_url (_attrname, null, _env);
http ('=', _env);
if (_attrvalue is null) -- NULL values of attributes are not printed.
return;
-- The non-null value of attribute is appended to "accumulator"
http_url (_attrvalue, null, _env);
}
create function ATTR_URI_FINAL (inout _env any) returns varchar
{
declare _res varchar;
-- For empty groups in the resultset, the initialization may be bypassed
-- so the value of "accumulator" remains NULL.
-- Finalization function should always handle such case
-- by signalling an error (like built-in AVG function) or
-- by returning some default value (like this function)
if (_env is null)
return '';
_res := string_output_string (_env);
if (_res = '')
return '';
-- In nonempty lists of URI attributes, the first '&' should be replace with '?'
aset (_res, 0, 63);
return _res;
}
create aggregate ATTR_URI (in _key varchar, in _val varchar) returns varchar
from ATTR_URI_INIT, ATTR_URI_ACC, ATTR_URI_FINAL;
</pre>
</div>
<p>Now we are ready to run some tests. Let's try the typical case:</p>
<div>
<pre class="programlisting">
select ATTR_URI (ATTR_NAME, ATTR_VALUE) as URI from USER_ATTRIBUTES where USER_TYPE='t1';
URI
VARCHAR
_______________________________________________________________________________
?key1=val1&key2=val2&key3=val3&key4=val4
1 Rows. -- 0 msec.
</pre>
</div>
<p>Now let's try the case of empty set of rows to aggregate:</p>
<div>
<pre class="programlisting">
select ATTR_URI (ATTR_NAME, ATTR_VALUE) as URI from USER_ATTRIBUTES where USER_TYPE='nosuchtype';
URI
VARCHAR
_______________________________________________________________________________
1 Rows. -- 0 msec.
OK, we've got an empty string. Now let's try the final case:
select ATTR_URI (ATTR_NAME, NULL) as URI from USER_ATTRIBUTES;
URI
VARCHAR
_______________________________________________________________________________
?key1=&key2=&key3=&key4=&key5=&key6=&key7=&key8=&key9=
1 Rows. -- 0 msec.
</pre>
</div>
<p>
The created aggregate is very fast but unusable if intermediate results should
be saved in a temporary table. It may happen if the SQL statement contains
clauses such as DISTINCT, ORDER BY, GROUP BY, especially if the grouping columns
are not the first columns of the primary key, or if SQL optimizer thinks that
it is the fastest way to calculate a join.
The following version produces the same results and may work with grouping,
but may be a bit slower because concatenation of long strings is slower than
writing to a string session:
</p>
<div>
<pre class="programlisting">
create procedure ATTR_URI2_INIT (inout _env varchar)
{
_env := ''; -- The "accumulator" is a string. Initially it is empty.
}
create procedure ATTR_URI2_ACC (
inout _env varchar, in _attrname varchar, in _attrvalue varchar ){
if (_attrname is null)-- Attributes with NULL names should not affect the result.
return;
if (_attrvalue is null)-- NULL values of attributes are not printed.
_env := concat (_env, sprintf('&amp;%U', _attrname));
else -- The non-null value of attribute is appended to "accumulator"
_env := concat (_env, sprintf('&amp;%U=%U', _attrname, _attrvalue));
}
create function ATTR_URI2_FINAL (inout _env any) returns varchar
{
-- For empty groups in the result set, the initialization may be bypassed
-- so the value of "accumulator" remains NULL.
-- Finalization function should always handle such case
-- by signaling an error (like built-in AVG function) or
-- by returning some default value (like this function)
if (_env is null)
return '';
if (_env = '')
return '';
-- In nonempty lists of URI attributes, the first '&amp;' should be replace with '?'
aset (_env, 0, 63);
return _env;
}
create aggregate ATTR_URI2 (in _key varchar, in _val varchar) returns varchar
from ATTR_URI2_INIT, ATTR_URI2_ACC, ATTR_URI2_FINAL;
select ATTR_URI2 (ATTR_NAME, ATTR_VALUE) as URI from USER_ATTRIBUTES where USER_TYPE='t1'
URI
VARCHAR
_______________________________________________________________________________
?key1=val1&key3=val3&key4=val4&key2=val2
1 Rows. -- 0 msec.
select USER_TYPE, ATTR_URI2 (ATTR_NAME, ATTR_VALUE) as URI from USER_ATTRIBUTES group by USER_TYPE
USER_TYPE URI
VARCHAR VARCHAR
_______________________________________________________________________________
t2 ?key5=val5&key6=val6&key7=val7
t1 ?key1=val1&key3=val3&key4=val4&key2=val2
t4 ?key9=val9
t3 ?key8=val8
4 Rows. -- 0 msec.
</pre>
</div>
<br />
<a name="createaggrxml" />
<h4>8.27.3.2. Aggregate for Composing XMLs</h4>
<p>
Virtuoso offers variety of ways to place relational data into XML documents,
and two best of them are select statements with FOR XML clause and XML views.
But in some rare cases you may prefer to compose XML as an output of
plain select statement, e.g. to get a few percent faster code.
</p>
<p>
Among other things, the CREATE XML VIEW statement creates
set of Virtuoso/PL functions that return various XML trees.
Those functions use special 'node building' vectors to store intermediate results.
NODEBLD (which stands for 'Node Building vector') stores a partially built XML tree.
A xte_nodebld() function returns a new empty NODEBLD that may be converted into an
element of XML tree in the future.
xte_nodebld_acc() appends new children to an existing NODEBLD.
xte_node_from_nodebld() converts NODEBLD into ready-to-use XML tree.
xte_nodebld_final() may work either as xte_node_from_nodebld() or
as a finalization function of user defined aggregate.
</p>
<div>
<pre class="programlisting">
create aggregate XTE_NODEBLD (in _child any) returns any
from xte_nodebld_init, xte_nodebld_acc, xte_nodebld_final;
</pre>
</div>
<p>
The use of this aggregate is not as easy as creating XML views, but
it avoids creating persistent objects:
</p>
<div>
<pre class="programlisting">
select
xml_tree_doc (
xte_node_from_nodebld (
xte_head ('UserAttributes'),
XTE_NODEBLD (
xte_node (xte_head ('Row', 'Id', ID, 'UserType', USER_TYPE),
xte_node (xte_head ('Attr', 'Name', ATTR_NAME),
ATTR_VALUE ) )
) ) )
from USER_ATTRIBUTES;
callret
VARCHAR
_______________________________________________________________________________
<UserAttributes>
<Row Id="1" UserType="t1"><Attr Name="key1">val1</Attr></Row>
<Row Id="2" UserType="t1"><Attr Name="key2">val2</Attr></Row>
<Row Id="3" UserType="t1"><Attr Name="key3">val3</Attr></Row>
<Row Id="4" UserType="t1"><Attr Name="key4">val4</Attr></Row>
<Row Id="5" UserType="t2"><Attr Name="key5">val5</Attr></Row>
<Row Id="6" UserType="t2"><Attr Name="key6">val6</Attr></Row>
<Row Id="7" UserType="t2"><Attr Name="key7">val7</Attr></Row>
<Row Id="8" UserType="t3"><Attr Name="key8">val8</Attr></Row>
<Row Id="9" UserType="t4"><Attr Name="key9">val9</Attr></Row>
</UserAttributes>
1 Rows. -- 0 msec.
</pre>
</div>
<p>The function xml_tree_doc() is used because there is no standard way to
print an XML tree without making a complete XML entity whose underlying XML
document consists of the given XML tree.</p>
<div>
<pre class="programlisting">
select
xml_tree_doc (
xte_node_from_nodebld (
xte_head ('AttrStatistics'),
XTE_NODEBLD (
xte_node (xte_head ('Row', 'UserType', groups.type, 'AttrCount', groups.cnt))
) ) )
from
(select USER_TYPE as type, COUNT (*) as cnt from USER_ATTRIBUTES group by USER_TYPE) groups;
callret
VARCHAR
_______________________________________________________________________________
<AttrStatistics>
<Row UserType="t2" AttrCount="3" />
<Row UserType="t1" AttrCount="4" />
<Row UserType="t4" AttrCount="1" />
<Row UserType="t3" AttrCount="1" />
</AttrStatistics>
1 Rows. -- 0 msec.
</pre>
</div>
<br />
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="besteffortunion.html" title="Best Effort Union">Previous</a>
<br />Best Effort Union</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="sqloptimizer.html" title="Virtuoso SQL Optimization">Next</a>
<br />Virtuoso SQL Optimization</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
54
