Имеем таблицу продаж:




Также есть история смены ролей продавца:




То есть, с определенной даты/время у продавца может быть новая роль (например: стажер, продавец, старший продавец).
В таблице продаж ID роли НЕ хранится.

Чтобы понять под какой ролью ROLE_ID был продавец в момент продажи - нужно в ROLE_HISTORY найти запись с наибольшей DT_CHANGE, где DT_CHANGE <= DT_SALE и от этой записи взять NEWROLE_ID.

Как получить сводную информацию:




на чистом ansi sql?

> Corrid ©   (21.12.15 18:29)  

А фиг ты его выполнишь, если у вас больше 1-го продавца, которые строем не ходят.

Может, я чего не понимаю, но почему в ROLE_HISTORY нет ID продавца?

Вот имеем смену роли - запись в ROLE_HISTORY. Как понять, какой именно продавец сменил роль?

А в SALES, наверное, неплохо было бы иметь ID проданного товара?

Кстати, раз в ROLE_HISTORY нет ссылки на продавца, то задача, ИМХО, в такой постановке решения не имеет. Поправьте, если не так.

> Юрий Зотов ©   (21.12.15 22:51) [2]

Да я ж почему в шутку, из-за в потрепаться.
А так в ROLE_HISTORY - ID продавца обязательно, а в SALES, там вариантов достаточно..., количество не нужно, если это PK. как в теме.

В sales ID работника, в ROLE_HISTORY - ID работника, дата, вступление в роль.
Не хватает ID работника в хистори.

ну а если работник один или работа у них никак не пересекается сменно, то можно так (в принципе тут даже id работника не нужен, если указываем на id продажи, просто покажет роль на момент продажи):

> backuper   (21.12.15 23:27) [6]

Ответ не верный, хотели в стандарте SQL, не помню какой последний.

> Ответ не верный, хотели в стандарте SQL, не помню какой
> последний.

Конечно можете написать, я не против.
Развернуть в селект все это чтобы даже на мускле заработало - не сильно много букв надо поменять.

> backuper   (21.12.15 23:37) [8]

Практически все:)

>Может, я чего не понимаю, но
>почему в ROLE_HISTORY
> нет ID продавца?

Прошу прощения, это мой косяк. Конечно же Id продавца есть, должно быть так:

ROLE_HISTORY - PK записи | id продавца | дата/время смены | ID новой роли
ID | SELLER_ID | DT_CHANGE | NEWROLE_ID

>backuper   (21.12.15 23:27) [6]
в этом запросе чудовищно все:
1. не возвращает то, что нужно автору;
2. предполагает наличие одного работника (сказочное допущение);
3. не в чистом ANSI;
4. демонстрирует порочную практику с nolock в MSSQL;
5. и, наконец,  "order by rr.DT_CHANGE desc" - это какой-то запредельный идиотизм. сколько записей вернет группировка? зачем сортировать одно и ту же дату-время?

п. 5 снимается - не заметил группировку по seller_id
добавляется новый п. 5: у двух (10, 1000) сменили роль в одно и то же время - запрос вернет декартово произведение - не бред ли?

> добавляется новый п. 5: у двух (10, 1000) сменили роль в
> одно и то же время - запрос вернет декартово произведение
> - не бред ли?

смотреть еще раз внимательно

select
 S.SELLER_ID, R.NEWROLE_ID, S.DT_SALE, S.CNT
where
 R.SELLER_ID = S.SELLER_ID and R.DT_CHANGE =
(select
 max(DT_CHANGE) from ROLE_HISTORY T
where
 T.SELLER_ID = S.SELLER_ID and T.DT_CHANGE <= S.DT_SALE
)
order by S.SELLLER_ID

ANSI, ты, видимо, пропустил строчку:

...
from SALES S
...

Но в подзапросе, например, Oracle не видит внешние таблицы (то есть, соответственно S).
Разве в ansi sql так можно?

Пропустил, конечно.

select
 S.SELLER_ID, R.NEWROLE_ID, S.DT_SALE, S.CNT
from
 SALES S, ROLE_HISTORY R
... далее по тексту.

Насколько понимаю, такой запрос - это классика (хотя по скорости, наверное, не лучший).

>backuper   (22.12.15 09:41) [13]
смотрите внимательно:
у двух РАЗНЫХ seller_id одинаковое значение dt_change;
=> данные задвоятся

>ANSI   (22.12.15 19:30) [16]
ещё раз подумайте:
1. объединяете SALES, ROLE_HISTORY;
2. снова заходите в ROLE_HISTORY, чтобы получить максимальный DT_CHANGE.

можно обойтись одним обращением к ROLE_HISTORY

>Corrid ©   (22.12.15 18:22) [15]
>Но в подзапросе, например, Oracle не видит внешние таблицы (то есть, >соответственно S).
это коррелированный подзапрос
и Oracle видит S, и стандарт ANSI не против

> у двух РАЗНЫХ seller_id одинаковое значение dt_change;
> => данные задвоятся

почему? Ведь там условие:

