最近在做一个需求,其中有部分逻辑,秉着一切皆可left join 解决的方式写完了,直到我师兄说我这部分代码可以换个方式写的时候,我:哇,原来还可以这样处理。
已知数据集:
取数要求:当uid的tag为人称、IP 时,则保留tag为人称、IP时的全部记录(意味着,剔除tag为领域的记录);当uid的tag没有人称、IP时,则取tag为领域的全部记录。
首先看我的代码:
SELECT T.uid
,T.tag
,T.tag_name
FROM
(
SELECT *
FROM T
WHERE tag = '领域'
) T
LEFT JOIN
(
SELECT uid
,tag
,tag_name
FROM T
WHERE tag IN ('人称', 'IP')
) T1
ON T.uid = T1.uid
WHERE T1.uid is null
UNION ALL
SELECT uid
,tag
,tag_name
FROM T
WHERE tag IN ('人称', 'IP') ;
结果:
我的逻辑就是先把所有tag='领域'和tag为人称和IP的记录分别挑出来,用uid去left join 就可以剔除拥有tag人称、IP、领域,tag='领域'的记录,只保留单纯拥有领域的记录,再和拥有人称领域的部分union。(只能说结果虽对,但看起来傻傻的)
使用rank() over()函数
这个函数本身是用来分组排序的,在这里先给tag(文本)一个数字标识,要取的内容打成相同数字标识,不取的打另外的数字标识,再通过分组倒序排序(按数字标识)就可以很容易的剔除不想要的记录了。
SELECT t3.uid
,t3.tag
,t3.tag_name
FROM
(
SELECT t2.uid
,t2.tag
,t2.tag_name
,rank() over(partition by uid ORDER BY rn desc) AS rank
FROM
(
SELECT T.uid
,T.tag
,T.tag_name
,CASE WHEN tag = '领域' THEN 1 ELSE 2 END AS rn
FROM T
)t2
) t3
WHERE t3.rank = 1
结果:
rank() over()函数在这里起到了辅助剔除记录的作用。