Конференция "Базы" » Как элегантно выполнить запрос на SQL?
 
  • Corrid © (21.12.15 18:29) [0]
    Имеем таблицу продаж:

    SALES - PK записи| ID продавца | дата/время продажи | Количество
    ID | SELLER_ID | DT_SALE | CNT



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

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



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

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

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

    SELLER_ID | ROLE_ID | DT_SALE | CNT



    на чистом ansi sql?
  • TohaNik © (21.12.15 21:23) [1]

    > Corrid ©   (21.12.15 18:29)  

    А фиг ты его выполнишь, если у вас больше 1-го продавца, которые строем не ходят.
  • Юрий Зотов © (21.12.15 22:51) [2]
    Может, я чего не понимаю, но почему в ROLE_HISTORY нет ID продавца?

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

    А в SALES, наверное, неплохо было бы иметь ID проданного товара?
  • Юрий Зотов © (21.12.15 23:12) [3]
    Кстати, раз в ROLE_HISTORY нет ссылки на продавца, то задача, ИМХО, в такой постановке решения не имеет. Поправьте, если не так.
  • TohaNik © (21.12.15 23:15) [4]

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

    Да я ж почему в шутку, из-за в потрепаться.
    А так в ROLE_HISTORY - ID продавца обязательно, а в SALES, там вариантов достаточно..., количество не нужно, если это PK. как в теме.
  • backuper (21.12.15 23:21) [5]
    В sales ID работника, в ROLE_HISTORY - ID работника, дата, вступление в роль.
    Не хватает ID работника в хистори.
  • backuper (21.12.15 23:27) [6]
    ну а если работник один или работа у них никак не пересекается сменно, то можно так (в принципе тут даже id работника не нужен, если указываем на id продажи, просто покажет роль на момент продажи):
    select top 1 rr.* from dbo.ROLE_HISTORY rr (nolock)
       join (
     select max(DT_CHANGE) as [DT_CHANGE] from dbo.ROLE_HISTORY r
     join SALES s (nolock) on s.id=12345 and s.DT_SALE<=r.DT_CHANGE
     group by s.SELLER_ID
       ) as pp on pp.DT_CHANGE=rr.DT_CHANGE
       order by rr.DT_CHANGE desc

  • TohaNik © (21.12.15 23:33) [7]

    > backuper   (21.12.15 23:27) [6]

    Ответ не верный, хотели в стандарте SQL, не помню какой последний.
  • backuper (21.12.15 23:37) [8]

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

    Конечно можете написать, я не против.
    Развернуть в селект все это чтобы даже на мускле заработало - не сильно много букв надо поменять.
  • TohaNik © (21.12.15 23:47) [9]

    > backuper   (21.12.15 23:37) [8]

    Практически все:)
  • Corrid © (22.12.15 00:49) [10]
    >Может, я чего не понимаю, но
    >почему в ROLE_HISTORY
    > нет ID продавца?

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

    ROLE_HISTORY - PK записи | id продавца | дата/время смены | ID новой роли
    ID | SELLER_ID | DT_CHANGE | NEWROLE_ID
  • Кщд © (22.12.15 07:12) [11]
    >backuper   (21.12.15 23:27) [6]
    в этом запросе чудовищно все:
    1. не возвращает то, что нужно автору;
    2. предполагает наличие одного работника (сказочное допущение);
    3. не в чистом ANSI;
    4. демонстрирует порочную практику с nolock в MSSQL;
    5. и, наконец,  "order by rr.DT_CHANGE desc" - это какой-то запредельный идиотизм. сколько записей вернет группировка? зачем сортировать одно и ту же дату-время?
  • Кщд © (22.12.15 07:19) [12]
    п. 5 снимается - не заметил группировку по seller_id
    добавляется новый п. 5: у двух (10, 1000) сменили роль в одно и то же время - запрос вернет декартово произведение - не бред ли?
  • backuper (22.12.15 09:41) [13]

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

    смотреть еще раз внимательно
  • ANSI (22.12.15 17:12) [14]
    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
  • Corrid © (22.12.15 18:22) [15]
    ANSI, ты, видимо, пропустил строчку:

    ...
    from SALES S
    ...


    Но в подзапросе, например, Oracle не видит внешние таблицы (то есть, соответственно S).
    Разве в ansi sql так можно?
  • ANSI (22.12.15 19:30) [16]
    Пропустил, конечно.

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

    Насколько понимаю, такой запрос - это классика (хотя по скорости, наверное, не лучший).
  • Кщд © (23.12.15 06:59) [17]
    >backuper   (22.12.15 09:41) [13]
    смотрите внимательно:
    у двух РАЗНЫХ seller_id одинаковое значение dt_change;
    => данные задвоятся
  • Кщд © (23.12.15 07:03) [18]
    >ANSI   (22.12.15 19:30) [16]
    ещё раз подумайте:
    1. объединяете SALES, ROLE_HISTORY;
    2. снова заходите в ROLE_HISTORY, чтобы получить максимальный DT_CHANGE.

    можно обойтись одним обращением к ROLE_HISTORY
  • Кщд © (23.12.15 07:06) [19]
    >Corrid ©   (22.12.15 18:22) [15]
    >Но в подзапросе, например, Oracle не видит внешние таблицы (то есть, >соответственно S).
    это коррелированный подзапрос
    и Oracle видит S, и стандарт ANSI не против
  • Chase © (23.12.15 11:28) [20]

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

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

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

    таким образом если РАЗНЫЕ seller_id, то задвоится не позволит условие:
    T.SELLER_ID = S.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
  • Кщд © (23.12.15 11:49) [21]
    >почему? Ведь там условие:
    мой комментарий относился к "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]

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

    а что такое коррелированный / не коррелированный?
    Где толково по русски это можно почитать?
  • Кщд © (23.12.15 15:05) [23]
    >Chase ©   (23.12.15 12:56) [22]
    судя по вопросам, вы даже не пытались гуглить
    судя по моему ответу, мне неинтересно разжевывать что-либо человеку, который не потрудился ни секунды, чтобы найти ответ на свои вопросы
  • backuper (23.12.15 18:28) [24]

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

    Еще раз смотреть внимательно. Сначала на первый пять слов в [6], затем понять почему запрос вернет одну строку из запроса где хоть 6 задвоений будет. Думать.
  • Chase © (23.12.15 18:31) [25]

    > судя по

    судя по тому, что вы не желаете помогать, я не понимаю зачем вы отвечаете.
  • Кщд © (24.12.15 08:08) [26]
    >backuper   (23.12.15 18:28) [24]
    зачем выдумывать массу нелепых ограничительных допущений, если можно быстро и просто написать универсальный запрос?
    на чистом ANSI SQL.
  • Кщд © (24.12.15 08:22) [27]
    >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
  • backuper (24.12.15 08:24) [28]

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

    Зачем вы тратите буквы вместо того чтобы внимательно читать? Не нравятся допущения - не воспринимайте запрос, а то сначала натрындели десяток сообщений по своей невнимательности, а теперь извиваетесь.
  • Кщд © (24.12.15 09:07) [29]
    п.5 снимается - это, действительно, моя невнимательность
    этого не отрицал
    в "Кщд ©   (22.12.15 07:12) [11]" еще четыре пункта
    комментарии по ним будут?
  • ANSI (24.12.15 13:46) [30]
    > Кщд ©   (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 был бы очень признателен. Вполне серьезно, без подколок.
  • Кщд © (24.12.15 14:32) [31]
    >ANSI   (24.12.15 13:46) [30]
    речь о том, что ROLE_HISTORY фигурирует в запросе дважды

    >А за пример того, как можно решить задачу одним обращением к >физической таблице ROLE_HISTORY был бы очень признателен.
    если хочется на чистом ANSI, то window functions же
  • backuper (24.12.15 20:30) [32]

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

    нет, не хочу кормить.
  • Кщд © (25.12.15 06:51) [33]
    >backuper   (24.12.15 20:30) [32]
    разумно
    в след. раз внимательно читайте вопрос автора
    предложенный вами запрос не отвечает ни одному из требованй ТС
  • Chase © (25.12.15 11:41) [34]
    Удалено модератором
 
Конференция "Базы" » Как элегантно выполнить запрос на SQL?
Есть новые Нет новых   [118241   +25][b:0][p:0.002]