Maschinenlaufzeiten erfassen

Ludwigmller

SQL-Guru
Beiträge
168
Moin,
ich möchte gerne die Laufzeiten einer Maschine erfassen. Dazu steht mir leider nur eine Logfile zur Verfügung in der der "ON"/"OFF" Status mit Uhrzeit protokolliert wird.
Daraus möchte ich Anzahl von Gesamtlaufzeit pro Tag (0-24 Uhr), Anzahl an Takten pro Tag, durchschnittliche min/Takt ermitteln.
Sollte ich ON und OFF mit der Uhrzeit in einer Tabelle speichern und die gewünschten drei Größen per Abfrage ermitteln?
Hier mal ein Beispiel, wie meine Idee für die Datenmodellierung wäre.

Code:
create table maschinen (
      id integer primary key,
      bezeichnung varchar(30)
  );

create table start_stop (
      id serial primary key,
      zeitstempel timestamp,
    status integer, /* 0: OFF, 1: ON */
    id_maschine    integer,
      CONSTRAINT fk_maschine
          FOREIGN KEY (id_maschine)
              REFERENCES maschinen(id)
);

insert into maschinen (id, bezeichnung) values
(1, 'Maschine 1'),
(2, 'Maschine 2'),
(3, 'Maschine 3');

insert into start_stop (zeitstempel, status, id_maschine) VALUES
('2022-01-15 01:00:00', 1, 1),
('2022-01-15 01:10:00', 0, 1),
('2022-01-15 02:00:00', 1, 1),
('2022-01-15 02:20:00', 0, 1),
('2022-01-15 03:00:00', 1, 1),
('2022-01-15 03:10:00', 0, 1),
('2022-01-15 01:30:00', 1, 2),
('2022-01-15 02:00:00', 0, 2);

select * from start_stop;

In diesem Beispiel, wären die gewünschten Ergebnisse:
Maschine 1: Takte: 3, Taktlänge: 10 min, Laufzeit: 40 min/24h
Maschine 2: Takte: 1, Taktlänge: 30 min, Laufzeit: 30 min
Maschine 3: Takte: 0, Taktlänge: 0 min, Laufzeit: 0 min

Es soll möglichst PostgreSQL genutzt werden.
 
Werbung:
die Frage wäre, wie das Logfileformat ist. Wenn das klar strukturiert ist (CSV etc.), dann kannst das via \copy in PG einlesen und dann weiter bearbeiten. Es gibt auch FDW auf z.B. CSV-Dateien.
 
Hier ein Auszug aus der unter Linux erstellten .log Datei:
Code:
01:00:00  Maschine[1] Status:ON
01:00:00  Maschine[2] Status:OFF
01:00:00  Maschine[3] Status:OFF
01:03:51  Maschine[1] Status:CLOSE
01:04:01  Maschine[1] Status:PENDING
01:04:35  Maschine[2] Status:ON
01:05:31  Maschine[1] Status:PAUSE
01:06:31  Maschine[1] Status:OFF
01:08:00  Maschine[2] Status:CLOSE
 
Dieses Format hat ja erstmal nichts mit Datenbanken zu tun.

Entweder wertest du das ohne DB aus, oder du musst das in eine DB importieren. In dem Zug kannst du das so umformatieren, dass die Daten in der DB direkt auswertbar sind.
 
mal als Schnellschuß:

Code:
postgres=# select * from ludwigmiller ;
                  t                   
--------------------------------------
 01:00:00  Maschine[1] Status:ON
 01:00:00  Maschine[2] Status:OFF
 01:00:00  Maschine[3] Status:OFF
 01:03:51  Maschine[1] Status:CLOSE
 01:04:01  Maschine[1] Status:PENDING
 01:04:35  Maschine[2] Status:ON
 01:05:31  Maschine[1] Status:PAUSE
 01:06:31  Maschine[1] Status:OFF
 01:08:00  
(9 rows)

postgres=# with foo as (select regexp_split_to_array(t,'\s+') as t from ludwigmiller ) select t[1] as zeit, t[2] maschine, t[3] status from foo;
   zeit   |  maschine   |     status     