T.SELLER_ID = S.SELLER_ID and T.DT_CHANGE <= S.DT_SALE

таким образом если РАЗНЫЕ seller_id, то задвоится не позволит условие:



А как можно лучше сделать запрос искомого?


> и Oracle видит S,

ну по крайней мере в Join'ах с Select'ом, типа:

select
 *
from table1 t1
join
 (select *
  from table2 t2
  where t2.xxx = t1.yyy --  < ---  t1.yyy
 ) s
...

Oracle ругается, что не знает, что такое t1.yyy

>почему? Ведь там условие:
мой комментарий относился к "backuper   (21.12.15 23:27) [6]"

>А как можно лучше сделать запрос искомого?
есть минимум два разных варианта для чистого ANSI
но разговаривать не о чем, пока не увидим, как вы ПЫТАЛИСЬ сделать

>ну по крайней мере в Join'ах с Select'ом, типа:
во-первых - это не коррелированный подзапрос, который фигурировал в "ANSI   (22.12.15 17:12) [14] "
во-вторых, если свежая версия Oracle, то читайте про lateral

> во-первых - это не коррелированный подзапрос

а что такое коррелированный / не коррелированный?
Где толково по русски это можно почитать?

>Chase ©   (23.12.15 12:56) [22]
судя по вопросам, вы даже не пытались гуглить
судя по моему ответу, мне неинтересно разжевывать что-либо человеку, который не потрудился ни секунды, чтобы найти ответ на свои вопросы

> смотрите внимательно:
> у двух РАЗНЫХ seller_id одинаковое значение dt_change;
> => данные задвоятся

Еще раз смотреть внимательно. Сначала на первый пять слов в [6], затем понять почему запрос вернет одну строку из запроса где хоть 6 задвоений будет. Думать.

> судя по

судя по тому, что вы не желаете помогать, я не понимаю зачем вы отвечаете.

>backuper   (23.12.15 18:28) [24]
зачем выдумывать массу нелепых ограничительных допущений, если можно быстро и просто написать универсальный запрос?
на чистом ANSI SQL.

>Chase ©   (23.12.15 18:31) [25]
>судя по тому, что вы не желаете помогать, я не понимаю зачем вы >отвечаете.
в [18], [19], [21] - дана исчерпывающая информация
помощь - это когда человек пытается и у него не выходит
что вы сделали для решения вопроса?
чем и зачем помогать человеку, который не способен в поисковой строке вбить: коррелированный подзапрос?

по случаю четверга отсыплю чутка:

букварь по SQL:
http://lmgtfy.com/?q=%D0%B3%D1%80%D0%B0%D0%B1%D0%B5%D1%80+%D0%BF%D0%BE%D0%BD%D0%B8%D0%BC%D0%B0%D0%BD%D0%B8%D0%B5+sql

коррелированный подзапрос:
http://lmgtfy.com/?q=%D0%BA%D0%BE%D1%80%D1%80%D0%B5%D0%BB%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%BD%D1%8B%D0%B9+%D0%BF%D0%BE%D0%B4%D0%B7%D0%B0%D0%BF%D1%80%D0%BE%D1%81

lateral:
http://lmgtfy.com/?q=oracle+lateral+view

> зачем выдумывать массу нелепых ограничительных допущений,
>  если можно быстро и просто написать универсальный запрос?

Зачем вы тратите буквы вместо того чтобы внимательно читать? Не нравятся допущения - не воспринимайте запрос, а то сначала натрындели десяток сообщений по своей невнимательности, а теперь извиваетесь.

п.5 снимается - это, действительно, моя невнимательность
этого не отрицал
в "Кщд ©   (22.12.15 07:12) [11]" еще четыре пункта
комментарии по ним будут?

> Кщд ©   (23.12.15 07:03) [18]
> ещё раз подумайте:
> 1. объединяете SALES, ROLE_HISTORY;
> 2. снова заходите в ROLE_HISTORY, чтобы получить максимальный
> DT_CHANGE.
>
> можно обойтись одним обращением к ROLE_HISTORY

Еще раз подумал. Таблица ROLE_HISTORY в запросе и в подзапросе - это две логически разные таблицы (чтобы подчеркнуть это, специально написаны алиасы). Поэтому заход в ROLE_HISTORY в подзапросе - это не второй заход в таблицу R, а первый заход в таблицу T.

Если я не прав, поправьте. А за пример того, как можно решить задачу одним обращением к физической таблице ROLE_HISTORY был бы очень признателен. Вполне серьезно, без подколок.

>ANSI   (24.12.15 13:46) [30]
речь о том, что ROLE_HISTORY фигурирует в запросе дважды

>А за пример того, как можно решить задачу одним обращением к >физической таблице ROLE_HISTORY был бы очень признателен.
если хочется на чистом ANSI, то window functions же

> комментарии по ним будут?

нет, не хочу кормить.

>backuper   (24.12.15 20:30) [32]
разумно
в след. раз внимательно читайте вопрос автора
предложенный вами запрос не отвечает ни одному из требованй ТС

Удалено модератором