order by и group by - DAX: Программирование

PavelX · 14.11.2007, 09:21

Всем привет!

Подскажите пожалуйста существует ли в Аксапте какой либо красивый способ получить из таблицы выборку отсортированную по агрегатному полю? То есть использовать в одном запросе и group by и order by. Кроме варианта с использованием промежуточной таблицы.

Задача в следующем. Есть временная таблица MyTbl. Нужно из нее получить первые N записей отсортированных по убыванию агрегатного поля (аналог SELECT TOP N) Сейчас это делается в два прохода: сперва в промежуточную таблицу записывается результат запроса

X++:

select sum(SumField) from MyTbl
        group by Field1, Field2

затем организуется выборка из промежуточной таблицы по убыванию поля SumField. Можно это как то оптимизировать?

Таблица временная.

**Gustav** · 14.11.2007, 10:19

Можно попробовать класс-коллекцию Set, в которую вставлять суммы со знаком минус. Получится требуемый порядок сортировки. А при последующем извлечении минус, естественно, "убирать". Если у вас возможны повторяющиеся значения сумм, то можно попробовать вставлять в Set контейнеры типа [-очередная сумма; очередное значение последовательного уникального счетчика].

X++:

static void TestQuasiSelectTop(Args _args)
{
    SetEnumerator en;
    int a, b;
    set s = new set (types::Container);

    s.add([-100,1]);
    s.add([-700,2]);
    s.add([-200,3]);
    s.add([-500,4]);
    s.add([-200,5]);

    en = s.getEnumerator();
    while (en.moveNext())
    {
        [a,b] = en.current();
        info(strfmt('%1 %2', -a,b));
    }
}

В инфологе видим (нас, естественно, интересует только первая колонка):

700 2
500 4
200 3
200 5
100 1

konopello · 14.11.2007, 10:19

я думая что без временной таблици не обойтись, но можно попробывать следующию конструкцию

X++:

insert_recordset table1 (field1, field2)
        select sum(field1), sum(field2) from table2;

    while select table1
        order by field2
    {
        .........
    }

может быстрей заработает

kashperuk · 14.11.2007, 11:45

Цитата:

Сообщение от konopello

может быстрей заработает

С учетом того, что таблица временная, быстрей не заработает.
insert_recrodset будет обычным по-строчным insert

konopello · 14.11.2007, 11:47

Цитата:

С учетом того, что таблица временная, быстрей не заработает.
insert_recrodset будет обычным по-строчным insert

ну значит не судьба помочь человеку

konopello · 14.11.2007, 11:51

Цитата:

Можно попробовать класс-коллекцию Set, в которую вставлять суммы со знаком минус. Получится требуемый порядок сортировки. А при последующем извлечении минус, естественно, "убирать". Если у вас возможны повторяющиеся значения сумм, то можно попробовать вставлять в Set контейнеры типа [-очередная сумма; очередное значение последовательного уникального счетчика].

кстати, похожим образом организована сортировка в локализованных финансовых отчетах, когда первый раз их смотрел глаза разбегались.

**Gustav** · 14.11.2007, 12:18

Цитата:

Сообщение от konopello

когда первый раз их смотрел глаза разбегались.

И? Это одобрение или осуждение?

konopello · 14.11.2007, 12:32

Цитата:

И? Это одобрение или осуждение?

констатировал факт, это одобрение т.к. другим способом видимо эту проблему решить нельзя.

**Gustav** · 14.11.2007, 13:17

Ну, можно еще попробовать любимое ADO (Поговорим об ADO), которое не только в Excel эффективно выводит, но и умеет сортировать (и фильтровать) набор данных в памяти.

Нижеследующий джоб выводит в инфолог отсортированные ПО УБЫВАНИЮ суммарные дебетовые обороты главной книги, сгруппированные по счетам бухучета за один день хозяйственной деятельности (1 сентября 2007):

X++:

