如何用T-SQL对超5万行用户IP地址匿名化并分组统计

基础版（IP分组≤26个） #

直接在原分组统计逻辑上，给每个IP分配A、B、C这类单字母标识，按记录数从多到少排序：

SELECT 
    CHAR(64 + ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC)) AS 匿名IP标识,
    COUNT(*) AS 记录数
FROM 
    [table]
GROUP BY 
    IPADDRESS
ORDER BY 
    记录数 DESC;

简单说明 #

• ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC)：按记录数从多到少给每个IP分组分配唯一序号（1、2、3...）
• CHAR(64 + 序号)：ASCII码中65对应'A'，所以64+1得到'A'，以此类推生成字母标识

进阶版（IP分组超过26个，支持AA、AB格式） #

如果IP分组数量超过26，基础版会生成非字母字符，这时可以生成类似Excel列名的多字母标识：

WITH IPStats AS (
    SELECT 
        IPADDRESS,
        COUNT(*) AS 记录数,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS 序号
    FROM 
        [table]
    GROUP BY 
        IPADDRESS
),
LetterGenerator AS (
    SELECT 
        序号,
        记录数,
        -- 生成多字母标识的逻辑
        CASE 
            WHEN 序号 <= 26 THEN CHAR(64 + 序号)
            ELSE CHAR(64 + ((序号 - 1) / 26)) + CHAR(64 + ((序号 - 1) % 26) + 1)
        END AS 匿名IP标识
    FROM 
        IPStats
)
SELECT 
    匿名IP标识,
    记录数
FROM 
    LetterGenerator
ORDER BY 
    记录数 DESC;

说明 #

• 先通过IPStats临时表获取每个IP的记录数和排序后的序号
• 当序号≤26时直接转单字母；超过26时，通过除法和取余拆分出多字母的每一位，比如27号对应AA，28号对应AB，以此类推

额外提醒 #

• 替换代码中的[table]为你的实际表名
• 如果需要同一IP每次查询都对应固定标识（不会因排序变化改变），可以把ROW_NUMBER()换成DENSE_RANK()，或者提前创建IP与标识的映射表，查询时关联该表（适合长期共享数据的场景）

内容的提问来源于stack exchange，提问作者Kadir Çolak