Workflow Zwischenstand über Zeit

[Benrath]

Naja sonst halt für die Belustigung. Ich hab ein Online Formular System mit Workflow und über die Zeit werden die Zwischenstände gespeichert.
Ich möchte gerne die Entwicklung über die Zeit betrachten also ob z.B. wo die Workflows vor X wochen standen.

Die Tabelle sieht in etwa so aus.
Encoded_key is eine unique id pro Workflow
Created das Datum an dem was passiert ist (an sich noch mit Uhrzeit)
seria_number geht dann von 1 bis n pro encoded key
action ist was passiert ist
und typ noch weil es den Workflow in unterschiedlichen Typen gibt, dann aber wieder mit anderer encoded key.

encoded_key	created	serial_number	action_name	typ
1​	10.10.2021​	1​	geöffnet	FB 1
1​	12.10.2021​	2​	Bei DL	FB 1
1​	14.10.2021​	3​	Bei DS	FB 1
2​	11.10.2021​	1​	geöffnet	FB 1
2​	13.10.2021​	2​	Bei DL	FB 1

Mir fehlt irgendwie die zündende Idee wie ich jetzt für die Tage vom 10-14.10 folgendes darstellen kann:
am 10.10. war ein workflow geöffnet
am 11.10. dann zwei
am 12.10 einer "bei DL" und einer geöffnet
usw...

Aktuell würde ich in SQL über datum und encoded key gruppieren um auf die max(action_name) zu kommen
und dann nochmal über datum und action_name gruppieren und zählen.

Glaub das geht jetzt sogar, dass ich mir es eigentlich hätte schenken können, aber vielleicht gibts bessere Ansätze und ich hab es schon geschrieben.

 

