MAX() 和 MAX() OVER PARTITION BY 在 Teradata 查询中产生错误 3504

Question

我正在尝试生成一个结果表，其中包含每个课程代码的最后完成的课程日期，以及每个员工最后完成的课程代码。下面是我的查询：

SELECT employee_number,
       MAX(course_completion_date) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number

此查询产生以下错误：

3504 : Selected non-aggregate values must be part of the associated group

如果我删除 MAX() OVER (PARTITION BY...) 行，查询执行得很好，所以我已将问题隔离到该行，但在搜索这些之后论坛和互联网我看不出我做错了什么。有人可以帮忙吗？

Answer 1

逻辑上OLAP函数是在GROUP BY/HAVING之后计算的，因此只能访问GROUP BY中的列或具有聚合函数的列。以下看起来很奇怪，但却是标准 SQL：

SELECT employee_number,
       MAX(MAX(course_completion_date)) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

并且由于 Teradata 允许重复使用别名，这也有效：

SELECT employee_number,
       MAX(max_date) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

Answer 2

正如 Ponies 在评论中所说，不能将 OLAP 函数与聚合函数混合在一起。

也许更容易获得每个员工的最后完成日期，并将其加入到包含三个目标课程中每门课程的最后完成日期的数据集。

这是一个未经测试的想法，希望能让您走上正确的道路：

  SELECT employee_number,
         course_code,
         MAX(course_completion_date) AS max_date,
         lcc.LAST_COURSE_COMPLETED
    FROM employee_course_completion ecc
         LEFT JOIN (
             SELECT employee_number,
                    MAX(course_completion_date) AS LAST_COURSE_COMPLETED
               FROM employee_course_completion
              WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
         ) lcc
         ON lcc.employee_number = ecc.employee_number
   WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code, lcc.LAST_COURSE_COMPLETED

Answer 3

我知道这是一个非常古老的问题，但其他人也问过我类似的问题。

我没有 TeraData，但是您不能执行以下操作吗？

SELECT employee_number,
       course_code,
       MAX(course_completion_date)                                     AS max_course_date,
       MAX(course_completion_date) OVER (PARTITION BY employee_number) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

GROUP BY 现在可确保每位员工每门课程一行。这意味着您只需要直接 MAX() 即可获取 max_course_date。

在您的 GROUP BY 只为每个员工提供一行之前，MAX() OVER() 试图为该行提供多个结果（每个员工一个）当然）。

相反，您现在需要 OVER() 子句来获取整个员工的 MAX()。现在这是合法的，因为每一行仅得到一个答案（因为它来自超集，而不是子集）。此外，出于同样的原因，OVER() 子句现在引用由 GROUP BY 子句定义的有效标量值； 员工编号。

也许简单地说，带有 OVER() 子句的 aggregate 必须是 GROUP BY 的超集code>，不是子集。

在表示所需行的级别使用 GROUP BY 创建查询，然后如果要在更高级别聚合，请指定 OVER() 子句。

Answer 4

我认为这会起作用，即使这已经是很久以前的事了。

SELECT employee_number, Row_Number()  
   OVER (PARTITION BY course_code ORDER BY course_completion_date DESC ) as rownum
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
   AND rownum = 1

如果您想在日期相同的情况下获取最后一个 Id，那么您可以使用它，假设您的主键是 Id。

SELECT employee_number, Row_Number()  
   OVER (PARTITION BY course_code ORDER BY course_completion_date DESC, Id Desc) as rownum    FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
   AND rownum = 1

Answer 5

SELECT employee_number, course_code, MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code