每天统计注册用户数的 SQL 查询 [英] SQL query to count registered users per day

问题描述

我在尝试解决这个问题时快要生气了:

在我的应用程序中，用户可以自行注册和删除.创建日期和删除日期作为时间戳保存在数据库中.我需要知道某天范围内的每一天，当天有多少注册用户和未删除用户.

因此，如果我在 2012-02-01 有 10 个现有用户，一个用户在 2012-02-03 删除了帐户，三个用户在 2012-02-04 注册，两个用户在 2012-02-06 删除，并查询2012-02-01到2012-02-07的注册用户总数我想得到这样的结果:

<前>天 total_users2012-02-01 102012-02-02 102012-02-03 92012-02-04 122012-02-05 122012-02-06 102012-02-07 10

这是我简化的用户表的样子:

user_id、user_name、created_at、deleted_at

获取特定日期的注册用户数和未删除用户数没有问题(此处为 2012-02-01，在我的示例中为 10):

选择application.application_id作为application_id，count(user.user_id)作为user_total从应用程序，用户其中 application.application_id = user.application_id和日期(user.created_at)<= '2012-02-01'和 ((date(user.deleted_at) > '2012-02-01') 或 (user.deleted_at 为空))

谁知道我如何创建一个查询(没有游标)来获得我的预期结果?我的数据库是 Debian 上的 MySQL 5.0.51.

提前致谢马库斯

解决方案

如果你有一个包含日期列表的表格(称为日历之类的东西)，你可以使用这样的查询:

select c.calendar_date, count(*) user_total从日历 c离开加入用户 u在 date(u.created_at) <= c.calendar_date 和(date(u.deleted_at) > c.calendar_date 或 u.deleted_at 为空)按 c.calendar_date 分组

如果您没有包含日期​​列表的表格，您可以使用以下查询模拟一个:

select * from(选择 adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) calendar_date from(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v在哪里 calendar_date 之间?/*日期范围的开始*/和?/*日期范围结束*/

I am nearly getting mad while trying to resolve this problem:

In my application users can register and delete themselfes. The create date and the delete date are persisted in the database as a timestamp. I need to know for each day in a range of days, how many registered and not deleted users existed on that day.

So if I have 10 existing users on 2012-02-01, one user that deleted the account on 2012-02-03, three users that registered on 2012-02-04 and two users deleted on 2012-02-06, and query the total number of registered users from 2012-02-01 until 2012-02-07 I'd like to get a result like this:

day              total_users
2012-02-01       10
2012-02-02       10
2012-02-03       9
2012-02-04       12
2012-02-05       12
2012-02-06       10
2012-02-07       10

This is how my simplified user table looks like:

user_id, user_name, created_at, deleted_at

It is no problem to get the number of registered and not deleted users for one specific day (here 2012-02-01, which will get me a 10 in my example):

select application.application_id as application_id, count(user.user_id) as user_total
from application, user
where application.application_id = user.application_id
and date(user.created_at) <= '2012-02-01'
and ((date(user.deleted_at) > '2012-02-01') or (user.deleted_at is null))

Who has a clue how I can create a query (without a cursor) that will have my expected result? My database is a MySQL 5.0.51 on Debian.

Thanks in advance Marcus

解决方案

If you have a table with a list of dates in it (called something like calendar), you could use a query like this:

select c.calendar_date, count(*) user_total
from calendar c
left join user u
       on date(u.created_at) <= c.calendar_date and 
          (date(u.deleted_at) > c.calendar_date or u.deleted_at is null)
group by c.calendar_date

If you don't have a table with a list of dates in it, you can simulate one with the following query:

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) calendar_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where calendar_date between ? /*start of date range*/ and ? /*end of date range*/ 

这篇关于每天统计注册用户数的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！