MySQL实现ip_available表连续递增ip序列的分组行数统计

这是典型的SQL孤岛（连续序列）识别场景，可通过以下两种方案实现，根据你的MySQL版本选择即可：

版本1：MySQL 8.0+（支持窗口函数，推荐） #

如果你的ip_address存储的是字符串格式的IPv4地址（例如192.168.1.1），使用如下SQL：

WITH ordered_ip AS (
    SELECT 
        parent,
        ip_address,
        INET_ATON(ip_address) AS ip_num,
        ROW_NUMBER() OVER (PARTITION BY parent ORDER BY INET_ATON(ip_address)) AS rn
    FROM ip_available
)
SELECT 
    MIN(ip_address) AS ip_address_min,
    parent,
    COUNT(*) AS `count`
FROM ordered_ip
GROUP BY parent, ip_num - rn
ORDER BY parent, ip_address_min;

如果ip_address本身已经存储为整型数值，可直接简化为：

WITH ordered_ip AS (
    SELECT 
        parent,
        ip_address,
        ROW_NUMBER() OVER (PARTITION BY parent ORDER BY ip_address) AS rn
    FROM ip_available
)
SELECT 
    MIN(ip_address) AS ip_address_min,
    parent,
    COUNT(*) AS `count`
FROM ordered_ip
GROUP BY parent, ip_address - rn
ORDER BY parent, ip_address_min;

版本2：MySQL 5.7及更早版本（不支持窗口函数） #

通过用户变量实现相同逻辑：

SELECT 
    MIN(ip_address) AS ip_address_min,
    parent,
    COUNT(*) AS `count`
FROM (
    SELECT 
        parent,
        ip_address,
        @group_num := IF(@prev_parent = parent AND @prev_ip + 1 = ip_num, @group_num, @group_num + 1) AS group_id,
        @prev_parent := parent,
        @prev_ip := ip_num
    FROM (
        SELECT 
            parent,
            ip_address,
            -- 如果ip是整型存储，此处替换为 ip_address AS ip_num 即可
            INET_ATON(ip_address) AS ip_num
        FROM ip_available
        ORDER BY parent, ip_num
    ) AS sorted_ip,
    (SELECT @prev_parent := NULL, @prev_ip := NULL, @group_num := 0) AS init_vars
) AS grouped_ip
GROUP BY parent, group_id
ORDER BY parent, ip_address_min;

实现逻辑说明 #

• 核心思路利用了「连续递增的数值减去等差数列行号，差值保持不变」的特性，给同一连续段的IP分配相同的分组标识
• 先按parent分组排序，再识别连续段，即使同一个parent下存在多个不连续的IP段，也会分别统计不会合并

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