order by и group by

[Gustav]

Думаю, что то, о чем сейчас поведаю, тоже можно рассмотреть как вариант. Будет и группировка, и сортировка - и практически "на месте", т.е. без утомительного создания новых структур.

Подход иллюстрируется на небольшой практической задачке, которую мне пришлось выполнить вчера.

В нашей компании складской учет в Аксапте ведется по партиям. Если какая-то номенклатура учитывается по серийным номерам, то комбинация "серийный номер + номер партии" должна быть уникальна по каждому из номеров в отдельности, т.е. один и тот же серийный номер не может встречаться (комбинироваться) с разными номерами партий - только с одной. Так должно быть. И в подавляющем большинстве случаев так оно и есть. Но, однако, не абсолютно во всех случаях. По каким-то причинам образовались дублирования: одному серийнику соответствуют более, чем одна партия. Причины сейчас выясняются.

Для обнаружения дубликатов был написан следующий запрос на классическом SQL (для Oracle):

SELECT 
    Tmp01.InventSerialId AS "Серийный номер", 
    Tmp02.Cnt AS "Кол-во повторений",   --// ...этого серийника для разных номеров партий 
    Tmp01.InventBatchId AS "Номер партии"
FROM 
    (SELECT InventSerialId, InventBatchId FROM InventDim 
    INNER JOIN InventTrans ON InventDim.InventDimId = InventTrans.InventDimId
    WHERE InventSerialId <> CHR(2) --// CHR(2) - аксаптовский null для Oracle
    GROUP BY InventSerialId, InventBatchId
    ) Tmp01 --// уникальные сочетания серийника и партии, встречающиеся в InventTrans
INNER JOIN
    (SELECT InventSerialId, COUNT(*) AS Cnt	FROM
        (SELECT InventSerialId, InventBatchId FROM InventDim 
        INNER JOIN InventTrans ON InventDim.InventDimId = InventTrans.InventDimId
        WHERE InventSerialId <> CHR(2)  
        GROUP BY InventSerialId, InventBatchId) --// ЕЩЕ РАЗ увы! :( : уникальные сочетания серийника и партии, встречающиеся в InventTrans 
    GROUP BY InventSerialId
    HAVING COUNT(*) <> 1
    ) Tmp02 --// уникальные серийники из уникальных сочетаний серийник+партия
ON Tmp01.InventSerialId = Tmp02.InventSerialId 
ORDER BY Tmp01.InventSerialId, Tmp01.InventBatchId

Поясню словами, что делает этот запрос:
1. Из таблицы InventDim (Складская аналитика), связанной по полю InventDimId с таблицей InventTrans (Складские проводки), группировкой выбираются уникальные комбинации серийных номеров и номеров партий (поля InventSerialId, InventBatchId).

2. Далее из получившейся выборки выбираются (извиняюсь за тавтологию!) уникальные серийные номера (группировкой уже только по InventSerialId) и для каждого серийного номера подсчитывается кол-во различающихся номеров партий (count); при этом отбрасываются строки с count = 1, так как в данном случае они нам не интересны.

3. Наконец, из выборки п.1 выбираются записи, в которых встречаются серийные номера, обнаруженные выборкой п.2. Записи сортируются, чтобы две различные партии, соответствующие одному серийнику, соседствовали в этом окончательном списке.


В принципе задачу обнаружения задвоений можно было бы считать законченной и приступать к поиску причин их возникновения, но захотелось попробовать реализовать этот запрос средствами Аксапты. И как-нибудь эдак..."прикольненько", чтобы не только результат, но и удовольствие от решения получить .

После перебора возможных способов я остановился на варианте с "превращением" постоянной таблицы во временную (и не один раз!) при помощи setTmp (справка: kerndoc://Classes/xRecord/setTmp) и с использованием свободных в данный момент полей практически без усилий созданных временных таблиц для хранения промежуточных вычислений. Умудрился даже похранить целое (count) в текстовом поле, потому что в таблице InventDim не оказалось подходящих целочисленных полей, кроме как несвободного святого RecId.

В общем, на мой взгляд, получилось действительно "прикольненько". Спешу поделиться джобом-примером, выводящим в окно infolog список проблемных серийных номеров:

static void KKu_FindDupleInventSerialId(Args _args)
{
    InventDim   inventDim;

    // промежуточные вычисления будут выполняться на временных клонах таблицы inventDim
    InventDim   inventDimTmp01;  // уникальные InventSerialId, InventBatchId
    InventDim   inventDimTmp02;  // уникальные InventSerialId

    InventTrans inventTrans;
    int         rowCounter;
;

//  --- Шаг 1. Уникальные InventSerialId, InventBatchId
    inventDimTmp01.setTmp();

    rowCounter = 0;
    ttsbegin;
    while
        select InventSerialId, InventBatchId from inventDim
        group by InventSerialId, InventBatchId
        exists join inventTrans
        where inventDim.InventDimId == inventTrans.InventDimId
    {
        if (inventDim.inventSerialId)
        {
            rowCounter++;

            inventDimTmp01.inventDimId      = strfmt('%1', rowCounter); // фиктивное заполнение обязательного поля
            inventDimTmp01.inventSerialId   = inventDim.inventSerialId;
            inventDimTmp01.inventBatchId    = inventDim.inventBatchId;
            inventDimTmp01.doInsert(); // для обхода метода insert основной таблицы, который может быть перекрыт
        }
    }
    ttscommit;

//  --- Шаг 2. Искомые уникальные InventSerialId c Count(*) > 1
    inventDimTmp02.setTmp();

    rowCounter = 0;
    ttsbegin;
    while
        select InventSerialId, count(RecId) from inventDimTmp01
        group by InventSerialId
    {
        if( inventDimTmp01.RecId != 1 )
        {
            rowCounter++;

            inventDimTmp02.inventDimId      = strfmt('%1', rowCounter); // фиктивное заполнение обязательного поля
            inventDimTmp02.inventSerialId   = inventDimTmp01.inventSerialId;
            inventDimTmp02.inventBatchId    = strfmt('%1', inventDimTmp01.RecId); // используем свободное строковое поле для хранения Count
            inventDimTmp02.doInsert();
        }
    }
    ttscommit;

//  --- Шаг 3. Искомые проблемные InventSerialId, повторяющиеся c разными номерами партий
    info('Серийный номер --- Кол-во повторений -- Номер партии');
    info('====================================================');

    while
        select inventDimTmp02 order by inventSerialId
        join inventDimTmp01 order by inventBatchId
        where inventDimTmp02.inventSerialId == inventDimTmp01.inventSerialId
    {
            info( strfmt('%1 --- %2 --- %3', 
                inventDimTmp01.inventSerialId,
                inventDimTmp02.inventBatchId, // в этом поле хранится Сount
                inventDimTmp01.inventBatchId ) );
    }
}

Что нравится мне самому:
* Возможность НЕ создавать в АОТ новые временные таблицы.
* Возможность многократного временного клонирования исходной основной таблицы и последующая связь клонов в операторах select (while select) как между собой, так и с другими таблицами.

ПРИМЕЧАНИЕ: если в вашей системе нет проблемы задвоения серийников или не ведется учёт по партиям, а работу джоба проверить хочется, то просто замените в нем условие if( inventDimTmp01.RecId != 1 ) на условие if( rowCounter < 100 )