Working with accumulated sums and sql partitioning-CodePudding

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;

When would the desired result be

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!

CodePudding user response：

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.