是否有可能以这种方式使用 AGG 函数进行 SQL 查询？

Question

假设我有以下聚合函数：

• AGG1
• AGG2
• AGG3
• AGG4

是否可以像这样编写有效的 SQL（以与数据库无关的方式）：

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 函数的参数。

注意：AGG1 和 AGG2 在结果中作为列返回（但不会出现在 HAVING CLAUSE 中，AGG3 和 AGG4 出现在 HAVING CLAUSE 中但不会在结果集中返回。

理想情况下，我想要一个与数据库无关的答案解决方案，但如果我必须绑定到数据库，我正在使用 PostgreSQL (v9.x)

编辑

只是澄清一下：我不反对在中使用 GROUP BY 。我的 SQL 不是很好，所以上面的示例 SQL 可能有点误导，我编辑了上面的伪 sql 语句，希望让我的意图更清楚

。使用 AGG 函数可以：

• 在返回的列中包含 agg 函数值，而不在 HAVING 子句中指定它们。
• 在 HAVING 子句中指定 agg 函数，但不会在结果集中返回

。看来这两个问题的答案都是是的，我唯一需要做的就是添加一个 GROUP BY 子句以确保返回的行是唯一的。

Answer 1

PostgreSQL 主要版本包括点后的第一个数字，因此“PostgreSQL (v9.x)”不够具体。正如@kekekela所说，没有（便宜的）完全与数据库无关的方法。即使 PostgreSQL 9.0 和 9.1 之间也存在重要的语法差异。

如果您只有分组值 AGG1(param1), AGG2(param2)，则无需提供显式的 GROUP BY 子句即可逃脱。由于混合了分组列和非分组列，因此您必须为 SELECT 中出现的所有非分组列提供一个 GROUP BY 子句。对于任何版本的 PostgreSQL 都是如此。阅读GROUP BY 并在手册中使用它。

但是，从版本 9.1 开始，一旦您在 GROUP BY 中列出主键，您就可以跳过此表的其他列，并仍然在 SELECT< /代码> 列表。 版本 9.1 的发行说明告诉我们：

当主查询目标列表中允许非 GROUP BY 列
键在 GROUP BY 子句中指定 (Peter Eisentraut)

关于参数

您打算向聚合函数提供一个常量值吗？有什么意义？ 文档告诉我们

聚合函数根据多个输入行计算单个结果。

或者您希望这些参数是列名称？只要在提交到数据库之前生成语句，这种动态 SQL 就可以工作。不适用于准备好的语句或简单的 sql 或 plpgsql 函数。您必须使用 EXECUTE为此目的，在 plpgsql 函数中。

为了防范 SQLi，请使用 USING $1, $2 语法来表示 值 和 quote_ident() 表示您的列或表名称。

Answer 2

不使用 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 起）。

请注意，您不需要对 Partition by 子句中的两个聚合使用相同的分组列。

如果您想坚持使用 ANSI SQL，那么您应该使用 row_number() 函数来限制结果。如果您仅在 PostgreSQL（或以某种方式支持 LIMIT 的其他 DBMS）上运行此命令，请将 LIMIT 原因移至派生表（内部查询）

Answer 3

从高层次的角度来看，这应该是可行的，除非您需要在 GROUP BY 语句中使用 COL1、COL2 等，否则它们在 SELECT 列表中将无效。在 SELECT 列表中包含 AGG1 等而不是在 HAVING 中不是问题。

就数据库无关性而言，无论你做什么，你都必须调整语法（例如，我立刻就知道 PostgreSQL、SQL SERVER 和 Oracle 中的 LIMIT 会有所不同），但是你如果您的高级表示是可靠的，则可以构建逻辑来正确地为每个语句构建语句。