sql - 在 Oracle 表中查找重复和缺失 (null) 值

问题描述

我正在查找表中的错误并希望报告重复值和缺失值。我不确定执行此操作的最佳方法，并且正在寻找有关更好方法的建议来完成此操作。这是在 Oracle 12c 中。

这似乎达到了预期的结果：

SELECT a.id, 
       a.mainfield, 
       a.location, 
       b.counter 
FROM   maintable a 
       INNER JOIN (
                    SELECT mainfield, 
                            Count(*) counter 
                    FROM   maintable 
                    GROUP  BY mainfield 
                    HAVING Count(mainfield) > 1 OR mainfield IS NULL
                  ) b ON a.mainfield = b.mainfield OR
                  ( a.mainfield IS NULL AND b.mainfield IS NULL ) 
ORDER  BY a.mainfield;

这有效并为我提供了 ID、可能为空的 MAINFIELD、重复的 MAINFIELD 值或空的 MAINFIELD 值的位置和计数。

我可以使用更简单或更高效的方法吗？我不得不承认我的 SQL 技能相当生疏。

样本数据可能有帮助，也可能没有帮助，但 ID 是主键，是一个数字，不能为空。其他字段都是 NVARCHAR2 并且可以为空。这些都没有被索引。这是输出的样子。有些记录是完全错误的。有些是明显的错别字。有些似乎是测试数据。

ID      MAINFIELD   LOCATION                            COUNTER
------- ---------   ---------------------------------   -------
16626   206000650   9A OLIVER ST CENTRAL STATION        2
18805   206000650   3 SWIFT CT CENTRAL STATION          2
22409   940000170   2 MARKET ST NEWARK DE               2
22003   940000170   1 MARKET ST NEWARK NJ               2
29533   970000030   95 MILL RD ANDOVER                  2
20256   970000030   12 RAILROAD AVE                     2
29018   978900050   44 BROAD STREET                     2
28432   978900050   WASHINGTON ST AND HAMILTON AVE      2
21831   980700050   BROADWAY NEWTOWN                    2
24147   980700050   MAIN STREET LEVITTOWN               2
26418                                                   3
26738               TEST DATA                           3
26755                                                   3

最后三行有一个空主字段，并且有三个这样的记录（其中两个的位置也为空）。

在对上面的数据添加一些见解之后，我意识到我可能会考虑使用 NVL 来消除部分条件，如下所示（假设我选择的值在主字段中不是有效值）：

SELECT a.id, 
       a.mainfield, 
       a.location, 
       b.counter 
FROM   maintable a 
       INNER JOIN (
                    SELECT mainfield, 
                            Count(*) counter 
                    FROM   maintable 
                    GROUP  BY mainfield 
                    HAVING Count(mainfield) > 1 OR mainfield IS NULL
                  ) b ON NVL(a.mainfield,'***NULL***') = NVL(b.mainfield.'***NULL***') 
ORDER  BY a.mainfield;

这执行得更快一些，并且似乎产生了预期的结果。我一直在尝试其他选择但没有成功，所以这可能是最好的选择。

我放弃的一种替代方案可能适用于稍微不同的场景（但对我来说表现最差）是这个：

SELECT  id, 
        mainfield, 
        location,
        COUNT (id) OVER (PARTITION BY mainfield) counter
FROM    maintable a
WHERE   mainfield IS NULL
OR      EXISTS(SELECT 1 from maintable b
            WHERE mainfield = a.mainfield AND ROWID <> a.ROWID)
ORDER BY a.mainfield;

我真的很喜欢这种组合方式，并希望它会有些效率。我们并不是说它会运行好几天，但我正在尝试在 Oracle 中重新学习在我使用 SQL/DS 编码时可能曾经是一项技能。

如果上述任何一项让任何人想到更好的选择，我都会全力以赴。（例如，有没有办法在 WHERE 子句中引用计数器 [the COUNT (id) over PARTITION BY mainfield]？）

再次感谢。

标签： sqloraclejoin

SELECT * FROM ( SELECT id, mainfield, location, COUNT (id) OVER (PARTITION BY mainfield) counter FROM maintable a ) where counter > 1 or mainfield IS NULL ORDER BY mainfield;

sql - 在 Oracle 表中查找重复和缺失 (null) 值

问题描述

解决方案

推荐阅读