时间戳

首页 > 解决方案 > 什么时候可以将聚合函数嵌套在标准 SQL 中？

sql - 什么时候可以将聚合函数嵌套在标准 SQL 中？

问题描述

我知道它在 SQL-92 中是不允许的。但从那以后它可能已经改变了，特别是当应用了一个窗口时。您能否解释这些变化并给出引入它们的版本（或版本，如果有更多）？

例子

  1. SUM(COUNT(votes.option_id)) OVER() 是每个标准 SQL:2016（或更早版本）的有效语法吗？

这是我在为什么在PostgreSQL中使用窗口函数时可以嵌套聚合函数？.

  1. Codewars的Calculating Running Total (SQL) kata是其最受好评的解决方案（使用 PostgreSQL 13.0，一个高度符合标准的引擎，因此代码很可能是标准的）这个：
SELECT
  CREATED_AT::DATE AS DATE,
  COUNT(CREATED_AT) AS COUNT,
  SUM(COUNT(CREATED_AT)) OVER (ORDER BY CREATED_AT::DATE ROWS UNBOUNDED PRECEDING)::INT AS TOTAL
FROM
  POSTS
GROUP BY
  CREATED_AT::DATE

（可以简化为：

SELECT
  created_at::DATE date,
  COUNT(*) COUNT,
  SUM(COUNT(*)) OVER (ORDER BY created_at::DATE)::INT total
FROM posts
GROUP BY created_at::DATE

我认为::s 是一种我不知道的新语法。现在允许从 TIMESTAMP 转换为 DATE（在 SQL-92 中不允许）。）

  1. 正如这个 SO 答案所解释的，即使没有窗口，Oracle 数据库也允许它GROUP BY从上下文中提取。我不知道标准是否允许。

标签： sqllanguage-lawyeraggregate-functionswindow-functionssql-standards

解决方案

您自己已经注意到了不同之处：一切都与窗户有关。COUNT(*)例如，没有OVER子句的是聚合函数。COUNT(*)withOVER子句是一个窗口函数。

通过使用聚合函数，您可以压缩在FROM子句和WHERE子句应用于指定组GROUP BY或在没有GROUP BY子句的情况下应用于一行之后获得的原始行。

之后应用窗口函数，也就是分析函数。它们不会更改结果行的数量，而只是通过查看所选数据的全部或部分行（窗口）来添加信息。

在

SELECT
    options.id,
    options.option_text,
    COUNT(votes.option_id) as vote_count,
    COUNT(votes.option_id) / SUM(COUNT(votes.option_id)) OVER() * 100.0 as vote_percentage
FROM options
LEFT JOIN votes on options.id = votes.option_id
GROUP BY options.id;

我们首先将投票加入选项，然后通过将连接的行聚合到每个选项的一个结果行来计算每个选项的投票 ( GROUP BY options.id)。我们依靠投票表 ( 中的一个不可为空的列COUNT(votes.option_id)，因此如果没有投票，我们将获得零计数，因为在外部连接行中，该列设置为空。

在聚合所有行并因此每个选项获得一行之后，我们SUM() OVER在此结果集上应用窗口函数 ( )。我们SUM对投票计数（SUM(COUNT(votes.option_id))通过查看整个结果集（空OVER子句）进行分析，从而在每一行中获得相同的总投票数。我们使用此值进行计算：选项的投票数除以总投票数乘以 100 ，这是选项占总票数的百分比。

PostgreSQL 查询非常相似。我们选择每个日期的帖子数（COUNT(created_at)仅是一个COUNT(*)）以及这些计数的运行总数（通过使用查看当前行之前的所有行的窗口）。

因此，虽然这看起来像我们嵌套了两个聚合函数，但事实并非如此，因为SUM OVER它不被视为聚合函数，而是一个分析/窗口函数。

Oracle 确实允许直接在另一个上应用聚合函数，从而在先前的聚合上调用最终聚合。这允许我们获得一个结果行，例如总和的平均值，而无需为此编写子查询。然而，这不符合 SQL 标准，甚至在当时的 Oracle 开发人员中也非常不受欢迎。

推荐阅读