Как максимально быстро и изящно сделать group by по максимальному времени создания?

LKU

Коллеги, интересно ваше мнение, как лучше всего решить такую задачку.

Имеем таблицу zdbtab в БД:
MATNR - ключ, номер товара
DOCNAM - ключ, номер документа
CRETIME - не ключ, временной штамп создания документа.

Задача: имеем список товаров lt_matnr, надо из таблицы zdbtab для каждого товара выбрать номер документа с максимальным временем создания.

Бородин Игорь

нет возможности проверить, но вроде, должно отработать:

Code:

SORT lt_matnr BY matnr docnam cretime DESCENDING .
DELETE ADJACENT DUPLICATES FROM lt_matnr COMPARING matnr docnam .

nicky555

Так тоже прикольно :lol:

(по смыслу таблица подходит для теста):

Code:

  DATA: lt_cdhdr TYPE STANDARD TABLE OF cdhdr.

  SELECT DISTINCT objectclas objectid udate
    INTO CORRESPONDING FIELDS OF TABLE lt_cdhdr
    FROM cdhdr
    WHERE udate = sy-datum
    ORDER BY objectclas objectid.

  SELECT objectclas objectid changenr
    INTO CORRESPONDING FIELDS OF TABLE lt_cdhdr
    FROM cdhdr
    FOR ALL ENTRIES IN lt_cdhdr
    WHERE udate    = lt_cdhdr-udate
      AND changenr = ( SELECT MAX( changenr )
                         FROM cdhdr
                         WHERE objectclas = lt_cdhdr-objectclas
                           AND objectid   = lt_cdhdr-objectid
                           AND udate      = lt_cdhdr-udate )
    .

Про оптимальность выборки тут речи не идет... :lol:

LKU

Бородин Игорь, насколько я понял, вы предлагаете выбрать из БД по списку товаров документы за все даты и уже на сервере приложений обработать табличку.
При наличии тысяч документов на один товар и индекса по matnr CRETIME в таблице, это явно не лучший способ.

nicky555, я так и написал, но результат отличался от ожиданий: для каждой порции товаров система находила максимальное время выгрузки в подселекте и уже его применяла к основному селкту.
Возможно, виноваты примененные без полного осознания хинты:

Code:

                %_HINTS ORACLE '&max_blocking_factor 100&'
                           ORACLE '&max_in_blocking_factor 100&'.

nicky555

Хинты здесь не нужны - они регулируют построение запроса по блокам записей таблицы. Даже затрудняюсь предположить, как они будут влиять на приведенную конструкцию с подзапросом.
А смысл выборки до безобразия прост - для каждой записи выбранной в первом селекте выбирается граничное значение (SubSelect), которое используется в результирующем селекте.
Возможно, коллеги, сведущие в Oracle (или какая у вас СУБД), подскажут конструкцию на Native SQL? :roll:

Это, наверное, будет круче и быстрее.

Бородин Игорь

извиняюсь, неправильно прочитал

LKU написал:

Задача: имеем список товаров lt_matnr...

думал, что lt_matnr и есть внутр.таблица с выбранными данными.
Тогда, или как Николай сказал, или таким образом:

Code:

LOOP AT lt_matnr INTO ls_matnr .
   SELECT *
     INTO TABLE lt_zdbtab_tmp 
     FROM zdbtab 
    WHERE matnr EQ ls_matnr-matnr .
   CHECK sy-subrc EQ 0 .

   SORT lt_zdbtab_tmp BY cretime DESCENDING .
   READ TABLE lt_zdbtab_tmp INTO ls_zdbtab INDEX 1 .

   APPEND ls_zdbtab TO lt_zdbtab .
ENDLOOP .

LKU

nicky555 написал:

Хинты здесь не нужны - они регулируют построение запроса по блокам записей таблицы. Даже затрудняюсь предположить, как они будут влиять на приведенную конструкцию с подзапросом.
А смысл выборки до безобразия прост - для каждой записи выбранной в первом селекте выбирается граничное значение (SubSelect), которое используется в результирующем селекте.
Возможно, коллеги, сведущие в Oracle (или какая у вас СУБД), подскажут конструкцию на Native SQL? :roll:

