MySQL Cumulative Sum by Distinct Contact ID by Year-Month

MySQL

I've seen some posts here regarding the SUM OVER function to calculate the cumulative sum. But the issue which I have is slightly different. I need to keep a running total of distinct inactive clients by year-month. So far I've created the query to give the result as:

date       InactiveClients  TotalInactiveClients
2016-04    91954             91954
2016-03    90065             182019 <-- sums 2016-04 and 2016-03
2016-02    84517             266536 <-- but in past 3 months i had around 80k of inactivity
2016-01    101561            368097
2015-12    113953            482050
2015-11    85425             567475
2015-10    95681             663156
2015-09    80503             743659
2015-08    82327             825986
2015-07    96672             922658
2015-06    84375             1007033
2015-05    83740             1090773
2015-04    96933             1187706
2015-03    68572             1256278

The query is which gives me the result is here:

SELECT
      Date_FORMAT(pq.date, "%Y-%m") date,
      pq.InactiveClients as 'InactiveClients',
      @runBal := @runBal + pq.InactiveClients as TotalInactiveClients
  FROM ( SELECT 
           distinct ss1.time date, 
           COUNT(DISTINCT c1.id) as InactiveClients
         FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
             ON (sv1.email = ss1.email 
             AND sv1.dispatchID = ss1.dispatchID 
             AND sv1.email is NULL)
         WHERE ss1.email = c1.email 
           AND ss1.time > DATE_SUB(now(), interval 13 MONTH)
         GROUP BY YEAR(date), MONTH(date)
         ORDER BY date desc ) pq,
      ( select @runBal := 0 ) sqlvars

PS: in case you are wondering with the following LEFT JOIN I am excluding all the active contacts.

LEFT JOIN `statengineViews` AS sv1 
  ON (sv1.email = ss1.email 
  AND sv1.dispatchID = ss1.dispatchID 
  AND sv1.email is NULL)
WHERE ss1.email = c1.email 
  AND ss1.time > DATE_SUB(now(), interval 13 MONTH)

I need help with the following:
Could anyone help me to make the result of TotalInactiveClients equal the sum of previous months unique contact IDs?
(For example 2015-03 TotalInactiveClients = uniqe count of contactIDs inbetween 2015-03 upto 2016-04. So for 2015-10 – i need all the contactID's which occurred from 2016-04 to 2015-10 (including 2015-10)).

Best Answer

OK since no-one seemed to reply (except for Michael Green - Thanks) i did the MySQL statement the long way. If anyone has an idea how to make it smaller or how to combine them, then please let me know:

Last months inactivity:

Select b.date , count(b.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b
WHERE a.ContactID = b.ContactID

The result was:

date    inactivity
2016-03 83108

2 Months ago inactivity:

Select c.date , count(c.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1 
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email 
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 2 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 2 MONTH)
                   ORDER BY date desc)c
WHERE a.ContactID = b.ContactID AND b.ContactID = c.ContactID

The result was:

date    inactivity
2016-02 82355

AS you can see right now i'm using 2 statements which already are very big and sloppy and also i need to run 12 different queries to get the whole year inactivity. Can anyone help me combine these or make it work as an interval for a whole year? It does the trick but as i said, it's very sloppy :(

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided. But, those aren't the real problem. The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too. So it may come down to whether you want to pay for it now or pay for it later.

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Best Answer

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

Related Question