Xpoint
   [напомнить пароль]

Выбрать максимальный временной интервал по датам

Метки: [без меток]
2005-11-16 14:52:46 [обр] Александр Сохин(0/21)[досье]

Приветствую!

Есть таблица с ID (ключ) и датой события.
Нужно выбрать максимальный интервал между двумя событиями за последние два года.

Т. е., допустим, есть такие значения:
1 - 01.01.2004
2 - 15.02.2004
3 - 11.04.2004
4 - 03.03.2005
5 - 05.05.2005
6 - 11.09.2005

из этих данных должно получиться 11 (месяцев): самый большой интервал между 11.04.2004 и 03.03.2005 (округляется в большую сторону - до 11)

спустя 42 минуты [обр] GRAy(8/259)[досье]

Александр Сохин[досье]
Без процедурной обработки это можно сделать только в Oracle 8i и выше. Примерно так:

select max(date_end - nvl(date_beg,date_end))
from ( 
  select date date_end, LAG(date) over (ORDER BY date) date_beg
  from table
)

На других СУБД, насколько мне известно такой трюк провернуть не удастся.

спустя 11 минут [обр] Александр Сохин(0/21)[досье]
сказали, что задача тривиальна... БД - MS Access.
спустя 18 минут [обр] Lynn «Кофеман»(0/571)[досье]

А почему не так?

select max(a.date - b.date) from my_tab as a, my_tab as b where a.date > b.date;

Возможно, вместо a.date - b.date надо использовать какую-то функцию, которая вычисляет интервал между датами.

