Using multiple aggregate functions in an algebraic expression in (ANSI) SQL statement
- by morpheous
I have the following aggregate functions (AGG FUNCs):
foo(), foobar(), fredstats(), barneystats().
I want to know if I can use multiple AGG FUNCs in an algebraic expression. This may seem a strange/simplistic question for seasoned SQL developers - however, the but the reason I ask is that so far, all AGG FUNCs examples I have seen are of the simplistic variety e.g. max(salary) < 100, rather than using the AGG FUNCs in an expression which involves using multiple AGG FUNCs in an expression (like agg_func1() agg_func2()).
The information below should help clarify further.
Given tables with the following schemas:
CREATE TABLE item (id int, length float, weight float);
CREATE TABLE item_info (item_id, name varchar(32));
# Is it legal (ANSI) SQL to write queries of this format ?
SELECT id, name, foo, foobar, fredstats FROM A, B
(SELECT id, foo(123) as foo, foobar('red') as foobar, fredstats('weight') as fredstats
FROM item GROUP BY id HAVING [ALGEBRAIC EXPRESSION] ORDER BY id AS A),
item_info AS B
WHERE item.id = B.id
Where:
ALGEBRAIC EXPRESSION is the type of expression that can be used in a WHERE clause - for example:
((foo(x) < foobar(y)) AND foobar(y) IN (1,2,3)) OR (fredstats(x) <> 0))
I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible.
Assuming it is legal to include AGG FUNCS in the way I have done above, I'd like to know:
Is there a more efficient way to write the above query ?
Is there any way I can speed up the query in terms of a judicious choice of indexes on the tables item and item_info ?
Is there a performance hit of using AGG FUNCs in an algebraic expression like I am (i.e. an expression involving the output of aggregate functions rather than constants?
Can the expression also include 'scaled' AGG FUNC? (for example: 2*foo(123) < -3*foobar(456) ) - will scaling (i.e. multiplying an AGG FUNC by a number have an effect on performance?)
How can I write the query above using INNER JOINS instead?
