我需要计算一个月内有多少人订阅，例如一月，当他们离开时:它看起来像:所以它看起来像:

例如查看一月份有 50 个订阅者

MONTH(t2.leavedate) || COUNT(t1.subscribed1) 
January             || 10
February            || 15
March               || 3

所以 1 月份队列中有 50 人(即 50 人在 1 月份订阅)并且还剩下 28 人 - 我希望我的查询在 28 人离开时输出。

这是我的表 (tableX)，它显示每个事件和 user_id:

我有一张包含事件的表格

id||user_id||event_name||date of event
4 ||   10  || subscribe|| 2016-01-25
5 ||   11  || subscribe|| 2016-01-30
6 ||   12  || subscribe|| 2016-01-30
7 ||   13  || leave    || 2016-02-02
8 ||   14  || subscribe|| 2016-02-03

如果我只筛选出一个用户，user_id = 10(user10) - 其中 user10 是重新加入者的示例(多个“订阅”事件和多个“离开”事件，表格如下:

id ||user_id||event_name||date of event
 4 ||   10  || subscribe|| 2016-01-25
20 ||   10  || leave    || 2016-01-30
32 ||   10  || subscribe|| 2016-01-30
45 ||   10  || leave    || 2016-02-02
60 ||   10  || subscribe|| 2016-02-03
70 ||   10  || leave    || 2016-03-10

这是我必须计算一个月订阅者数量的查询:

SELECT MONTHNAME(t1.joindate), COUNT(t1.subscribed1)
FROM tableX
JOIN
(SELECT tableX.user_id AS subscribed1, tableX.date_of_event AS joindate
    FROM 
        tableX
    WHERE tableX.event_name = "subscribed"
    GROUP BY tableX.id
    )t1
    ON t1.subscribed1 = tableX.user_id
WHERE event_name = 'subscribed'
GROUP BY MONTHNAME(t1.joindate)
ORDER BY t1.joindate ASC;

我遇到的问题是，如果我正在查看例如一月 - 重新加入，例如user10，在计算订户数量时将出现 2 (COUNT(tableX.ids)) - 但当我 COUNT(t1.subscribed1) 1 月份离开两次，3 月份离开一次时，将出现 3 次(总而言之，它将显示 user10 作为 1 月群组的一部分离开了 3 次，然后 user10 将再次出现在我的 2 月群组订阅者计数中 - 但显示用户离开了 3 次。当我想要的 2 月群组时，会为user10 2月份算一次，3月份离开一次)

我想按顺序计算这些事件，例如二月的下一个休假日期是三月。我试过使用 MAX-MIN 和 t2.leavedate >= t1.joindate 等函数

这是我迄今为止尝试过的方法，但它似乎没有反射(reflect)出正确的数字:

SELECT MONTHNAME(t2.leavedate), COUNT(t2.cancelled2)

    FROM tableX

JOIN 

(SELECT MONTHNAME(tableX.date_of_event), tableX.user_id AS subscribed1, tableX.date_of_event AS joindate
    FROM 
        user_account_events
    WHERE tableX.event_name = "subscribed"

    GROUP BY tableX.id
    )t1

    ON t1.subscribed1 = tableX.user_id

JOIN 

    (SELECT tableX.user_id AS cancelled2, tableX.date_of_event AS leavedate
        FROM tableX
    WHERE
        tableX.event_name = "leave"
    GROUP BY tableX.user_id
    )t2
    ON t2.cancelled2 = t1.subscribed1


WHERE tableX.date_of_event = 'leave'
AND t1.joindate BETWEEN '2016-01-01 00:00:00' AND '2016-02-01 00:00:00'
*AND t2.leavedate >= t1.joindate*

GROUP BY MONTH(t2.leavedate)
ORDER BY t2.leavedate;

1 月份队列的预期输出，即:(t1.joindate BETWEEN '2016-01-01 00:00:00' AND '2016-02-01 00:00:00')

MONTH(t2.leavedate) || COUNT(t1.subscribed1) 
January             || 10
February            || 15

所以，最终，我想创建一个如下所示的表:

                    Nov || Dec || Jan || Feb ||
No. of Subscribers|| 10 || 15  || 50  || 25  ||

Leave Month
              Nov || 1  ||     ||
              Dec || 3  || 2   ||
              Jan || 1  || 5   || 10 || 
              Feb || 2  || 3   || 15 || 6 ||
               ____________________________
 Total 'leaves'   || 7 || 10  || 25  || 6 ||

即11月订阅的10人中，7人已经离开。 (11 月份只有 3 名活跃订阅者)

任何帮助将不胜感激!

最佳答案

这个查询让你知道“user_id”在某个日期是否被订阅

SELECT user_id, SUM(IF(event_name="suscribe", 1, -1)) AS is_suscribed FROM `tableX` WHERE date_of_event < '2016-01-25' GROUP BY user_id

如果您希望订阅者在一个月内计数:

SELECT COUNT(*) FROM (SELECT user_id, SUM(IF(event_name="suscribe", 1, -1)) AS is_suscribed FROM `tableX` WHERE MONTH(date_of_event)=1 AND YEAR(date_of_event)=2016 GROUP BY user_id) AS real_suscription WHERE is_suscribed > 0

或者，如果您希望在一个月内计算退订人数:

SELECT COUNT(*) FROM (SELECT user_id, SUM(IF(event_name="suscribe", 1, -1)) AS is_suscribed FROM `tableX` WHERE MONTH(date_of_event)=1 AND YEAR(date_of_event)=2016 GROUP BY user_id) AS real_suscription WHERE is_suscribed < 0

关于mysql - 如何获得下一个日期/如何获得每次停留的时间，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35985924/