Vraag SQL om teltijd

[dblog22]

Ik heb een tabel met een groot aantal taak-ID's, alle statussen daarvoor en het tijdstip waarop de status is ingesteld. Rijen met dezelfde id kunnen worden herhaald vanwege het verschijnen van een nieuwe status voor hen. Het bereik van ID's is van 1 tot 5000 of meer. Statussen kunnen zijn: Taken, Hold, Unhold, Done.
Tabelkolommen:
1.task_id
2.status
3.date_time
De taak is deze - voor elke id moet je de tijd tussen statussen berekenen met behulp van de volgende formule: time = time(Done) - time(Taken) - SUM(date(Unhold) - date(Hold)).
Het resultaat zou een tabel moeten zijn met alle mogelijke unieke id's en de uitvoeringstijd van elk.
Ik voeg een screenshot van deze tabel bij voor één id.
Ik probeer een verzoek in te dienen zoals:
SELECT
“task_id",
COUNT(“task_id") as count
FROM "HistoryStatus" WHERE deleted=false GROUP BY “task_id";
Maar het lukt me niet om COUNT(“task_id") te verwijzen naar de tijd van verschillende statussen.

[dblog22]

[Woy]

Wat wil je nu precies tellen? Binnen een groep van TaskId de unieke date_times?

https://docs.microsoft.co...sql?view=sql-server-ver16

Dan kun je gebruik maken van COUNT met DISTINCT.

[Janoz]

Om dit in SQL te doen is lastig. SQL is meer gericht op set operaties (unions, intersecties, filters, aggregaties) en operaties die iets volgordelijk moeten doen met records zijn dan ook veel lastiger tot niet mogelijk. Dit probleem is makkelijker op te lossen met bijvoorbeeld een cursor of een externe oplossing. Eerst een gewone query die alles van een taakId ophaalt gesorteerd op date_time. Vervolgens door deze lijst heenlopen en telkens de twee opvolgende records verwerken in de totale gebruikte tijd.

[Tsurany]

Je zit met naar mijn idee behoorlijk beperkte data, een brak proces en onduidelijke requirements. Hoe kan je eenzelfde taak nu meerdere keren op done zetten? En hoezo is 'Unhold' een status? En wat is 'Not done' voor een status? Dat is in principe namelijk elke andere status ook.

Wat je eigenlijk wilt is weten wanneer een task naar 'Hold' ging en wat het eerste moment is dat hij van 'Hold' af kwam naar een andere status. Om dat dit meerdere keren voor komt zul je met subqueries moeten gaan werken die dit voor je kunnen realiseren.
Dus een select die alle records pakt waarbij status = 'Hold' en date_time als start_date_time. Dan join je dat aan de eerstvolgende actie die plaats vind en pak je daar de date_time van als end_date_time. Dan kan je daar een date difference op los laten.

Dan hoef je daarna enkel nog de eerste en de laatste tijdstip te hebben, daar de date difference van de pakken en de on hold durations er van af te halen.

Maar bagger in is bagger uit. Hier zou je normaliter eerst heel andere wijzigingen in doorvoeren. Ik denk direct aan een status tabel en een history tabel waarbij je elke keer dat de status aangepast wordt automatisch de oude data in een history tabel zet met daarbij ook de date_time dat de update is doorgevoerd. Dan heb je al gelijk je duration velden. Je kan in de primary tabel eveneens de original start date en de resolution start date opslaan zodat je dat ook niet elke keer uit hoeft te rekenen.

[Rannasha]

Woy schreef op woensdag 25 mei 2022 @ 10:20:
Wat wil je nu precies tellen? Binnen een groep van TaskId de unieke date_times?

https://docs.microsoft.co...sql?view=sql-server-ver16

Dan kun je gebruik maken van COUNT met DISTINCT.

Ik vermoed dat TS per taak (met unieke TaskID) wil weten hoe lang de uitvoering van de taak heeft geduurd. De uitvoering is in principe de tijd die verstreken is tussen het oppakken van de taak (event met status "Taken") en het voltooien ervan (event met status "Done"). Maar, een taak kan ook op pauze worden gezet en later weer hervat worden (events met status "Hold" en "Unhold") en deze pauze-tijd moet van de totale uitvoeringstijd afgetrokken worden.

[Woy]

@Rannasha als dat het geval is dan zal het ieder geval niet met een count moeten, maar zal je een start/unhold event moeten joinen aan het eerstvolgende hold/Done event, en de gevonden periodes moeten summen.

Op zich nog wel te doen met SQL, maar zoals @Janoz terrecht opmerkt zijn dit soort dingen vaak makkelijker met meer procedurele code die door een cursor heenloopt.

[Maasluip]

Je moet met subqueries werken omdat je per task meerdere statussen hebt. Per status maak je één subquery om de juiste tijd te krijgen.
Een paar aannames:
• Er is één status done en één status taken. Dat is niet in je dataset en ik twijfel of dat klopt. Waarom kan een task twee keer done zijn?
• Alle 4 statussen zijn in de database. Zo niet, dan moeten je bepalen wat de eeste status is (taken?) en de andere tabellen outer joinen
• Je hebt een fatsoenlijke database die met subqueries kan werken.

SQL:

select stat_done.task_id, stat_done.date_time - stat_taken.date_time - stat_unhold.date_time - stat_hold.date_time
from (select task_id, date_time from HistoryStatus where status = 'Done') stat_done,
     (select task_id, date_time from HistoryStatus where status = 'Taken') stat_taken,
     (select task_id, sum(date_time) as date_time from HistoryStatus where status = 'Unhold') stat_unhold,
     (select task_id, sum(date_time) as date_time from HistoryStatus where status = 'Hold') stat_hold
where stat_done.task_id = stat_taken.stat_id
      and stat_unhold.task_id = stat_taken.stat_id
      and stat_hold.task_id = stat_taken.stat_id

[Woy]

@Maasluip volgens mij klopt sowieso je logica met de SUM(date_time) van UNHOLD en HOLD niet, want je zult daar de bijbehorende andere row bij moeten zoeken om de periode te bepalen, je kunt niet gewoon de datums bij elkaar optellen.

[Maasluip]

@Woy als je tijd als een getal beschouwt is dat geen probleem.
Voorbeeld uit Excel:
hold 25-5-2022 06:00 (44,706.25 numeriek)
unhold 25-5-2022 07:30 (44,706.3125 numeriek)
hold 25-5-2022 10:00 (44,706.4166667 numeriek)
unhold 25-5-2022 11:00 (44,706.4583333 numeriek)

sum hold 89,412.66667
sum unhold 89,412.7708333

unhold-hold 0.10416667 (02:30:00 in tijdnotatie)

Het kan zijn dat je de datums dan eerst moet casten naar een int of zo. Ik zie dat Oracle geen sums van datums doet.

[ Voor 13% gewijzigd door Maasluip op 25-05-2022 11:06 ]

[Janoz]

Tsurany schreef op woensdag 25 mei 2022 @ 10:31:
....onduidelijke requirements... ....bagger.....

Mwah, dat vind ik nogal overdreven. Goed, dat een taak meer dan 1 keer TAKEN en/of meer dan 1 keer DONE heeft klopt inderdaad niet. En UNHOLD zou misschien beter ook RESUMEND kunnen zijn. Maar dat in acht nemende is dit gewoon een standaard time serie met statusovergangen. Het probleem van de topicstarter is echter dat hij niet de data in de records nodig heeft, maar er tussen. Dat is inderdaad op te lossen door het datamodel aan te passen en meer controles en handelingen bij het invoeren te doen. Maar dat kan net zo goed door een beetje controle aan de voorkant toe te voegen (validaties op mogelijke status overgang) en een aggregatie achteraf (bv op het moment dat een taak DONE is, of wanneer de rapportage opgemaakt wordt)

[Woy]

Maasluip schreef op woensdag 25 mei 2022 @ 10:56:
@Woy als je tijd als een getal beschouwt is dat geen probleem.
Voorbeeld uit Excel:
hold 25-5-2022 06:00 (44,706.25 numeriek)
unhold 25-5-2022 07:30 (44,706.3125 numeriek)
hold 25-5-2022 10:00 (44,706.4166667 numeriek)
unhold 25-5-2022 11:00 (44,706.4583333 numeriek)

sum hold 89,412.66667
sum unhold 89,412.7708333

unhold-hold 0.10416667 (02:30:00 in tijdnotatie)

Het kan zijn dat je de datums dan eerst moet casten naar een int of zo. Ik zie dat Oracle geen sums van datums doet.

Het gaat mij niet om het feit of je wel/niet datums kunt sommeren, maar dat je daarmee niet de tijd krijgt dat hij in die status was. Dat is namelijk de tijd die tussen de twee datums van twee entries vallen

Je zult meer zoiets moeten doen

SQL:

SELECT task_id, SUM(COALESCE(endDate, getdate()) - startDate) as totalTime
FROM (
    SELECT start.task_id, start.date_time as startDate, MIN(end.date_time) as endDate
    FROM HistoryStatus as start
    LEFT JOIN HistoryStatus as end
        ON start.task_id = end.task_id AND end.date_time > start.date_time
    WHERE start.status IN ( 'TAKEN', 'UNHOLD' )
    AND end.status IN ('HOLD', 'DONE' )
    GROUP BY start.task_id, start.date_time)

edit: Ik zie inderdaad dat jouw voorbeeld ook goed uit kan komen als er inderdaad gegarandeerd kan worden dat er altijd 1 ( en ook maximaal 1 ) Unhold event voor een Hold event is. Mijn voorbeeld is denk iets robuuster omdat je ook bij per ongeluk 2 unhold events gewoon goed gaat (Ik heb de query nog iets aangepast zodat hij ook nog een resultaat geeft als de taak op dit moment nog niet is afgesloten)

[ Voor 34% gewijzigd door Woy op 25-05-2022 11:18 ]

[Kalentum]

Ik zou LEAD gebruiken. LEAD geeft de waarde van de volgende rij in een resultaatset terug.

Iets als (ongetest)

SQL:

with status_duration as (
  SELECT task_id, status, ((LEAD(date_time) OVER (ORDER BY date_time) - date_time) as duration FROM history_status
)
select task_id, status, sum(duration) from status_duration group by task_id, status

Je hebt dan eerst een subquery die de tijdsduur van elke status geeft, welke je dan vervolgens per task_id en status op kan tellen.

[Kalentum]

Janoz schreef op woensdag 25 mei 2022 @ 10:22:
Om dit in SQL te doen is lastig. SQL is meer gericht op set operaties (unions, intersecties, filters, aggregaties) en operaties die iets volgordelijk moeten doen met records zijn dan ook veel lastiger tot niet mogelijk.

Met window functies is dit prima in SQL te doen.

[Tsurany]

Janoz schreef op woensdag 25 mei 2022 @ 10:57:
[...]


Mwah, dat vind ik nogal overdreven. Goed, dat een taak meer dan 1 keer TAKEN en/of meer dan 1 keer DONE heeft klopt inderdaad niet. En UNHOLD zou misschien beter ook RESUMEND kunnen zijn. Maar dat in acht nemende is dit gewoon een standaard time serie met statusovergangen. Het probleem van de topicstarter is echter dat hij niet de data in de records nodig heeft, maar er tussen. Dat is inderdaad op te lossen door het datamodel aan te passen en meer controles en handelingen bij het invoeren te doen. Maar dat kan net zo goed door een beetje controle aan de voorkant toe te voegen (validaties op mogelijke status overgang) en een aggregatie achteraf (bv op het moment dat een taak DONE is, of wanneer de rapportage opgemaakt wordt)

Het is een vrij simplistische serie waarbij je heel snel tegen issues aan gaat lopen. Je hebt requirements die niet aansluiten bij de data die opgeslagen is waardoor je aardig wat rekenwerk moet gaan doen. Je maakt dan de afweging tussen welke data je op wilt slaan, met het risico op dubbele data, en de complexiteit in je reporting qua rekenwerk.
Op dit moment zie je dat de complexiteit op reporting vrij groot wordt terwijl het met een iets meer data een stuk makkelijker zou gaan.

Je ziet hier heel goed hoe het met iets meer werk een stuk eenvoudiger zou kunnen zijn om vervolgens je data uit het systeem te halen.

[Janoz]

Tsurany schreef op woensdag 25 mei 2022 @ 11:24:
[...]

Het is een vrij simplistische serie waarbij je heel snel tegen issues aan gaat lopen. Je hebt requirements die niet aansluiten bij de data die opgeslagen is waardoor je aardig wat rekenwerk moet gaan doen. Je maakt dan de afweging tussen welke data je op wilt slaan, met het risico op dubbele data, en de complexiteit in je reporting qua rekenwerk.
Op dit moment zie je dat de complexiteit op reporting vrij groot wordt terwijl het met een iets meer data een stuk makkelijker zou gaan.

Je ziet hier heel goed hoe het met iets meer werk een stuk eenvoudiger zou kunnen zijn om vervolgens je data uit het systeem te halen.

Ik zeg niet dat je ongelijk hebt hoor, maar in principe doe jij nu ook aannames over de requirements. Misschien is dit wel een gedistribueerd systeem wat periodiek gesynchroniseerd wordt, of een export uit meerdere systemen.

Er is sowieso meer werk nodig. Dat kun je aan het begin doen, zoals jij aangeeft, maar achteraf is niet onmogelijk en ook niet per definitie fout.

[Tsurany]

Janoz schreef op woensdag 25 mei 2022 @ 12:44:
[...]


Ik zeg niet dat je ongelijk hebt hoor, maar in principe doe jij nu ook aannames over de requirements. Misschien is dit wel een gedistribueerd systeem wat periodiek gesynchroniseerd wordt, of een export uit meerdere systemen.

Er is sowieso meer werk nodig. Dat kun je aan het begin doen, zoals jij aangeeft, maar achteraf is niet onmogelijk en ook niet per definitie fout.

Achteraf is niet per definitie fout maar wel veel meer werk. Maar ik hoop dat ik ook daarvoor in ieder geval een suggestie heb gegeven.

offtopic:
En iets zegt me dat dit een huiswerkopdracht is...

[Reinier]

Je kunt met lead() en lag() naar volgende of vorige records gaan. Althans, in SQL Server, ik weet niet welke database hier gebruikt wordt.
Ik heb in DWH's heel vaak met zulke data te maken en over het algemeen kun je het datamodel dan niet eenvoudig veranderen. Moet het in één query of heb je de mogelijkheid om de boel in stukken te knippen in tijdelijke tabellen e.d.? Dat zou het enorm vereenvoudigen.

@Maasluip impliciete joins

[ Voor 4% gewijzigd door Reinier op 25-05-2022 13:01 ]

[Maasluip]

Reinier schreef op woensdag 25 mei 2022 @ 12:59:

@Maasluip impliciete joins

Is maar waar je mee opgegroeid bent. Ik doe ook vi en spaces, geen tabs. Shoot me.
Ik vind het ook een veel overzichtelijkere notatie.

[luukvr]

Woy schreef op woensdag 25 mei 2022 @ 11:09:
[...]

Het gaat mij niet om het feit of je wel/niet datums kunt sommeren, maar dat je daarmee niet de tijd krijgt dat hij in die status was. Dat is namelijk de tijd die tussen de twee datums van twee entries vallen

Je zult meer zoiets moeten doen

SQL:

SELECT task_id, SUM(COALESCE(endDate, getdate()) - startDate) as totalTime
FROM (
    SELECT start.task_id, start.date_time as startDate, MIN(end.date_time) as endDate
    FROM HistoryStatus as start
    LEFT JOIN HistoryStatus as end
        ON start.task_id = end.task_id AND end.date_time > start.date_time
    WHERE start.status IN ( 'TAKEN', 'UNHOLD' )
    AND end.status IN ('HOLD', 'DONE' )
    GROUP BY start.task_id, start.date_time)

edit: Ik zie inderdaad dat jouw voorbeeld ook goed uit kan komen als er inderdaad gegarandeerd kan worden dat er altijd 1 ( en ook maximaal 1 ) Unhold event voor een Hold event is. Mijn voorbeeld is denk iets robuuster omdat je ook bij per ongeluk 2 unhold events gewoon goed gaat (Ik heb de query nog iets aangepast zodat hij ook nog een resultaat geeft als de taak op dit moment nog niet is afgesloten)

end.status conditie in where stoppen verlies je het nut van de left join een beetje en de extra superquery is een beetje nutteloos maar voor de rest wel de handigste oplossing lijkt me

Zoiets dus:

SQL:

SELECT hold.task_id, hold.date_time, MIN(unhold.date_time)
FROM historystatus hold
LEFT JOIN historystatus unhold
ON hold.task_id=unhold.task_id AND unhold.status='Unhold' AND unhold.date_time>hold.date_time
WHERE hold.status='Hold'
GROUP BY hold.task_id, hold.date_time