| Tertium |
| Отправлено: 11.06.2005, 01:42 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
Запрос на засыпку 
mySQL 4.1. TADOQuery
В таблице по датам записаны цифры:
datetime | int | int
Как вывести только те записи, разброс по времени между которыми, скажем час? Только желательно не используя кучу переборов всей таблицы, ибо таблица может содержать десятки тысяч записей с разбегом по времени в секунды.
Если бы это было в масссиве, то алгоритм такой: вешается переменная типа last_time. Пишем в неё первую дату, а затем, перебирая дожидаемся даты, которая была бы на Х больше. Тогда выбираем запись, присваиваем last_time её дату-время и так далее. Но как это оформить в SQL??
А то пока я это вижу как select * from mytable, а потом при фетче отбраковка и забивка в массив.
|
|
| AVC |
| Отправлено: 13.06.2005, 08:21 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Опять буду дублировать ответ — не возражаете?
Пока не знаю, какими функциями это реализовать в мускуле (надо почитать док) но формально так:
Вас интересуют записи у которых значение в поле datetime кратно некой константе.
Конкретно по вашему примеру:
Where минуты_и_секунды(datetime) = :минуты_и_секунды_datetime_1й_записи
Это вариант в два запроса. Если поднапрячься, то, наверное, можно и в один.
|
|
| Tertium |
| Отправлено: 14.06.2005, 14:52 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
Повторенье — мать ученья.
Спасибо, интересная идея. Надо будет поробовать
|
|
| olegenty |
| Отправлено: 20.06.2005, 09:49 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
а комбинировать ">=" и "<=" для создания коридора?
|
|
| Tertium |
| Отправлено: 20.06.2005, 15:23 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
что <= => ? Ты не понял вопрос. Как в запросе комбинировать <= => по нескольким строкам (а не столбцам!) таблицы? Сравнивать-то нужно i-тый с (i-1)-ым. SQL вроде не работает с итым-житым элементом. Это уже в ровсете мона так, а в запросе нельзя. А чего ты добьёшься, сравнивая остаток от деления даты на число секунд? Вот и я ничего покамест не добился. Просто ровсет от select * будет ГИГАНТСКИМ, и не со всех хостов его можно позволить себе залить (юзаем клиентский курсор), поэтому и возникла задача.
|
|
| Tertium |
| Отправлено: 21.06.2005, 02:03 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
После прекрасного вечера секса с mssql получилось-тки вот такое решение проблемы. Пока не оптимизировано, но все жё...
Создача ф-ции:
CREATE FUNCTION find_interval(@id int, @begin datetime, @end datetime, @sec_period int )
RETURNS @ret_table TABLE
(
currentIN bigint,
currentOUT bigint,
last_query datetime
)
AS
BEGIN
DECLARE @last_datime datetime
DECLARE @tmp_datime datetime
DECLARE @max_datime datetime
DECLARE @last_IN bigint
DECLARE @last_OUT bigint
DECLARE @iterator bigint
DECLARE @quit int
SET @quit = 0
select top 1 @max_datime=max(last_query) from stats where machine_id=@id and last_query<=@end
select top 1 @last_datime=last_query from stats where machine_id=@id and last_query>=@begin
if @last_datime<=@end
begin
insert @ret_table select top 1 stats.currentIn,stats.currentOut,stats.last_query
from stats where last_query=@last_datime and machine_id=@id
end
declare @l_d datetime
set @l_d=@last_datime
while @quit=0
begin
select top 1 @tmp_datime=last_query, @last_IN=currentIn, @last_OUT=currentOut
from stats
where machine_id=@id and DATEDIFF(second, @last_datime, last_query)>= @sec_period
order by last_query
set @last_datime=@tmp_datime
if @last_datime=@l_d or @last_datime>@max_datime
begin
set @quit=1
end
else
begin
if @last_datime<=@max_datime
insert into @ret_table(currentIN,currentOUT,last_query)
values(@last_IN,@last_OUT,@last_datime)
end
set @l_d=@last_datime
continue
end
return
END
вызов ея:
| SQL | select * from find_interval(1, style='color:red'>'2004-06-24 18:47:49','2006-12-04 16:17:49',7200)
|
вот оно как. тока решение это для mssql. Для mysql такое возможно только с 5.0, а она пока бета. так что видимо придётся переползать на мелкософт...
эк декоратор форума все аккуратные выравнивания побил... Ну да кому охота разберётся
Отредактировано Tertium — 21/06/2005, 02:05
|
|
| AVC |
| Отправлено: 21.06.2005, 08:30 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
| QUOTE | Даты записей, к сожалению идут не ноздря к ноздре, а что называется хаотически Вот такие вот дополнительные условия к задачке. Ну как, возможно сие в SQL?
|
Если использовать только Select, то ваше требование сводится к поиску последней записи в группе, ограниченной временем по условию кратности. Сие в SQL возможно, но так как вы уже сделали функцию придумывать select не хочу. (А в два запроса это вообще решается элементарно.) |
|
| Tertium |
| Отправлено: 21.06.2005, 19:28 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
все же, если это так просто, нельзя ли код в студию? а то ведь я же не от хорошей жизни за ф-цию взялся... Конечно, предпочтительно запросом. Но сможет ли он работать с теми же условиями, что и написанная ф-ция? И при этом есть меньше времени?
вот к примеру, есть даты с неравномерным случайным периодом от получаса до двух суток, и надо достать все, которые друг от друга как минимум на два часа отстоят. Сможет так запрос?
Уж растравили аппетит, жду запроса
ЗЫ: Кстати, чтой-то форум глючит, мне приписали пост AVC на неск-ко постов раньше.
|
|
| AVC |
| Отправлено: 22.06.2005, 09:02 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
| QUOTE |
есть даты с неравномерным случайным периодом от получаса до двух суток, и надо достать все, которые друг от друга как минимум на два часа отстоят
|
Гарантируете ли вы, что пара machine_id и last_query в совокупности однозначно идентифицируют запись?
Можно ли считать, что вас интересуют времена по абсолютной величине кратные целому числу часов (запрос будет проще)?
Решение будет для Oracle, но без использования его хитрых возможностей, а только лишь со стандартными функциями. Замену им придется искать вам.
Для MySql запрос, скорее всего, будет работать дольше функции.
Вас устраивает? Писать запрос? Сообщите, работать впустую не хочется.
PS.
Решение в два запроса напоминает вашу функцию. Первым, группирующим, запросом находим Max(last_query) внутри группы, принадлежащей одному интервалу. Проходим по курсору и для каждой записи находим в stats единственную строку. (Возможно я бы делал это на клиенте).
Хотелось бы высказать пару советов по SQL коду, не возражаете?
select top 1 @last_datime=last_query from stats where machine_id=@id and last_query>=@begin
Вы можете получить не min(last_query) так как в SQL принято считать что порядок записей не определен, пока явно не задана сортировка.
select top 1 @max_datime=max(last_query) from stats where machine_id=@id and last_query<=@end
Функция max и так вернет ровно одну запись, так что top не очень нужно, хотя и не мешает, а например для Oracle вообще желательно.
Зачастую вместо max(min) (особенно в функциях и процедурах) выгоднее использовать конструкцию типа
Select top1 ... From ... Order by last_query desc (asc)
|
|
| Tertium |
| Отправлено: 23.06.2005, 01:54 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
| QUOTE | | Гарантируете ли вы, что пара machine_id и last_query в совокупности однозначно идентифицируют запись? |
Однозначно, они даже мультиколумн индекс.
| QUOTE | | Можно ли считать, что вас интересуют времена по абсолютной величине кратные целому числу часов (запрос будет проще)? |
В общем, можно и так.
| QUOTE | | ... для Oracle, но без использования его хитрых возможностей |
Это и надо.
Для mySQL функция вообще не работает. Нет их там
Если у Вас нет больших проблем с лишним временем, то запрос увидеть бы очень хотелось. Тем более, что я до сих пор сомневаюсь в возможности вернуть одним запросом (не считая первого подготовительного) ровсет с наложенными мной ограничениями.
| QUOTE | | Вы можете получить не min(last_query) так как в SQL принято считать что порядок записей не определен, пока явно не задана сортировка. |
Да, точно, упустил order by. Правда глюк этот не всплыл, поскольку без сортировки это, как выясняется, по порядку prim id (?), короче порядок добавления и дат совпадал. Спасибо за поправку. И за вторую тоже спасибо. Всё руки не доходили оттестить, что быстрее order by или max|min.
|
|
| AVC |
| Отправлено: 24.06.2005, 17:20 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Очень извиняюсь за задержку, но как всегда, когда хочешь заняться чем-то интересным у начальства проявляются свои желания.
Пока совершенно загружен.
Проблема оказалась сложнее, чем казалось вначале. Простыми группами не обойтись, упустил из виду, что одна запись может быть в конце группы, а другая в начале  что противоречит условию минимального времени. Но все равно попытаюсь найти решение. |
|
| Tertium |
| Отправлено: 24.06.2005, 21:58 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
начальство да, против лома не попрёшь. с слава богу почти сам себе начальство. Почти — в смысле мне говорят что, но я решаю, как, когда и надо ли
а задачка...
вот я и говорю, с первого взгляда притивная задачка, а копнёшь... не скуэльная специфика судя по всему:)
|
|
| AVC |
| Отправлено: 25.06.2005, 17:09 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
| QUOTE |
сам себе начальство. Почти — в смысле мне говорят что, но я решаю, как, когда и надо ли
|
Счастливчик. А тут из республиканского правительства распоряжение — сложно решить вопрос нужности. Ну это к слову.
По проблеме получились следующие результаты:
(Генерировать таблицу с временами поленился, работал с днями, но смысл тот же.)
Если нужно получать данные в неких абсолютных точках отсчета с определенным заранее интервалом, то тут действительно проблем больших нет. Как я и писал — группы. Вот пример запроса
| CODE |
Select
i2.*
From
avc.int_s i2
,(-- выбрать записи с диапазоном в n дней
Select machine_id, Min(last_query) as mlq From avc.int_s
Where last_query between :from_date and :to_date
Group by machine_id
,Trunc((last_query — To_Date('31.12.1899')) / :ndays)
) i1
Where 1 = 1
and i2.machine_id = i1.machine_id
and i2.last_query = i1.mlq
Order By i2.machine_id, i2.last_query
|
Но если на него наложить ограничение "между соседними записями должна быть дистанция НЕ МЕНЕЕ чегото там" то решения на уровне Select я найти не смог. Я то же считаю, что для такой вещи удобнее всего написать SP с проходом по курсору и отбором нужных строк. Вариантов таких процедур много. Я бы, наверное, делал примерно так (схема):
Select PK, machine_id, last_query From замеры Order by machine_id, last_query
Проход по курсору и сохранение PK нужных записей во временной таблице
Для работы выборка из Замеры тех PK, которые есть во временной таблице.
|
|
| Георгий |
| Отправлено: 27.06.2005, 01:58 |
|
Почетный железнодорожник
Группа: Модератор
Сообщений: 874
|
извините, что вмешиваюсь, но мне кажется, что SQL в принципе не может решить эту задачу. SQL отвечает на вопрос "какие данные" должны быть выбраны, а здесь надо ещё и ответить на вопрос "как выбрать" данные, что выходит за рамки реляционного исчисления в целом и SQL в частности. |
|
| Tertium |
| Отправлено: 27.06.2005, 02:33 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
2AVC:
мне кажется, выборка select * from замеры where id IN (tmp_table) — это постоянный пробег временной таблы, в то время как надо грузить просто временную таблу, что собсно моя ф-ция и делает. А условие именно "не меньше чем".
2Георгий: мне тоже так казалось. Но больше интерисовал вопрос менне общего плана — можно ли поручить серверу отобрать, что я хочу... Оказалось можно, но ценой перехода на мелкомягкого монстра... ну да он не так уж и страшен с 3-м sp
Спасибо всем за дискуссию, особенно AVC!
Отредактировано Tertium — 27/06/2005, 02:34
|
|
| Tertium |
| Отправлено: 27.06.2005, 13:42 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
PS: написал более изящный вариант, работает гораздо быстрее
| SQL | CREATE FUNCTION find_interval(@id int, @begin datetime, @end datetime, @sec_period int )
RETURNS @ret_table TABLE
(
currentIN bigint,
currentOUT bigint,
last_query datetime
)
AS BEGIN if (@end<@begin)
begin declare @tmp datetime
set @tmp = @end
set @end = @begin set @begin = @tmp
end
DECLARE @last_datime datetime
DECLARE @tmp_datime datetime
set @tmp_datime = DATEADD(second, -@sec_period, @begin)
DECLARE @last_IN bigint
DECLARE @last_OUT bigint
DECLARE stats_cursor CURSOR FOR
select last_query, currentIn, currentOut from stats where machine_id=@id and last_query>=@begin and last_query<=@end
order by last_query
OPEN stats_cursor
FETCH NEXT FROM stats_cursor INTO @last_datime, @last_IN, @last_OUT
while @@FETCH_STATUS = 0
begin if (DATEDIFF(second, @tmp_datime, @last_datime) >= @sec_period)
begin insert into @ret_table(currentIN,currentOUT,last_query) values(@last_IN,@last_OUT,@last_datime)
set @tmp_datime=@last_datime
end
FETCH NEXT FROM stats_cursor INTO @last_datime, @last_IN, @last_OUT
end
CLOSE stats_cursor
DEALLOCATE stats_cursor
return
END
|
Гда модеры! Добавьте в форум правило замены /t (таба) на & nbsp;& nbsp;& nbsp;& nbsp; (четыре неразрывных пробела)! А то код превращается в галиматью
|
|
|
