选择最近7天最大的价值 [英] Select the Largest Value For Every Last 7 Days

问题描述

我通过每小时记录一次描述这些项目的记录来跟踪这些项目.

I'm keeping track of some items by inserting records describing these items every hour as a cron job.

我有一张桌子

steamid(int),
eventid(auto_increment),
itemid(int),
value(int),
time(unix format).

我正在使用每小时统计信息来生成一个图形，以显示24小时概览.

I'm using the hourly statistics to generate a graph to show a 24 hour overview.

现在，我正在尝试生成7天的历史视图.

Now I'm trying to generate a 7 day historical view.

如何选择过去7天每天都会出现的最大值?

How can I select the largest value to occur every day for the last 7 days?

或者，如果这更容易，我怎么可能选择过去7天内每一天的第一个或最后一个条目?

Or, if this is easier, how could I perhaps select the first or last entry for each day in the last 7 days?

提前谢谢.

推荐答案

SELECT   DATE(FROM_UNIXTIME(time)) AS daygroup, 
         MAX(value) AS max_value, 
         MIN(value) AS min_value
FROM     tbl
WHERE    time >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 7 DAY)
GROUP BY daygroup

这将获得过去7天内每天的最小和最大value.

This will get the minimum and maximum of value for each day in the past 7 days.

:更新了解决方案以获取整个行的数据:

Updated solution to get the data of entire row:

SELECT a.*
FROM tbl a
INNER JOIN
(
    SELECT   DATE(FROM_UNIXTIME(time)) AS daygroup, 
             MAX(value) AS max_value 
    FROM     tbl
    WHERE    time >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 7 DAY)
    GROUP BY daygroup
) b ON DATE(FROM_UNIXTIME(a.time)) = b.daygroup AND a.value = b.max_value

这篇关于选择最近7天最大的价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！