ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите с запросом
13 сообщений из 13, страница 1 из 1
помогите с запросом
    #34857390
AndreyVo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreyVo
Гость
попалась коварная задачка
есть таблица:

Код: plaintext
1.
2.
3.
4.
5.
CREATE TABLE calls (
  connect_time     timestamp( 1 ) WITHOUT TIME ZONE,
  disconnect_time  timestamp( 1 ) WITHOUT TIME ZONE,
  duration         integer
) WITH (  OIDS = TRUE );

connect_time - начало звонка
disconnect_time - разрыв соединения
duration - длительность в секундах

необходимо определить максимальное количество одновременных звонков в сутки.
количество записей в сутки - около 20000.

простая пробежка курсором по таблице и поиск пересекающихся интервалов выполняется очень долго.
пробовал каким-то образом прикрутить к решению дополнительную таблицу, в которой 86400 записей - количество секунд в сутках, но до конца не придумал как мне с ней обращаться - идея была в том, чтобы найти с помощью этой доп.таблицы количество одновременных сессий для каждой записи, а потом из этого множества выбрать max.

Очень хочется решить эту задачу одним запросом, пусть с вложенными и минимизировать время вычисления.

Все идеи приветствуются.
спасибо.
...
Рейтинг: 0 / 0
09.10.2007, 15:58
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857499
Winnipuh
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Winnipuh
Участник
кстати,
нужно ли это поле duration integer ?
оно производно и зависит от начала и конца
вроде и нету смысла хранить
...
Рейтинг: 0 / 0
09.10.2007, 16:20
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857520
ilejn
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ilejn
Участник
Какие индексы созданы над таблицей?
...
Рейтинг: 0 / 0
09.10.2007, 16:24
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857527
AndreyVo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreyVo
Гость
Winnipuhкстати,
нужно ли это поле duration integer ?
оно производно и зависит от начала и конца
вроде и нету смысла хранить
хранить его нужно, но привел я его напрасно - согласен
в вычислениях это поле использовать нельзя и не нужно, потому что например duration может быть 0, а disconnect_time - connect_time > 0, например трубку если не берут или разрыв в результате ошибки какой-ниудь случился и получается нулевая длительность.
...
Рейтинг: 0 / 0
09.10.2007, 16:25
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857539
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
AndreyVoпростая пробежка курсором по таблице и поиск пересекающихся интервалов выполняется очень долгокак это делали?

пробежаться в pl-pgsql в цикле по результатам нижеприведенного запроса изменяя значение счетчика $CNT++ или $CNT-- в засисимости от type, и запоминая $MAX=$CNT if $MAX<$CNT.

select * from ( select 'start' as type, connect_time as time from calls union all select 'stop' as type, disconnect_time as time from calls ) order by time
...
Рейтинг: 0 / 0
09.10.2007, 16:28
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857541
AndreyVo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreyVo
Гость
ilejnКакие индексы созданы над таблицей?
отдельные индексы B-Tree на connect_time и disconnect_time.

Если необходимо создать другие - составные может быть - подскажите - обсуждаются любые решения
...
Рейтинг: 0 / 0
09.10.2007, 16:30
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857828
AndreyVo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreyVo
Гость
LeXa NalBat[quot AndreyVo]простая пробежка курсором по таблице и поиск пересекающихся интервалов выполняется очень долгокак это делали?
некрасиво делал - для каждой строки считал в подзапросе count пересечений интервалов, сам запрос уже не существует в природе -воспроизводить это чудовище не хочется, чтобы здесь привести.
Код: plaintext
1.
select * from ( select 'start' as type, connect_time as time from calls union all select 'stop' as type, disconnect_time as time from calls ) order by time[/quot]

ваше решение мне нравится - спасибо за помощь
...
Рейтинг: 0 / 0
09.10.2007, 17:21
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857921
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
насколько я понял - одна строка в этой таблице - это один _отдельный_ целиковый звонок ? и насколько я понял, два звонка считаются одновременными если время начала или конца первого звонка попадает в промежуток времени второго ?

тогда если без цикла - одним запросом, то имхо можно вот как-то так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
--- соединим каждый звонок с одновременными с ним звонками
select a.connect_time, a.disconnect_time, a.duration,
  b.connect_time as b_connect_time, b.disconnect_time as b_disconnect_time, b.duration as b_duration
from calls a
inner join calls b on (
  (b.connect_time >= a.connect_time and b.connect_time <= a.disconnect_time)
  or (b.disconnect_time >= a.connect_time and b.disconnect_time <= a.disconnect_time)
);
ну и соответственно если я нигде не накосячил :) то ответ на изначальный вопрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
select max(count) from (
    select a.connect_time, count(*) from calls a
    inner join calls b on (
        (b.connect_time >= a.connect_time and b.connect_time <= a.disconnect_time)
        or (b.disconnect_time >= a.connect_time and b.disconnect_time <= a.disconnect_time)
    )
    group by a.connect_time --- так как pk нету, группировать по началу звонка ?
) as foo

--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
09.10.2007, 17:46
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857975
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
взять максимум от
Код: plaintext
1.
2.
3.
4.
SELECT c.connect_time , count(c1.*)
FROM (SELECT DISTINCT connect_time FROM calls) AS c
 LEFT JOIN calls AS c1
 ON c.connect_time BETWEEN c1.connect_time AND c1.disconnect_time
GROUP BY c.connect_time
...
Рейтинг: 0 / 0
09.10.2007, 18:00
| Ответить | Цитировать | Написать  
помогите с запросом
    #34857993
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
Ёш токо достаточно отсекать по началу (то что попадает в промежуток можно не считать - они считаются в сумме другой строки - по началу попавшего в промежуток). - т.е. реперы "начало" - это та самая опорная табличка , вместо "86400 записей - количество секунд в сутках", в которые считаются одновременные звонки.
...
Рейтинг: 0 / 0
09.10.2007, 18:05
| Ответить | Цитировать | Написать  
помогите с запросом
    #34858023
ilejn
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ilejn
Участник
У меня под рукой нет PostgreSQL, но не думаю, чтобы я сделал слишком много глупостей в этом запросе

select max(counter) from
(select t1.oid,count(*) as counter from calls as t1, calls as t2 where t1.oid <> t2.oid
and t1.connect_time<=t2.connect_time and t1.disconnect_time>=t2.disconnect_time
group by t1.oid)

Индекс над таблицей д.б. только один - первое поле connect_time, второе disconnect_time.

В результате у нас будет seq_scan, а для каждого элемента еще один поиск по диапазону.
...
Рейтинг: 0 / 0
09.10.2007, 18:12
| Ответить | Цитировать | Написать  
помогите с запросом
    #34858035
ilejn
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ilejn
Участник
ilejnне думаю, чтобы я сделал слишком много глупостей в этом запросе


Сделал-таки. Запрос будет врать на единицу, т.е. ее нужно прибавлять к полученному результату.
...
Рейтинг: 0 / 0
09.10.2007, 18:15
| Ответить | Цитировать | Написать  
помогите с запросом
    #34860352
AndreyVo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreyVo
Гость
спасибо, мужики!
все варианты рабочие - уже прикрутил - все в порядке.
хороший опыт.
...
Рейтинг: 0 / 0
10.10.2007, 14:28
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / помогите с запросом
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]