我用SQL求得了微信的共同好友！

「哎呀，我們竟然有共同好友」

「哎呀，沒想到你們也認(rèn)識(shí)」

經(jīng)常在朋友圈評(píng)論區(qū)能夠看到類似的評(píng)論，這些評(píng)論反映的其實(shí)就是共同好友這個(gè)概念。那如果你是負(fù)責(zé)微信的數(shù)據(jù)分析師，現(xiàn)在業(yè)務(wù)方想看下微信中任意兩個(gè)人之間有多少個(gè)共同好友，應(yīng)該怎么看呢？

我們先創(chuàng)建一個(gè)模擬數(shù)據(jù)表，創(chuàng)建代碼如下：

create?table?weixin_friends?
(uid?bigint,
tuid?bigint);

insert?into?weixin_friends?(uid,tuid)?
values?
(100,200),
(100,300),
(100,400),
(200,100),
(200,300),
(200,400),
(300,100),
(300,200),
(400,100)

通過上述代碼就創(chuàng)建了好友關(guān)系表weixin_friends，該表的詳細(xì)(模擬)數(shù)據(jù)如下：

uid表示每個(gè)用戶在微信后臺(tái)的用戶id，tuid表示uid對(duì)應(yīng)的微信好友id，現(xiàn)在我們要通過這張表查詢出任意兩個(gè)人之間的共同好友數(shù)，應(yīng)該怎么做呢？

想一下，如果現(xiàn)在需要我們?nèi)斯ふ页錾媳碇腥我鈨蓚€(gè)人的共同好友的話，你會(huì)怎么做呢？

我會(huì)先從tuid著手，看下每一個(gè)tuid是哪些人的共同好友，也就是tuid鏈接了哪些人？

如果我們能整理出上表這樣的話，任意兩個(gè)人的共同好友數(shù)就一目了然了，只需要按照好友1、好友2同時(shí)group by，然后count(tuid)就可以得到任意兩個(gè)用戶的共同好友數(shù)了。

那我們?cè)趺礃硬趴梢缘玫缴厦孢@張的表呢？那就是把表weixin_friends通過tuid進(jìn)行自連接，實(shí)現(xiàn)代碼如下：

select?
?t1.uid?t1_uid
?,t1.tuid?t1_tuid
?,t2.uid?t2_uid
?,t2.tuid?t2_tuid
from?
?(select?uid,tuid?from?weixin_friends)?t1?
join?
?(select?uid,tuid?from?weixin_friends)?t2?
on?t1.tuid?=?t2.tuid

運(yùn)行上面代碼會(huì)得到如下結(jié)果：

上面結(jié)果存在兩個(gè)問題，一個(gè)是自己和自己是共同好友(紅框圈出來的部分)，即t1_uid = t2_uid，另外一個(gè)就是AB和BA是兩條記錄(綠框圈出來的部分)，實(shí)際上背后是相同的兩個(gè)人。為了解決這兩個(gè)問題，我們就需要對(duì)t1_uid和t2_tuid進(jìn)行限制，代碼如下：

select?
?t1.uid?t1_uid
?,t1.tuid?t1_tuid
?,t2.uid?t2_uid
?,t2.tuid?t2_tuid
from?
?(select?uid,tuid?from?weixin_friends)?t1?
join?
?(select?uid,tuid?from?weixin_friends)?t2?
on?t1.tuid?=?t2.tuid?and?t1.uid?

運(yùn)行上面代碼會(huì)得到如下結(jié)果：

然后再針對(duì)這個(gè)表中的t1_uid、t2_uid同時(shí)進(jìn)行group by即可，實(shí)現(xiàn)代碼如下：

select?
?t1_uid
?,t2_uid
?,count(t1_tuid)?cnt
from?
?(select?
??t1.uid?t1_uid
??,t1.tuid?t1_tuid
??,t2.uid?t2_uid
??,t2.tuid?t2_tuid
?from?
??(select?uid,tuid?from?weixin_friends)?t1?
?join?
??(select?uid,tuid?from?weixin_friends)?t2?
?on?t1.tuid?=?t2.tuid?and?t1.uid??)t?
group?by?
?t1_uid
?,t2_uid

運(yùn)行上面代碼會(huì)得到如下結(jié)果：

這就是我們想要的任意兩個(gè)用戶之間的共同好友數(shù)了。