Previous Row based on double where condition and group max sum

Question

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

Answer 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