Reputation: 55
I have two table:
One is a_tbl(uid, login_ip, login_time);
here ip is a 32 bit integer, time is the unix timestamp.
another is b_tbl(country, province, city, ip_from, ip_to)
I want to find out that each range in b_tbl
has how many unique uid in a_tbl
.
Notice that b_tbl
has a lot of unique rows, and a_tbl
may exist dunplicated uid.
Thank you very much.
Upvotes: 3
Views: 189
Reputation: 3383
SELECT city, count(uid)
FROM (
SELECT DISTINCT A.uid, B.city
FROM a_tbl A
LEFT JOIN b_tbl B
ON A.login_ip >= B.ip_from AND A.login_ip <= B.ip_to
)
GROUP BY city
Upvotes: 0
Reputation: 63
try this:
SELECT ip_from
, ip_to
, COUNT(distinct uid) as NumberOfUniqueUserId
FROM a_tbl
INNER JOIN b_tbl on login_ip BETWEEN ip_from AND ip_to
GROUP BY ip_from
, ip_to
Upvotes: 1
Reputation: 1547
To implement this, you can write a JOIN using BETWEEN as its predicate. That will develop the ranges; then, just count the items falling in each range. Something like this should get you started:
SELECT b_tbl.ip_From, b_tbl.ip_to, COUNT(a_tbl.login_ip)
FROM a_tbl
JOIN b_tbl ON a_tbl.login_ip BETWEEN b_tbl.ipfrom AND b_tbl.ip_to
GROUP BY b_tbl.ip_From, b_tbl.ip_to;
Upvotes: 2