Cumulative sum with mysql

Question

I have the following query:

set @cumulativeSum := 0;
select
  (@cumulativeSum:= @cumulativeSum + (count(distinct `ce`.URL, `ce`.`IP`))) as `uniqueClicks`,
  cast(`ce`.`dt` as date) as `createdAt`
from (SELECT DISTINCT min((date(CODE_EVENTS.CREATED_AT))) dt, CODE_EVENTS.IP, CODE_EVENTS.URL
      FROM CODE_EVENTS
      GROUP BY CODE_EVENTS.IP, CODE_EVENTS.URL) as ce
      join ATTACHMENT on `ce`.URL = ATTACHMENT.`ID`
where ATTACHMENT.`USER_ID` = 6
group by cast(`ce`.`dt` as date)
  ORDER BY ce.URL;

It works almost ok, I would like to have as result set a date and amount of cumulative sum as uniqueClicks, the problem is that in my result set it is not added up together.

uniqueClicks createdAt
1            2018-02-01
3            2018-02-03
1            2018-02-04

and I'd like to have

uniqueClicks createdAt
1            2018-02-01
4            2018-02-03
5            2018-02-04

Answer 1

I believe you can obtain a rolling sum of the unique clicks without needing to resort to dynamic SQL:

SELECT
    t1.CREATED_AT,
    (SELECT SUM(t2.uniqueClicks) FROM
     (
         SELECT CREATED_AT, COUNT(DISTINCT IP, URL) uniqueClicks
         FROM CODE_EVENTS
         GROUP BY CREATED_AT
     ) t2
     WHERE t2.CREATED_AT <= t1.CREATED_AT) uniqueClicksRolling
FROM
(
    SELECT DISTINCT CREATED_AT
    FROM CODE_EVENTS
) t1
ORDER BY t1.CREATED_AT;

The subquery aliased as t2 computes the number of unique clicks on each given day which appears in your table. The distinct count of IP and URL is what determines the number of clicks. We can then subquery this intermediate table and sum clicks for all days up and including the current date. This is essentially cursor style action, and can replace your use of session variables.