MySQL:来自不同子查询的多个运行总计

Question

当我使用以下公式运行单个查询以使第一列返回月份/年份，第二列返回每月签名的人数，第三列返回签名者的运行总数时，效果很好：

SET @runtot1:=0;
SELECT
   1rt.MONTH,
   1rt.1signed,
   (@runtot1 := @runtot1 + 1rt.1signed) AS 1rt
FROM 
   (SELECT
       DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
       IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 791796 THEN s.id ELSE NULL END),0) AS 1signed
    FROM  table1 s
    JOIN table2 m ON s.id = m.id AND m.current = "Yes"
    WHERE STR_TO_DATE(s.datecontacted,'%m/%d/%Y') > '2015-03-01'
    GROUP  BY MONTH
    ORDER  BY MONTH) AS 1rt

通过上面的查询，我得到以下结果表，如果我只需要计算一件事，这正是我想要的：

MONTH   1signed 1rt
2015-03 0       0
2015-04 1       1
2015-05 0       1
2015-08 1       2
2015-10 1       3
2015-11 1       4
2016-01 0       4
2016-02 0       4

但是我不知道如何使用多个子查询来做到这一点，因为我需要同时对多个列执行此操作。例如，我正在尝试这样的事情(不起作用(：

SET @runtot1:=0;
SET @runtot2:=0;
select 
  DATE_FORMAT(STR_TO_DATE(s1.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
  t1.1signed,
  (@runtot1 := @runtot1 + t1.1signed) AS 1rt,
  t2.2signed,
  (@runtot2 := @runtot2 + t2.2signed) AS 2rt
from
    (select 
     DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
     IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 791796 THEN s.id ELSE NULL END),0) AS 1signed
     from table1 s 
     left join table2 m ON m.id = s.id
     where m.current = "Yes"
     GROUP BY MONTH
     ORDER BY MONTH) as T1,
     (select 
     DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
     IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 846346 THEN s.id ELSE NULL END),0) AS 2signed
     from table1 s 
     left join table2 m ON m.id = s.id
     where m.current = "Yes"
     GROUP BY MONTH
     ORDER BY MONTH) as T2,
     table1 s1
LEFT JOIN table2 m1 ON m1.id = s1.id AND m1.current = "Yes"
WHERE STR_TO_DATE(s1.datecontacted,'%m/%d/%Y') > '2015-03-01'
GROUP BY DATE_FORMAT(STR_TO_DATE(s1.datecontacted,'%m/%d/%Y'),'%Y-%m')
ORDER BY DATE_FORMAT(STR_TO_DATE(s1.datecontacted,'%m/%d/%Y'),'%Y-%m')

这严重破坏了我的结果 - 我也尝试了左连接来让这两个彼此相邻，但这也没有用。

这是一个SQL小提琴，其中包含一些值，查询位于顶部，但查询不需要看起来像下面的想法。

如果代码的多个子查询版本有效，下面是理想的最终结果：

MONTH   1signed 1rt 2signed 2rt
2015-03 0       0   1       1
2015-04 1       1   0       1
2015-05 0       1   1       2
2015-08 1       2   0       2
2015-10 1       3   0       2
2015-11 1       4   0       2
2016-01 0       4   0       2
2016-02 0       4   1       3

只是试图找出一种方法来使用相同的查询为两个不同的调查问题按月获取计数和自 2015 年 3 月以来的滚动总数。任何帮助将不胜感激！

Answer 1

你的尝试实际上非常接近。我刚刚摆脱了 S1 并将两个子查询一起连接到它们的 MONTH 列上：

SET @runtot1:=0;
SET @runtot2:=0;
select 
  T1.MONTH,
  t1.1signed,
  (@runtot1 := @runtot1 + t1.1signed) AS 1rt,
  t2.2signed,
  (@runtot2 := @runtot2 + t2.2signed) AS 2rt
from
    (select 
     DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
     IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 791796 THEN s.id ELSE NULL END),0) AS 1signed
     from table1 s 
     left join table2 m ON m.id = s.id
     where m.current = "Yes" and STR_TO_DATE(s.datecontacted,'%m/%d/%Y') > '2015-03-01'
     GROUP BY MONTH
     ORDER BY MONTH) as T1,
     (select 
     DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
     IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 846346 THEN s.id ELSE NULL END),0) AS 2signed
     from table1 s 
     left join table2 m ON m.id = s.id
     where m.current = "Yes" and STR_TO_DATE(s.datecontacted,'%m/%d/%Y') > '2015-03-01'
     GROUP BY MONTH
     ORDER BY MONTH) as T2
WHERE 
T1.MONTH=T2.MONTH
GROUP BY T1.MONTH
ORDER BY T1.MONTH

我还没有测试过草莓的解决方案，它看起来更优雅。但我想你想知道你的方法(单独解决运行总计，然后将结果连接在一起(也会起作用。

Answer 2

看来你正在追求这样的事情......

数据集：

DROP TABLE IF EXISTS table1;
CREATE TABLE table1
( id INT NOT NULL
, date_contacted DATE NOT NULL
, survey_id INT NOT NULL
, PRIMARY KEY(id,survey_id)
);
DROP TABLE IF EXISTS table2;
CREATE TABLE table2
(id INT NOT NULL PRIMARY KEY
,is_current TINYINT NOT NULL DEFAULT 0
);
INSERT INTO table1 VALUES
(1,"2015-03-05",846346),
(2,"2015-04-15",791796),
(2,"2015-05-04",846346),
(3,"2015-06-07",791796),
(3,"2015-06-08",846346),
(4,"2015-08-02",791796),
(5,"2015-10-15",791796),
(6,"2015-11-25",791796),
(6,"2016-01-02", 11235),
(6,"2016-02-06",846346);
INSERT INTO table2 (id,is_current) VALUES
(1,1),
(2,1),
(3,0),
(4,1),
(5,1),
(6,1);

查询：

SELECT x.*
     , @a:=@a+a rt_a
     , @b:=@b+b rt_b
  FROM 
     ( SELECT DATE_FORMAT(date_contacted,'%Y-%m') month
            , SUM(survey_id = 791796) a
            , SUM(survey_id = 846346) b
         FROM table1 x 
         JOIN table2 y 
           ON y.id = x.id 
        WHERE y.is_current = 1
        GROUP 
           BY month
     ) x
  JOIN (SELECT @a:=0,@b:=0) vars
 ORDER 
    BY month;
+---------+------+------+------+------+
| month   | a    | b    | rt_a | rt_b |
+---------+------+------+------+------+
| 2015-03 |    0 |    1 |    0 |    1 |
| 2015-04 |    1 |    0 |    1 |    1 |
| 2015-05 |    0 |    1 |    1 |    2 |
| 2015-08 |    1 |    0 |    2 |    2 |
| 2015-10 |    1 |    0 |    3 |    2 |
| 2015-11 |    1 |    0 |    4 |    2 |
| 2016-01 |    0 |    0 |    4 |    2 |
| 2016-02 |    0 |    1 |    4 |    3 |
+---------+------+------+------+------+