基于近3个月频次的ID维度IP排名需求

需求说明 #

• 按每个ID分组，基于近3个月内的IP登录频次（即Count_of_Logins_Three_Months_Prior字段）进行排名
• 频次最高的IP排名为1，频次越低排名越靠后
• 若多个IP的登录频次相同，则分配相同排名（并列排名后，后续排名不会跳跃，如两个并列第1，下一个为第2）

实现代码（SQL） #

不同SQL方言的窗口函数语法略有差异，以下是主流数据库的实现方式：

MySQL 8.0+/PostgreSQL/SQL Server #

SELECT
    ID,
    Login_TS_Three_Months_Prior,
    Login_TS,
    IP,
    Count_of_Logins_Three_Months_Prior,
    DENSE_RANK() OVER (PARTITION BY ID ORDER BY Count_of_Logins_Three_Months_Prior DESC) AS IP_Ranking
FROM
    你的输入表名;

说明：如果需要跳跃式排名（如两个并列第1后，下一个为第3），可将DENSE_RANK()替换为RANK()函数

旧版MySQL（无窗口函数） #

SELECT
    t1.ID,
    t1.Login_TS_Three_Months_Prior,
    t1.Login_TS,
    t1.IP,
    t1.Count_of_Logins_Three_Months_Prior,
    (SELECT COUNT(DISTINCT t2.Count_of_Logins_Three_Months_Prior)
     FROM 你的输入表名 t2
     WHERE t2.ID = t1.ID
       AND t2.Count_of_Logins_Three_Months_Prior >= t1.Count_of_Logins_Three_Months_Prior) AS IP_Ranking
FROM
    你的输入表名 t1
ORDER BY
    t1.ID, IP_Ranking;

示例输入输出表 #

输入表 #

输出表（使用DENSE_RANK()的结果） #

内容的提问来源于stack exchange，提问作者Stanleyrr