how can I calculate the SUM in 4days buckets over all dates

Question

I have a MySQL DB where one column is the DATE and the other column is the SIGNAL. Now I would like to calculate the SUM over Signal for 4 days each.

f.e.

    SUM(signal over DATE1,DATE2,DATE3,DATE4)
    SUM(signal over DATE5,DATE6,DATE7,DATE8)
    ...

    whereas Date_N =  successor of DATE_N-1 but need not to be the day before

Moreless the algo should be variable in the days group. 4 ist just an example.

Can anyone here give me an advice how to perform this in MySQL?

I have found this here group by with count, maybe this could be helpful for my issue? Thanks

Edit: One important note: My date ranges have gaps in it. you see this in the picture below, in the column count(DISTINCT(TradeDate)). It should be always 4 when I have no gaps. But I DO have gaps. But when I sort the date descending, I would like to group the dates together always 4 days, f.e. Group1: 2017-08-22 + 2017-08-21 + 2017-08-20 + 2017-08-19, Group2: 2017-08-18 + 2017-08-17+2017-08-15+2017-08-14, ...

maybe I could map the decending dateranges into a decending integer autoincrement number, then I would have a number without gaps. number1="2017-08-17" number2="2017-08-15" and so on ..

Edit2: As I see the result from my table with this Query: I might I have double entries for one and the same date. How Can I distinct this date-doubles into only one reprensentative?

     SELECT SUM(CondN1),count(id),count(DISTINCT(TradeDate)),min(TradeDate),max(TradeDate)  ,min(TO_DAYS(DATE(TradeDate))),id FROM marketstat where Stockplace like '%'   GROUP BY TO_DAYS(DATE(TradeDate)) DIV 4 order by TO_DAYS(DATE(TradeDate))

@TimBiegeleisen DATE1 is a DateTIME f.e. 2017-08-25 13:00:00 and DATE2 is f.e. 2017-08-22 10:00:00 — Walter Schrabmair, Aug 26 '17 at 07:52

KIKO Software · Answer 1 · 2017-08-26T08:04:41.500

2

SUM() is a grouping function, so you need to GROUP BY something. That something should change only every four days. Let's start by grouping by one day:

SELECT SUM(signal) 
FROM tableName
GROUP BY date

date should really be of type DATE, like you mentioned, not DATETIME or anything else. You could use DATE(date) to convert other date types to dates. Now we need to group by four dates:

SELECT SUM(signal) 
FROM tableName
GROUP BY TO_DAYS(date) DIV 4

Note that this will create an arbitary group of four days, if you want control over that you can add a term like this:

SELECT SUM(signal) 
FROM tableName
GROUP BY (TO_DAYS(date)+2) DIV 4

edited Aug 26 '17 at 08:04

answered Aug 26 '17 at 07:52

KIKO Software

15,283
3
18
33

One more question to your great solution: I have not all dates continously I have dateranges with leaps. maybe we can make a select where we order by id or date and take always the corresponding 4 days-dates. Can you understand what I mean. – Walter Schrabmair Aug 26 '17 at 12:20
If you just want to to group by four successive dates, you only have one row per date and your id is sequential and auto-increasing, then you can simply replace `TO_DAYS(date)` by `id` to achieve what you want. In all other cases it will get more complex. Since this is clearly different from you current question you should ask a new question. – KIKO Software Aug 26 '17 at 21:35
Thanks to verify my assumption, as I did it in my answer. You was a great help – Walter Schrabmair Aug 27 '17 at 06:54

score 0 · Answer 2 · answered Aug 26 '17 at 13:39

In the meantime and with help of KIKO I have found the solution: I make a temp table with

    CREATE TEMPORARY TABLE  if not EXISTS tradedatemaptmp (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT Tradedate AS Tradedate, CondN1, CondN2 FROM marketstat WHERE marketstat.Stockplace like 'US' GROUP BY TradeDate ORDER BY TradeDate asc;

and use instead the originate tradedate the now created id in the temp table. So I could manage that - even when I have gaps in the tradedate range, the id in the tmp table has no gaps. And with this I can DIV 4 and get the always the corresponding 4 dates together.

how can I calculate the SUM in 4days buckets over all dates

2 Answers2