仅使用 SQL 中的 MAX 函数更新重复行

2022-01-17 00:00:00 sql-update sql oracle12c oracle

我有一个这样的表，假设为了举例，NAME 是一个唯一标识符.

I have a table like this, where, suppose for the sake of an example, NAME is a unique identifier.

NAME    AGE         VALUE
Jack    Under 65    3
Jack    66-74       5 
John    66-74       7
John    Over 75     9
Gill    25-35       11

一些 NAME 有多个 AGE，这是不可取的，因为这是由于数据不干净.

Some NAMEs have more than one AGE, which is undesirable, as this is due to dirtiness of the data.

我的目标是将重复项更新为每个 NAME 中只有一个 AGE.因此，所需的输出是:

My aim is to update the duplicates only to have one AGE within each NAME. The desired output is thus:

NAME    AGE         VALUE
Jack    Under 65    3
Jack    Under 65    5 
John    66-74       7
John    66-74       9
Gill    25-35       11

类似这样的 UPDATE 语句应该可以工作，但它不能.

Something like this UPDATE statement should work, but it doesn't.

UPDATE table t1
SET t1.age=MAX(t1.age)
WHERE EXISTS (SELECT COUNT(t2.AGE)
              FROM table t2
              WHERE t1.NAME=t2.NAME
              GROUP BY t2.NAME
              HAVING COUNT(t2.AGE) > 1)

SQL Error: ORA-00934: group function is not allowed here

第二期

即使我得到了上述声明，还有第二个问题.想法是在字符串上使用 MAX(或 MIN)函数为组内的所有重复设置相同的值.

Even if I got the above statement to work, there is a second issue. The idea there is to use the MAX (or MIN) function on strings to set the same value for all repeats within a group.

但不幸的是，这也无法按预期工作.为了保持一致性，理想情况下，年龄将默认为最低年龄组.但是因为 MAX/MIN 比较字符串的字母顺序，这会给出，例如:

But unfortunately, this too would not quite work as desired. For consistency, ideally an age would default to the lowest age group. But because MAX/MIN compare alphabetic order on strings, this would give, e.g.:

  • "66-74" 和 "Under 65" => MAX="Under 65" -- 最低
  • "66-74" 和 "Over 75" => MAX="Over 75" -- 最高

只有四个年龄段，可以指定自定义顺序吗?

There are only four age groups, would it be possible to specify a custom order?

  • NB1:我正在使用 Oracle SQL.
  • NB2:我不介意是否有办法使用 SELECT 而不是 UPDATE 语句来实现结果.
  • NB1: I am using Oracle SQL.
  • NB2: I do not mind if there is a way to achieve the result using a SELECT instead of an UPDATE statement.

可重现的示例

SELECT 'Jack' as NAME, 'Under 65' as AGE, 3 as VALUE from dual
UNION ALL
SELECT 'Jack' as NAME, '66-74' as AGE, 5 as VALUE from dual
UNION ALL
SELECT 'John' as NAME, '66-74' as AGE, 7 as VALUE from dual
UNION ALL
SELECT 'John' as NAME, 'Over 75' as AGE, 9 as VALUE from dual
UNION ALL
SELECT 'Gill' as NAME, '25-35' as AGE, 11 as VALUE from dual

推荐答案

您可以使用 case when 子句定义自定义顺序，然后使用解析 max().这适用于给定的示例:

You can define custom order with case when clause and then use analytic max(). This worked for given examples:

update t1 set age = (
    select max(age) keep (dense_rank last 
           order by case when age = 'Over 75'  then 1
                         when age = '66-74'    then 2
                         when age = 'Under 65' then 3
                         when age = '25-35'    then 4
                    end)
    from t1 tx where tx.name = t1.name )

相关文章