Van twee naar één query
Voor een analyse van mijn logfile heb ik de volgende vraag:
Ik wil bij een IP-adres de naam van het land van herkomst tonen.
Hiervoor heb ik twee werkende queries maar omdat ik dit in één tabel wil tonen moet ik één query maken/hebben.
Hoe voeg ik deze queries nu samen?
Moet ik gebruik maken van de JOIN-functie of een SUBQUERY?
Query 1, toont de basisgegevens:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
l.id AS id,
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel
FROM
sys__logfile AS l
WHERE
l.robot = 0 AND
YEAR(l.datum) = '$nJaar' AND MONTH(l.datum) = '$nMaand' AND DAY(l.datum) = '$nDag'
ORDER BY
datum
l.id AS id,
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel
FROM
sys__logfile AS l
WHERE
l.robot = 0 AND
YEAR(l.datum) = '$nJaar' AND MONTH(l.datum) = '$nMaand' AND DAY(l.datum) = '$nDag'
ORDER BY
datum
Query 2, toont het land van herkomst o.b.v. van een IP-adres:
Bron query: http://www.ip2nation.com/ip2nation/Sample_Scripts/Output_Full_Country_Name
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT
c.country
FROM
ip2nationCountries c,
ip2nation i
WHERE
i.ip < INET_ATON('l.ipadres')
AND
c.code = i.country
ORDER BY
i.ip DESC
LIMIT 0,1
c.country
FROM
ip2nationCountries c,
ip2nation i
WHERE
i.ip < INET_ATON('l.ipadres')
AND
c.code = i.country
ORDER BY
i.ip DESC
LIMIT 0,1
Wie kan/wil mij helpen?
George
Gewijzigd op 27/03/2015 14:28:23 door George van Baasbank
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT l.id AS id,
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel,
c.country AS country
FROM sys__logfile AS l
LEFT JOIN ip2nationCountries AS c
ON (l.ipadres = c.ip)
LEFT JOIN ipnation AS i
ON (c.code = i.country)
WHERE l.robot = 0
AND YEAR(l.datum) = '$nJaar'
AND MONTH(l.datum) = '$nMaand'
AND DAY(l.datum) = '$nDag'
ORDER BY datum
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel,
c.country AS country
FROM sys__logfile AS l
LEFT JOIN ip2nationCountries AS c
ON (l.ipadres = c.ip)
LEFT JOIN ipnation AS i
ON (c.code = i.country)
WHERE l.robot = 0
AND YEAR(l.datum) = '$nJaar'
AND MONTH(l.datum) = '$nMaand'
AND DAY(l.datum) = '$nDag'
ORDER BY datum
Wel raar dat je apart jaar, maand en dag moet opgeven, waarom niet gewoon een datum vergelijken met een datum?
Bedankt voor je suggestie. Helaas werkt het niet omdat jij velden hebt gebruikt die niet in de tabel voorkomen. Zoals c.ip
De reden voor de splitsing van de datum komt omdat het veld DATUM een DATETIME-veld is.n
Toevoeging op 27/03/2015 16:31:48:
Omdat het idee van Eddy niet werkt ben ik maar eens de query stap voor stap gaan opbouwen.
Bij het eerste toevoegen middels de LEF JOIN gaat het al fout.
Het gaat in principe om ca. 24 records uit de logfile die na de JOIN zo'n 1,1 miljoen records toont
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
l.id AS id,
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel,
i.country AS landcode
FROM
sys__logfile AS l
LEFT JOIN
ip2nation AS i
ON
i.ip < INET_ATON(l.ipadres)
WHERE
l.robot = 0 AND
YEAR(l.datum) = '2015' AND MONTH(l.datum) = '3' AND DAY(l.datum) = '24'
ORDER BY
i.ip DESC
l.id AS id,
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel,
i.country AS landcode
FROM
sys__logfile AS l
LEFT JOIN
ip2nation AS i
ON
i.ip < INET_ATON(l.ipadres)
WHERE
l.robot = 0 AND
YEAR(l.datum) = '2015' AND MONTH(l.datum) = '3' AND DAY(l.datum) = '24'
ORDER BY
i.ip DESC
Volgens mij ligt het aan de ON-clausule i.ip < INET_ATON(l.ipadres)
Wie ziet de manco??
Toevoeging op 27/03/2015 16:41:48:
en volgende stap toont het juiste aantal records maar is overal de landcode hetzelfde
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
l.id AS id,
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel,
i.country
FROM
sys__logfile AS l
LEFT JOIN
ip2nation AS i
ON
i.ip < INET_ATON(l.ipadres)
WHERE
l.robot = 0 AND
YEAR(l.datum) = '2015' AND MONTH(l.datum) = '3' AND DAY(l.datum) = '24'
GROUP BY
l.ipadres
ORDER BY
i.ip DESC
l.id AS id,
l.ipadres AS ipadres,
l.browser AS browser,
l.omschrijving AS omschrijving,
l.tablet AS tablet,
l.mobiel AS mobiel,
i.country
FROM
sys__logfile AS l
LEFT JOIN
ip2nation AS i
ON
i.ip < INET_ATON(l.ipadres)
WHERE
l.robot = 0 AND
YEAR(l.datum) = '2015' AND MONTH(l.datum) = '3' AND DAY(l.datum) = '24'
GROUP BY
l.ipadres
ORDER BY
i.ip DESC
Gewijzigd op 27/03/2015 16:50:16 door George van Baasbank
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
l.id,
l.ipadres,
l.browser,
c.country
FROM
sys__logfile l
JOIN
ip2nation i
ON i.ip < INET_ATON(l.ipadres)
JOIN
ip2nationCountry c
ON i.country = c.code
LEFT JOIN
ip2nation p
ON i.ip > p.ip AND p.ip < INET_ATON(l.ipadres)
WHERE p.ip IS NULL
l.id,
l.ipadres,
l.browser,
c.country
FROM
sys__logfile l
JOIN
ip2nation i
ON i.ip < INET_ATON(l.ipadres)
JOIN
ip2nationCountry c
ON i.country = c.code
LEFT JOIN
ip2nation p
ON i.ip > p.ip AND p.ip < INET_ATON(l.ipadres)
WHERE p.ip IS NULL
Ik ga er even vanuit dat die iptabel volledig is (wat ik betwijfel want ik heb er één met bijna 2x zoveel records)
Ik heb je er in een ander topic ook al op geattendeerd dat het vergelijken op functies over kolommen moet zien te vermijden. Hetzelfde geldt ook voor de join voorwaarden.
De juiste indexen en het kunnen gebruiken ervan zijn essentieel voor een goede performance.
Gewijzigd op 28/03/2015 18:32:29 door Ger van Steenderen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT SQL_NO_CACHE
l.id,
l.ipadres,
c.country,
INET_NTOA(i.ip) ip
FROM
sys__logfile l
JOIN
ip2nation i
ON i.ip < INET_ATON(l.ipadres)
JOIN
ip2nationcountries c
ON i.country = c.code
WHERE i.ip = (SELECT MAX(ip) FROM ip2nation p WHERE p.ip < INET_ATON(l.ipadres))
AND l.datum BETWEEN '2015-03-27 00:00:00' AND '2015-03-27 23:59:59'
>> 183 rows in 8.872 sec
l.id,
l.ipadres,
c.country,
INET_NTOA(i.ip) ip
FROM
sys__logfile l
JOIN
ip2nation i
ON i.ip < INET_ATON(l.ipadres)
JOIN
ip2nationcountries c
ON i.country = c.code
WHERE i.ip = (SELECT MAX(ip) FROM ip2nation p WHERE p.ip < INET_ATON(l.ipadres))
AND l.datum BETWEEN '2015-03-27 00:00:00' AND '2015-03-27 23:59:59'
>> 183 rows in 8.872 sec
Gewijzigd op 31/03/2015 14:09:48 door Ger van Steenderen
In jouw eerste reactie vroeg jij jezelf af of e.e.a. wel goed zou werken omdat jouw tabel eens zo groot was.
Ik kom er nu achter dat de versie die ik gebruik inderdaad niet de juiste landen weergeeft. Waarom weet ik niet maar er gaat e.e.a. mis.
Daarom de vraag aan jou: Waar heb jij de tabellen vandaan die wellicht wel beter zijn? Ik heb de tabellen gevonden op: http://www.ip2nation.com/ip2nation/Sample_Scripts/Output_Full_Country_Name
George
http://lite.ip2location.com/database-ip-country#ipv4-mysql