----------+-------------+----------------
 01:00:00 | Maschine[1] | Status:ON
 01:00:00 | Maschine[2] | Status:OFF
 01:00:00 | Maschine[3] | Status:OFF
 01:03:51 | Maschine[1] | Status:CLOSE
 01:04:01 | Maschine[1] | Status:PENDING
 01:04:35 | Maschine[2] | Status:ON
 01:05:31 | Maschine[1] | Status:PAUSE
 01:06:31 | Maschine[1] | Status:OFF
 01:08:00 |             | 
(9 rows)

postgres=#
 
Die Daten in die DB zu bekommen ist das eine. Wie würden dann Abfragen zu Laufzeit pro Tag, Taktanzahl (vermutlich einfach die ON zählen), durchschnittliche Taktlänge aussehen? Da habe ich noch keinen Ansatz, da erstmal die einzelnen Taktlängen berechnet werden müssen
 
nun, schauen wir mal ...

Code:
postgres=# with foo as (select regexp_split_to_array(t,'\s+') as t from ludwigmiller ) select t[1]::time as zeit, t[2] maschine, t[3] status, t[1]::time - lag(t[1]::time) over (partition by t[2] order by t[1]::time) as dauer from foo;
   zeit   |  maschine   |     status     |  dauer   
----------+-------------+----------------+----------
 01:08:00 |             |                | 
 01:00:00 | Maschine[1] | Status:ON      | 
 01:03:51 | Maschine[1] | Status:CLOSE   | 00:03:51
 01:04:01 | Maschine[1] | Status:PENDING | 00:00:10
 01:05:31 | Maschine[1] | Status:PAUSE   | 00:01:30
 01:06:31 | Maschine[1] | Status:OFF     | 00:01:00
 01:00:00 | Maschine[2] | Status:OFF     | 
 01:04:35 | Maschine[2] | Status:ON      | 00:04:35
 01:00:00 | Maschine[3] | Status:OFF     | 
(9 rows)

postgres=#
 
Werbung:
where status in ... und mit lag() prüfen, ob vorher ON war.

Du schaffst das!
Die LAG()-Funktion schreckt mich immer ab ;) aber so schwer ist es eigentlich gar nicht
Hier mal mein Versuch, der zumindest das gewünschte Ergebnis liefert. Verbesserungsvorschläge?
Code:
create table maschinen (
      id integer primary key,
      bezeichnung varchar(30)
  );

create table start_stop (
      id serial primary key,
      zeitstempel timestamp,
    status integer, /* 0: OFF, 1: ON */
    id_maschine    integer,
      CONSTRAINT fk_maschine
          FOREIGN KEY (id_maschine)
              REFERENCES maschinen(id)
);


insert into maschinen (id, bezeichnung) values
(1, 'Maschine 1'),
(2, 'Maschine 2'),
(3, 'Maschine 3');

insert into start_stop (zeitstempel, status, id_maschine) VALUES
('2022-01-15 01:00:00', 1, 1),
('2022-01-15 01:10:00', 0, 1),
('2022-01-15 02:00:00', 1, 1),
('2022-01-15 02:20:00', 0, 1),
('2022-01-15 03:00:00', 1, 1),
('2022-01-15 03:10:00', 0, 1),
('2022-01-15 01:30:00', 1, 2),
('2022-01-15 02:00:00', 0, 2);
Code:
with cte as(
  select
      zeitstempel,
      id_maschine,
      status,
      zeitstempel - lag(zeitstempel) over
          (partition by id_maschine order by zeitstempel) as taktlaenge,
      lag(status) over
          (partition by id_maschine order by zeitstempel) as prev_status

  from start_stop
)
select zeitstempel, id_maschine, status, taktlaenge, prev_status from cte
where prev_status = 1;

with cte as(
  select
      zeitstempel,
      id_maschine,
      status,
      zeitstempel - lag(zeitstempel) over
          (partition by id_maschine order by zeitstempel) as taktlaenge,
      lag(status) over
          (partition by id_maschine order by zeitstempel) as prev_status

  from start_stop
)
select
    id_maschine,
    avg(taktlaenge) as durchschnitt_taktlaenge,
    sum(taktlaenge) as zeit_pro_tag
from cte
where prev_status=1
group by id_maschine ;
 
Zurück
Oben