Using multiple aggregate functions in an (ANSI) SQL statement
- by morpheous
I have aggregate functions foo(), foobar(), fredstats(), barneystats()
I want to create a domain specific query language (DSQL) above my DB, to facilitate using a domain language to query the DB.
The 'language' comprises of algebraic expressions (or more specifically SQL like criteria) which I use to generate (ANSI) SQL statements which are sent to the db engine.
The following lines are examples of what the language statements will look like, and hopefully, it will help further clarify the concept:
**Example 1**
DQL statement:
foobar('yellow') between 1 and 3 and fredstats('weight') > 42
Translation:
fetch all rows in an underlying table where computed values for aggregate function foobar() is between 1 and 3 AND computed value for AGG FUNC fredstats() is greater than 42
**Example 2**
DQL statement:
fredstats('weight') < barneystats('weight') AND foo('fighter') in (9,10,11) AND foobar('green') <> 42
Translation:
Fetch all rows where the specified criteria matches
**Example 3**
DQL statement:
foobar('green') / foobar('red') <> 42
Translation:
Fetch all rows where the specified criteria matches
**Example 4**
DQL statement:
foobar('green') - foobar('red') >= 42
Translation:
Fetch all rows where the specified criteria matches
Given the following information:
The table upon which the queries above are being executed is called 'tbl'
table 'tbl' has the following structure (id int, name varchar(32), weight float)
The result set returns only the tbl.id, tbl.name and the names of the aggregate functions as columns in the result set - so for example the foobar() AGG FUNC column will be called foobar in the result set. So for example, the first DQL query will return a result set with the following columns: id, name, foobar, fredstats
Given the above, my questions then are:
What would be the underlying SQL required for Example1 ?
What would be the underlying SQL required for Example3 ?
Given an algebraic equation comprising of AGGREGATE functions, Is there a way of generalizing the algorithm needed to generate the required ANSI SQL statement(s)?
I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible.
