Frage zu Distinct/GroupBy (?)

Hallo,

Ein Table hat folgende Struktur:

id,irgendwas,timestamp (als UNIX-Timestamp)

mit Beispielinhalten:

1,abc,11111111
2,def,11111112
3,abc,11111113
4,abc,11111114
5,abc,11111115
6,abc,11111116
7,hij,11111117

Ich möchte jetzt doppelte Einträge filtern, ABER SO, dass nur
aufeinanderfolgende irgendwas-Daten gefiltert werden. Konkret soll also
rauskommen

1,abc,11111111
2,def,11111112
3,abc,11111113
7,hij,11111117

Wenn ich nur ein Distinct bzw. GroupBy mache, dann hab ich ABC nur
einmal mit der id 1, aber nicht mehr mit der ID 3 (4-6 sollen als
DARAUF FOLGENDE identische Zeilen nicht mehr angezeigt werden).
Wie mach ich das?=20

Danke euch!
Peter.

Re: Frage zu Distinct/GroupBy (?)

Peter Stoss wrote:
> Ein Table hat folgende Struktur:
>
> id,irgendwas,timestamp (als UNIX-Timestamp)
>
> mit Beispielinhalten:
>
> 1,abc,11111111
> 2,def,11111112
> 3,abc,11111113
> 4,abc,11111114
> 5,abc,11111115
> 6,abc,11111116
> 7,hij,11111117
>
> Ich möchte jetzt doppelte Einträge filtern, ABER SO, dass nur
> aufeinanderfolgende irgendwas-Daten gefiltert werden. Konkret soll also
> rauskommen
>
> 1,abc,11111111
> 2,def,11111112
> 3,abc,11111113
> 7,hij,11111117

Ich gehe mal davon aus, dass die Reihenfolge von id vorgegeben wird
(auch wenn das danach klingt, dass du dem Primary Key eine Bedeutung
gibts...)

Zweifachselfjoin:

SELECT a.id, a.irgendwas, a.timestamp
FROM tabelle AS a
LEFT JOIN tabelle AS b ON b.id>a.id
LEFT JOIN tabelle AS c ON c.id AND c.id>a.id
WHERE c.id IS NULL
AND b.irgendwas!=a.irgendwas
ORDER BY a.id

Sollte funktionieren (ungetestet)

Wähle alle Einträge aus der Tabelle.
Zu jedem einzelnen hole alle mit einer größeren id.
Wähle daraus denjenigen, der keine kleinere id besitzt (das ist der
nächst größere nach a).
Dieser soll nur ausgegeben werden, wenn sich b.irgendwas von a.irgendwas
unterscheidet.

Ich hoffe ich hab jetzt keinen allzugroßen Bock geschossen.

Grüße
Kai

--
This signature is left as an exercise for the reader.

Re: Frage zu Distinct/GroupBy (?)

