0

I have two tables in PostgreSQL like this:

Process 
- id : uuid

Log
 - created : date
 - status : string
 - parentProcess : uuid

A process can have several logs related to it, and their status can be 'SUCCESS', 'FAILED', 'PENDING'.

I would like to group all of my processes based on the latest associated Log status:

PENDING FAILED SUCCESS
7 4 23

For example, if a Process has 3 Logs with the following data

Process a {
   id: 1
}

Log 1 {
 - created : 2020-05-18 11:02:23.035+00
 - status: "PENDING"
 - parentProcess: 1
}

Log 2 {
 - created : 2020-05-18 17:57:44.002+00
 - status: "SUCCESS"
 - parentProcess: 1
}

Log 3 {
 - created : 2020-05-18 09:31:02.081+00
 - status: "FAILED"
 - parentProcess: 1
}

this process should be increase the 'SUCCESS' column because its latest log (Log 2) has 'SUCCESS' status.

How would I write the SELECT to be able to do this?

1

One way to accomplish this is with a window function and grouping applied using the GROUP BY clause and an aggregate function with a case statement like so:

WITH CTE_Log_Sorted AS -- Creates a CTE (can do this in a subquery too) to generate unique row numbers for each status within a partition of parentProcess starting with the latest status first (ordered by created descending).
(
    SELECT status, ROW_NUMBER() OVER (PARTITION BY parentProcess ORDER BY created DESC) AS SortId
    FROM Log
)

SELECT
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS PENDING, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS FAILED, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS SUCCESS -- Case statements to sum up only the status we want to count in this column
FROM CTE_Log_Sorted
WHERE SortId = 1 -- Filter out everything but the latest status of each parentProcess
GROUP BY status -- Group on each status so we can sum up the count of each in our SELECT clause

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.