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
;
https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=9ad89b0a8d8902c9ead5d7658f76c738