спустя 11 минут [обр] Александр Сохин(0/21)[досье]
что-то не работает, долго думает, потом ругается. (((
спустя 12 минут [обр] GRAy(8/259)[досье]

Александр Сохин[досье] Не работает, потому что не правильно ;)
Подумал я тут, и решил, что плюнув на эффективность запроса можно сделать так (проверил тут у себя в Access - всё путём):

select max(delta) as min  from (
 SELECT t.date -  Max(t2.date) as delta
 FROM table1 AS t, table1 AS t2
 WHERE t.date>t2.date
 GROUP BY t.date
)

Но, хочу предупредить, если записей в таблице достаточно много будет тормозить.

спустя 5 минут [обр] Александр Сохин(0/21)[досье]
а смысл t.date - Max(t2.date) ???
нам же нужно интервал не от максимальной даты, а максимальный интервал между датами...
спустя 2 минуты [обр] Александр Сохин(0/21)[досье]
и тоже ругается. (
спустя 5 минут [обр] Lynn «Кофеман»(0/571)[досье]
SELECT Max(DateDiff('m', b.date, a.date)) AS MaxInterval
FROM my_tab AS a, my_tab AS b
WHERE a.date > b.date;
спустя 14 минут [обр] Александр Сохин(0/21)[досье]

должно получитсья что-то такое:

SELECT SHL_VENDORRELACE.STOCKID, Max(DateDiff('m', b.DATE, a.DATE)) AS MaxInterval
FROM SHL_VENDORRELACE AS a, SHL_VENDORRELACE AS b
WHERE a.DATE>b.DATE
AND a.DATE>=Forms!Main!DateTo-720
AND b.DATE>=Forms!Main!DateTo-720
AND a.STOCKID=b.STOCKID;

но все ругается: ODBC - ошибка вызова... (((

спустя 8 минут [обр] Александр Сохин(0/21)[досье]
неправильно!!! мы же выбираем интервал только между двумя, седующими друг за другом!!!
спустя 5 минут [обр] Lynn «Кофеман»(0/571)[досье]
Ошибка в том, что вы в одном запросе пытаетесь использовать аггрегирующую функцию (Max) и просто поле которого нет в блоке GROUP BY.
SELECT b.STOCKID, Max(DateDiff('m', b.DATE, a.DATE)) AS MaxInterval
FROM SHL_VENDORRELACE AS a, SHL_VENDORRELACE AS b
WHERE a.DATE > b.DATE
AND b.DATE>=Forms!Main!DateTo-720
AND a.STOCKID=b.STOCKID
GROUP BY b.STOCKID;
спустя 2 минуты [обр] Александр Сохин(0/21)[досье]
да, все равно!!! нужно из каждой даты вычитать только предыдущую!!!
спустя 23 минуты [обр] Закиров Руслан(12/343)[досье]

Lynn «Кофеман»[досье] Нужен не максимальный интервал по всем а датам, а максимальное расстояние между соседними датами.

Александр Сохин[досье] Сразу предупреждаю Access не использовал.
Вам нужно две задачи решить. Во-первых, найти интервалы, которые вам нужны, вот один из вариантов:

SELECT a.date, MIN(b.date)
FROM my_tab AS a, my_tab AS b
WHERE a.date > b.date GROUP BY a.date;

Во-вторых, найти среди них самый длинный. Либо подзапросом:

SELECT MAX(start - end) FROM (
  SELECT a.date AS start, MIN(b.date) AS end
  FROM my_tab AS a, my_tab AS b
  WHERE a.date > b.date GROUP BY a.date;
) AS intervals

или возможно вот это правильно сработает(я не уверен, не знаю как фунуции ведут себя, когда аргумент содержит поле по которому ведется группировка):

SELECT MAX(a.date - MIN(b.date))
FROM my_tab AS a, my_tab AS b
WHERE a.date > b.date GROUP BY a.date;

А access поддерживает подзапросы? И поддерживает limit или first?

спустя 2 минуты [обр] GRAy(8/259)[досье]
Закиров Руслан[досье] Ну и чем ваше предложение отличается от моего, кроме того что оно неправильное? :)
Александр Сохин[досье] Почему вы решили что мой селект неправильный?
Max(t2.date) как раз возвращает максимальную дату меньше даты в текущей строке t.date - как раз то что вам нужно. Вы упустили из виду group by t.date
спустя 28 минут [обр] Закиров Руслан(12/343)[досье]
GRAy[досье] Извините проглядел ваш вариант, но вот ваше решение точно дает неправильный результат, внутри подзапроса надо выбирать MIN, а иначе вы получите максимальный интервал по всем датам, а не между соседними датами. Смотрю на ваш запрос и не понимаю чем отличается от:
SELECT MAX(date) - MIN(date) FROM table;
спустя 26 минут [обр] GRAy(8/259)[досье]

Закиров Руслан[досье] Ох, блин не хотел я это объяснять - лень было ;) но вы меня вынуждаете.
Для каждой записи в таблице my_tab AS a с определённой датой мы с помощью вашего селекта:

SELECT a.date, MIN(b.date)
FROM my_tab AS a, my_tab AS b
WHERE a.date > b.date 
GROUP BY a.date;

найдём минимальную дату из набора всех записей my_tab AS b дата которых МЕНЬШЕ текущей даты из таблицы my_tab AS a. Но ведь нам надо взять ближаюшую к текущей, но при этом меньше её. Именно поэтому я использовал MAX(b.date).
Посчитав разницу между двумя этими датами я нашёл максимальную с помощью вложенного селекта:

select max(delta) as min  from (
 SELECT t.date -  Max(t2.date) as delta
 FROM table1 AS t, table1 AS t2
 WHERE t.date>t2.date
 GROUP BY t.date
)

А ваша проблема Александр[досье] в том, что вы видимо не врубаетесь в предлагаемое вам решение ;) попробую адаптировать свой вариант на ваши данные:

Select stockid, max(delta)
(
SELECT a.STOCKID as stockid, a.DATE - max(b.DATE) AS delta
FROM SHL_VENDORRELACE AS a, SHL_VENDORRELACE AS b
WHERE a.DATE>b.DATE 
AND a.DATE>=Forms!Main!DateTo-720 
AND b.DATE>=Forms!Main!DateTo-720 
AND a.STOCKID=b.STOCKID
GROUP BY a.STOCKID, a.DATE
)
GROUP BY stockid

p.s. и ДА, я проверил - в Access это точно работает.

спустя 24 минуты [обр] Закиров Руслан(12/343)[досье]
GRAy[досье] Oops, блин, ступил :) Простите. В моем запросе надо знак > поменять на <, я хотел в правой таблице брать даты больше и искать минимальную. И конечнно в полном варианте нужно end - start. Еще раз извиняюсь за внесенную путаницу.
спустя 14 часов [обр] Александр Сохин(0/21)[досье]
GRAy[досье]
в смысл я врубаюсь. Но, во-первых, у меня это не работает (все та же ошибка). Во-вторых, нужно уйти от программного решения позаписной обработки. По-моему, такую задачу можно решить таблично... Только вот как именно - что-то не могу придумать пока. (
спустя 6 минут [обр] Александр Сохин(0/21)[досье]

например, облегчим задачу, астаргируемся от всего и допустим, что у нас есть некая матрица из двух колонок (номер и число для этого номера):
1 23
2 20
3 5
4 3
5 1

нужно, соответственно, определить максимальный интервал между соседними элементами, т. е., если решать позаписно, то получаем такие интервалы:
3 (23 - 20)
15 (20 - 5)
2 (5 - 3)
2 (3 - 1)

максимальный получается 15.

вот такую задачку нужно решить таблично (вроде, решается данная задачка математически или из статистики).

спустя 1 час 36 минут [обр] Александр Сохин(0/21)[досье]

GRAy[досье]

немного преобразовал запрос:

SELECT StockID, max(delta) AS MaxInterval FROM
(
SELECT a.StockID AS StockID, DateDiff('m', Max(b.Date), a.Date) AS delta
FROM SHL_VendorRelace AS a, SHL_VendorRelace AS b
WHERE a.Date>b.Date
AND a.Date>=Date( )-720
AND b.Date>=Date( )-720
AND a.StockID=b.StockID
GROUP BY a.StockID, a.Date
)
GROUP BY StockID ORDER BY StockID

так работает. Но я не совем понимаю DateDiff('m', Max(b.Date), a.Date) (т. е. a.Date - max(b.Date). Получается, что мы берем максимальную дату и вычитаем из нее другую дату. Непонятно - разве это предыдущая дата???

спустя 13 минут [обр] GRAy(8/259)[досье]
Александр Сохин[досье] Таки ДА! это предыдущая. Я про это вам талдычу уже половину этого топика. Причина того что Access до этого ошибки выдавал мне неизвестна, у меня он честно выдавал разницу в днях.
спустя 7 минут [обр] Александр Сохин(0/21)[досье]
GRAy[досье]
да, я же говорю - уже работает, я знаю, в чем ошибка была.
И проверил - действительно выдает максимальный срок. Огромное спасибо!!!
Единственное, я не просекаю - каким образом организуется это (ну, что именно предыдущая). Поясните вкратце, если несложно. Просто очень нужно понять...
спустя 16 минут [обр] GRAy(8/259)[досье]
С помощью max(b.date) находится максимальная дата из списка дат меньше текущей в a.date (это получается из условия a.date > b.date) логично предположить, что это и будет как раз предыдущая существующая дата. Прочитайте внимательно мои объяснения для Руслана[досье]
спустя 16 минут [обр] Александр Сохин(0/21)[досье]
GRAy[досье]
благодарю, я так подумал, но сомневался.
Еще раз спасибо.
спустя 7 часов [обр] Кирилл [Kirk] Королев(88/673)[досье]
В синтаксисе Акцесса не силен, сорри.
select b.date-a.date from tbl a
inner join tbl b on a.date<b.date
left join tbl c on c.date<b.date and c.date>a.date
where c.date is null
order by a.date-b.date
limit 1
Powered by POEM™ Engine Copyright © 2002-2005