Geek Answers Handbook

Is it possible to have a SQL query that uses AGG functions in this way?

Assuming I have the following aggregate functions:

  • AGG1
  • AGG2
  • AGG3
  • AGG4

Is it possible to write valid SQL (in the agnostic way db) as follows:

SELECT [COL1, COL2 ....], AGG1(param1), AGG2(param2) FROM [SOME TABLES]
WHERE [SOME CRITERIA]
HAVING AGG3(param2) >-1 and AGG4(param4) < 123
GROUP BY COL1, COL2, ... COLN
ORDER BY COL1, COLN ASC
LIMIT 10

Where COL1 ... COLN are the columns in the requested tables, and param1 ... paramX are the parameters passed to the AGG function.

Note. AGG1 and AGG2 are returned as columns in the results (but are not displayed in the HAVING CLAUSE, and AGG3 and AGG4 are displayed in the HAVING CLAUSE, but are not returned in the result set.

Ideally, I want to get an agnostic answer to the solution, but if I need to bind to db, I use PostgreSQL (v9.x).

Edit

: GROUP BY . SQL , SQL , , . sql , .

, , , AGG, :

  • agg , HAVING.
  • agg, HAVING, .

, , , - . , , SQL, - GROUP BY, , .

+3
3

PostgreSQL , "PostgreSQL (v9.x)" . @kekekela, () db . PostgreSQL 9.0 9.1 .

AGG1(param1), AGG2(param2), GROUP BY. , GROUP BY , SELECT. PostgreSQL. GROUP BY .

9.1, , GROUP BY, - SELECT. 9.1 :

GROUP BY , GROUP BY (Peter Eisentraut)

? ? docs

.

, ? SQL , . sql plpgsql. EXECUTE plpgsql.

SQLi USING $1, $2 quote_ident() .

+1

GROUP BY - . , , , :

SELECT *
FROM (
    SELECT [COL1, COL2 ....], 
           AGG1(param1) over (partition by some_grouping_column) as agg1, 
           AGG2(param2) over (partition by some_grouping_column) as agg2,
           row_number() over () as rn
    FROM [SOME TABLES]
    WHERE [SOME CRITERIA]
    ORDER BY COL1
)  t
WHERE AGG3 >-1 
  AND AGG4 < 123
  AND rn <= 10
ORDER BY col1

ANSI SQL , PostgreSQL ( 8.4).

, .

ANSI SQL, row_number(), . PostgreSQL ( , LIMIT - ), LIMIT ( )

+1

, , COL1, COL2 .. GROUP BY, SELECT. AGG1 .. SELECT, HAVING, .

dn agnostic, , (, LIMIT PostgreSQL, SQL SERVER Oracle, ), , .

0

More articles:


All Articles