与 mysql 的累积和 [英] Cumulative sum with mysql

问题描述

我有以下查询:

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

我想要

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

推荐答案

我相信您可以获得唯一点击次数的滚动总和，而无需求助于动态 SQL:

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;

别名为 t2 的子查询计算出现在表中的每一天的唯一点击次数.IP 和 URL 的不同计数决定了点击次数.然后我们可以子查询这个中间表并总结所有天的点击次数，包括当前日期.这本质上是光标样式操作，可以替代您对会话变量的使用.

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.

这篇关于与 mysql 的累积和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！