SQL code zum Prüfen auf Datenänderung

[braini4c]

Hallo,

ich hätte eine Frage bezüglich einer Tabelle mit folgender Struktur:

table1
id wert datum
1, 5, 30.12.2008
1, 5, 31.12.2008
1, 5, 1.1.2009
2, 3, 30.12.2008
2, 8, 31.12.2008
2, 20, 1.1.2009
3, 4, 30.12.2008
3, 10, 31.12.2008
3, 10, 1.1.2009
...

Ist es möglich eine SQL Abfrage zu schreiben die mir ausgibt seit wieviel Tagen sich der 'wert' einer 'ID'# nicht geändert hat ? (Pro Tag gibt es einen Datensatz)

In diesem falle also:
ID erg
1 3
2 1
3 2

Das einzige was ich bisher hinbekommen habe war etwas, was zumindest den fall ID 1 erschlägt, wenn sich der Wert noch nie geändert hat:

SQL-Code:

 Select ID,Count(datum) from table1 group by id having Max(wert)=Min(wert);

Kann man das irgendwie auf den allgemeineren Fall erweitern?
(ich möchte ungern ein script schreiben, was die ID alle einzeln durchgeht, da es sehr viele Datensätze sind)
Wäre schön wenn mir jemand einen Tip geben könnte.

Viell. noch als Ergänzung: ich habe auch noch eine table2 in der u.a. der jeweils aktuelle 'wert' und die 'ID' drinsteht.

MfG
Julian

[omata]

Vielleicht so...

SQL-Code:

SELECT id, DAY(CURRENT_TIMESTAMP - MAX(datum)) - 1 AS tage
FROM table1 
GROUP BY id

[braini4c]

sry falls ich mich unklar ausgedrückt haben sollte.
Aber jeder Datensatz bekommt ein update pro Tag. Egal ob sich 'wert' ändert oder nicht, daher kann ich nicht nach dem letzten Datum suchen und es mit dem heutigen vergleichen.
Mfg
Julian

[omata]

Ups, sorry habe nicht genau gelesen...

Neuer Vorschlag...

SQL-Code:

SELECT id, MIN(datum) datum, TO_DAYS(CURRENT_TIMESTAMP) - TO_DAYS(MIN(datum)) AS tage
FROM tabelle1 x
WHERE EXISTS (SELECT *
              FROM tabelle1 y
              WHERE EXISTS (SELECT *
                            FROM tabelle1 z
                            WHERE datum = (SELECT MAX(datum)
                                           FROM tabelle1
                                           WHERE id = z.id)
                              AND id = y.id
                              AND wert <> y.wert)
                AND id = x.id
              GROUP BY id
              HAVING x.datum > MAX(datum))
GROUP BY id

Edit: Hier nochmal ein weiterer Vorschlag, für den Sonderfall, dass es keine Änderungen innerhalb einer ID gab...

SQL-Code:

SELECT id, MIN(datum) datum, TO_DAYS(CURRENT_TIMESTAMP) - TO_DAYS(MIN(datum)) AS tage
FROM tabelle1 x
WHERE EXISTS (SELECT *
              FROM tabelle1 y
              WHERE (   EXISTS (SELECT *
                                FROM tabelle1 z
                                WHERE datum = (SELECT MAX(datum)
                                               FROM tabelle1
                                               WHERE id = z.id)
                                  AND id = y.id
                                  AND wert <> y.wert)
                     OR NOT EXISTS (SELECT *
                                    FROM tabelle1
                                    WHERE id = y.id
                                      AND wert <> y.wert))
                AND id = x.id
              GROUP BY id
              HAVING x.datum > MAX(datum)
                  OR (SELECT COUNT(DISTINCT wert)
                      FROM tabelle1
                      WHERE id = y.id) = 1)
GROUP BY id

[alzaimar]

Hier ist mein Beitrag (in T-SQL):

SQL-Code:

select distinct id, cnt 
  from (
    select id, wert, count (*) as cnt 
     from test
    group by id,wert
    ) x 
where x.cnt = (
  select max (cnt) from (
    select id, wert, count (*) as cnt 
     from test
    group by id,wert
    ) y where y.id=x.id
  )
order by 1

oder mit einer temporären Tabelle (übersichtlicher, geht aber in MySQL vermutlich nicht)

SQL-Code:

declare @Table table (id int, wert int, cnt int)
insert into @Table
select id, wert, count (*) as cnt from test group by id,wert

select distinct id, cnt 
  from @Table x 
  where x.cnt = (
  select max (cnt) from @Table y where x.id = y.id
  )
order by 1

[omata]

Und wo sind jetzt die Tage :gruebel:

[alzaimar]

Zitat:

Zitat von braini4c

In diesem falle also:
ID erg
1 3
2 1
3 2

Wollte er die auch? :gruebel:

[edit]Fragezeichen eingefügt---[/edit]

[omata]

Achso, ich dachte bezogen auf das aktuelle Datum.

[braini4c]

Hallo,

erstmal vielen Danke für die Antworten. Leider scheint es noch nicht richtig zu funktionieren.

@omata: deinen Code habe ich nach >1h laufzeit abgebrochen (habe etwa 12000 ID's mit je etwa 10 Tage Daten), kann also nicht sagen ob es funktioniert. (Es lief auf dem localhost des servers, der zugegeben nicht der schnellste ist, aber dauert das wirklich so lange??)

@alzaimar: leider bringt mir der Query Browser beim ausführen des codes den fehler:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as cnt
from table1
group by id,wert
) x
where x.' at line 3

leider konnte ich nicht sehen woran es genau liegt. Hast Du eine Idee?

Nur um nochmal klarzumachen was ich möchte:
Im prinzip will ich wissen wielange (=seit wieviel Tagen) hat sich der 'wert' pro 'ID' NICHT geändert.

alternativ habe ich mir schon überlegt einen unique key auf den wert und die ID zu setzen... dann passiert nichts beim Insert into und es wird kein neuer Datensatz eingefügt mit neuem Datum... Dann wäre es einfach eine suche "where Date>'xy' and date<'xyz'"

Mfg
Julian

[omata]

Meine Abfrage greift ja auch sehr oft auf deine Tabelle. Hast du da keinen Primärschlüssel definiert?

Naja, meine Abfrage ist recht komplex. Ich habe wohl nicht wirklich verstanden, was du da eigentlich haben möchtest und wohl zu kompliziert gedacht.

Aber vielleicht ist die Abfrage von alzaimar ja das was du haben möchtest...

Entferne mal die insinnigen Leerzeichen hinter den Funktionsnamen...

SQL-Code:

select distinct id, cnt
from (select id, wert, count(*) as cnt
      from tabelle1
      group by id,wert) x
where x.cnt = (select max(cnt) 
               from (select id, wert, count(*) as cnt
                     from tabelle1
                     group by id, wert) y 
               where y.id = x.id)
order by 1

[alzaimar]

Halt! In meiner Query ist ein kleiner Schusseligkeitsfehler: Ich zähle die Anzahl der Tage, aber wir wollen ja die Differenz zwischen dem niedrigsten und höchsten Datum, zwischen dem sich der Wert einer ID *nicht* geändert hat. Meine Query funktioniert also nur (und nur dann!), wenn an jedem Tag ein Eintrag in der Tabelle existiert.

Bezogen auf meinen Ansatz, der eine temporäre Tabelle verwendet, würde '@Table' also wie folgt initialisiert:

SQL-Code:

insert into @Table
select id, wert, 1 + DateDiff (day, min (Datum), max (Datum)) as Tage 
  from test
group by id,wert

Das ist -wohl gemerkt- Microsoft-SQL, also nicht kompatibel zu MySQL. Falls Du mit temporären Tabellen arbeiten kannst, müsstest du die Syntax nur entsprechend anpassen. Die hier verwendete Funktion 'DateDiff' berechnet die Anzahl der Tage zwischen den beiden Tagen. Ich hoffe, MySQL hat eine entsprechende Funktion.

Bezüglich der schwächelnden Performance kann ich nur omata beipflichtet, das hier offenbar ein Index fehlt. Noch was: Temporäre Tabellen wirken oft Wunder, speziell, wenn sie -wie hier- eine Query stark vereinfachen, die sonst eine komplexe Subquery mehrmals ausführen würde.
Ich erwarte im Übrigen eine Abarbeitungszeit (wenn der Index auf ID+Wert gesetzt ist) von unter einer Sekunde, falls die Query syntaktisch korrekt ist.

Entschuldige, das ich kein MySQL kann, aber ich denke, die Gurus kriegen das hin.

[omata]

Leider kann MySQL eine temporäre Tabelle nur einmal in einer Abfrage verwenden.

@alzaimar: Ist cnt jetzt tage?

SQL-Code:

select distinct id, tage
from (select id, wert, datediff(min(Datum), max(Datum)) AS tage 
      from tabelle1
      group by id, wert) x
where x.tage = (select max(tage) 
                from (select id, wert, datediff(min(Datum), max(Datum)) AS tage 
                      from tabelle1
                      group by id, wert) y
                where y.id = x.id)
order by 1

Zitat:

Zitat von alzaimar