static void KKu_TestAdoDescSortingInMemory(Args _args)
{
    LedgerTrans ledgerTrans;

    COM rstAxa;    // ADO: Recordset
    COM flds, fld; // ADO: Fields, Field

    str currAccountNum;
    real currAmountMST;

    int adoTypeToExcel(str _type)
    {
        switch (_type)
        {
            case 'num' : return   5; // adDouble
            case 'str' : return   8; // adBSTR
            case 'date': return 133; // adDBDate
        }
        return 8;
    }

    anytype adoValueFromExcel(COMVariant _val, int _type)
    {
        switch (_type)
        {
            case   5:      return _val.double();    // adDouble
            case   6:      return _val.currency();  // adCurrency
            case   7:      return _val.date();      // adDate
            case  11:      return _val.boolean();   // adBoolean
            case 202, 203: return _val.bStr();      // adVarWChar, adLongVarWChar("memo")
        }
        return '';
    }
;
    rstAxa = new COM('ADODB.Recordset');

    flds = rstAxa.Fields(); // сооружаем "таблицу" из двух полей в памяти
    flds.Append('AccountNum' , adoTypeToExcel('str' ));
    flds.Append('AmountMST'  , adoTypeToExcel('num' ));

    rstAxa.Open();

    while select AccountNum, sum(AmountMST) 
        from ledgerTrans
        group by AccountNum
        where ledgerTrans.TransDate == 01\09\2007 
           && ledgerTrans.Crediting == NoYes::No
    {
        rstAxa.AddNew();
            fld = flds.Item('AccountNum' ); fld.Value(ledgerTrans.AccountNum );
            fld = flds.Item('AmountMST'  ); fld.Value(ledgerTrans.AmountMST  );
        rstAxa.Update();
    }

    rstAxa.Sort('AmountMST DESC'); // задаем убывающую сортировку по полю AmountMST

    rstAxa.MoveFirst();
    while (!rstAxa.EOF())
    {
        fld = flds.Item('AccountNum' ); 
        currAccountNum = adoValueFromExcel(fld.Value(), fld.Type());

        fld = flds.Item('AmountMST'  );
        currAmountMST  = adoValueFromExcel(fld.Value(), fld.Type());

        info(strfmt('%1 --- %2', currAccountNum, currAmountMST));
        rstAxa.MoveNext();
    }

    rstAxa.Close();
}

Вложенные функции и обозначения переменных я на скорую руку заимстовал из примеров Поговорим об ADO (поэтому пусть не пугает подстрока "Excel" в их названии). Там же можно почитать подробные комментарии.

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

PavelX · 14.11.2007, 14:36

Всем спасибо, особенно Gustav!
Картина в общем ясна

fomenka · 14.11.2007, 22:27

Это все здорово как упражнения для изучения системы.

Но в чем выигрыш?

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

Или включить в запрос?
К тому же ADO - это внешняя компонента по отношению к Axapta, это тоже минус. Работа с COM объектами - это накладные расходы, еще проверить по производительности не мешало бы, что выгоднее.
С учетом того, что временные таблицы - это родной механизм Axapta и встречаются в ней сплошь и рядом, что может испортить еще одна временная таблица? Кстати, konopello тоже неполохой вариант предложил.

Добавлено. Мда...тестирование производительности варианта с временной таблицей и ADO RecordSet не выявило существенной разницы. Надо машинку послабее взять, видимо.
В коде Gustav пришлось изменить строку

X++:

fld.Value(ledgerTrans.AccountNum )

на

X++:

fld.Value( COMVariant::createFromStr(ledgerTrans.AccountNum) )

belugin · 14.11.2007, 23:04

еще можно хранить отсортированный массив из N элементов - тогда не придется держать в памяти таблицу из всех групп

glibs · 14.11.2007, 23:32

Если у вас 3.0 или выше и в запросе нет объеднений, отличных от inner join, то можно попробовать view.

**Gustav** · 14.11.2007, 23:32

2 fomenka: Ваша пламенная речь ко мне обращена? Я должен как-то реагировать?

Цитата:

Сообщение от fomenka

В коде Gustav пришлось изменить строку

X++:

fld.Value(ledgerTrans.AccountNum )

на

X++:

fld.Value( COMVariant::createFromStr(ledgerTrans.AccountNum) )

Спасибо. Это на 4-ке? (у меня мой вариант работает на 3-ке SP3, SP4)

fomenka · 15.11.2007, 03:09

Gustav Ничего личного, код понравился, вот и высказался.

А запускал на такой же 3-ке, удивился сам. Может, версия mdac - причина?

**Gustav** · 15.11.2007, 09:18

Цитата:

Сообщение от fomenka

Gustav Ничего личного, код понравился, вот и высказался.

Всё хорошо

Цитата:

Сообщение от fomenka

А запускал на такой же 3-ке, удивился сам. Может, версия mdac - причина?

Признаться, про мдак тоже подумалось. Сегодня проверил на разных машинах - с версиями 2.53 и 2.80 (номер версии смотрел в реестре, Как определить версию MDAC) - всё работает. Ваш вариант с COMVariant тоже работает - как более общий случай (если сочинять класс, то надо так и сделать "по-взрослому"). Но почему то работает, то не работает короткий вариант fld.Value - пока загадка...

**Gustav** · 21.11.2007, 21:08

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

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

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

Для обнаружения дубликатов был написан следующий запрос на классическом 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 список проблемных серийных номеров:

X++:

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 )

Владимир Максимов · 22.11.2007, 12:01

Не знаю, работает ли это в Oracle, но в MS SQL есть такая фича

PHP код:


			
SELECT InventSerialId, COUNT(DISTINCT InventBatchId) AS Cnt    

FROM InventDim 

INNER JOIN InventTrans ON InventDim.InventDimId = InventTrans.InventDimId

WHERE InventSerialId <> CHR(2)  

GROUP BY InventSerialId

HAVING COUNT(DISTINCT InventBatchId) <> 1

COUNT(DISTINCT InventBatchId) - это количество уникальных значений указанного поля, а не вообще всех отобранных записей.

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

Кроме того, судя по коду, у Вас только одна компания, раз нет фильтра по DataAreaId.

