Previous Row based on double where condition and group max sum



  • 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        | 

    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



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2