1
votes

Comment obtenir la dernière ligne en fonction d'une condition avec groupe par colonne dans postgres

J'ai une table

task_id     time_diff_minutes
1           1
3           2

J'ai besoin d'obtenir les données uniquement pour les tâches où event_type est fermé, qui est la dernière ligne pour cet identifiant de tâche. Dans l'exemple ci-dessus, je devrais obtenir les données pour task_id 1 et 3 mais pas pour 2 car la tâche 2 n'est pas fermée. Donc, pour chaque tâche, si la dernière ligne est fermée, alors il devrait obtenir la différence de temps entre la dernière et l'avant-dernière ligne pour cette tâche.

task_id     timestamp                           event_type
1           "2018-12-20 14:43:53.661153+01"     "open"
1           "2018-12-20 14:43:53.661153+01"     "interrupted"
1           "2018-12-20 14:43:53.661153+01"     "opened"
1           "2018-12-20 14:44:53.661153+01"     "closed"
2           "2018-12-20 14:43:53.661153+01"     "opened"
2           "2018-12-20 14:43:53.661153+01"     "interrupted"
2           "2018-12-20 14:43:53.661153+01"     "opened"
3           "2018-12-20 14:43:53.661153+01"     "opened"
3           "2018-12-20 14:45:53.661153+01"     "closed"

postgresql sql

2 commentaires

Pouvez-vous poster le code que vous avez déjà essayé.

Une tâche peut-elle être rouverte? Ou y a-t-il toujours un seul fermé par tâche et c'est toujours la dernière ligne?

3 Réponses :

1
votes

Nous pouvons agréger par task_id , puis faire pivoter les horodatages d'ouverture et de fermeture, si les deux existent. Si une tâche a en fait un horodatage de clôture, nous le signalons.

SELECT
    task_id,
    EXTRACT(epoch from MAX(CASE WHEN event_type = 'closed' THEN timestamp END) -
        MAX(CASE WHEN event_type <> 'closed' THEN timestamp END)) / 60 AS time_diff_minutes
FROM yourTable
GROUP BY task_id
HAVING COUNT(CASE WHEN event_type = 'closed' THEN 1 END) > 0;

3 commentaires

Il est plus facile d'utiliser event_type <> 'closed' au lieu de = 'open' pour obtenir l ' deuxième dernière ligne puis HAVING MAX (CASE WHEN event_type = 'closed' THEN timestamp END)> MAX (CASE WHEN event_type <> 'closed' THEN timestamp END)

@dnoeth Je vais aller un peu plus loin et dire que ma réponse, avant la modification de votre commentaire, était fausse. Je pensais que l'OP voulait juste du temps ouvert total, mais pas du tout.

Eh bien, vous devez également modifier le HAVING, il y a 'open' et 'open' :-)

1
votes

Vous pouvez utiliser la fonction LAG de postgres. Vérifiez le SQL ci-dessous: -

select test_id, extract(epoch from(event_time - prev_time))/60 as diff
from (select test_id,
LAG(event_time) over(partition by test_id) prev_time
,event_time
,event_type
from test
) a11
where event_type = 'closed'

SQL Fiddle a >

0 commentaires

0
votes

select task_id
   ,extract(epoch from(event_time - prev_time))/60 as diff
from 
 ( select task_id,
      row_number()     -- to filter the latest row
      over(partition by task_id
           order by event_time desc) as rn
     ,lead(event_time) -- second last row for that task
      over(partition by task_id
           order by event_time desc) as prev_time
     ,event_time
     ,event_type
   from test
 ) as dt
where rn = 1                -- last event
  and event_type = 'closed' -- must be closed

0 commentaires