mysql用户注册在组之间计数 [英] mysql user signup counts between groups
问题描述
假设我有一个表' wp_users '，包含以下数据：
Lets say I have a table 'wp_users' with the following data:
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 ：
I then have another table 'wp_usermeta' where 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
我想要的是一种生成一些摘要列的方法，这些摘要列可以计算订阅者注册数量，而s2member_level2注册每天的 wp_users.user_registered 日期（我不在乎一天的小时，只是日期）。要更好地定义它们：
What I'm looking for is a way to generate some summary columns that tallies the number of subscriber signups vs. s2member_level2 signups for every day of the wp_users.user_registered date (I don't care about hour of day, just date). To define those a little better:
subscriber ：不会有 meta_key = wp_s2member_subscr_id - 它只是不存在。注意用户1,4,5和6没有这一行，因此他们是订阅者。另外在行 wp_capabilities 中，他们将有 subscriber 而不是 s2member_level2 。这两个标准不一定要满足，因为它们对所有订阅者都是真的（我提到两个在一个比较容易检查比另一个）。
"subscriber": will NOT have a row where meta_key = wp_s2member_subscr_id - it just doesn't exist. Notice how users 1,4,5 and 6 do not have this row so they are "subscribers". Also in the row "wp_capabilities" they will have the word "subscriber" instead of "s2member_level2". Both criteria do NOT have to be met because they are both true for all subscribers (I mention both in case one is easier to check for than another).
s2member_level2 ：有一行其中 meta_key = wp_s2member_subscr_id 有一些随机字母值（用户2,3,7,8上表）。它还将在 meta_value 的值中包含单词 s2member_level2 。这两个标准不一定要满足，因为它们对所有s2member_level2都是真的（我提到两个在一个比较容易检查比另一个）。
"s2member_level2": DOES have a row where meta_key = wp_s2member_subscr_id with some random letter values (users 2,3,7,8 in tables above). It also will have the word "s2member_level2" in the value for meta_value. Both criteria do NOT have to be met because they are both true for all s2member_level2's (I mention both in case one is easier to check for than another).
理想的输出对于上面的示例表如下：
The ideal output would then look like this for the example tables above:
Date subscriber s2member_level2
2008-05-14 1 2
2008-05-15 1 0
2008-05-16 2 2
这是一个更复杂的问题（和答案），这里只是寻找任何类型的订阅者总数。这试图区分订阅者和s2member_level2。我假设这可以使用一些花哨的连接语句，并将感谢任何帮助！
This is a more complicated question of the question (and answer) found here that just looked for total number of subscribers of any kind. This seeks to differentiate between the subscribers vs. s2member_level2 . I'm assuming this can be done using some fancy join statements and would appreciate any help!
推荐答案
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
这篇关于mysql用户注册在组之间计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
