我要改善的非常特定的MySQL查询 [英] Very specific MySQL query I want to improve

问题描述

这是我的情况:我有一个包含事件的表，每个事件都有一个名为"created"的字段，带有创建该事件的时间戳.现在，我需要将事件从最新到最旧进行排序，但是我不希望MySQL返回所有事件.我只需要给定时间间隔内的最新消息，例如24小时范围内(我希望有一个灵活的解决方案，不仅适用于24小时范围，而且可能每隔几个小时).我只需要过去10天.我已经实现了这一点，但是我敢肯定会以最无效率的方式进行操作，即:

This is my scenario: I have a table that contains events, every event has a field called 'created' with the timestamp in which that event was created. Now I need to sort the events from newest to oldest, but I do not want MySQL to return them all. I need only the latest in a given interval, for example in a range of 24 hours ( I'd like to have a flexible solution, not only for a 24 hours range, but maybe every few hours). And I only need for the last 10 days. I have achieved that but i'm sure in the most inefficient ways possible, that is, something like that:

$timestamp = time();

for($i = 0; $i < 10; $i++) {
    $query = "SELECT * FROM `eventos` WHERE ... AND `created` < '{$timestamp}' ORDER BY `created` DESC LIMIT 1";    
    $return = $database->query( $query );

    if($database->num( $return ) > 0) {
        $event = $database->fetch( $return );
        $events[] = $event;

        $timestamp = $timestamp - 86400;
    }
}

我希望我足够清楚.谢谢， 耶苏斯.

I hope I was clear enough. Thanks, Jesús.

推荐答案

假定您要在过去10天内每天都进行最新的活动(具有最大的创建日期).

Assuming you want the latest (having the greatest created date) event per day for the last 10 days.

所以让我们获取每天的最新时间戳

so let's get the latest timestamp per day

$today = date('Y-m-d');
$tenDaysAgo = date('Y-m-d', strtotime('-10 day'));

$innerSql = "SELECT date_format(created, '%Y-%m-%d') day, MAX(created) max_created FROM eventos WHERE date_format(created, '%Y-%m-%d') BETWEEN '$today' and '$tenDaysAgo' GROUP BY date_format(created, '%Y-%m-%d')";

然后我们可以选择所有与创建日期匹配的事件

Then we can select all the events that match those created dates

$outerSql = "SELECT * FROM eventos INNER JOIN ($innerSql) as A WHERE eventos.created = A.max_created";

我还没有机会进行测试，但是原理应该足够合理.

I haven't had a chance to test this, but the principles should be sound enough.

如果要按其他任意小时数分组，则可以更改innerSql:

If you want to group by some other arbitrary number of hours you would change innerSql:

$fromDate = '2012-07-06' // or if you want a specific time '2012-07-06 12:00:00'
$intervalInHours = 5;
$numberOfIntervals = 10;

$innerSql = "SELECT FLOOR(TIMESTAMPDIFF(HOUR, created, '$fromDate') / $intervalInHours) as grouping, MAX(created) as max_created FROM eventos WHERE created BETWEEN DATE_SUB('$fromDate', INTERVAL ($intervalInHours * $numberOfIntervals) HOUR) AND '$fromDate' GROUP BY FLOOR(TIMESTAMPDIFF(HOUR, created, '$fromDate') / $intervalInHours)";

这篇关于我要改善的非常特定的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！