Hm, um ehrlich zu sein, sorgt das ganze zwar für keinen Fehler, aber
führt auch zu keinem Ergebnis, da die Load meines Servers dabei steigt
und MySQL gar keine Rückmeldung gibt... - bei deiner Funktion, die ich
einigermaßen verstehe, scheint sich irgendwas totzurechnen (bislang
sinds nur ca. 60.000 Einträge in dem Table). Hmmmmmmmmm :-(

Re: Frage zu Distinct/GroupBy (?)

Moin,
*Peter Stoss* schrub:
> Hm, um ehrlich zu sein, sorgt das ganze zwar für keinen Fehler, aber
> führt auch zu keinem Ergebnis, da die Load meines Servers dabei steigt
> und MySQL gar keine Rückmeldung gibt... - bei deiner Funktion, die ich
> einigermaßen verstehe, scheint sich irgendwas totzurechnen (bislang
> sinds nur ca. 60.000 Einträge in dem Table). Hmmmmmmmmm :-(

Schick mal den Query, der den Load so in die Höhe treibt, und die
Ausgabe des Querys, wenn du ihm ein EXPLAIN voranstellst.
mfg
Nico

--
www.buchtips.net - Rezensionen online

Re: Frage zu Distinct/GroupBy (?)

mysql> explain log;
+-------------------------+-------------+------+-----+------ ---+-----------=
-----+
| Field | Type | Null | Key | Default | Extra
|
+-------------------------+-------------+------+-----+------ ---+-----------=
-----+
| id | int(11) | | PRI | NULL |
auto_increment |
| userid | int(11) | | | 0 |
|
| irgendwas | varchar(10) | | | |
|
| timestamp | int(11) | | | 0 |
|


Der Query, der die Load in die Höhe treibt und keinen Output liefert:

SELECT
a.id,
a.userid,
a.irgendwas,
a.timestamp
FROM log AS a
LEFT JOIN log AS b ON b.id>a.id
LEFT JOIN log AS c ON c.ida.id
WHERE c.id IS NULL
AND b.userid!=3Da.userid
AND userid =3D '8';


... und mit einem EXPLAIN vorneweg als Ausgabe (das kannte ich gar
nicht *duck*):

+-------+------+---------------+------+---------+------+---- ---+-----------=
-----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+---- ---+-----------=
-----------------------------------------------+
| b | ALL | PRIMARY | NULL | NULL | NULL | 63947 |
|
| a | ALL | PRIMARY | NULL | NULL | NULL | 63947 | Range
checked for each record (index map: 1) |
| c | ALL | PRIMARY | NULL | NULL | NULL | 63947 | Range
checked for each record (index map: 1); Not exists |
+-------+------+---------------+------+---------+------+---- ---+-----------=
-----------------------------------------------+

Gruß. Peter!

Re: Frage zu Distinct/GroupBy (?)

Peter Stoss wrote:
> Hm, um ehrlich zu sein, sorgt das ganze zwar für keinen Fehler, aber
> führt auch zu keinem Ergebnis, da die Load meines Servers dabei steigt
> und MySQL gar keine Rückmeldung gibt... - bei deiner Funktion, die ich
> einigermaßen verstehe, scheint sich irgendwas totzurechnen (bislang
> sinds nur ca. 60.000 Einträge in dem Table). Hmmmmmmmmm :-(

Eigen*lich sind 60.000 Einträge nicht viel. Welche Indizes hast du gesetzt?

Ein Denkfehler war noch drin:

Ich habe gegen den nächst nachfolgenden Datensatz verglichen. Dadurch
kam der letzten einer Reihe heraus. Außerdem fehlte die Ausgabe des
letzten Elements.

Die folgende Variante [1] vergleicht gegen den Vorgänger und gibt
zusätzlich die erste Zeile als Start mit aus.

Mir persönlich sind da ein paar type:ALL zu viel, ich wüsste jetzt
allerdings nicht wie die weggehen sollten.

Eine zweite Variante wäre, auf die OR-Bedingung zu verzichten, und statt
dessen mittels UNION den ersten Datensatz hinzuzuholen.

Man könnte auch darüber nachdenken, ob es auch mit Subselects eine
Lösung gibt.

[1]
mysql> EXPLAIN SELECT a.* FROM tabelle AS a LEFT JOIN tabelle AS b ON
b.idb.id AND c.id IS NULL AND (a.irgendwas!=b.irgendwas or b.irgendwas IS NULL)\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: id
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Range checked for each record (index map: 0x1)
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: id
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Range checked for each record (index map: 0x1); Not exists

USE test;
DROP TABLE IF EXISTS `tabelle`;
CREATE TABLE `tabelle` (
`id` int(10) unsigned NOT NULL,
`irgendwas` char(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `tabelle` VALUES
(1,'abc'),(2,'def'),(3,'abc'),(4,'abc'),(5,'abc'),(6,'abc'), (7,'hij');

--
This signature is left as an exercise for the reader.

Re: Frage zu Distinct/GroupBy (?)

Kai Ruhnau wrote:
> Peter Stoss wrote:
>
>> Hm, um ehrlich zu sein, sorgt das ganze zwar für keinen Fehler, aber
>> führt auch zu keinem Ergebnis, da die Load meines Servers dabei steigt
>> und MySQL gar keine Rückmeldung gibt... - bei deiner Funktion, die ich
>> einigermaßen verstehe, scheint sich irgendwas totzurechnen (bislang
>> sinds nur ca. 60.000 Einträge in dem Table). Hmmmmmmmmm :-(


+-------+------+---------------+------+---------+------+---- ---+-------------------------------------------------------- --+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+---- ---+-------------------------------------------------------- --+
| b | ALL | PRIMARY | NULL | NULL | NULL | 63947 |
|
| a | ALL | PRIMARY | NULL | NULL | NULL | 63947 | Range
checked for each record (index map: 1) |
| c | ALL | PRIMARY | NULL | NULL | NULL | 63947 | Range
checked for each record (index map: 1); Not exists |
+-------+------+---------------+------+---------+------+---- ---+-------------------------------------------------------- --+

>
>
> Eigen*lich sind 60.000 Einträge nicht viel. Welche Indizes hast du gesetzt?

60000 * 60000 * 60000 sind aber ne ganze Menge

>
> Ein Denkfehler war noch drin:

Ich wage mal zu behaupten, daß es noch mehr sind aber dazu fehlt mir die
Zeit.

> mysql> EXPLAIN SELECT a.* FROM tabelle AS a LEFT JOIN tabelle AS b ON
> b.idb.id AND c.id > IS NULL AND (a.irgendwas!=b.irgendwas or b.irgendwas IS NULL)\G

Ein Join über 3 Tabellen, eine Where-Clause die nicht (irgendwas) auf
Felder mit Indizes geht, irgendwie bereitet mir das alles furchtbare
Bauchschmerzen, ich hoffe, der MySQL Server des Delinquenten ist noch zu
retten (mysqladmin proc - mysqladmin kill TASK, notfalls mysql server
restarten oder hoffen, daß der Query irgendwann zuende ist).

Lass besser mal die Finger von den Queries hier oder teste erst mal auf
einer development maschine. Ich hab jedenfalls ein mieses Gefühl aber
ich will gleich nach Hause :)
>
> USE test;
> DROP TABLE IF EXISTS `tabelle`;
> CREATE TABLE `tabelle` (
> `id` int(10) unsigned NOT NULL,
> `irgendwas` char(10) NOT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> INSERT INTO `tabelle` VALUES
> (1,'abc'),(2,'def'),(3,'abc'),(4,'abc'),(5,'abc'),(6,'abc'), (7,'hij');
>

Bist du sicher gegangen, daß der OP InnoDB verwendet oder den Support
für InnoDB installiert hat?


gruß, Frank

Re: Frage zu Distinct/GroupBy (?)

Frank Schenk schrieb:

> 60000 * 60000 * 60000 sind aber ne ganze Menge

Das ist der springende Punkt. Ich habe gearde mal nen Test-Table wie
von Kai geschrieben aufgebaut und seine Funktion drüberlaufen lassen.
Läuft soweit... aber mit ner handvoll Datensätze, wie du sie auch
getestet hast.
Ich habe die Zahl mal auf 1000 hochgestockt und siehe da, es dauert
bereits viele Sekunden bis das Ergebnis zurückkommt... bei 60.000 also
kein Wunder, dass die Maschine keucht... und wenn es irgendwann in den
6- und 7-stelligen Bereich geht, komme ich damit also leider nicht
weiter!

> Ein Join über 3 Tabellen, eine Where-Clause die nicht (irgendwas) auf
> Felder mit Indizes geht, irgendwie bereitet mir das alles furchtbare
> Bauchschmerzen, ich hoffe, der MySQL Server des Delinquenten ist noch zu
> retten (mysqladmin proc - mysqladmin kill TASK, notfalls mysql server
> restarten oder hoffen, daß der Query irgendwann zuende ist).

Dazu hat man ja Testsysteme :-)

> Ich hab jedenfalls ein mieses Gefühl aber

Hast du denn eine bessere Lösung für mich ? Die Sache von Kai ist
leider nicht praktikabel, da dabei der Prozessor durchschmoren und der
Anwender leicht ungehalten werden könnte.

Gruß
Peter.

Re: Frage zu Distinct/GroupBy (?)

Frank Schenk wrote:
> Kai Ruhnau wrote:
>> Peter Stoss wrote:
>>
>>> Hm, um ehrlich zu sein, sorgt das ganze zwar für keinen Fehler, aber
>>> führt auch zu keinem Ergebnis, da die Load meines Servers dabei steigt
>>> und MySQL gar keine Rückmeldung gibt... - bei deiner Funktion, die ich
>>> einigermaßen verstehe, scheint sich irgendwas totzurechnen (bislang
>>> sinds nur ca. 60.000 Einträge in dem Table). Hmmmmmmmmm :-(
>
>
> +-------+------+---------------+------+---------+------+---- ---+-------------------------------------------------------- --+
>
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
> +-------+------+---------------+------+---------+------+---- ---+-------------------------------------------------------- --+
>
> | b | ALL | PRIMARY | NULL | NULL | NULL | 63947 |
> |
> | a | ALL | PRIMARY | NULL | NULL | NULL | 63947 | Range
> checked for each record (index map: 1) |
> | c | ALL | PRIMARY | NULL | NULL | NULL | 63947 | Range
> checked for each record (index map: 1); Not exists |
> +-------+------+---------------+------+---------+------+---- ---+-------------------------------------------------------- --+

Ja, schrieb ich auch.

>> Eigen*lich sind 60.000 Einträge nicht viel. Welche Indizes hast du
>> gesetzt?
>
> 60000 * 60000 * 60000 sind aber ne ganze Menge

Die Aussage, dass 60.000 Datensätze als Grundmenge nicht viel sind, bleibt.

>> Ein Denkfehler war noch drin:
>
> Ich wage mal zu behaupten, daß es noch mehr sind aber dazu fehlt mir die
> Zeit.

Angenehm konstruktiv; vielen Dank.

>> mysql> EXPLAIN SELECT a.* FROM tabelle AS a LEFT JOIN tabelle AS b ON
>> b.idb.id AND c.id >> IS NULL AND (a.irgendwas!=b.irgendwas or b.irgendwas IS NULL)\G
>
> Ein Join über 3 Tabellen, eine Where-Clause die nicht (irgendwas) auf
> Felder mit Indizes geht, irgendwie bereitet mir das alles furchtbare
> Bauchschmerzen, ich hoffe, der MySQL Server des Delinquenten ist noch zu
> retten (mysqladmin proc - mysqladmin kill TASK, notfalls mysql server
> restarten oder hoffen, daß der Query irgendwann zuende ist).

Sie tut das, was sie soll - tatsächlich!
Zum Thema Index: Ich hatte mit den Indizes ein wenig rumgespielt.
Dass der Index auf ID sein muss, sollte klar sein. Auf der anderen Seite
bringt ein Index auf `irgendwas` egal ob als kombinierter mit ID oder
als separater gar nichts.

> Lass besser mal die Finger von den Queries hier oder teste erst mal auf
> einer development maschine. Ich hab jedenfalls ein mieses Gefühl aber
> ich will gleich nach Hause :)

Gut, und damit das hier nicht nur in Aufregen über die überflüssige
Antwort endet: Hier eine effizientere Lösung:

mysql> SET @i=0;
mysql> SET @last_irgendwas='';

mysql> SELECT @i:=@i+IF(@last_irgendwas!=irgendwas,1,0),
@last_irgendwas:=irgendwas, sub_tabelle.* FROM (SELECT * FROM tabelle
ORDER BY id) AS sub_tabelle GROUP BY 1 ORDER BY id;

Bis auf die Tatsache, dass es nur einen beliebigen Datensatz aus der
Reihe ergibt und dass das GROUP BY-Verhalten nicht standardkonform ist,
ist dies eine Variante ohne join.

Die einzige Frage, die sich mir stellt ist, ob garantiert ist, dass die
Datensätze aus dem Subselect sortiert herauskommen? Ohne diese Annahme
sehe ich für eine effizientere Lösung schwarz.

>> USE test;
>> DROP TABLE IF EXISTS `tabelle`;
>> CREATE TABLE `tabelle` (
>> `id` int(10) unsigned NOT NULL,
>> `irgendwas` char(10) NOT NULL,
>> PRIMARY KEY (`id`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>> INSERT INTO `tabelle` VALUES
>> (1,'abc'),(2,'def'),(3,'abc'),(4,'abc'),(5,'abc'),(6,'abc'), (7,'hij');
>>
>
> Bist du sicher gegangen, daß der OP InnoDB verwendet oder den Support
> für InnoDB installiert hat?

Nein, der OP hat bereits Daten auf denen er rumspielt (zugegebenermaßen
zuviele zum rumspielen) - diese Daten sind für andere, um eine
Testgrundlage zu haben. Bei mir ist InnoDB als Defaulttabelentyp
eingestellt - ich kann mich nur mit Tipaufwand dagegen wehren und dafür
war mir die Zeit zu schade. Das entfernen des ENGINE=InnoDB kostet nicht
wirklich Zeit und wird auch mit einer ordentlichen Fehlermeldung
quittiert, falls es nicht vorhanden ist. Der OP sagt gar nichts über die
eingesetzt Version. Insbesondere die Frage, ob ENGINE oder CHARSET
überhaupt verstanden werden (MySQL>=4.1) ist damit auch nicht
beantwortbar. Und schließlich: alle aktuellen MySQL-Versionen, die
ausgeliefert werden, haben InnoDB standardmäßig mit an Bord und InnoDB
wird im configure-Script auch standardmäßig integriert. Damit bleibt
meines Erachtens eher nur der Fall übrig, das es in der
Konfigurationsdatei abgestellt ist.

Grüße
Kai

--
This signature is left as an exercise for the reader.

Re: Frage zu Distinct/GroupBy (?)

"Peter Stoss" wrote:

[ Lauflängenkompression einer Zeitreihe ]

> Wie mach ich das?

Idealerweise nicht in SQL. In einer Programmiersprache deiner Wahl
macht der folgende (fürchterlich triviale) Algorithmus das Gewünschte:

1. Initialisierung: $last := "Wert der garantiert nie vorkommt"
2. while read($wert, $timestamp)
3. if ($wert = $last) then 6.
4. write($wert, $timestamp)
5. $last := $wert
6. end while

Wenn du möchtest, kannst du das sicher mit einem on-INSERT-Trigger
implementieren (als Übungsaufgabe für den Leser ;-)


XL

Re: Frage zu Distinct/GroupBy (?)

Axel Schwenke schrieb:

> Idealerweise nicht in SQL.

Unter SQL gibt es also keinen effizienten Lösungsansatz, wenn ich das
richtig interpretiere

> In einer Programmiersprache deiner Wahl
> macht der folgende (fürchterlich triviale) Algorithmus das Gewünschte:

Darauf muss ich dann, wenn es mit SQL nicht effizient und schnell geht,
wohl drauf zurückgreifen... die Frage ist, wie schnell PHP (darauf
läufts dann hinaus) ein paar Millionen Datensätze handlen kann.

Peter.

Re: Frage zu Distinct/GroupBy (?)

Peter Stoss wrote:

> id,irgendwas,timestamp (als UNIX-Timestamp)
>
> mit Beispielinhalten:
>
> 1,abc,11111111
> 2,def,11111112
> 3,abc,11111113
> 4,abc,11111114
> 5,abc,11111115
> 6,abc,11111116
> 7,hij,11111117
>
> Ich möchte jetzt doppelte Einträge filtern, ABER SO, dass nur
> aufeinanderfolgende irgendwas-Daten gefiltert werden. Konkret soll also
> rauskommen
>
> 1,abc,11111111
> 2,def,11111112
> 3,abc,11111113
> 7,hij,11111117

Hm, habe mal so ähnlich getestet:

create temporary table t (a int, b char(20), c int);
insert into t values (1,'abc',1),(2,'def',2),(3,'abc',4),(4,'abc',9)
(5,'def',1);
set @last='';
select *,@last:=b from t where b != @last;
->
*************************** 1. row ***************************
a: 1
b: abc
c: 1
@last:=b: abc
*************************** 2. row ***************************
a: 2
b: def
c: 2
@last:=b: def
*************************** 3. row ***************************
a: 3
b: abc
c: 4
@last:=b: abc
*************************** 4. row ***************************
a: 5
b: def
c: 1
@last:=b: def
4 rows in set (0,00 sec)

Ist es das?

Evtl. wäre noch ein order by a (bzw. in Deinem Fall by id) hilfreich...


Thomas
--
Alle Völker lachen auf unserer Erde, aber selten über die
gleichen Dinge. (Pierre Daninos)