Конференция "Базы" » Как элегантно выполнить запрос на 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 не против
 
Конференция "Базы" » Как элегантно выполнить запрос на SQL?
Есть новые Нет новых   [118238   +18][b:0][p:0.001]