是否可以通过这种方式使用AGG函数进行SQL查询？ [英] Is it possible to have an SQL query that uses AGG functions in this way?
问题描述
假设我具有以下聚合函数：
Assuming I have the following aggregate functions:
- AGG1
- AGG2
- AGG3
- AGG4
- AGG1
- AGG2
- AGG3
- AGG4
是否可以编写有效的SQL （以db不可知方式）：
Is it possible to write valid SQL (in a db agnostic way) like this:
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
其中COL1 ... COLN是要查询的表中的列，而param1 ... paramX是传递给AGG函数的参数。
Where COL1 ... COLN are columns in the tables being queried, and param1 ... paramX are parameters passed to the AGG funcs.
注意：结果中的AGG1和AGG2作为列返回（但不出现在HAVING CLAUSE中，而AGG3和AGG4出现在HAVING CLAUSE中，但不在结果集中返回。
Note: AGG1 and AGG2 are returned in the results as columns (but do not appear in the HAVING CLAUSE, and AGG3 and AGG4 appear in the HAVING CLAUSE but are not returned in the result set.
理想情况下，我希望该解决方案具有数据库不可知的答案，但是如果必须绑定到数据库，则我正在使用PostgreSQL（v9.x）。
Ideally, I want a DB agnostic answer to the solution, but if I have to be tied to a db, I am using PostgreSQL (v9.x).
只需澄清一下：我不反对在查询中使用GROUP BY，我的SQL并不是很好，所以该示例
Just a matter of clarification: I am not opposed to using GROUP BY in the query. My SQL is not very good, so the example SQL above may have been slightly misleading. I have edited the pseudo sql statement above to hopefully make my intent more clear.
我想了解的主要内容是是否选择使用AGG函数的查询可以：
The main thing I wanted to find out was whether a select query that used AGG functions could:
- 在未在HAVING子句中指定的情况下，在返回的列中具有agg函数值。
- 在HAVING子句中指定的agg函数，但没有返回结果集。
从到目前为止我收到的答案来看，似乎两个问题的答案都是肯定的。纠正SQL的唯一想法就是添加GROUP BY子句以确保返回的行是唯一的。
From the answers I have received so far, it would seem the answer to both questions is YES. The only think I have to do to correct my SQL is to add a GROUP BY clause to make sure that the returned rows are unique.
推荐答案
PostgreSQL的主要版本包含点号后的第一位，因此 PostgreSQL（v9.x）不够具体。正如@kekekela所说，没有（便宜的）完全db不可知论的方式。即使在PostgreSQL 9.0和9.1之间，在语法上也有重要区别。
PostgreSQL major version include the first digit after the dot, thus "PostgreSQL (v9.x)" is not specific enough. As @kekekela said, there is no (cheap) completely db agnostic way. Even between PostgreSQL 9.0 and 9.1 there is an important syntactical difference.
如果只有分组值 AGG1（param1），AGG2（param2） ，您无需提供明确的 GROUP BY 子句就可以摆脱困境。由于您混合了分组列和非分组列，因此您必须提供一个 GROUP BY 子句，该子句将出现在<$ c中$ c>选择。任何版本的PostgreSQL都是如此。在手册中阅读有关 GROUP BY并进行使用的信息。
If you had only the grouped values AGG1(param1), AGG2(param2) you would get away without providing an explicit GROUP BY clause. Since you mix grouped and non-grouped columns you have to provide a GROUP BY clause with all non-grouped columns that appear in the SELECT. That's true for any version of PostgreSQL. Read about GROUP BY and HAVING it in the manual.
从版本 9.1 开始，但是，一旦您在 GROUP BY ，您可以跳过此表的其他列，并仍在 SELECT 列表中使用它们。 版本9.1的发行说明告诉我们：
Starting with version 9.1, however, once you list a primary key in the GROUP BY you can skip additional columns for this table and still use them in the SELECT list. The release notes for version 9.1 tell us:
当在GROUP BY子句中指定了主
键时，允许查询目标列表中的非GROUP BY列（Peter Eisentraut）
Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)
关于参数
喂一个常数值到一个聚合函数？重点是什么？ 文档告诉我们
聚合函数从多个输入行计算单个结果。
An aggregate function computes a single result from multiple input rows.
您希望这些参数成为列名吗？只要在提交到数据库之前 生成语句，就可以使用这种动态SQL。对于准备好的语句或简单的 sql 或 plpgsql 函数不起作用。您必须使用执行为此，在 plpgsql 函数中。
Or do you want those parameters to be column names? That kind of dynamic SQL works as long as the statement is generated before committing to the database. Does not work for prepared statements or simple sql or plpgsql functions. You have to use EXECUTE in a plpgsql function for that purpose.
为保护SQLi，请使用 USING $ 1 ，值和语法nofollow>您的列或表名称的quote_ident（）。
As safeguard against SQLi use the USING $1, $2 syntax for values and quote_ident() for your column or table names.
这篇关于是否可以通过这种方式使用AGG函数进行SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
