2

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

6
  • What version are you using? MYSQL ET al have improved a lot in recent versions. Also, create a dbfiddle.uk/?rdbms=mysql_8.0 that demonstrates the problem and update your question with the url and expected result Jun 3 at 21:00
  • @Lennart MariaDB 10.5.15 dbfiddle.uk/… it can also be a procedure, if it is not possible to do it this way from SQL Query level (e.g. using WITH), but in both cases I do not know how to do it or where to start
    – DeepSea
    Jun 3 at 21:09
  • So for first date you want value as value and after that it should be the cumulative sum? Jun 3 at 21:14
  • yes, exactly as it is in the example I described
    – DeepSea
    Jun 3 at 21:21
  • At 2022-06-04 09:00, should not valueA be 19 (17+2)? Jun 3 at 22:29

1 Answer 1

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

1
  • 1
    Yes, this is exactly the result I wanted to achieve. Thanks for your help and posting so many solutions :)
    – DeepSea
    Jun 4 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.