MAX（）和MAX（）OVER PARTITION BY在Teradata查询中产生错误3504 [英] MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query

问题描述

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

I am trying to produce a results table with the last completed course date for each course code, as well as the last completed course code overall for each employee. Below is my query:

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 ...）行，查询执行得很好，所以我将问题隔离到了该行，但是在搜索了这些论坛和互联网之后，我看不到我在说什么做错了。有人可以帮忙吗？

If I remove the MAX() OVER (PARTITION BY...) line, the query executes just fine, so I've isolated the problem to that line, but after searching these forums and the internet I can't see what I'm doing wrong. Can anyone help?

推荐答案

正如小马在评论中说的那样，您不能将OLAP函数与聚合函数混在一起。

As Ponies says in a comment, you cannot mix OLAP functions with aggregate functions.

也许更容易获得每个员工的最后完成日期，并将其与包含三个目标课程中每个课程的最后完成日期的数据集结合起来。

Perhaps it's easier to get the last completion date for each employee, and join that to a dataset containing the last completion date for each of the three targeted courses.

这是一个未经检验的想法，有望使您走上正确的道路：

This is an untested idea that should hopefully put you down the right path:

  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

这篇关于MAX（）和MAX（）OVER PARTITION BY在Teradata查询中产生错误3504的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！