SQL(Impala)-查找包含空值的重复值
SQL(Impala)-查找包含空值的重复值
提问于 2021-06-02 10:23:30
我有一个名为baseTable的表，包含许多列，但是我使用了名为Material_Type、Material_Desc (有空值)、Material_Number来使用row_num和分区by查找重复项的3列。注意:我需要根据3种条件过滤副本。
当Material_Type
- When Material_Desc = Material_Desc
- When Material_Number <> Material_Number
时，
样本表：
Material_Type Material_Desc Material_Number
ABC XYZ 1
ABC XYZ 1
ABC XYZ 2
ABC XYZ 3
DEF IMM 1
LMN NULL 1
LMN NULL 2我只希望在newTable中有重复的值，并且希望删除不同的值。
期望产出：
Material_Type Material_Desc Material_Number new
ABC XYZ 1 1
ABC XYZ 1 2
ABC XYZ 2 3
ABC XYZ 3 4
LMN NULL 1 1
LMN NULL 2 2我使用了下面的查询，但没有获得预期的输出，因为它不包括来自Material_Desc列的空值，也没有使用null创建分区，也创建了不必要的重复记录。
使用的查询：
create table newTable as
with mycte as
(
select
m.MATERIAL_NUMBER
,m.MATERIAL_TYPE
,m.Material_Desc,
row_number() over(partition BY d.MATERIAL_TYPE,d.Material_Desc order by d.MATERIAL_NUMBER) as new
from baseTable m
inner join
(
select MATERIAL_NUMBER,MATERIAL_TYPE,Material_Desc,count(*) from baseTable group by
MATERIAL_NUMBER,MATERIAL_TYPE,Material_Desc having count(*) > 1
) d on d.MATERIAL_NUMBER <> m.MATERIAL_NUMBER and d.MATERIAL_TYPE=m.MATERIAL_TYPE
and d.Material_Desc= m.Material_Desc)
select * from mycte 任何帮助都将不胜感激。
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-06-02 10:25:11
只需使用row_number()和count(*)作为窗口函数：
select Material_Type, Material_Desc, Material_Number,
row_number() over (partition by Material_Type, Material_Desc order by Material_Number) as new
from (select t.*,
count(*) over (partition by Material_Type, Material_Desc) as cnt
from t
) t
where cnt > 1;这适用于您提供的数据，只需计算每种类型和描述的行数即可。如果确实需要不同的材料编号，有一种方法是min()和max()
select Material_Type, Material_Desc, Material_Number,
row_number() over (partition by Material_Type, Material_Desc order by Material_Number) as new
from (select t.*,
min(Material_Number) over (partition by Material_Type, Material_Desc) as min_Material_Number,
max(Material_Number) over (partition by Material_Type, Material_Desc) as max_Material_Number
from t
) t
where min_Material_Number <> max_Material_Number;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67803288
复制相关文章
相似问题
腾讯云开发者
