需要 IP 重复查询

社区 数据与报告
1

你好，我在论坛、GitHub 和谷歌上搜索了，但未能找到用于搜索重复 IP 地址的 #plugin:data-explorer 查询语句。有人可以分享一个吗？

2 
WITH users_per_ip AS (
    SELECT
        COUNT(1) AS user_count,
        u.registration_ip_address AS ip,
        MAX(u.created_at) AS last_create,
        MIN(u.created_at) AS first_create,
        (MAX(u.created_at) - MIN(u.created_at)) AS diff,
        CASE 
            WHEN (MAX(u.suspended_at) IS NOT NULL 
                  OR MAX(u.silenced_till) IS NOT NULL) 
            THEN 1 
            ELSE 0 
        END AS bad
    FROM users u
    GROUP BY ip
)

SELECT
    u.id AS user_id,
    DATE_TRUNC('day', u.created_at)::DATE AS created,
    DATE_TRUNC('day', upi.diff) AS days,
    bad,
    upi.ip AS ip_address
FROM users_per_ip upi
JOIN users u
    ON u.registration_ip_address = upi.ip
WHERE upi.user_count > 1
ORDER BY upi.last_create DESC
3

谢谢，运行完美！如果我想获取最后使用的 IP 地址而不是注册时使用的 IP 地址，需要修改哪些内容？

4

ip_address 而非 registration_ip_address。