Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Wie Datensätze am geschicktesten auswerten?

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von Pizza47, 30 April 2013.

  1. Pizza47

    Pizza47 Benutzer

    Hallo,

    ich schreibe derzeit an einem Programm welches die Zählerstände unserer Drucker erfasst.

    Derzeit speichere ich die Datensätze in folgendem Format:

    id, datum, druckername, zaehler

    Wobei nicht immer alle Drucker erfasst werden weil Sie zB nicht eingeschaltet sind.
    Ich hatte vor den Datenerfassungslauf einmal wöchentlich zu starten.
    Nun meine Frage:

    Wie werte ich die Daten am geschicktesten aus?
    Es geht mir darum, kaum genutze Drucker zu erkennen.
    Also die durchschnittlich gedrucktem Seiten pro Tag
    Gibt es eine Abfrage wie:

    Zaehlerstand bei Datumdatum(max)-Zaehlerstand bei Datum(min) geteilt durch Datum(max) - Datum(min) group by Druckername?

    oder kann man so etwas besser durch im Programm lösen ?

    Gruß

    Günter
     
  2. akretschmer

    akretschmer Datenbank-Guru


    Da hab ich eine gute und eine schlechte Nachricht für Dich. Zuerst einmal die gute: SQL kennt für sowas sog. WINDOW-Funktionen, diese gehen wie folgt, kurze Demo:

    Code:
    test=*# select * from pizza;
     id |   datum    |   name    | zaehler
    ----+------------+-----------+---------
      1 | 2013-01-01 | drucker 1 |      10
      2 | 2013-01-01 | drucker 2 |      20
      3 | 2013-01-01 | drucker 3 |      25
      4 | 2013-02-01 | drucker 1 |      25
      5 | 2013-02-01 | drucker 3 |      55
      6 | 2013-03-01 | drucker 1 |      55
      7 | 2013-03-01 | drucker 2 |     255
      8 | 2013-03-01 | drucker 3 |      75
    (8 rows)
    
    Time: 0,180 ms
    test=*# select *, datum - lag(datum) over (partition by name order by datum) as tage, zaehler - lag(zaehler) over (partition by name order by datum) as gedruckte_seiten from pizza order by name;
     id |   datum    |   name    | zaehler | tage | gedruckte_seiten
    ----+------------+-----------+---------+------+------------------
      1 | 2013-01-01 | drucker 1 |      10 |      |
      4 | 2013-02-01 | drucker 1 |      25 |   31 |               15
      6 | 2013-03-01 | drucker 1 |      55 |   28 |               30
      2 | 2013-01-01 | drucker 2 |      20 |      |
      7 | 2013-03-01 | drucker 2 |     255 |   59 |              235
      3 | 2013-01-01 | drucker 3 |      25 |      |
      5 | 2013-02-01 | drucker 3 |      55 |   31 |               30
      8 | 2013-03-01 | drucker 3 |      75 |   28 |               20
    (8 rows)
    
    Damit kann man also quasi ein Fenster über die Datensätze ziehen (over) und den Vorgänger (lag()) innerhalb einer Gruppe (partition by) und in dieser sortiert (order by).
    Daraus nun Seiten/Tag zu ermitteln ist dann einfach.


    Die schlechte Nachricht: MySQL kann das nicht.

    Ich hab mal so Versuche gesehen, das in MySQL zu simulieren, z.B. hier: http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql

    Wäre vermutlich einfacher, gleich eine richtige Datenbank zu nehmen ...

    Andreas
     
  3. Pizza47

    Pizza47 Benutzer

    Danke für die schnelle Antwort.

    Kann MSSQL-Express soetwas?

    Könnte ich mir helfen, indem ich anhand einer (schon vorhandenen) Druckernamentabelle jeden Drucker einzeln mit Hilfe einer Programmschleife abfrage?

    Also prinzipiell die gleiche Abfrage wie oben , nur bezogen auf einen Druckernamen. (where Druckername='Drucker1')

    Aber dann müßte ich wahrscheinlich im Programm sortieren lassen oder ist es sinnvoller das Ganze in eine temp-Tabelle laufen zu lassen und dann sortiert auszugeben?
    Wobei die Sortierung nicht ganz so wichtig wäre. Einen Eintrag wie 0.1 Seiten pro Tag erkennt man schnell ;-)

    Gruß

    Günter
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Weiß ich nicht. PostgreSQL kann es , Oraggle definitiv auch. Aber Oraggle - naja. PG ist frei verfügbar, ohne Limits und mit wirklich vielen guten Features.

    SQL in Schleifen ist nur sehr selten eine gute Idee. Die Laufzeit Deiner Anwendung steigt schneller als die Anzahl Datensätze ;-)
     
  5. ukulele

    ukulele Datenbank-Guru

    Ich denke das geht mit jedem SQL nur werden eben nicht alle Funktionen unterstützt so das man mehr rechnen muss. Natürlich kannst du, wenn du sowieso neu anfängst, auch gleich auf PG oder MSSQL setzen, das würde ich unterstützen :)

    Aber wie willst du Zählerstände pro Tag ermitteln wenn nicht jeder Drucker jeden Tag einmal den Stand übermittelt? Natürlich könnte man nicht vorhandene Stände ausen vor lassen aber mit den Informationen id, datum, druckername, zaehler einmal wöchentlich kannst du nur die Veränderung der letzten 7 Tage errechnen und daraus einen Durchschnitt.

    Da dich vermutlich sowieso nur der Monats- oder Jahrsschnitt interessiert würde ich so vorgehen:
    Du Joinst die Tabelle mit sich selbst nur um einen Datensatz "versetzt". Je nach Daten und SQL Version ist das ein bischen tricky. Du hast dann immer den alten und den neuen Zählerstand (des selben Druckers) und ein Datum von bis. Dann rechnechst du einfach Zeit * Zuwachs und das wiederrum gruppierst du nach Jahren und ziehst die Summe.
     
  6. Pizza47

    Pizza47 Benutzer


    Hast du noch irgendwo ein Beispiel für solch eine Abfrage?
     
  7. akretschmer

    akretschmer Datenbank-Guru

    Für mein gezeigtes Beispiel:

    Code:
    test=*# select name, max(datum), max(zaehler), pizza2.* from pizza left join (select name, min(datum) as mindatum, min(zaehler) as minzaehler from pizza group by name) pizza2 using(name) group by name, pizza2.name, pizza2.mindatum, pizza2.minzaehler order by pizza.name;
       name    |    max     | max |   name    |  mindatum  | minzaehler
    -----------+------------+-----+-----------+------------+------------
     drucker 1 | 2013-03-01 |  55 | drucker 1 | 2013-01-01 |         10
     drucker 2 | 2013-03-01 | 255 | drucker 2 | 2013-01-01 |         20
     drucker 3 | 2013-03-01 |  75 | drucker 3 | 2013-01-01 |         25
    (3 rows)
    
     
    ukulele gefällt das.
  8. ukulele

    ukulele Datenbank-Guru

    Ich wollte es zuerst für jeden zu ermittelnden Zeitraum machen, was aber eigentlich blödsinn ist. Dir reicht ja der min und max Wert wie ihn akretschmer nutzt. Es gibt viele Wege, spannend ist immer wie deine Beispieldaten dann aussehen, z.B. ob sie lückenlos durchnumeriert sind und was du als Ergebniss bruachst (z.B. in welchen Zeitabständen du Daten erfasst und auswerten möchtest).
     
  9. Pizza47

    Pizza47 Benutzer

    Schön wäre es natürlich einen Zeitraum anzugeben, aber es geht mir nur darum zu sehen, wo Drucker untätig herumstehen.

    Danke für die Hilfe
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Wer oder was hindert Dich ein WHERE zu nutzen?

    Code:
    test=*# select name, max(datum), max(zaehler), pizza2.* from pizza left join (select name, min(datum) as mindatum, min(zaehler) as minzaehler from pizza group by name) pizza2 using(name) where pizza.datum < '2013-03-01' group by name, pizza2.name, pizza2.mindatum, pizza2.minzaehler order by pizza.name;
       name    |    max     | max |   name    |  mindatum  | minzaehler
    -----------+------------+-----+-----------+------------+------------
     drucker 1 | 2013-02-01 |  25 | drucker 1 | 2013-01-01 |         10
     drucker 2 | 2013-01-01 |  20 | drucker 2 | 2013-01-01 |         20
     drucker 3 | 2013-02-01 |  55 | drucker 3 | 2013-01-01 |         25
    (3 rows)
    
     
  11. Pizza47

    Pizza47 Benutzer

    So diese Abfrage liefert mir jetzt die durchschnittliche Tagesleistung der Drucker in den letzten 30 Tagen :)))

    select druckerzaehler.druckermac, max(datum), max(zaehler_alle),test.* ,max(zaehler_alle)-minzaehler as Diff, datediff(max(datum),mindatum) as Tage,
    round((max(zaehler_alle)-minzaehler)/(datediff(max(datum),mindatum))) as PPD from druckerzaehler
    left join(select druckerzaehler.druckermac, min(datum) as mindatum, min(zaehler_alle) as minzaehler from druckerzaehler group by druckerzaehler.druckermac)
    test using(druckermac) where druckerzaehler.datum > '2013-04-01'
    group by druckerzaehler.druckermac, test.druckermac, test.mindatum, test.minzaehler order by PPD desc;


    Allerdings habe ich natürlich keine Namen in der Zählerstandstabelle gespeichert, sondern nur die (hoffentlich) eindeutigen MAC-Adressen.
    Jetzt müßte ich das Ganze noch mit der Druckgeraetetabelle namens druckgeraete verknüpfen um die Druckertypen und Anschlußnamen herauszubekommen.

    bei einer anderen Abfrage funktionierte das:

    select druckertyp,zaehler_alle,datum,druckgeraete.druckermac,druckgeraete.druckeranschluss
    from drucker.druckgeraete inner join druckerzaehler on druckgeraete.druckermac=druckerzaehler.druckermac
    where druckgeraete.druckermac = ' 00 1B A9 34 E4 95' order by datum desc limit 1;

    Wo muß ich diesen join einfügen?
    inner join druckgeraete on druckgeraete.druckermac=druckerzaehler.druckermac

    Gehen überhaupt 2 joins?

    Gruß

    Günter
     
  12. Pizza47

    Pizza47 Benutzer

    Habs gefunden, mußte nur das using ersetzen.:)

    Dann ging es auch mit dem 2. join.

    Gruß und vielen Dank für die Hilfe

    Günter
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden