How to calculate the difference of two sums in SQL

Question

I'm using Grafana to show some data from Clickhouse. The data comes from a table containing itime, count and some other columns.

id  method   count        itime
1    aaa      12     2021-07-20 00:07:06
2    bbb      9      2021-07-20 00:07:06
3    ccc      7      2021-07-20 00:07:07
...

Now I can execute the following SQL to get the sum of count between two itimes:

SELECT toUnixTimestamp(toStartOfMinute(itime)) * 1000 as t,
       method,
       sum(count) as c
FROM me.my_table
WHERE itime BETWEEN toDateTime(1631870605) AND toDateTime(1631874205)
      and method like 'a%'
GROUP BY method, t
HAVING c > 500
ORDER BY t

It works as expected.

Now, I want to select the sum(count) according to the difference between sum(count) - sum(count)<--7-day-ago. Something like SELECT ... FROM ... WHERE ... HAVING c - c<--7-day-ago >= 100. But I don't know how.

Answer 1

i had some similar problem a while ago

please check the SQLfiddle
to see the result press buttons: first- build schema, second: run sql

naming

i assumed that you want for the same period A you selected a seven days later period B of time to compare (you need to be more specific, what you really looking for).

• period A = your selected time period (between from and to)
• period B = your selected time period one week in the past

problem

this is a real delicate question, if i understood the question right.
your example is grouped by minute inside a period A. this means, you really need to have data in period A for every minute you have data in period B, otherwise you will ignore period B data inside your chosen period.

as you can see in the sqlfiddle, i made two query strings. the first one is working, but ignores B data. the second one does a right join (sadly mysql does not support full outer joins to show all in one table) and shows 2 ignored entries.

it even makes it worse, because you group by method too.
(in this case for the fiddle you have to change the last line of the join and add:)

as b on a.unix_itime = b.unix_itime and a.method = b.method

this means, you need for every selected method and period minutewise data.

it would be better if you group only by the method and not time, as you already use a time condition (period A) to keep it small.
or do the stepping bigger, by hour or day..

this code should fit your envirement (mysql does not support toUnixTimestamp, toStartOfMinute, toDateTime):

SELECT 
    a.unix_itime * 1000 as t,
    a.method,
    a.sum AS c,
    b.sum AS c2,
    ifnull(a.sum,0) - ifnull(b.sum,0) as diff,
    
FROM (select method, sum(count) as sum, toUnixTimestamp(toStartOfMinute(itime)) as unix_itime
      from my_table
      WHERE method like 'a%' and
          itime BETWEEN toDateTime(1631870605) 
                    AND toDateTime(1631874205)
      GROUP BY method, unix_itime) 
          as a

  LEFT JOIN (select method, sum(count) as sum, toUnixTimestamp(toStartOfMinute(itime + INTERVAL 7 DAY)) as unix_itime
            from my_table
            WHERE method like 'a%' and
                itime BETWEEN toDateTime(1631870605)- INTERVAL 7 DAY 
                          AND toDateTime(1631874205)- INTERVAL 7 DAY
            GROUP BY method, unix_itime) 
                as b on a.unix_itime = b.unix_itime and a.method = b.method

ORDER BY a.unix_itime;