Это мой сценарий: у меня есть таблица, содержащая события, каждое событие имеет поле под названием «created» с меткой времени, в которой было создано это событие. Теперь мне нужно отсортировать события от самых новых до самых старых, но я не хочу, чтобы MySQL возвращал их все. Мне нужен только последний за данный интервал, например, в течение 24 часов (EDIT: я бы хотел иметь гибкое решение не только в течение 24 часов, но, возможно, каждые несколько часов). И мне нужны только последние 10 дней. Я достиг этого, но я уверен в самых неэффективных возможностях, то есть что-то вроде этого:
$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; } } Надеюсь, я был достаточно ясен. Спасибо, Хесус.
Предполагая, что вы хотите получить последнее (имеющее наибольшую созданную дату) событие в день за последние 10 дней.
поэтому давайте получим последнюю метку времени в день
$today = date('Ym-d'); $tenDaysAgo = date('Ym-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')";
Затем мы можем выбрать все события, соответствующие тем, которые были созданы
$outerSql = "SELECT * FROM eventos INNER JOIN ($innerSql) as A WHERE eventos.created = A.max_created";
У меня не было возможности проверить это, но принципы должны быть достаточно хорошими.
Если вы хотите группировать какое-то другое произвольное количество часов, вы должны изменить 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)";
Если у вас есть индекс с created в качестве ведущего столбца, MySQL может выполнить обратное сканирование. Если у вас есть 24-часовой период, который не имеет каких-либо событий, вы можете вернуть строку, которая НЕ с этого периода. Чтобы убедиться, что вы получаете строку в этот период, вам действительно нужно включить нижнюю границу в created столбец, что-то вроде этого:
SELECT * FROM `eventos` WHERE ... AND `created` < FROM_UNIXTIME( {$timestamp} ) AND `created` >= DATE_ADD(FROM_UNIXTIME( {$timestamp} ),INTERVAL -24 HOUR) ORDER BY `created` DESC LIMIT 1
Я думаю, что большой ключ к производительности здесь – это индекс с created в качестве ведущего столбца вместе со всеми (или большинством) других столбцов, на которые ссылается предложение WHERE, и убедитесь, что этот индекс используется вашим запросом.
Если вам нужен другой временной интервал, вплоть до второго, этот подход может быть легко обобщен.
SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL 0*{$nsecs} SECOND) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*{$nsecs} SECOND) ORDER BY `created` DESC LIMIT 1
Из вашего кода похоже, что 24-часовые периоды ограничены в произвольное время … если функция времени вернется, например, 1341580800 ('2012-07-06 13:20'), то ваши десять периодов будут все с 13 : 20 в определенный день до 13:20 на следующий день.
(ПРИМЕЧАНИЕ: убедитесь, что если ваш параметр является целым числом timestamp unix, это правильно интерпретируется базой данных.)
Возможно, более эффективно вывести десять строк в одном запросе. Если есть гарантия, что «timestamp» уникальна, тогда можно создать такой запрос, но текст запроса будет значительно сложнее, чем у вас сейчас. Мы могли бы возиться с получением MAX (timestamp_) в течение каждого периода, а затем присоединиться к этому, чтобы получить строку … но это будет очень грязно.
Если бы я попытался вытащить все десять строк, я бы, вероятно, попытался использовать подход UNION ALL , не очень красивый, но, по крайней мере, его можно было бы настроить.
SELECT p0.* FROM ( SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL 0*24 HOUR) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*24 HOUR) ORDER BY `created` DESC LIMIT 1 ) p0 UNION ALL SELECT p1.* FROM ( SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -1*24 HOUR) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -2*24 HOUR) ORDER BY `created` DESC LIMIT 1 ) p1 UNION ALL SELECT p2.* FROM ( SELECT * FROM `eventos` WHERE ... AND `created` < DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -2*24 HOUR) AND `created` >= DATE_ADD(FROM_UNIXTIME({$timestamp}),INTERVAL -3*24 HOUR) ORDER BY `created` DESC LIMIT 1 ) p2 UNION ALL SELECT p3.* FROM ...
Опять же, это может быть обобщено, чтобы пройти через несколько секунд в качестве аргумента. Замените HOUR на SECOND и замените «24» на параметр привязки, который имеет несколько секунд.
Он довольно длинный, но он должен работать нормально.
Еще один действительно запутанный и сложный способ вернуть это в единый результирующий набор – это использовать встроенный просмотр, чтобы получить конечную метку времени для десяти периодов, примерно так:
SELECT p.period_end FROM (SELECT DATE_ADD(t.t_,INTERVAL -1 * i.i_* {$nsecs} SECOND) AS period_end FROM (SELECT FROM_UNIXTIME( {$timestamp} ) AS t_) t JOIN (SELECT 0 AS i_ UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) i ) p
А потом присоединитесь к своему столу …
ON `created` < p.period_end AND `created` >= DATE_ADD(p.period_end,INTERVAL -1 * {$nsecs} SECOND)
И оттяните MAX (созданный) за каждый период GROUP BY p.period_end, оберните это во встроенный просмотр.
Затем присоединитесь к этой таблице, чтобы получить каждую строку.
Но это действительно очень грязно, трудно понять и вряд ли будет быстрее (или более эффективным), чем то, что вы уже делаете. Самое лучшее, что вы могли бы сделать, – это время, затрачиваемое на выполнение 9 ваших запросов.
Я бы добавил еще один столбец, который является датой (а не временем), а затем используйте MySQL «group by», чтобы получить самую последнюю для каждой даты.
http://www.tizag.com/mysqlTutorial/mysqlgroupby.php/
Этот учебник делает именно это, но по типу продукта вместо даты. Это должно помочь!
Вы хотите, чтобы все события в течение 10 дней или только одно событие в день в течение 10-дневного периода?
В любом случае, рассмотрите функции даты MySQL для помощи. Это должно помочь вам получить нужный диапазон дат.
Вот тот, который даст вам первое событие дня за последние 10 дней.
SELECT * FROM eventos WHERE created BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 10 DAY) AND DATE_ADD(DATE(NOW()), INTERVAL 1 DAY) GROUP BY DATE(created) ORDER BY MAX(created) DESC LIMIT 10
Попробуй это:
SELECT * FROM eventos WHERE created BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 10 DAY) AND DATE_ADD(DATE(NOW()), INTERVAL 1 DAY) ORDER BY created DESC LIMIT 10