如何通过一个查询从上一小时，最后一天和上个月获取数据？

I'm logging queries which have been sent to my API like this:

我正在记录已经发送到我的API的查询，如下所示：

id  |  timestamp
----+---------------------
1   |  2015-01-19 18:01:47
2   |  2015-01-19 20:41:37
3   |  2015-01-20 14:15:06
4   |  2015-01-21 13:02:51
5   |  2015-01-23 05:02:12

Now, I need to know how much queries have been made in the last 60 minutes, 24 hours and 30 days and group it like that:

现在，我需要知道在过去的60分钟，24小时和30天内进行了多少查询，并将其分组：

HOURLY | DAILY | MONTHLY
-------+-------+--------
0      | 1     | 5

Is this possible to do in one query?

这可以在一个查询中执行吗？

Edit:

编辑：

I've used the suggestions from here and played a bit with the logical things until it worked the way I want it to.

我已经使用了这里的建议，并对逻辑事物进行了一些调整，直到它按照我想要的方式工作。

SELECT SUM(CASE WHEN DATE_SUB(NOW(), interval 1 HOUR) <= `TIMESTAMP` THEN 1 ELSE 0 END) HOURLY,
       SUM(CASE WHEN DATE_SUB(NOW(), interval 1 DAY) <= `TIMESTAMP` THEN 1 ELSE 0 END) DAILY,
       SUM(CASE WHEN DATE_SUB(NOW(), interval 1 MONTH) <= `TIMESTAMP` THEN 1 ELSE 0 END) MONTHLY
FROM `REQUESTS`;

Thanks a lot for your help guys!

非常感谢你的帮助！

3 个解决方案

#1

select sum(case when timestamp between now() - interval 1 hour and now() then 1 else 0) hourly,
       sum(case when timestamp between now() - interval 1 day and now() then 1 else 0) daily,
       sum(case when timestamp between now() - interval 1 month and now() then 1 else 0) monthly
from   your_table

edited...

编辑...

#2

select sum(timestamp >= now() - interval 1 hour) as hour,
       sum(timestamp >= now() - interval 1 day) as day,
       sum(timestamp >= now() - interval 1 month) as month
from your_table

SQLFiddle demo

#3

Another neat trick you can use here is the SUM() function with just a boolean expression inside of it. When you do that, MySQL will effectively count the number of rows that meet the condition. So, by using something like:

你可以在这里使用的另一个巧妙的技巧是SUM（）函数，里面只有一个布尔表达式。当你这样做时，MySQL将有效地计算满足条件的行数。所以，通过使用类似的东西：

SUM(timeCol >= (NOW() - INTERVAL 1 HOUR))

It will just count the number of rows that have a timestamp within the last hour. Try this query:

它只计算在过去一小时内有时间戳的行数。试试这个查询：

SELECT SUM(timecol >= (NOW() - INTERVAL 1 HOUR)) AS hourly,
  SUM(timeCol >= (NOW() - INTERVAL 1 DAY)) AS daily,
  SUM(timeCol >= (NOW() - INTERVAL 1 MONTH)) AS monthly
FROM myTable;

It worked for me in SQL Fiddle.

它在SQL Fiddle中对我有用。

EDIT

编辑

The above solution doesn't work if your table does have future dates, so if you want to ensure that you're only getting values in the last hour, day, or month, that do not exceed the current time, just add a where clause:

如果您的表确实有未来日期，则上述解决方案不起作用，因此如果您希望确保仅在过去一小时，一天或一个月内获得的值不超过当前时间，则只需添加一个条款：

SELECT SUM(timecol >= (NOW() - INTERVAL 1 HOUR)) AS hourly,
  SUM(timeCol >= (NOW() - INTERVAL 1 DAY)) AS daily,
  SUM(timeCol >= (NOW() - INTERVAL 1 MONTH)) AS monthly
FROM myTable
WHERE timeCol <= NOW();

Here is an updated Fiddle, that has an invalid record to show that it works.

这是一个更新的小提琴，它有一个无效的记录，表明它的工作原理。