sql实现数据归一化

工作中真实数据因涉及法律责任不便展示，以下为模拟数据。

注：务必看完此文章因为第一种实现存在bug，后边附带解释以及完整代码。

模拟数据：

我们拿三个列实现归一作为举例，n列同理。

   SELECT '1' unionid,'3' userid,'1' phoneid
   UNION ALL 
   SELECT '1' unionid,'4' userid,'3' phoneid
   UNION ALL 
   SELECT '2' unionid,'3' userid,'1' phoneid
   UNION ALL 
   SELECT '5' unionid,'1' userid,'7' phoneid
   UNION ALL 
   SELECT '7' unionid,'9' userid,'7' phoneid

目标实现：

实现解释

在所有数据中unionid,userid,phoneid只要有任意一个列出现相等情况
则认为是同一个人。
	例如：
		   在模拟数据中row2.userid = row3.userid = 3
		   所以row2和row3为同一个人
		   又因为row2.unionid = row4.unionid = 1
		   所以row2和row4为同一个人
		   即row2=row3=row4为同一个人
		   我们给了一个唯一标识字段win3

实现代码：

SELECT 
    win3tb.*
    ,min(win2) over(PARTITION by phoneid) win3
FROM 
(
    SELECT 
        win2tb.*
        ,min(win1) over(PARTITION by userid) win2
    FROM 
    (
        SELECT 
            win1tb.*
            ,min(concat(unionid,",",userid,",",phoneid)) over(PARTITION by unionid) win1
        FROM 
        (
            SELECT '1' unionid,'3' userid,'1' phoneid
            UNION ALL 
            SELECT '1' unionid,'4' userid,'3' phoneid
            -- UNION ALL 
            -- SELECT '0' unionid,'4' userid,'9' phoneid
            UNION ALL 
            SELECT '2' unionid,'3' userid,'1' phoneid
            UNION ALL 
            SELECT '5' unionid,'1' userid,'7' phoneid
            UNION ALL 
            SELECT '7' unionid,'9' userid,'7' phoneid
        )win1tb
    )win2tb
)win3tb

实现结果

供大家考究

bug再现

我们新添加一条数据0 4 9
新添加的数据中 userid=4 与之前的数据row4.userid=4 相等 所以目标结果应该是row2=row3=row4和新加的数据0 4 9为同一个人，应该又相同的标识win3
现在将实现代码的两行注释解开再执行
结果如下

bug解析

******bug1:******
没有出现row2 row3 row4 row7的标识win3相等。
因为我们在开窗过程中的唯一标识取的三个列拼接后的最小值（最大值同理），
所以在第一次根据unionid开窗后win1标识着是唯一没有问题的（重复数据也可，标识相等表示是同一个人），
但是（拿win1=131标识为例）在第二次根据userid开窗后row4和row7属于同一个窗口，我们的取得是min(win1)，使得row4与row1关联的win1在进一步成为win2，因为row7的缘故变成了更小的049，导致之后的row4无法关联上row1。
之后的列同理也可能存在此bug。

******bug2:******
理清楚了上面的代码逻辑我再补充一个bug，昨晚做了个梦，梦见了这个sql可能存在的这个bug并解决了，假设存在这样两条数据：
SELECT '' unionid,'11' userid,'13' phoneid
UNION ALL 
SELECT '' unionid,'14' userid,'15' phoneid
这两条数据本身不属于同一个人，但因为unionid都为空，导致最终结果会使得这两条数据的win3相等，其他字段同理。

完整实现代码

select
    win3tb.*
    ,min(tmpwin3) over(PARTITION by win2) win3
from
(
    select 
        tmpwin3tb.*
        ,if(phoneid<>''and phoneid is not null
            ,min(win2) over(PARTITION by phoneid) 
            ,win2
        )tmpwin3
    from 
    (
        select 
            win2tb.*
            ,min(tmpwin2) over(PARTITION by win1) win2
        FROM (
            SELECT 
                tmpwin2tb.*
                ,if(userid<>''and userid is not null
                    ,min(win1) over(PARTITION by userid) 
                    ,win1
                )tmpwin2
            FROM 
            (
                SELECT 
                    win1tb.*
                    ,if(unionid<>''and unionid is not null
                        ,min(concat(unionid,",",userid,",",phoneid)) over(PARTITION by unionid) 
                        ,concat(unionid,",",userid,",",phoneid)
                    )win1
                FROM 
                (
                    SELECT '1' unionid,'3' userid,'1' phoneid
                    UNION ALL 
                    SELECT '2' unionid,'3' userid,'1' phoneid
                    UNION ALL 
                    SELECT '1' unionid,'4' userid,'3' phoneid
                    UNION ALL 
                    SELECT '0' unionid,'4' userid,'9' phoneid
                    UNION ALL 
                    SELECT '5' unionid,'1' userid,'7' phoneid
                    UNION ALL 
                    SELECT '7' unionid,'9' userid,'7' phoneid
                    UNION ALL 
                    SELECT '' unionid,'11' userid,'13' phoneid
                    UNION ALL 
                    SELECT '' unionid,'14' userid,'15' phoneid
                )win1tb
            )tmpwin2tb
        )win2tb
    )tmpwin3tb
)win3tb

完整实现结果

完整实现逻辑

******bug1:******
从第二级开窗开始，我们在每次生成唯一标识后（如tmpwin2），先对前一级标识（如win1）进行一次开窗，然后取min(tmpwin2)。
这样就保证了相同的标识，如果有一任意个标识变小了（我们一直取得min，所以是变小了，max则变大了），我们通过这个操作将所有标识都变小。
最终实现标识的唯一，即实现了数据的归一化。
归一化以后我们可以在此基础上根据最后的winx（x标识如果有x个列进行归一）进一步collect_set或者其他操作。

******bug2:******
对于存在null或者''的情况我们只需要加一个判断，不为空时进行开窗，例如：
if(phoneid<>''and phoneid is not null
,min(win2) over(PARTITION by phoneid) 
,win2
)

本文地址：https://blog.csdn.net/qq_44011386/article/details/112646115