Rolling count of total transactions over time

Question

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that week, but need to also get the transactions from any time before that week as well.

So let's say I have a table like this:

TransactionID  | Date
---------------|-----------
             1 | 8-04-2014
             2 | 8-05-2014
             3 | 8-18-2014
             4 | 8-18-2014
             5 | 8-20-2014

I want to have a select statement that would provide me with something like

TotalTransactionsToDate | Week | Year
------------------------|------|------
                      2 |    1 | 2014
                      5 |    3 | 2014

I am using MySql 5.5.38

score 2 · Accepted Answer · edited Mar 05 '16 at 10:59

What you want is called the cumulative sum, you can do something like:

create table transactions (transactionid int, d date);
insert into transactions (transactionid, d) 
    values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18')
         , (4, '2014-08-18'), (5,'2014-08-20');

select x.y, x.w,  count(1) 
from ( 
   select distinct year(d) as y, week(d) as w 
   from transactions
) as x 
join transactions y 
    on year(y.d) < x.y
    or ( year(y.d) = x.y
     and week(y.d) <= x.w ) 
group by x.y, x.w;  

+------+------+----------+
| y    | w    | count(1) |
+------+------+----------+
| 2014 |   31 |        2 |
| 2014 |   33 |        5 |
+------+------+----------+

I did not see your additional request for 2 2 for 2014. You can do that by replacing:

select distinct year(d) as y, week(d) as w 
from transactions

...with an expression that creates the whole domain for weeks. It is often a good idea to create a calendar table that you can use to join against to get reports for missing values etc.

score 2 · Answer 2 · answered Aug 21 '14 at 18:03

To get the basic data you need an aggregation:

select 1 + floor(datediff(date, mind) / 7) as week,
       year(date) as year,
       count(*) as num
from atable t cross join
     (select min(date) as mind
      from atable
     ) td
group by 1 + floor(datediff(date, mind) / 7),
         year(date)

You can extend this using variables to get the cumulative sum:

select week, year, num, (@cum := @cum + num) as cum
from (select 1 + floor(datediff(date, mind) / 7) as week,
             year(date) as year,
             count(*) as num
      from atable t cross join
           (select min(date) as mind
            from atable
           ) td
      group by 1 + floor(datediff(date, mind) / 7),
               year(date)
     ) x cross join
     (select @cum := 0) vars
order by year, week;

RolandoMySQLDBA · Answer 3 · 2014-08-21T17:31:38.950

Maybe you can group it by the most recent Monday of the Date

SELECT COUNT(1) Transactions,WEEK(monday) Week,YEAR(monday) Year
(
    SELECT (Date - INTERVAL WEEKDAY(Date) DAY) monday
    FROM mytable WHERE Date >= MAKEDATE(YEAR(NOW()),1)
) A GROUP BY YEAR(monday),WEEK(monday);

NOTE: The WHERE clause in the subquery collects everything from the first of the Year. You can use whatever WHERE clause is appropriate.

If I understand you correctly, you want to rollup each week with the sum of all weeks prior to a date like a spreadsheet. You would have to set up a user-defined variable and perform the iteration and summation

SET @S = 0;
SELECT
    (@S:=@S+Transactions) TotalTransactionsToDate,
    Week,Year
FROM
(
    SELECT COUNT(1) Transactions,WEEK(monday) Week,YEAR(monday) Year
    (
        SELECT (Date - INTERVAL WEEKDAY(Date) DAY) monday
        FROM mytable WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    ) A GROUP BY YEAR(monday),WEEK(monday)
) AA;

Give it a Try !!!

Rolling count of total transactions over time

3 Answers3

Linked

Related