Ну, а собственно JOB мне не нравится избыточным количеством проходов. Поскольку уже первый запрос упорядочивает данные по полям InventSerialId, InventBatchId, то какие проблемы тут же и подсчитать количество повторов? Ведь записи выстроены в нужной последовательности.

Собственно, все можно решить за один проход. И без каких-либо временных таблиц.

X++:

static void KKu_FindDupleInventSerialId(Args _args)
{
    InventDim       inventDim;
    InventTrans     inventTrans;
    int                  nextI;

    InventSerialId  inventSerialIdPrev;
    Container       conValue;

    ;

    info('Серийный номер --- Кол-во повторений -- Номер партии');
    info('====================================================');

    while
        select InventSerialId, InventBatchId from inventDim
        group by InventSerialId, InventBatchId
        exists join inventTrans
        where inventDim.InventDimId == inventTrans.InventDimId
    {
        if (inventDim.inventSerialId != inventSerialIdPrev)
        {
            if (inventSerialIdPrev && conLen(conValue) > 1)
            {
                for (nextI = 1; nextI <= conLen(conValue); nextI++ )
                {
                    info( strfmt('%1 --- %2 --- %3',
                        inventSerialIdPrev,
                        conLen(conValue), 
                        conPeek(conValue,nextI) ) );
                }
            }
            // Сброс значений очередной группы
            inventSerialIdPrev = inventDim.inventSerialId;
            conValue = conNull();
        }
        else
        {
            conValue += [inventDim.inventBatchId];
        }
        
    }

    if (inventSerialIdPrev && conLen(conValue) > 1)
    {
        for (nextI = 1; nextI <= conLen(conValue); nextI++ )
        {
            info( strfmt('%1 --- %2 --- %3',
                inventSerialIdPrev,
                conLen(conValue), 
                conPeek(conValue,nextI) ) );
        }
    }

}

ice · 22.11.2007, 13:45

Цитата:

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

оффтоп:
по-моему совсем не очевидно, что комбинация "серийный номер + номер партии" должна быть уникальна..., тк номер партии не уникальный и серийный номер тоже.

**Gustav** · 22.11.2007, 15:44

2 Владимир Максимов

Владимир, конечно же есть такая фича COUNT(DISTINCT ) и в Оракле. Не могу сказать, что она в данном случае сильно спасает, так как номера партий все равно нужно откуда-то доставать. Обычно подобные запросы я пишу с использованием фразы WITH, и тогда повторяющийся фрагмент (который "ЕЩЕ РАЗ увы") выносится за "скобки" основного запроса и прописывается один раз перед ним:

Код:

WITH Tmp01 AS
    (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
    ) --// уникальные сочетания серийника и партии, встречающиеся в InventTrans
SELECT 
    Tmp01.InventSerialId AS "Серийный номер", 
    Tmp02.Cnt AS "Кол-во повторений",   --// ...этого серийника для разных номеров партий 
    Tmp01.InventBatchId AS "Номер партии"
FROM 
    Tmp01
INNER JOIN
    (SELECT InventSerialId, COUNT(*) AS Cnt FROM Tmp01
    GROUP BY InventSerialId
    HAVING COUNT(*) <> 1
    ) Tmp02 --// уникальные серийники из уникальных сочетаний серийник+партия
ON Tmp01.InventSerialId = Tmp02.InventSerialId 
ORDER BY Tmp01.InventSerialId, Tmp01.InventBatchId

Здесь я эти "скобки" раскрыл, чтобы пример гарантированно мог сработать и в SQL Server (после замены CHR(2) на пустую строку).

Но вообще речь-то не обо всем об этом.

Виноват, если показалось по-другому, но я не ставил целью обсуждать способ нахождения задвоений (и максимально его конкретно оптимизировать - и в SQL, и в Аксе). Это просто пример о чем-нибудь.

Цель другая - показать, как довольно сложный запрос SQL можно достаточно прозрачно реализовать в Аксапте серией последовательных простых select'ов на основе временных таблиц, которые не нужно готовить заранее! А также показать, как в рамках одного метода (джоба) можно фактически реализовать вычисления типа SELEСT * FROM (SELEСT * FROM (SELEСT * FROM (SELEСT * FROM (SELEСT * FROM (SELEСT * FROM... )))))

Нельзя в лоб сделать в аксаптовском select'е одновременно группировку и сортировку (причем, убывающую)? Пожалуйста, разделим процесс на 2 запроса. А если что-то посложнее, то и на 3, и на 4, и на более.

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
axStart: Cannot mix old-style sorting with new-style Group By or Order By on a query.	Blog bot	DAX Blogs	0	06.11.2008 12:05
dynamicsmatters: Order stock allocation mechanism	Blog bot	DAX Blogs	0	24.11.2006 17:50
Говорят вышел SP2 для Axapta 3. Кто нибуть что знает на эту тему?	soin	DAX: Прочие вопросы	10	13.10.2003 10:43
group , но не by	slava	DAX: Программирование	9	21.05.2003 19:05