Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

вопрос по работе join

Posted Декабрь 22nd, 2013 by andrey2020
in

Здравствуйте! есть 2 таблицы table1 и table2, вычисляю временное пересечение путем запроса

SELECT DISTINCT table1.login, table1.timeby, table1.timesell, table1.selection, table2.login, table2.timeby, table2.timesell, table2.selection
FROM table1
LEFT OUTER JOIN table2 ON (table1.timeby<table2.timesell AND table1.timesell>table2.timeby)
ORDER BY table1.timeby, table2.timeby;

содержание 1 таблицы

login | timeby | timesell | selection |

LA | 2012-11-02 7:20:00 | 2012-11-02 7:30:00
HJ | 2012-11-02 7:40:00 | 2012-11-02 7:46:00
LR | 2012-11-02 7:50:00 | 2012-11-02 7:57:00

содержание 2 таблицы

login | timeby | timesell | selection |

LL | 2012-11-02 7:00:00 | 2012-11-02 8:30:00
KK | 2012-11-02 8:30:00 | 2012-11-02 9:00:00
RR | 2012-11-02 9:50:00 | 2012-11-02 10:57:00

у нас получается при запросе с join такой результат, где 00:20:00 повторяется столько раз, сколько отрезков входит в заданный интервал
LA | 2012-11-02 7:20:00 | 2012-11-02 7:30:00 | | LL | 2012-11-02 7:00:00 | 2012-11-02 8:30:00 |00:10:00|
HJ | 2012-11-02 7:40:00 | 2012-11-02 7:46:00 | | LL | 2012-11-02 7:00:00 | 2012-11-02 8:30:00 |00:10:00|
LR | 2012-11-02 7:50:00 | 2012-11-02 7:57:00 | | LL | 2012-11-02 7:00:00 | 2012-11-02 8:30:00 |00:10:00|
*****************************************************| KK | 2012-11-02 8:30:00 | 2012-11-02 9:00:00 | |
*****************************************************| RR | 2012-11-02 9:50:00 | 2012-11-02 10:57:00 | |

вот, что хотелось бы получить...подойдут любые варианты, уже и не знаю что делать

LA | 2012-11-02 7:20:00 | 2012-11-02 7:30:00 | | LL | 2012-11-02 7:00:00 | 2012-11-02 8:30:00 |00:10:00|
HJ | 2012-11-02 7:40:00 | 2012-11-02 7:46:00 | | LL | 2012-11-02 7:00:00 | 2012-11-02 8:30:00 |00:06:00|
LR | 2012-11-02 7:50:00 | 2012-11-02 7:57:00 | | LL | 2012-11-02 7:00:00 | 2012-11-02 8:30:00 |00:07:00|
*****************************************************| KK | 2012-11-02 8:30:00 | 2012-11-02 9:00:00 | |
*****************************************************| RR | 2012-11-02 9:50:00 | 2012-11-02 10:57:00 | |

