/ [SQL] Vergleichen von Daten

Legithos · 02/26/2020, 17:47

Hallo zusammen,

ich bin mir nicht sicher, welchen SQL-Query für folgende Situation am Besten nutzen kann.

Ich habe eine Tabelle, welche die IP-Adressen aller User mit Zeitstempel speichert, wenn diese sich einloggen. Ich würde jetzt gerne im Hinblick auf Multiaccounts überprüfen, ob sich denn verschiedene User mit der gleichen IP (optional: in einer bestimmten Zeit) eingeloggt haben.

Code:

Tablename - users_ip

+-------+------------+----------+
|user_id|     ip     |   date   |
+-------+------------+----------+
|   1   |  127.0.0.1 |23.02.2020|
+-------+------------+----------+
|   1   |  194.3.2.6 |24.02.2020|
+-------+------------+----------+
|   2   |  127.5.5.5 |25.02.2020|
+-------+------------+----------+
|   1   |  127.0.0.1 |26.02.2020|
+-------+------------+----------+
|   4   |  194.3.2.6 |27.02.2020|
+-------+------------+----------+
|   3   |  127.5.5.5 |28.02.2020|
+-------+------------+----------+
|   5   |  127.5.5.5 |29.02.2020|
+-------+------------+----------+

Nun hätte ich gerne folgendes Ergebnis aus der Tabelle ausgelesen:

User 1 und 4 hatten die gleiche IP (optional User 1 am 24.02.2020, User 4 am 27.02.2020)
User 2, 3 und 5 hatten die gleiche IP (optional User 2 am 25.02.2020, User 3 am 28.02.2020 und User 5 am 29.02.2020).

Wäre super froh, wenn mir jemand helfen kann !

florian0 · 02/26/2020, 18:36

Code:

SELECT ip, GROUP_CONCAT ( DISTINCT user_id ) AS user_ids
FROM log
GROUP BY ip

Das wäre "Zeige alle User die diese IP jemals hatten".

Legithos · 02/26/2020, 19:14

Quote:
Originally Posted by florian0
Code:
SELECT ip, GROUP_CONCAT ( DISTINCT user_id ) AS user_ids
FROM log
GROUP BY ip
Das wäre "Zeige alle User die diese IP jemals hatten".

Was meinst du denn mit "diese IP jemals hatten" ? Welche IP ?

florian0 · 02/26/2020, 19:25

Die ausgegebene IP. Das GROUP BY ip gruppiert alle Einträge nach IP. d.H. jede IP kommt in der Ausgabe nur ein Mal vor. Das DISTINCT sorgt dafür, dass jede Id nur ein Mal vorkommt. Das macht die Ausgabe übersichtlicher.

Code:

+--------+------------+
|user_ids|     ip     |
+--------+------------+
|   1    |  127.0.0.1 |
+--------+------------+
|  1, 4  |  194.3.2.6 |
+--------+------------+
| 2, 3, 5|  127.5.5.5 |
+--------+------------+

Wenn du magst, kannst du den Query natürlich noch mit WHERE eingrenzen.

Legithos · 02/26/2020, 21:40

Habs verstanden danke - klappt auch! Kann ich denn mit einer WHERE-Bedingung noch dranpacken, dass ich nur Einträge will bei denen die IP mehrmals von unterschiedlichen user_ids verwendet wird ?

Gerade bekomme ich alle IPs, also auch die, welche nur von einem user verwendet werden.

EDIT:

Habs mit HAVING COUNT(user_id)>1 hinbekommen!

False · 02/26/2020, 21:47

Quote:

Originally Posted by Legithos

Habs verstanden danke - klappt auch! Kann ich denn mit einer WHERE-Bedingung noch dranpacken, dass ich nur Einträge will bei denen die IP mehrmals von unterschiedlichen user_ids verwendet wird ?

Gerade bekomme ich alle IPs, also auch die, welche nur von einem user verwendet werden.

Um das Ergebnis von group by einzuschränken nutzt man having:

Aber um deine Frage zu beantworten, ja es ist möglich :-)

Devsome · 02/27/2020, 01:25

Und immer hier drauf achten:

florian0 · 02/27/2020, 07:47

Quote:

Originally Posted by Legithos

Habs verstanden danke - klappt auch!

Man muss sich halt überlegen ob das GROUP BY , bzw gerade das GROUP_CONCAT für eine Weiterverarbeitung Sinn macht. Du bekommst als Ausgabe eine Auflistung die du in z. B. PHP erstmal wieder zerlegen musst.
Ggf. macht es Sinn nur die Anzahl durch GROUP BY zu berechnen und dann durch Folgequeries Details zu den einzelnen IPs zusammenzustellen.

Legithos · 02/27/2020, 17:28

Quote:

Originally Posted by florian0

Man muss sich halt überlegen ob das GROUP BY , bzw gerade das GROUP_CONCAT für eine Weiterverarbeitung Sinn macht. Du bekommst als Ausgabe eine Auflistung die du in z. B. PHP erstmal wieder zerlegen musst.
Ggf. macht es Sinn nur die Anzahl durch GROUP BY zu berechnen und dann durch Folgequeries Details zu den einzelnen IPs zusammenzustellen.

Da bin ich sogar schon dran

.

EDIT:

Wobei ich noch ein weiteres Problem hab .. Also aktuell sieht mein Query so aus

Code:

$sql = "SELECT ip, GROUP_CONCAT( DISTINCT ip_user_id) AS ip_user_id
FROM users_ip
WHERE ip_hint='0' GROUP BY ip
HAVING COUNT(DISTINCT ip_user_id)>1";

