基於雙 where 條件和組最大總和的上一行

我有這樣一張桌子，我想得到這些值：

+───────────────────+────────+──────────+──────────────────────────────────────────────────────+
| 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       |                                                      |
+───────────────────+────────+──────────+──────────────────────────────────────────────────────+

我可以像這樣繪製每天的最大值：

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

我可以像這樣增加每一行的值：

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

我只是不知道如何獲得這種效果，當天的第一行是前一天的最大總和的值（正是我上面描述的範例）這在正常 SQL 查詢級別是否可行?

範例： https ://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=0201a0c943a1a499791e2279be545d5f

不是最優雅的解決方案，但我認為我們應該將其分為兩種情況，第一天或剩余天數：

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
;

我們真的不需要 CTE:s for first_day, rem_days 所以稍微簡化一下：

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  

可以進一步簡化為：

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  
;

還有一種方法是對累積和進行分區，因為 case 表達式只考慮 init <> 1，我們將獲得其餘部分的執行總和。然後我們需要添加偏移量：

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://dba.stackexchange.com/questions/312934