получается что видимо мне нужно как то сохранить то что выдает join а потом использовать расчет времени пересечений...в моем варианте когда я сначала делаю расчет а потом join у меня выводятся повторения...и высчитывается неправильная сумма колонки selection...вообщем такая беда ((

‹ Передача значения переменной в запрос из программы на с++ в базу данных, написанной на Postgresql Работа с массивом ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Можете словами написать что

On Декабрь 22nd, 2013 admin says:

Можете словами написать что нужно получить? Т.е. как должны данные из двух таблиц взаимодействовать?
Скажу сразу, мне крайне не нравится то, что вы пытаетесь сделать. Возможно вам нужно просто использовать функцию проверки вхождения времени в интервал, которая есть в PostreSQL, возможно просто использовать подзапрос. А вы пока что соединяете обе таблицы с собой же, да ещё и DISTINCT которого нужно бы категорически избегать. За такой запрос вам люди спасибо не скажут - долго, затратно по ресурсам.

мне бы хотелось записать

On Декабрь 22nd, 2013 andrey2020 says:

мне бы хотелось записать значение пересечения в поле selection, а поскольку join возвращает дубликаты(потому, что к одному отрезку может относиться 3 отрезка, т.е в один большой отрезок может входить более одного отрезка, в нашем примере их 3) когда отрезок попадает на отрезок, то все хорошо, повторений нет... join отрабатывает как надо, он добавляет дублирующие записи в вывод запроса, так оно и должно работать, но вот как избавиться от дубликатов? причем в данном примере дубликаты появляются на таблице 2, но ведь бывают ситуации когда в таблице 2 попадается большой отрезок и тогда он добавляет дубликаты в таблицу 1...сейчас я делаю так, вывод запроса с join я
экспортирую в exel и уже в ручную нахожу дубликаты и пересчитываю значения колонки selection...
ссылка для скачивания таблицы до ручного просчета и после него(то что выдает join c дубликатами)
http://dropmefiles.com/mCbev

Может я чего-то не понимаю,

On Декабрь 22nd, 2013 admin says:

Может я чего-то не понимаю, но откуда у вас берётся selection, если в первой таблице оно пустое и во второй тоже? Я даже создал указанные вами таблицы, прогнал данный вами запрос и получил такой результат:

 login |       timeby        |      timesell       | selection | login |       timeby        |      timesell       | selection 
-------+---------------------+---------------------+-----------+-------+---------------------+---------------------+-----------
 LA    | 2012-11-02 07:20:00 | 2012-11-02 07:30:00 |           | LL    | 2012-11-02 07:00:00 | 2012-11-02 08:30:00 | 
 HJ    | 2012-11-02 07:40:00 | 2012-11-02 07:46:00 |           | LL    | 2012-11-02 07:00:00 | 2012-11-02 08:30:00 | 
 LR    | 2012-11-02 07:50:00 | 2012-11-02 07:57:00 |           | LL    | 2012-11-02 07:00:00 | 2012-11-02 08:30:00 |

а не тот который вы написали в начальном посте.
Вот таблицы:

tmp=# select * from table1;
 login |       timeby        |      timesell       | selection 
-------+---------------------+---------------------+-----------
 LA    | 2012-11-02 07:20:00 | 2012-11-02 07:30:00 | 
 HJ    | 2012-11-02 07:40:00 | 2012-11-02 07:46:00 | 
 LR    | 2012-11-02 07:50:00 | 2012-11-02 07:57:00 | 
(3 строки)

tmp=# select * from table2;
 login |       timeby        |      timesell       | selection 
-------+---------------------+---------------------+-----------
 LL    | 2012-11-02 07:00:00 | 2012-11-02 08:30:00 | 
 KK    | 2012-11-02 08:30:00 | 2012-11-02 09:00:00 | 
 RR    | 2012-11-02 09:50:00 | 2012-11-02 10:57:00 | 
(3 строки)

Если прикинутся шлангом и тупо написать то, что вы хотите получить, то можно написать так:

SELECT DISTINCT table1.login, table1.timeby, table1.timesell, table1.selection, table2.login, table2.timeby, table2.timesell, (table1.timesell-table1.timeby) AS selection
FROM table1 LEFT OUTER JOIN table2 ON (table1.timeby<table2.timesell AND table1.timesell>table2.timeby) ORDER BY table1.timeby, table2.timeby;

и получить

 login |       timeby        |      timesell       | selection | login |       timeby        |      timesell       | selection 
-------+---------------------+---------------------+-----------+-------+---------------------+---------------------+-----------
 LA    | 2012-11-02 07:20:00 | 2012-11-02 07:30:00 |           | LL    | 2012-11-02 07:00:00 | 2012-11-02 08:30:00 | 00:10:00
 HJ    | 2012-11-02 07:40:00 | 2012-11-02 07:46:00 |           | LL    | 2012-11-02 07:00:00 | 2012-11-02 08:30:00 | 00:06:00
 LR    | 2012-11-02 07:50:00 | 2012-11-02 07:57:00 |           | LL    | 2012-11-02 07:00:00 | 2012-11-02 08:30:00 | 00:07:00

но мне сомнительно, что именно это вы и хотели

вот этими запросами я получаю

On Декабрь 23rd, 2013 andrey2020 says:

вот этими запросами я получаю selection

update table1 set selection=table2.timesell-table1.timeby from table2 where (table2.timesell>=table1.timeby and table2.timesell<=table1.timeby) and (table1.timeby<=table2.timesell and table1.timesell>=table2.timeby);

update table1 set selection=table1.timesell-table1.timeby from table2 where (table1.timeby=table2.timeby and table1.timesell=table2.timesell) and (table1.timeby<=table2.timesell and table1.timesell>=table2.timeby);

update table1 set selection=table1.timesell-table2.timeby from table2 where (table2.timeby<=table1.timesell and table2.timesell>=table1.timesell) and (table1.timeby<=table2.timesell and table1.timesell>=table2.timeby);
проверка на пересечения (текст с подчеркиванием)

здесь описаны 3 случая пересечения, при помощи этих запросов и заполняется колонка selection...
ваш вариант неплох, но как бы добавить теперь эти условия в запрос?

SELECT DISTINCT table1.login, table1.timeby, table1.timesell, table2.login, table2.timeby, table2.timesell, (table1.timesell-table1.timeby) AS selection where (table1.timeby=table2.timeby and table1.timesell=table2.timesell) and (table1.timeby<=table2.timesell and table1.timesell>=table2.timeby)
FROM table1 LEFT OUTER JOIN table2 ON (table1.timeby<=table2.timesell) AND table1.timesell>=table2.timeby) ORDER BY table1.timeby, table2.timeby;

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

частично решил задачу таким

On Декабрь 24th, 2013 andrey2020 says:

частично решил задачу таким способом...но данных стало в среднем на 100 строк больше, если убрать distinct то строк вообще в 3 раза дольше...в данном примере считается динамическим способом колонка selection, повторений что были раньше уже нет, но запрос смотрится как то не изящно...как бы его сгруппировать, да и избавиться бы от лишних строк?

вот сам запрос

select distinct on (table1.login, table1.timeby, table1.timesell), (table2.timesell-table1.timeby) as selection, table2.login, table2.timeby, table2.timesell from sdd1 LEFT OUTER JOIN sdd2 ON (table1.timeby=table2.timeby and table1.timesell=table2.timesell) where (table1.timeby>='2012-10-01 00:00:00' and table1.timesell<='2012-10-31 00:00:00') union

select distinct table1.login, table1.timeby, table1.timesell, (table2.timesell-table1.timeby) as selection, table2.login, table2.timeby, table2.timesell from sdd1 LEFT OUTER JOIN sdd2 ON (table1.timebytable2.timeby and table2.timesell>table1.timeby and table2.timesell='2012-10-01 00:00:00' and table1.timesell<='2012-10-31 00:00:00' union

select distinct table1.login, table1.timeby, table1.timesell, (table1.timesell-table2.timeby) as selection, table2.login, table2.timeby, table2.timesell from sdd1 LEFT OUTER JOIN sdd2 ON (table1.timebytable2.timeby and table2.timebytable1.timesell) where table1.timeby>='2012-10-01 00:00:00' and table1.timesell<='2012-10-31 00:00:00' order by 2, 3, 4, 5 ;

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023