如何在没有统计信息的情况下从Mysql中获取数据

I want to get the number of Registrations back from a time period (say a week), which isn't that hard to do, but I was wondering if it is in anyway possible to in MySQL to return a zero for days that have no registrations.

我希望从一段时间(比如说一周)中获得注册数量,这并不难做到,但我想知道在MySQL中是否有可能在没有数天的情况下返回0注册。

An example: DATA:

一个例子:DATA:

ID_Profile    datCreate
1   2009-02-25 16:45:58
2   2009-02-25 16:45:58
3   2009-02-25 16:45:58
4   2009-02-26 10:23:39
5   2009-02-27 15:07:56
6   2009-03-05 11:57:30

SQL:

    SELECT 
        DAY(datCreate) as RegistrationDate, 
    COUNT(ID_Profile) as NumberOfRegistrations 
    FROM tbl_profile
    WHERE DATE(datCreate) > DATE_SUB(CURDATE(),INTERVAL 9 DAY)
    GROUP BY RegistrationDate
    ORDER BY datCreate ASC;

In this case the result would be:

在这种情况下,结果将是:

RegistrationDate    NumberOfRegistrations
25  3
26  1
27  1
5   1

Obviously I'm missing a couple of days in between. Currently I'm solving this in my php code, but I was wondering if MySQL has any way to automatically return 0 for the missing days/rows. This would be the desired result:

显然我在两天之间错过了几天。目前我在我的PHP代码中解决这个问题,但我想知道MySQL是否有任何方法可以自动为缺少的日期/行返回0。这将是理想的结果:

RegistrationDate    NumberOfRegistrations
25  3
26  1
27  1
28      0
1       0
2       0
3       0
4       0
5   1

This way we can use MySQL to solve any problems concerning the number of days in a month instead of relying on php code to calculate for each month how many days there are, since MySQL has this functionality build in.

这样我们可以使用MySQL来解决有关一个月内天数的任何问题,而不是依靠php代码来计算每个月有多少天,因为MySQL已经内置了这个功能。

Thanks in advance

提前致谢

2 个解决方案

#1

No, but one workaround would be to create a single-column table with a date primary key, preloaded with dates for each day. You'd have dates from your earliest starting point right through to some far off future.

不,但一种解决方法是创建一个带有日期主键的单列表,预先加载每天的日期。你有从最早的起点到遥远的未来的日期。

Now, you can LEFT JOIN your statistical data against it - then you'll get nulls for those days with no data. If you really want a zero rather than null, use IFNULL(colname, 0)

现在,您可以将您的统计数据左键加入 - 然后您将获得没有数据的那些日子的空值。如果你真的想要零而不是null,请使用IFNULL(colname,0)

#2

Thanks to Paul Dixon I found the solution. Anyone interested in how I solved this read on:

感谢Paul Dixon,我找到了解决方案。任何对我如何解决这个问题感兴趣的人都会:

First create a stored procedure I found somewhere to populate a table with all dates from this year.

首先创建一个存储过程,我发现在某处填充一个包含今年所有日期的表。

CREATE Table calendar(dt date not null);

CREATE PROCEDURE sp_calendar(IN start_date DATE, IN end_date DATE, OUT result_text TEXT)
  BEGIN
    SET @begin = 'INSERT INTO calendar(dt) VALUES ';
    SET @date = start_date;
    SET @max = SUBDATE(end_date, INTERVAL 1 DAY);
    SET @temp = '';
    REPEAT
      SET @temp = concat(@temp, '(''', @date, '''), ');
      SET @date = ADDDATE(@date, INTERVAL 1 DAY);
      UNTIL @date > @max
    END REPEAT;
    SET @temp = concat(@temp, '(''', @date, ''')');
    SET result_text = concat(@begin, @temp);
  END


   call sp_calendar('2009-01-01', '2010-01-01', @z);

   select @z;

Then change the query to add the left join:

然后更改查询以添加左连接:

SELECT
    DAY(dt) as RegistrationDate,
    COUNT(ID_Profile) as NumberOfRegistrations
FROM calendar
LEFT JOIN 
    tbl_profile ON calendar.dt = tbl_profile.datCreate
WHERE dt  BETWEEN DATE_SUB(CURDATE(),INTERVAL 6 DAY)  AND CURDATE()
GROUP BY RegistrationDate
ORDER BY dt ASC

And we're done.

我们已经完成了。

Thanks all for the quick replies and solution.

感谢所有人的快速回复和解决方案。

#1