Discussion :

[Krysstof]

Bonjour
désolé du post un peu long, mais je suis tout seul pour trouver une solution à un probleme, et je ne sais pas si je pars dans la bonne direction, mais j'aimerai faire des query pure SQL pour répondre à un besoin d'éviter des programme/script externes à SQL


j'ai une table de "logging" qui stocke des déroulement de sessions de tâches, avec une seule colonne de temps
dans une session j'ai des tache qui démarre, avec un id du type de tache
la fin de tache est un autre id de type de tache
une tache peut ne pas avoir de fin de tache, car la session est terminée

cela donne en exemple ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
id_session | id_type | timestamp
1          | 1       | 2022-03-10 10:00:00
1          | 2       | 2022-03-10 10:00:00
1          | 3       | 2022-03-10 10:05:00
1          | 4       | 2022-03-10 10:25:00
1          | 5       | 2022-03-10 10:20:00
1          | 2       | 2022-03-10 10:29:00
1          | 7       | 2022-03-10 10:30:00
2          | 1       | 2022-03-10 11:00:00
2          | 2       | 2022-03-10 11:00:00
2          | 3       | 2022-03-10 11:05:00
2          | 7       | 2022-03-10 10:05:00

dans cet exemple j'ai une session qui démarre qui dure 30 minute entre le type 1 et 7 (start session, end session)
et j'ai 3 sous-tache qui démarre avec l'id 2 et se termine avec le type 3 ou démarre avec l'id 4 et se termine avec l'id 5
je peux avoir plusieur tache de plusieur type différent (chacune avec son id de start et end, j'en ai 2 à gérer, je peux donc hardcoder que le type 2 et 3 vont ensemble, et 4 et 5 vont ensemble)

je dois calculer le temps des sessions et le temps des tâche, partant du principe que si je n'ai pas la fin de tache, c'est la fin de session qui compte pour la tache en cours.

(pour contexte, c'est une table de logging qui est dans une app propriétaire, qui logge comme cela et je n'ai pas la main dessus)

idéalement je souhaiterai obtenir 2 tables
table Session

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ID_Session | Start | End | duration

Table tache

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ID_Session | ID_Type | Start | End | duration

le ID_Type ne serait que le l'id de démarrage (2 ou 4 dans mon exemple)

j'ai codé l'algorithme a coup de programmation externe mais je souhaiterai faire cela au travers de query SQL pour sortir 2 table et essayer d'inclure cela dans un ETL qui n'aura pas besoin d'un programme externe. l'algorithme est assez "simple", je boucle sur l'id de session, j'itère chaque ligne avec des variables et dès que je trouve l'id de fin correspondant je stocke la date de fin associée a la date de début, et je calcule la durée, avec le cas ou la session se termine avec une tache en cours.



j'utilise SQL Server, je peux utiliser des procédures stockées si nécessaire et adapter l'algorithme que j'ai en c#, mais je me demandais si il existait un moyen de faire cela en query sans procédure/programmation et je ne suis pas assez avancé en SQL pour trouver comment faire, j'aimerai comprendre comment je peux arriver à ce résultat ou si c'est simplement impossible en SQL et qu'il faut que je passe par une procédure stockée, je peux me débrouiller avec cela
je peux rajouter des table comme l'association des id de début et fin de tache si cela peut simplifier la query

Merci d'avance, et d'avoir lu

[Krysstof]

Je vais déja m'autorépondre pour la première table :

table Session

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ID_Session | dateStart | dateEnd | duration

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select a.id_session, a.timestamp as dateStart, b.timestamp as dateEnd 
from table_log a, table_log b 
where a.id_session = b.id_session 
and a.id_type=1 and b.id_type=7

cela me pique les yeux et le cerveau mais j'ai mon résultat et qui a l'air ok si je parcours ma table manuellement

pour la 2eme partie, les taches à l'interieur d'une session, je n'y arrive pas, car je n'ai pas d'unicité sur la tache avec les id 2 et 4, s'il se produisent plusieurs fois dans la meme session, j'ai plusieur occurence de fin de tache pour chaque début (dans mon exemple pour 2 tache ID2, j'ai 4 lignes au total si j'essaye de matcher l'id 3 ou 7 de fin de session, ou si j'ai 4 taches qui se terminent bien avec l'id3, j'ai 4x4=16 lignes...)
je suis assez mauvais en jointure (comme on peut le voir sur le bout de sql juste au dessus) mais je n'arrive pas à limiter sur une date de fin supérieure à la date de début, et limiter à la 1ere occurence, sur la jointure

j'ai cherché des exemples ou autre, mais tous partent du principe qu'il y a un Unique ID quelque part, et ce n'est pas mon cas malheureusement

[AaâÂäÄàAaâÂäÄàAaâÂäÄ]

Vous écrivez des jointures à l'ancienne.
Avec la norme sql actuelle, on fait plutôt :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select a.id_session, a.timestamp as dateStart, b.timestamp as dateEnd 
from table_log a
inner join table_log b 
       on a.id_session = b.id_session and a.id_type=1 and b.id_type=7

[Krysstof]

Bonjour

déja merci AaâÂäÄàAaâÂäÄàAaâÂäÄ, effectivement j'ai trouvé cela avec un poil de recherche et apprentissage des join

j'avoue avoir passer les dernieres 24h dans les méandres des doc sql pour comprendre les jointures, les jointure latérale/cross etc...

j'étais presque arrivé a quelque chose avec un outer cross sur une sub-query, tout en réalisant que quoi que je fasse la sous query ne se ré-exéctutait pas quand je le voulais et tous mes timestamp de fin de job étaient bloqués sur la 1ere de la liste

finalement j'ai réussi à comprendre quelque chose d'assez fondamental qui m'a débloqué : faire une sub-query scalaire dans la clause select permet de réutiliser les valeurs de la query principale, ce qui est impossible clause from, ou ne fonctionne pas comme je le souhaite en jointure...

en gros cela donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select a.id_session, a.timestart as DateStart, (select top 1 b.timestamp from log_table b where a.id_session = b.id_session and b.type = 7 and a.timestamp < b.timestamp order by b.timestamp) as DateEnd
from log_table a
where a.type = 1

pour une raison étrange j'ai 2 ligne identique qui remontent en sortie, du coup un "Distinct" résoud ce probleme

l'avantage de cette méthode, c'est que la meme subquery peut s'appliquer à tous les niveaux (session et job)
rajoutons à cela les cas multiples, avec possibilité que le job j'ai pas de fin propre avant la fin de session, la query devient un poil plus complexe mais gérable tout de meme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select distinct a.id_session, a.timestart as DateStart, 
  case a.type 
    WHEN 1 (select top 1 b.timestamp from log_table b where a.id_session = b.id_session and type = 7 and a.timestamp < b.timestamp order by b.timestamp) 
    WHEN 2 (select top 1 b.timestamp from log_table b where a.id_session = b.id_session and (type = 3 or type = 7) and a.timestamp < b.timestamp order by b.timestamp) 
    WHEN 4 (select top 1 b.timestamp from log_table b where a.id_session = b.id_session and (type = 5 or type = 7) type = 7 and a.timestamp < b.timestamp order by b.timestamp) 
  END AS DateEnd,
  case a.type
    WHEN 1 'Session'
    WHEN 2 'JobA'
    WHEN 4 'JobB'
  END AS JobType 
from log_table a
where a.type in (1,2,4)
Order by a.timestamp

bien sur maintenant que j'ai ca qui fonctionne, mon boss remarque que des sessions se termine apres 20h00 et qu'il ne faut pas logger le temps passer 20h00, du coup je suis parti pour aller affiner cela quelque par, je pense à coup de datepart et concaténation

mais bon, question "résolue", mais qui que ce soit à une meilleure idée, ou une explication sur pourquoi j'ai 2 lignes sur la query que j'ai mis en premier, et que je doive faire un distinct, la je comprend pas vraiment