...Noch was: Temporäre Tabellen wirken oft Wunder, speziell, wenn sie -wie hier- eine Query stark vereinfachen, die sonst eine komplexe Subquery mehrmals ausführen würde.

Ich danke dir für diesen mutigen Hinweis, ich verwende diesen Denkansatz auch häufiger und bin immer wieder positiv überrascht. Für solche Aussagen wird man hier ja auch häufiger mal gesteinigt. Aber wer es nicht glauben will, dem ist eben nicht mehr zu helfen.

MSSQL ist in dieser Hinsicht einfach performanter und flexibler. Leider ist MySQL hier etwas hinterweltlich.

Zitat:

Zitat von alzaimar

...Ich erwarte im Übrigen eine Abarbeitungszeit (wenn der Index auf ID+Wert gesetzt ist) von unter einer Sekunde, falls die Query syntaktisch korrekt ist.

In MsSQL ja, in MySQL wäre ich mit solcher Aussage vorsichtig. Da dauert eine Abfrage, die MSSQL in 0 Sekunden erledigt, eventuell schonmal einige Minuten.

[globetrotter77]

Dafür werde ich jetzt wahrscheinlich gesteinigt, aber ich glaube, hier einen typischen Fall völlig überflüssiger SQL-Euphorie vorzufinden!
Was wollt ihr mit solchen SQL-Statements sparen?
Zeit? Aufwand?
Das wird nicht gehen!

Es geht doch wirklich viel einfacher.
Einfach alle Datensätze genau einmal lesen,
nämlich sortiert nach ID (aufsteigend) und Datum (absteigend)
mit weniger kommt auch das raffinierteste SQL-Statement nicht aus.
Dann lässt sich alles ganz easy im Programm selbst auswerten ... :-D

[Sir Rufo]

Ist das raffiniert genug? ;)

SQL-Code:

SELECT id, COUNT(*)
FROM table1
JOIN table2 
ON table1.id=table2.id
AND table1.wert=table2.wert
GROUP BY id

Allerdings gibt es hier eine Einschränkung:

Der Wert darf niemals wieder einen historischen Wert annehmen. In deinem Beispiel sieht es so aus, als ob der Wert sich immer erhöht, wenn er sich ändert. Dann sollte der Select funktionieren.

Sollte der Wert sich in 2 Richtungen bewegen (+/-) dann sehe ich als einzig vernünftige Möglichkeit hier mit einem Trigger auf Tabelle1 zu arbeiten. Die Tabelle2 würde ich dann über den Trigger füllen lassen und dort ein zusätzliches Feld anlegen mit der Anzahl der Tagen seit der letzten Änderung.

cu

Oliver

[globetrotter77]

Zitat:

Der Wert darf niemals wieder einen historischen Wert annehmen

Eben!
Es dreht sich um ein Problem bezüglich der Reihenfolge, in der die Werte auftreten, deshalb sind WHERE-Klauseln fast schon für die Katz

[Sir Rufo]

Darum ja auch in dem Fall der sich wiederholenden Werten pro Id der Hinweis auf die Trigger.

Er hat ja eh schon eine Tabelle mit den aktuellen Werten, da kann er auch noch eine Spalte vertragen, wo die Anzahl der Tage seit der letzen Änderung drin steht.

Denn ansonsten muss - as u said - wirklich jeder Datensatz durchgelesen werden ... und wer will das schon ...

cu

Oliver

[globetrotter77]

Zitat:

da kann er auch noch eine Spalte vertragen

das ist natürlich wahr!

[braini4c]

Erst einmal vielen Dank für die raschen Antworten.

Der Wert kann auch wieder abnehmen, ist unwahrscheinlicher, aber kommt vor.

Ich habe mit hilfe einer anleitung etwas zusammengeschustert, was mir im Prinzip das gibt was ich benötige, wenn auch nicht exakt das, was ich ursprünglich beschrieben hatte (Anzahl der Tage pro ID)

SQL-Code:

SELECT id_ FROM
    (SELECT id as id_, wert as wert_
     FROM table1
     WHERE datum >= SUBDATE(NOW(), INTERVAL 5 DAY) )
     as tmp_wert
GROUP BY id_ HAVING MAX(wert_) = MIN(wert_) and COUNT(wert_) >= 5;

Muss gestehen so ganz hab ich noch nicht verstanden warum es funktioniert...
Sollte mir mal näher den subquery syntax anschauen.
Aber es geht fix und ich muss "nur" die Zahlen anpassen. Da ich aber eh an nem fixen Wert für die Anzahl der Tage interessiert bin (5) :-) is das für mich OK.

Vielen Dank an Alle !
MfG
Julian