代码之家 › 专栏 › 技术社区 › Hal

如何最大限度地正确使用group by?

having group-by oracle sql

Hal · 技术社区 · 6 年前

我正在尝试使用group by查找计数的最大值

第1个代码:

 SELECT MAX (COUNT (studid)) AS total,
         unitcode,
         semester,
         TO_CHAR (ofyear, 'yyyy') AS "Year of Offer"
    FROM uni.enrolment
   WHERE TO_CHAR (ofyear, 'YYYY') = '2013'
GROUP BY semester, ofyear, unitcode
ORDER BY total;

结果:

ORA-00937:非单组群函数 00937年。00000-“非单组组功能” *原因:
*操作: 第18行第36列出错

第二代码:

  SELECT unitcode,
         TO_CHAR (ofyear, 'YYYY') AS year,
         semester,
         COUNT (studid) AS student_count
    FROM uni.enrolment
GROUP BY unitcode, ofyear, semester
  HAVING COUNT (studid) = (  SELECT MAX (COUNT (studid))
                               FROM uni.enrolment
                           GROUP BY unitcode, ofyear, semester
                             HAVING TO_CHAR (ofyear, 'YYYY') = '2013')
ORDER BY unitcode;

结果:

ââââââââââââââââââââââââââââââââââââââââââââââââââ
â UNITCODE    YEAR     SEMESTER    STUDENT_COUNT â
â âââââââââââââââââââââââââââââââââââââââââââââââââ£
â EG2004      2013     1           8             â
ââââââââââââââââââââââââââââââââââââââââââââââââââ

当我运行第一个代码时,它会给我一个错误,但第二个代码工作得很好。我不太明白这个错误。为什么我必须使用子查询来解决它?

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

嵌套聚合函数是Oracle ISM。我个人觉得他们很困惑。但是 MAX(COUNT( . . . )) 与以下内容相同:

select max(cnt)
from (select . . . , count(<something>) as cnt
      from t
      group by . . .
     ) x;

也就是说,它执行两个聚合,一个与 group by 一个没有。外部列不能返回未聚合的列。这就是为什么你会出错。

我要注意的是,对于您的第二个查询,您需要在内部和外部对年份进行筛选。因此,查询可能会返回正确的结果,但您希望这样做:

SELECT unitcode, TO_CHAR(ofyear, 'YYYY') AS year, semester,
       COUNT(studid) AS student_count
FROM uni.enrolment
WHERE TO_CHAR(ofyear, 'YYYY') = '2013')
GROUP BY unitcode, TO_CHAR(ofyear, 'YYYY'), semester
HAVING COUNT(studid) = (SELECT MAX (COUNT (studid))
                        FROM uni.enrolment
                        GROUP BY unitcode, ofyear, semester
                        HAVING TO_CHAR(ofyear, 'YYYY') = '2013'
                       )
ORDER BY unitcode;

注意,我还将表达式放在 GROUP BY 对于 year . 您希望按年份而不是按原始日期汇总。

Adrian Maxwell 6 年前

您需要先计算计数,然后才能确定哪些是最大值或等于最大值。我建议使用 RANK() OVER() 或 DENSE_RANK() OVER() 对于第二步,如果此结果等于1(按顺序描述时),则计数最大。在此查询中,窗口功能将在 GROUP BY 作为 SELECT 子句(select子句不是第一个处理的子句),因此count()可作为排名的输入。

SELECT
        total
      , unitcode
      , semester
      , ofyear
FROM (
      SELECT
              count(studid) AS total
            , unitcode
            , semester
            , to_char(ofyear, 'yyyy') AS "Year of Offer"
            , dense_rank() OVER (ORDER BY count(studid) DESC) AS rnk
      FROM uni.enrolment
      WHERE to_char(ofyear, 'YYYY') = '2013'
      GROUP BY
              semester
            , ofyear
            , unitcode
      ) d
WHERE rnk = 1 --<< this is how we only list the maximum(s)
ORDER BY
        unitcode
      , semester
;

也请注意,您可以使用 EXTRACT(YEAR FROM ofyear) 而不是用来