[parats']

Reicht dir nicht ein distinct count auf encoded_key mit group by auf date/action?
Ggf per window function noch encoded key säubern wenn pro tag nur der letzte/erste usw. usf. gezählt werden soll.

 

[Benrath]

Ne weil sich der Status ja wiederholt

Habs noch auf SO versucht

Summary of Workflow History over Time

I am collecting data using a web formular and a workflow with four different status. Started Review by Admin Back to User Finished Status 2 and 3 are repeated as long as it is needed. The applica...

stackoverflow.com

Dann wäre die Antwort wohl für sql was mit case und dann summieren

 

[parats']

Den Status hast du aber oben nicht im Beispiel, oder bin ich blind?

 

[Benrath]

Oben hieß es action name und ich hatte die Wiederholung nicht dargestellt. Bei SO fehlt an sich die Typ Spalte.

Hoffe dass ich es in SQL mit case und group bei hinbekomme. Sonst halt für jeden Typ eine Abfrage in power bi

 

[parats']

Dann verstehe ich deine Frage glaube ich nicht. Du willst doch nur die Anzahl der Status/Action Einträge pro Tag auf Basis des Workflows. Wenn ein Workflow auf dem Aggregat Status/Datum mehrere Einträge liefert und das nicht gewollt ist, dann muss das doch nur per window function bereinigt werden.

 

[Benrath]

Wird nicht aus der SO Frage klar was ich will?

Ich habe zu jedem Zeitpunkt eine Verteilung meines workflows. N haben gerade angefangen, M werden geprüft, etc. Und X sind fertig. Das dafür habe ich diese historie Tabelle und möchte jetzt rückwirkend die Verteilung zu jedem Zeitpunkt nachstellen

 

[parats']

Aber dann reicht dir doch genau das oben geschriebene. Gruppierung auf Datum/Zeit und Status. Wenn dich immer der letzte Zustand zum betrachteten Zeitpunkt interessiert und der solange mitgenommen werden muss, bis der sich ändert, könnte dir Last_Value() mit einer derived table helfen.

€:
Hast du ein sample mal als CSV Datei, oder sind die eigentlichen Inhalte zu sensitiv?

 

[Benrath]

Gerne spätestens Montag. Ne ist ja nur eine historie mit ids die dir nichts sagen.

 

[Benrath]

@parats'
Geht auch als txt? War erst csv und dann zu txt. csv kann ich hier nicht direkt hochladen

Typ : "Typ 1" , "Typ 2", "Typ 3"
encoded_key unique id pro workflow
serial_number 1:n pro encoded_key
created: datetime
action_name sollte die 4 enthalten

1. Erstellt
2. Validierung
3. Korrektur
4. Fertig

2 und 3 können sich wiederholen. 4 an sich auch, aber glaub nicht in dem Beispiel.

 

[parats']

Ich habe es jetzt nur grob überflogen und nur ein Ausschnitt vom Start ab Mai, aber sowas in der Art wolltest Du doch, oder?

 

[Benrath]

@parats' ja so in etwa

 

[parats']

Dann wird dir wohl das SQL dazu reichen um den Rest zu verfeinern?

 

[Benrath]

ich hoffe es

 

[parats']

Dann drop ich das erstmal so. Vielleicht dennoch ein paar Punkte:
1. Du wirst häufiger die Wandlung des Datums finden, das liegt einfach daran, dass die Spalte selbst ein NVARCHAR() ist und ich keine Lust hatte unseren AutoImporter auf dem blob storage anzupassen, damit dieser ein deutsches Datum samt Uhrzeit direkt sauber in zwei Integer konvertiert. Konkret geht es um: CONCAT(CONVERT(DATE, f.created, 104), CONVERT(TIME, RIGHT(f.created,5), 114)) DESC)
2. Ich weiß nicht wie perfomant das auf einem herkömmlichen SQL Server läuft. Ich hab das hier einmal mit Azure Synapse und dann mit einer on premise Microsoft APS getestet, da funktioniert es jeweils tadellos. Kann aber sein, dass Du auf Zwischentabellen ausweichen müsstest, wenn es so gar nicht läuft.
3. Im wesentlichen ist es dein Ansatz oben. Der Trick ist eigentlich nur das Cross Apply.

SELECT m.created, SUM(m.Erstellt) AS Erstellt, SUM(m.Validierung) AS Validierung, SUM(m.Korrektur) AS Korrektur, SUM(m.Fertig) AS Fertig, SUM(m.Erstellt+m.Validierung+m.Korrektur+m.Fertig) AS Gesamt
FROM (
    SELECT n.created, n.action_name,
        CASE WHEN n.action_name = 'Erstellt' THEN COUNT(n.encoded_key) ELSE 0 END AS Erstellt,
        CASE WHEN n.action_name = 'Validierung' THEN COUNT(n.encoded_key) ELSE 0 END AS Validierung,
        CASE WHEN n.action_name = 'Korrektur' THEN COUNT(n.encoded_key) ELSE 0 END AS Korrektur,
        CASE WHEN n.action_name = 'Fertig' THEN COUNT(n.encoded_key) ELSE 0 END AS Fertig
    FROM (
        SELECT f.encoded_key, f.created AS created_org, f.action_name, created.created,
            ROW_NUMBER() OVER(PARTITION BY f.encoded_key, created.created ORDER BY CASE WHEN f.created = created.created THEN 1 ELSE 2 END, CONCAT(CONVERT(DATE, f.created, 104), CONVERT(TIME, RIGHT(f.created,5), 114)) DESC) AS RowNumber
        FROM tmp.ProcessInstanceHist f
            CROSS APPLY (SELECT created FROM tmp.ProcessInstanceHist GROUP BY created) AS created
        WHERE CONCAT(CONVERT(DATE, f.created, 104), CONVERT(TIME, RIGHT(f.created,5), 114)) <= CONCAT(CONVERT(DATE, created.created, 104), CONVERT(TIME, RIGHT(created.created,5), 114))
    ) n
    WHERE n.RowNumber = 1
    GROUP BY n.created, n.action_name
) m
GROUP BY m.created
ORDER BY CONCAT(CONVERT(DATE, m.created, 104), CONVERT(TIME, RIGHT(m.created,5), 114)) ASC

 

[Benrath]

Danke. das wäre jetzt sonst mein Ansatz geworden mit case, so wie der Typ von SO das quasi in Power BI über die IFs gemacht hat und dann countet.
Bei mir ist das created ja timedate schon in der Datenbank.

Ich passe es mal an

 

[parats']

Interessant wird es, wenn da mehr Zeilen kommen. Durch das Kreuzprodukt kann das irgendwann doch recht intensiv werden, dann müsste man sich entweder über logische Grenzen wie rollierende Zeiträume Gedanken machen oder das Konstrukt nochmal umbauen.

 

[Benrath]

@parats'
Die ganzen CONCAT(CONVERT(DATE, f.created, 104), CONVERT(TIME, RIGHT(f.created,5), 114)) kann ich einfach durch f.created austauschen, wenn created bei mir schon date_time ist?

Muss noch lernen was cross apply macht das kannte ich noch nicht

 

[parats']

Ja kannst du. Ich habe das einfach als String importiert und dann funktioniert das sortieren eines Datums oder einer Uhrzeit nicht sauber.
Die 104 bzw. 114 sind daher nur eine feste Vorgabe, wie der String zu interpretieren ist, bevor die Zielkonvertierung stattfindet.

Zugegeben braucht man cross apply auch wirklich selten. Hab spontan auch nur so zwei Szenarien konkret im Kopf, wo man sowas mal benutzt.

 

[Benrath]

Ich muss mir deins nochmal in Ruhe angucken. Nur das austauschen hat leider nicht geklappt.
Glaub dein Code berücksichtig noch nicht die Typ Spalte.

Ich habs jetzt so gemacht und hoffe das stimmt ^^

von Hinten nach vorne
1. Lag Table mit dem Action_name der Reihe davor. Das brauch ich im später bei den Spalten die Doppelzählung zu vermeiden
2. Change Table mit den Case Spalten wo ich die Spalte lag_action nutze, damit ich nicht ins Minus gerate bei Erstellt, wenn der Datensatz zur Prüfung oder zur Korrektur geht
3. Aufsummieren über Typ geordnet nach Typ und zeit.

Den Weg kann ich zumindest nachvollziehen.

Select top 600 created, form_uuid,  Cum_Erstellt, Cum_Review, Cum_Korrektur, Cum_Fertig from (
Select *,
 sum(Erstellt) over(Partition by form_uuid order by  form_uuid, created) as Cum_Erstellt,
  sum(Review) over(Partition by form_uuid order by  form_uuid, created) as Cum_Review,
  sum(Korrektur) over(Partition by form_uuid order by  form_uuid, created) as Cum_Korrektur,
  sum(Fertig) over(Partition by form_uuid order by  form_uuid, created) as Cum_Fertig,
  Erstellt+Review+Korrektur+Fertig as Vergleich
from (
Select  created, form_uuid, encoded_key, action_name, lag_action,
    CASE WHEN action_name = 'Datensatz erstellt und zur Bearbeitung an Datenlieferanten übergeben' THEN 1
        When action_name = 'Datensatz zur Prüfung an Datensammler' and
        lag_action='Datensatz erstellt und zur Bearbeitung an Datenlieferanten übergeben' THEN -1
        Else 0 END AS Erstellt,
        CASE WHEN action_name = 'Datensatz zur Prüfung an Datensammler' THEN 1
        When action_name= 'Datensatz zur Korrektur an Datenlieferanten' Then -1
        when Action_name= 'Datensatz erneut Öffnen' then 1
        When action_name='Datensatz durch Datensammler bestätigt' Then -1
        Else 0 END AS Review,
        CASE
        WHEN action_name = 'Datensatz zur Prüfung an Datensammler' and
        lag_action!='Datensatz erstellt und zur Bearbeitung an Datenlieferanten übergeben'  THEN -1
        When action_name= 'Datensatz zur Korrektur an Datenlieferanten'  Then 1
        Else 0 END AS Korrektur,
        CASE WHEN action_name = 'Datensatz durch Datensammler bestätigt' THEN 1
        When action_name= 'Datensatz erneut Öffnen' Then -1
        Else 0 END AS Fertig
from (
Select created, form_uuid, encoded_key, action_name, lag(action_name,1,NULL)
over( order by  encoded_key,  form_uuid, created)  as lag_action
from process_instance_hist ) lag_table
) change_table
) cum_table
order by created, encoded_key
--group by created, form_uuid

 

[parats']

Eigentlich musst du nur die Tabelle austauschen. Die Spalten heißen genauso wie im flatfile, wenn es da also keine Unterschiede gibt, sollte es passen.
Stimmt, den Typ hatte ich nicht drin. Der steht doch aber in der Beziehung zum encoded_key. Insofern kann der einfach mit in die Aggregation.
Um es zu vereinfachen kannst du einfach ein CONCAT() mit Trennzeichen benutzen.

Wenn beides läuft, kannst du ja mit HASHBYTES() einfach das Datum + alle Summenspalten hashen und miteinander abgleichen.

 

[Benrath]

Interessant wird es, wenn da mehr Zeilen kommen. Durch das Kreuzprodukt kann das irgendwann doch recht intensiv werden, dann müsste man sich entweder über logische Grenzen wie rollierende Zeiträume Gedanken machen oder das Konstrukt nochmal umbauen.

Eventuell komme ich da in Probleme. Die Tabelle process_instance_hist hat aktuelle 23812 Zeilen

Hab mal auf 194 reduziert und dann bleiben in der inneren Tabellen des CROSS APPLY noch 188 Zeilen.
Das was du dann als n definierst hat schon 18.845 Zeilen
Eventuell geht es bei dir auch schneller, weil du auf Minuten kürzt.

Muss noch den Typ einbauen, aber zumindest passiert was
Wahrscheinlich brauch ich noch die form_uuid im ersten group by etc.
Und dann im CROSS APPLY als zweite Bedingung.

SELECT m.created, SUM(m.Erstellt) AS Erstellt, SUM(m.Validierung) AS Validierung, SUM(m.Korrektur) AS Korrektur, SUM(m.Fertig) AS Fertig, SUM(m.Erstellt+m.Validierung+m.Korrektur+m.Fertig) AS Gesamt
FROM (
    SELECT n.created, n.action_name,
        CASE WHEN n.action_name = 'Datensatz erstellt und zur Bearbeitung an Datenlieferanten übergeben' THEN COUNT(n.encoded_key) ELSE 0 END AS Erstellt,
        CASE WHEN n.action_name = 'Datensatz zur Prüfung an Datensammler' THEN COUNT(n.encoded_key) ELSE 0 END AS Validierung,
        CASE WHEN n.action_name = 'Datensatz zur Korrektur an Datenlieferanten' THEN COUNT(n.encoded_key) ELSE 0 END AS Korrektur,
        CASE WHEN n.action_name = 'Datensatz durch Datensammler bestätigt' THEN COUNT(n.encoded_key) ELSE 0 END AS Fertig
    FROM (
        SELECT f.encoded_key, f.created AS created_org, f.action_name, created.created,
            ROW_NUMBER() OVER(PARTITION BY f.encoded_key, created.created ORDER BY CASE WHEN f.created = created.created THEN 1 ELSE 2 END, 
            f.created DESC) AS RowNumber
        FROM process_instance_hist f
            CROSS APPLY (SELECT created FROM process_instance_hist  GROUP BY form_uuid, created) AS created
        WHERE f.created <= created.created
    ) n
    WHERE n.RowNumber = 1
    GROUP BY n.created, n.action_name
) m
GROUP BY m.created
ORDER BY m.created

 

[parats']

Du hattest glaube ich nur Stunden und Minuten in der Datei.
Die Performance wäre halt das größte Fragezeichen und möglich, dass reguläre SQL Server dort irgendwann aussteigen. :/