时间戳

首页 > 解决方案 > 使用单个更新语句将所有空值与oracle sql中相应列的最常见值进行归类

sql - 使用单个更新语句将所有空值与oracle sql中相应列的最常见值进行归类

问题描述

我正在尝试估算 oracle 数据库表中存在的所有空值。假设表包含以下行

ID    Col1    Col2
-------------------
1     Male    USA
2     Male    USA
3     Female  Russia
4     (null)  USA
5     Male    (null)
6     Male    USA
7     Female  USA
8     (null)  Canada
9     Male    USA

现在，我们可以看到“Male”是 Col1 中最常见的值，“USA”是 Col2 中最常见的值。我希望将 Col1 中的所有空值替换为“Male”，并将 Col2 中的所有空值替换为“USA”。在平局的情况下，任何值都可以用来替换。

因此，决赛桌将如下所示。

ID    Col1    Col2
-------------------
1     Male    USA
2     Male    USA
3     Female  Russia
4     Male    USA
5     Male    USA
6     Male    USA
7     Female  USA
8     Male    Canada
9     Male    USA

到目前为止，我所做的就是这样。

UPDATE tablename
    SET
        col1 = (
            SELECT
                col1
            FROM
                tablename
            GROUP BY
                col1
            ORDER BY
                COUNT(*) DESC
            FETCH FIRST 1 ROWS ONLY
        )
    WHERE 
        col1 IS NULL;


UPDATE tablename
    SET
        col2 = (
            SELECT
                col2
            FROM
                tablename
            GROUP BY
                col2
            ORDER BY
                COUNT(*) DESC
            FETCH FIRST 1 ROWS ONLY
        )
    WHERE 
        col2 IS NULL;

我在这里所做的是为每一列找到最常见的值并更新它。显然，这适用于只有 2 列的表。但是如果我有一个超过 20 列的表，这个过程就会变得混乱。有一个更好的方法吗？

标签： sqloracle

解决方案

计算stats_mode单独查询中的每一列，并nvl通过交叉连接。像这样：

with
  inputs (id, col1, col2) as (
    select 1, 'Male'  , 'USA'    from dual union all
    select 2, 'Male'  , 'USA'    from dual union all
    select 3, 'Female', 'Russia' from dual union all
    select 4, null    , 'USA'    from dual union all
    select 5, 'Male'  , (null)   from dual union all
    select 6, 'Male'  , 'USA'    from dual union all
    select 7, 'Female', 'USA'    from dual union all
    select 8, null    , 'Canada' from dual union all
    select 9, 'Male'  , 'USA'    from dual
  )
select i.id,
       nvl(i.col1, m.col1_mode) as col1, 
       nvl(i.col2, m.col2_mode) as col2
from   inputs i cross join
       (select stats_mode(col1) as col1_mode,
               stats_mode(col2) as col2_mode from inputs) m
;

        ID COL1   COL2  
---------- ------ ------
         1 Male   USA   
         2 Male   USA   
         3 Female Russia
         4 Male   USA   
         5 Male   USA   
         6 Male   USA   
         7 Female USA   
         8 Male   Canada
         9 Male   USA

推荐阅读