How to sum for previous n number of days for a number of dates in MySQL-CodePudding

I have a list of dates each with a value in MYSQL.

List of values

For each date I want to sum the value for this date and the previous 4 days.

I also want to sum the values for the start of that month to the present date. So for example:

For 07/02/2021 sum all values from 07/02/2021 to 01/02/2021
For 06/02/2021 sum all values from 06/02/2021 to 01/02/2021
For 31/01/2021 sum all values from 31/01/2021 to 01/01/2021

The output should look like:

Final Output

Any help would be appreciated.

Thanks

CodePudding user response：

In MYSQL 8.0 you get to use analytic/windowed functions.

SELECT
  *,
  SUM(value) OVER (
        ORDER BY date
    ROWS BETWEEN 4 PRECEEDING
             AND CURRENT ROW
  )                                AS five_day_period,
  SUM(value) OVER (
    PARTITION BY DATE_FORMAT(date, '%Y-%m-01')
        ORDER BY date
  )                                AS month_to_date
FROM
  your_table

In the first case, it's just saying sum up the value column, in date order, starting from 4 rows before the current row, and ending on the current row.

In the second case, there's no ROWS BETWEEN, and so it defaults to all the rows preceding the current row up to the current row. Instead, we add a PARTITION BY which says to treat all rows with the same calendar month separately from any rows on a different calendar month. This, all rows before the current one only looks back to the first row in the partition, which is the first row in the current month.

In MySQL 5.x there are no such functions. As such I would resort to correlated sub-queries.

SELECT
  *,
  (
    SELECT SUM(value)
      FROM your_table  AS five_day_lookup
     WHERE date >= DATE_SUB(your_table.date, INTERVAL 4 DAYS)
       AND date <=          your_table.date
  )
    AS five_day_period,
  (
    SELECT SUM(value)
      FROM your_table  AS monthly_lookup
     WHERE date >= DATE(DATE_FORMAT(your_table.date, '%Y-%m-01'))
       AND date <=                  your_table.date
  )
    AS month_to_date
FROM
  your_table

CodePudding user response：

Here is a other way to do that:

Select
      t1.`mydate` AS 'Date'
    , t1.`val` AS 'Value'
    
    , SUM( IF(t2.`mydate` >= t1.`mydate` - INTERVAL 4 DAY,t2.val,0)) AS '5 Day Period'
    , SUM( IF(t2.`mydate` >= DATE_ADD(DATE_ADD(LAST_DAY(t1.`mydate` ),INTERVAL 1 DAY),INTERVAL - 1 MONTH),t2.val,0)) AS 'Month of Date'

FROM tab t1
LEFT JOIN tab t2 ON t2.`mydate`
    BETWEEN LEAST( DATE_ADD(DATE_ADD(LAST_DAY(t1.`mydate` ),INTERVAL 1 DAY),INTERVAL - 1 MONTH),
            t1.`mydate` - INTERVAL 4 DAY)
    AND t1.`mydate`
GROUP BY t1.`mydate`
ORDER BY t1.`mydate` desc;

sample

MariaDB [bkvie]> SELECT * FROM tab;
 ---- ------------ ------ 
| id | mydate     | val  |
 ---- ------------ ------ 
|  1 | 2021-02-07 |   10 |
|  2 | 2021-02-06 |   30 |
|  3 | 2021-02-05 |   40 |
|  4 | 2021-02-04 |   50 |
|  5 | 2021-02-03 |   10 |
|  6 | 2021-02-02 |   20 |
|  7 | 2021-01-31 |   20 |
|  8 | 2021-01-30 |   10 |
|  9 | 2021-01-29 |   30 |
| 10 | 2021-01-28 |   40 |
| 11 | 2021-01-27 |   20 |
| 12 | 2021-01-26 |   30 |
| 13 | 2021-01-25 |   10 |
| 14 | 2021-01-24 |   40 |
| 15 | 2021-02-01 |   10 |
 ---- ------------ ------ 
15 rows in set (0.00 sec)

result

MariaDB [bkvie]> Select
    ->  t1.`mydate` AS 'Date'
    ->  , t1.`val` AS 'Value'
    ->  , SUM( IF(t2.`mydate` >= t1.`mydate` - INTERVAL 4 DAY,t2.val,0)) AS '5 Day Period'
    ->  , SUM( IF(t2.`mydate` >= DATE_ADD(DATE_ADD(LAST_DAY(t1.`mydate` ),INTERVAL 1 DAY),INTERVAL - 1 MONTH),t2.val,0)) AS 'Month of Date'
    -> FROM tab t1
    -> LEFT JOIN tab t2 ON t2.`mydate`
    ->  BETWEEN LEAST( DATE_ADD(DATE_ADD(LAST_DAY(t1.`mydate` ),INTERVAL 1 DAY),INTERVAL - 1 MONTH),
    ->  t1.`mydate` - INTERVAL 4 DAY)
    ->  AND t1.`mydate`
    -> GROUP BY t1.`mydate`
    -> ORDER BY t1.`mydate` desc;
 ------------ ------- -------------- --------------- 
| Date       | Value | 5 Day Period | Month of Date |
 ------------ ------- -------------- --------------- 
| 2021-02-07 |    10 |          140 |           170 |
| 2021-02-06 |    30 |          150 |           160 |
| 2021-02-05 |    40 |          130 |           130 |
| 2021-02-04 |    50 |          110 |            90 |
| 2021-02-03 |    10 |           70 |            40 |
| 2021-02-02 |    20 |           90 |            30 |
| 2021-02-01 |    10 |          110 |            10 |
| 2021-01-31 |    20 |          120 |           200 |
| 2021-01-30 |    10 |          130 |           180 |
| 2021-01-29 |    30 |          130 |           170 |
| 2021-01-28 |    40 |          140 |           140 |
| 2021-01-27 |    20 |          100 |           100 |
| 2021-01-26 |    30 |           80 |            80 |
| 2021-01-25 |    10 |           50 |            50 |
| 2021-01-24 |    40 |           40 |            40 |
 ------------ ------- -------------- --------------- 
15 rows in set (0.00 sec)



MariaDB [bkvie]>