Это, наверное, будет круче и быстрее.

Провел эксперимент - важно чтобы в основном запросе все ограничения where использовали передачу параметра из таблицы FAE. Если хотя бы одно ограничение задано константой - в подзапросе максимального времени товары объединяются пачкой через IN, что ломает весь смысл. Вот такой вот очередной прикол FAE.

В Oracle да, была какая-то фича, позволяющая выбрать данные одним запросом group by, но я не знаю как в native sql подать на вход внутреннюю табличку значений lt_matnr, это только FAE умеет.

LKU

По поводу Native SQL наверное сработало бы что-нибудь вроде:

Code:

select max(docnr) KEEP (DENSE_RANK LAST ORDER BY CRETIME )
from zdbtab group by matnr
where matnr in (подать на вход lt_matnr)

UKY

А как-нибудь так работает?

Code:

  SELECT matnr docnam MAX( cretime ) AS cretime
    INTO CORRESPONDING FIELDS OF TABLE lt_zdbtab
    FROM zdbtab
    WHERE matnr IN lr_matnr
    GROUP BY matnr docnam cretime
    HAVING cretime = ( SELECT MAX( cretime )
                     FROM zdbtab AS tab
                     WHERE tab~matnr = zdbtab~matnr
                       AND tab~docnam = zdbtab~docnam )
  .

Ну и если скорости ещё захочется, то добавить индекс по полям matnr docnam cretime.

John Doe

Может быть есть смысл добавить в эту табличку поле с индикатором "Активно" и немного подредактировать логику ее заполнения и обновления? Это конечно не спортивно, в плане темы топика, но работать будет железно.

PS
Либо добавить табличку:
MATNR - ключ, номер товара
DOCNAM - не ключ, номер документа
и параллельно с первой заполнять.

LKU

Второй вариант с отдельной табличкой (по сути - материализованный view) - это уж очень круто для такой задачи.

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

Сложновато и есть риск неконсистентности (например, последний документ взяли и просто удалили

), но выборки потом будут быстрее работать, это точно

LKU

Для информации - пока я остановился на варианте nicky555 с FAE и подзапросом.
В принципе, работает и вполне быстро (так как позапрос выполняется целиком по индексу, а потом идет доступ к основной таблице по первичному ключу).

Paul_80

Если я правильно понял вопрос, то:

Code:

        SELECT a~col1 a~date
        FROM table1 AS a
        INTO TABLE lt_table1
        WHERE a~col1 = 'COL1'
           AND a~date =
            ( SELECT MAX( b~date ) FROM table1 AS b
              WHERE b~col1 = a~col1
                AND b~date BETWEEN lv_date_beg AND lv_date_end ).

Такой селект выбирает для каждой группы (table1-col1) максимальную дату (table1-date) и получает значение col1, соответствующее максимальной дате для группы

LKU

Ну да, только на входе у меня не одно значение col1 = 'COL1', а целый список в dj внутренней табличке lt_matnr, т.е. нужно либо применять цикл, либо FAE, либо преобразовывать в ranges, но следить чтобы не упало в дамп из-за превышения размера селекта.

Paul_80

Ну, с выборкой, адаптированной под FOR ALL ENTRIES у меня сработало:

Code:

  SELECT a~matnr a~docnum a~cretime
  INTO CORRESPONDING FIELDS OF TABLE lt_doctab
  FROM table1 AS a
  FOR ALL ENTRIES IN lt_matnr
  WHERE a~matnr = lt_matnr-table_line
    AND a~cretime =
        ( SELECT MAX( b~cretime )
          FROM table1 AS b
          WHERE b~matnr = a~matnr
            AND b~docnum = a~docnum ).

Правила форума

Как максимально быстро и изящно сделать group by по максимальному времени создания?

Кто сейчас на конференции