Ich bekomme zwar ein nettes Ergebnis, nur zeigt er mir nun etwas zu viel an.

Code:

+--------+------------+
|user_ids|     ip     |
+--------+------------+
|   1    |  127.0.0.1 |
+--------+------------+
| 1, 4, 1|  194.3.2.6 |
+--------+------------+
| 2, 3, 5|  127.5.5.5 |
+--------+------------+

Ich bekomme zB. bei der IP 194.3.2.6 die user_id 1 insgesamt 2x angezeigt. Was ich will ist ja nur zu wissen, ob sich zwei unterschiedliche IDs mit der gleichen IP einloggen. Wenn mir nun aber alle IDs zu einer IP x-fach angezeigt werden, wird das ziemlich viel pro IP

Ich habe folgende Datenbank:

Tabellenname - users_ip
- ip_id (die AI ID des Eintrages in die users_ip Tabelle)
- ip_user_id (fuer die userid der jeweiligen IP)
- ip (die eigentliche IP)
- ip_hint (wenn dieser 0 ist, soll es mir angezeigt werden)

Shadow992 · 02/28/2020, 18:30

Quote:
Originally Posted by Legithos
Da bin ich sogar schon dran .

EDIT:

Wobei ich noch ein weiteres Problem hab .. Also aktuell sieht mein Query so aus
Code:
$sql = "SELECT ip, GROUP_CONCAT( DISTINCT ip_user_id) AS ip_user_id
FROM users_ip
WHERE ip_hint='0' GROUP BY ip
HAVING COUNT(DISTINCT ip_user_id)>1";
Ich bekomme zwar ein nettes Ergebnis, nur zeigt er mir nun etwas zu viel an.
Code:
+--------+------------+
|user_ids|     ip     |
+--------+------------+
|   1    |  127.0.0.1 |
+--------+------------+
| 1, 4, 1|  194.3.2.6 |
+--------+------------+
| 2, 3, 5|  127.5.5.5 |
+--------+------------+
Ich bekomme zB. bei der IP 194.3.2.6 die user_id 1 insgesamt 2x angezeigt. Was ich will ist ja nur zu wissen, ob sich zwei unterschiedliche IDs mit der gleichen IP einloggen. Wenn mir nun aber alle IDs zu einer IP x-fach angezeigt werden, wird das ziemlich viel pro IP

Ich habe folgende Datenbank:

Tabellenname - users_ip
- ip_id (die AI ID des Eintrages in die users_ip Tabelle)
- ip_user_id (fuer die userid der jeweiligen IP)
- ip (die eigentliche IP)
- ip_hint (wenn dieser 0 ist, soll es mir angezeigt werden)

Gibt sicher schönere und performantere Möglichkeiten, aber eine relativ intuitive Lösung, wäre es einfach in einer Subquery alle doppelten Einträge rauszufiltern:

Code:

$sql = "SELECT ip, GROUP_CONCAT( DISTINCT ip_user_id) AS ip_user_id
FROM (SELECT DISTINCT ip_user_id,ip  FROM users_ip WHERE ip_hint='0') 
GROUP BY ip
HAVING COUNT(DISTINCT ip_user_id)>1";

(ungetestet, sollte aber funzen)

False · 02/28/2020, 19:44

Quote:
Originally Posted by Legithos
Da bin ich sogar schon dran .

EDIT:

Wobei ich noch ein weiteres Problem hab .. Also aktuell sieht mein Query so aus
Code:
$sql = "SELECT ip, GROUP_CONCAT( DISTINCT ip_user_id) AS ip_user_id
FROM users_ip
WHERE ip_hint='0' GROUP BY ip
HAVING COUNT(DISTINCT ip_user_id)>1";
Ich bekomme zwar ein nettes Ergebnis, nur zeigt er mir nun etwas zu viel an.
Code:
+--------+------------+
|user_ids|     ip     |
+--------+------------+
|   1    |  127.0.0.1 |
+--------+------------+
| 1, 4, 1|  194.3.2.6 |
+--------+------------+
| 2, 3, 5|  127.5.5.5 |
+--------+------------+
Ich bekomme zB. bei der IP 194.3.2.6 die user_id 1 insgesamt 2x angezeigt. Was ich will ist ja nur zu wissen, ob sich zwei unterschiedliche IDs mit der gleichen IP einloggen. Wenn mir nun aber alle IDs zu einer IP x-fach angezeigt werden, wird das ziemlich viel pro IP

Ich habe folgende Datenbank:

Tabellenname - users_ip
- ip_id (die AI ID des Eintrages in die users_ip Tabelle)
- ip_user_id (fuer die userid der jeweiligen IP)
- ip (die eigentliche IP)
- ip_hint (wenn dieser 0 ist, soll es mir angezeigt werden)

Irgendwas stimmt bei deinem Beitrag nicht, entweder deine Query oder dein Ergebniss.
Das Distinct im select part sollte schon dafür sorgen das keine Doppelten user ids angezeigt werden.
Du hast sogar ein distinct im having part, was dafür sorgt das keine Einträge angezeigt werden wo die Ips mehrmals geloggt wurde aber immer vom selben User.

Daher sollte deine Query so schon funktionieren.
Beispiel:

Legithos · 02/28/2020, 23:17

Ich hab den Fehler gefunden, habs mir mit der zweiten verschachtelten Query selbst verbockt !

Wirklich vielen lieben Dank euch !