Working with accumulated sums and sql partitioning

user_9889127 0
Viewed 74

I have to work with some processes and I need to calculate their lifetime until a certain date, but as they can be suspended, I'm having difficulties. I have a suspension table (I left it below). If you get the time (total days) of suspension on the base date, I can solve the problem. I thought of working with the accumulated sum, but as a process can be suspended several times, this sum doesn't work. In the table I have the process ID, the base date I want the suspension time and the date of a suspension. The table is intuitive.

For example, process 2301194 has two suspensions and when I make the accumulated sum I get the following result in Sqlite

SELECT
    *,
    SUM(TIME_SUSPENSION) OVER (PARTITION BY ID_PROCESS ORDER BY DATA_BASE) TIME_AUX
FROM
    (
    SELECT
        *,
        JULIANDAY(DATA_BASE) - JULIANDAY(DATA_SUSPENSION) TIME_SUSPENSION
    FROM
         SUSPENSIONS
    ORDER BY
        ID_PROCESS,
        DATA_BASE)
 WHERE ID_PROCESS = 2301194;

enter image description here

When would the desired result be

enter image description here

Which in this case would be the time on that date (base date) plus the accumulated time until the last suspension.

The data https://raw.githubusercontent.com/jacksonMaike/database/master/trego/suspensions.csv To make it easier, I left a .cd containing the table in the repository.

Does anyone have any suggestions? Thanks in advance!

date-arithmetic window-functions date sqlite sql
1 Answers

You can use a CTE to calculate the accumulated time until the last suspension and then join to the table:

WITH cte AS (
  SELECT DATA_SUSPENSION, 
         SUM(MAX(JULIANDAY(DATA_BASE) - JULIANDAY(DATA_SUSPENSION))) 
         OVER (ORDER BY DATA_SUSPENSION ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ACC_TIME_SUSPENSION
  FROM SUSPENSIONS 
  WHERE ID_PROCESS = 2301194  
  GROUP BY DATA_SUSPENSION
)
SELECT s.*, 
       JULIANDAY(s.DATA_BASE) - JULIANDAY(s.DATA_SUSPENSION) TIME_SUSPENSION,
       JULIANDAY(s.DATA_BASE) - JULIANDAY(s.DATA_SUSPENSION) + COALESCE(c.ACC_TIME_SUSPENSION, 0) TIME_AUX
FROM SUSPENSIONS s INNER JOIN cte c
ON c.DATA_SUSPENSION = s.DATA_SUSPENSION
ORDER BY s.DATA_BASE;

Or, for all the ID_PROCESS:

WITH cte AS (
  SELECT ID_PROCESS, DATA_SUSPENSION, 
         SUM(MAX(JULIANDAY(DATA_BASE) - JULIANDAY(DATA_SUSPENSION))) 
         OVER (
           PARTITION BY ID_PROCESS 
           ORDER BY DATA_SUSPENSION ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
         ) ACC_TIME_SUSPENSION
  FROM SUSPENSIONS 
  GROUP BY ID_PROCESS, DATA_SUSPENSION
)
SELECT s.*, 
       JULIANDAY(s.DATA_BASE) - JULIANDAY(s.DATA_SUSPENSION) TIME_SUSPENSION,
       JULIANDAY(s.DATA_BASE) - JULIANDAY(s.DATA_SUSPENSION) + COALESCE(c.ACC_TIME_SUSPENSION, 0) TIME_AUX
FROM SUSPENSIONS s INNER JOIN cte c
ON c.ID_PROCESS = s.ID_PROCESS AND c.DATA_SUSPENSION = s.DATA_SUSPENSION
ORDER BY s.ID_PROCESS, s.DATA_BASE;

See the demo.

user_104988280
Related