Previous Row based on double where condition and group max sum

All we need is an easy explanation of the problem, so here it is.

I have such a table and I would like to get these values:

+───────────────────+────────+──────────+──────────────────────────────────────────────────────+
| TS                | Value  | ValueA   |                                                      |
+───────────────────+────────+──────────+──────────────────────────────────────────────────────+
| 2022-06-03 05:00  | 1      | 1        |                                                      |
| 2022-06-03 06:00  | 2      | 2        |                                                      |
| 2022-06-03 07:00  | 3      | 3        |                                                      |
| 2022-06-03 08:00  | 4      | 4        |                                                      |
| 2022-06-03 09:00  | 5      | 5        |                                                      |
| 2022-06-03 10:00  | 6      | 6        | <-- Max Value for 2022-06-03 (6)                     |
| 2022-06-04 05:00  | 2      | 8        | <-- Max from 2022-06-03 + current group value (6+2)  |
| 2022-06-04 06:00  | 5      | 13       | <-- previous value + next from row (6+5)             |
| 2022-06-04 07:00  | 1      | 14       |                                                      |
| 2022-06-04 08:00  | 3      | 17       |                                                      |
| 2022-06-04 09:00  | 2      | 20       |                                                      |
| 2022-06-04 10:00  | 5      | 25       |                                                      |
| 2022-06-05 05:00  | 1      | 26       | <-- Max from 2022-06-04 + current group value (25+1) |
| 2022-06-05 06:00  | 1      | 27       | <-- previous value + next from row (25+1)            |
| 2022-06-05 07:00  | 9      | 36       |                                                      |
| 2022-06-05 08:00  | 3      | 39       |                                                      |
| 2022-06-05 09:00  | 2      | 41       |                                                      |
| 2022-06-05 10:00  | 1      | 42       |                                                      |
+───────────────────+────────+──────────+──────────────────────────────────────────────────────+

The maximum values from each day I can draw like this:

select DATE_FORMAT(DATE_ADD(ts, INTERVAL 30 MINUTE),'%Y-%m-%d') as time, max(Value)
from MyTable
group by time

and I can increase the values in each row like this:

select DATE_FORMAT(DATE_ADD(ts, INTERVAL 30 MINUTE),'%Y-%m-%d %H:00') as time, ROUND(sum(Value) over (order by time),2) as 'ValueA' from MyTable order by time

I just have no idea how to get this effect, where in the first row of the day is the value of the maximum sum of the previous day (exactly the example as I described above)
Is this feasible at the level of a regular SQL Query?

Example:
https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=0201a0c943a1a499791e2279be545d5f

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Not the most elegant solution, but I believe we should divide it into two cases, first day or remaining days:

with classify (ts, value, init) as 
-- classify rows as first_day or remaining day
(
  select ts, value
       , first_value(date(ts)) over (order by ts) = date(ts) as init
  from MyTable
), first_day as ( 
  select ts, value, value as valueA
  from classify 
  where init = 1
), rem_days as (
  select ts, value, sum(value) over (order by ts) as valueA
  from classify
  where init = 0
)
select ts, value, valueA from first_day
union all
select ts, value, valueA + (select max(value) from first_day) 
from rem_days
order by ts
;

We don’t really need the CTE:s for first_day, rem_days so a slight simplification is:

with classify (ts, value, init) as 
(
  select ts, value
       , first_value(date(ts)) over (order by ts) = date(ts) as init
  from MyTable
)
select ts, value
     , case when init = 1 
       then value
       else sum(value) over (order by ts) 
            - (select sum(value) from classify where init = 1)
            + (select max(value) from classify where init = 1)
       end as valueA
from classify
order by ts  

Which can be further simplified as:

with classify (ts, value, init) as 
(
  select ts, value
       , first_value(date(ts)) over (order by ts) = date(ts) as init
  from MyTable
)
select ts, value
     , case when init = 1 
            then value
            else sum(value) over (order by ts)
               -- remove all but last row from offset 
               - (select sum(value)-max(value) 
                  from classify where init = 1)
       end as valueA
from classify
order by ts  
;

Yet another way is to partition the cumulative sum, since the case expression only accounts for init <> 1, we will get the running sum for the rest. We then need to add the offset:

with classify (ts, value, init) as 
(
  select ts, value
       , first_value(date(ts)) over (order by ts) = date(ts) as init
  from MyTable
)
select ts, value
     , case when init = 1 
            then value
               -- cumulative sum for init = 0 
            else sum(value) over (partition by init order by ts)
               -- add offset
               + (select max(value) from classify where init = 1)
       end as valueA
from classify
order by ts  
;

Fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply