sql實作使用者ID關系資料歸一化綁定（可根據多列聚合，存在多列，任意一個列出現相等情況則這些資料屬于同一個人或者說同一個辨別）

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
)