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

MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query

提问by dneaster3

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

This query produces the following error:

此查询产生以下错误：

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

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?

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

采纳答案by mechanical_meat

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

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

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

回答by dnoeth

Logically OLAP functions are calculated after GROUP BY/HAVING, so you can only access columns in GROUP BY or columns with an aggregate function. Following looks strange, but is Standard SQL:

逻辑上 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

And as Teradata allows re-using an alias this also works:

由于 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

回答by MatBailie

I know this is a very old question, but I've been asked by someone else something similar.

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

I don't have TeraData, but can't you do the following?

我没有 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

The GROUP BYnow ensures one row per course per employee. This means that you just need a straight MAX()to get the max_course_date.

在GROUP BY现在确保每个员工当然一行。这意味着您只需要直接MAX()获得max_course_date.

Before your GROUP BYwas just giving one row per employee, and the MAX() OVER()was trying to give multiple results for that one row (one per course).

在您GROUP BY只为每个员工提供一行之前，并且MAX() OVER()试图为该一行提供多个结果（每门课程一个）。

Instead, you now need the OVER()clause to get the MAX()for the employee as a whole. This is now legitimate because each individual row gets just one answer (as it is derived from a super-set, not a sub-set). Also, for the same reason, the OVER()clause now refers to a valid scalar value, as defined by the GROUP BYclause; employee_number.

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

Perhaps a short way of saying this would be that an aggregatewith an OVER()clause must be a super-set of the GROUP BY, not a sub-set.

也许简而言之，aggregatewithOVER()子句必须是 的超集，而GROUP BY不是子集。

Create your query with a GROUP BYat the level that represents the rows you want, then specify OVER()clauses if you want to aggregate at a higher level.

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

回答by jwize

I think this will work even though this was forever ago.

我认为这会奏效，即使这是很久以前的事了。

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

If you want to get the last Id if the date is the same then you can use this assuming your primary key is Id.

如果您想在日期相同的情况下获得最后一个 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

回答by user11188555

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