查找表中非法的值组合(组合键)

Question

我需要在一个大数据集中发现无效的值组合。下面的演示是一个非常小的数据集;然而，它说明了原理。(我使用红移)

我收到一个3列的数据集:Client_id, t_number, t_name.

Client_id, t_number, t_name在数据集中重复多次(当客户端记录一个新事务时，这些中的每一个都被记录为文件中的新行)。T_number(事务号-(事务的id))和t_name(事务名)应该是唯一的组合。一个特定的t_number应该只匹配一个t_name(这些是重复的数据，本质上是number和name应该反映相同的活动)。

数据集没有空值。t_number比t_names要多。这表明必须有一个分配了多个t_number的t_name (t_name的集合)。

简化后的数据集如下:

create table tmp.test_v1 (
id varchar(5)  
, t_number smallint  
, t_name varchar(5));
Insert into tmp.test_v1 values  
('id_1', 1, 'aaa')  
,('id_2', 1, 'aaa')  
,('id_3', 2, 'bbb')  
,('id_4', 3, 'ccc')  
,('id_5', 2, 'bbb')  
,('id_6', 4, 'ddd')  
,('id_3', 5, 'aaa')  
,('id_1', 2, 'bbb')  
,('id_2', 3, 'ccc')  
,('id_4', 4, 'ddd')  
,('id_7', 6, 'eee')  
,('id_8', 6, 'eee')  
,('id_9', 6, 'eee');

t_name 'aaa'与t_number: 1和5相关联。这是我想了解的情况。

我得到了以下查询:

select t_name, t_number from tmp.test_v1  
where t_name = (select t_name from (  
select a.t_name, b.t_number  
from tmp.test_v1 as a   
left join tmp.test_v1 as b on a.t_name = b.t_name  
group by a.t_name, b.t_number  
order by a.t_name) c  
group by t_name  
having count(t_name) > 1)  
group by t_name, t_number;

上述查询返回:

'---------------------'  
| t_name  | t_number  |  
'---------------------'    
| aaa     |    1      |
'---------------------'   
| aaa     |    5      |        
'---------------------' 

我可以看到t_name 'aaa'有数字1,5分配给它。

问题:这不能很好地扩展。在一个有1.3亿条记录的数据集上，它似乎没完没了。

达到相同/相似结果的更有效方法是什么?

Answer 1

我认为连接占用了所有的时间。你可以使用:

来避免连接，或者让连接更快。

select distinct t_name, t_number from test_v1 where t_name in (
select t_name
from ( select distinct t_name, t_number from test_v1 ) t1
group by t_name
having count(t_number)>1
)

试试:https://www.db-fiddle.com/f/vhwn1WKB7uV2yUZ4FhNPH/0

Answer 2

入会是花费你所有时间的。这基本上是一个循环条件，随着重复值的数量，数据会爆炸。因为你想同时得到违反的名字和数字这就有点棘手了。下面是我要做的:

select t_name, t_number
from (
select t_name, t_number, 
count(t_number) over(partition by t_name) as cnt
from test_v1
group by 1, 2 ) sub
where cnt > 1

这只扫描表一次，不循环连接，并通过"逐组"快速降低数据大小。