Wie Datensätze am geschicktesten auswerten?

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

 

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

 

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

 

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 ;-)

 

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.

 

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

 

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)

 

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).

 

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

 

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)

 

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

 

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