php - mysql组之间的用户注册计数

Question

假设我有一个表“wp_users”，其中包含以下数据:

Name            user_registered        ID
dog            2008-05-14 18:53:30     1
cat            2008-05-14 12:13:20     2
mouse          2008-05-14 08:51:32     3
giraffe        2008-05-15 22:13:31     4
Moose          2008-05-16 13:20:30     5
monkey         2008-05-16 08:51:32     6
mongoose       2008-05-16 22:13:31     7
fish           2008-05-16 13:00:30     8

然后我有另一个表“wp_usermeta”，其中wp_users.ID == wp_usermeta.user_id:

user_id   meta_key               meta_value
1         wp_capabilities            a:1:{s:10:"subscriber";s:1:"1";}
2         wp_capabilities         a:1:{s:15:"s2member_level2";s:1:"1";}
2         wp_s2member_subscr_id    I-SDJKSDD
3         wp_capabilities          a:1:{s:15:"s2member_level2";s:1:"1";}
3         wp_s2member_subscr_id    I-sdfsdfsdf
4         wp_cabilities            a:1:{s:10:"subscriber";s:1:"1";}
5         wp_cabilities            a:1:{s:10:"subscriber";s:1:"1";}
6         wp_cabilities            a:1:{s:10:"subscriber";s:1:"1";}
7         wp_capabilities          a:1:{s:15:"s2member_level2";s:1:"1";}
7         wp_s2member_subscr_id    I-sd45gds
8         wp_capabilities          a:1:{s:15:"s2member_level2";s:1:"1";}
8         wp_s2member_subscr_id    I-3fskkhh

我正在寻找一种生成一些摘要列的方法，用于统计 wp_users.user_registered 日期每天的订户注册数量与 s2member_level2 注册数量(我不在乎大约一天中的某个小时，只是日期)。为了更好地定义它们:

“subscriber”:不会有 meta_key = wp_s2member_subscr_id 的行 - 它根本不存在。请注意，用户 1、4、5 和 6 没有此行，因此他们是“订阅者”。此外，在“wp_capability”行中，它们将具有“subscriber”一词，而不是“s2member_level2”。这两个条件都不必满足，因为它们对于所有订阅者都适用(我提到这两个条件是为了防止其中一个比另一个更容易检查)。

“s2member_level2”:确实有一行 meta_key = wp_s2member_subscr_id 并带有一些随机字母值(上表中的用户 2、3、7、8)。 meta_value 的值中还会包含“s2member_level2”一词。这两个条件都不必满足，因为它们对于所有 s2member_level2 都适用(我提到这两个条件是为了防止其中一个比另一个更容易检查)。

对于上面的示例表，理想的输出将如下所示:

Date              subscriber     s2member_level2
2008-05-14         1              2
2008-05-15         1              0
2008-05-16         2              2

这是一个比发现的问题(和答案)更复杂的问题here这只是寻找任何类型的订阅者总数。这旨在区分订阅者与 s2member_level2 。我假设这可以使用一些奇特的连接语句来完成，并且希望得到任何帮助!

Answer 1

select date, SUM(case when subscriber = 1 and s2member_level2 = 0 then 1 else 0 end) subscriber,
           SUM(case when s2member_level2 = 1 then 1 else 0 end) s2member_level2
from
(   select  date(user_registered) date,
             id,
            sum(case when B.meta_key = "wp_capabilities" then 1 else 0 end) as subscriber,
            sum(case when B.meta_key = "wp_s2member_subscr_id" then 1 else 0 end) as s2member_level2
    from wp_users A LEFT JOIN wp_usermeta B ON A.Id = B.user_id            
    group by date(user_registered), id
) C
group by date