Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Импорт в PowerQuery трех файлов .dbf, LeftOuterJoin двух таблиц и агрегация внутри функции OleDb.DataSource
 
#1
25.02.2020 03:05:50
Доброго времени суток!

Во вложенном архиве файл LeftJoinSQL.pbix.
Запрос "PQJoin" возвращает таблицу, которую я хочу получить кодом на SQL внутри OleDb.DataSource.
Запросом "SQLJoin (1 таблица)" я осилил соединение одной таблицы;
А в запросе "SQLJoin (2 таблицы)" висит ошибка, которую я никак не могу понять. Попытки воспроизвести на SQL шаг с агрегированием, соответственно, не было :(

Предыстория:
Запрос "PQJoin" до завершения шага с первым левым соединением на реальных данных (к 1,08кк строк тяну 42к) выполняется в два раза (!) быстрее, чем "SQLJoin (1 таблица)". Но на шаге #"Вычисленное значение Receipt" в рабочем файле запрос "PQJoin" умирает: за 4 часа не справляется (в таблицах в столбце 'Receipt' от 1 до 10 строк), дальше мне терпения не хватило ждать.

Прошу подсказать:
1) Что я сделал не так в коде SQL в "SQLJoin (2 таблицы)" и возможно ли это вообще?
2) Как будет выглядеть готовый код SQL, если после второго объединения еще добавить агрегирование (вернуть таблицу из шага #"Сгруппированные строки" в запросе "PQJoin"?
3) Есть ли менее ресурсоемкий, чем Table.AggregateTableColumn, способ в PQ развернуть с агрегированием присоединенные таблицы? При этом, применяемые функции PQ должны быть доступны в версии надстройки для Excel2013.

Спасибо всем, кто отзовется.
Изменено: genosser - 27.02.2020 13:37:39 (на все вопросы ответ получил, пример обновил)
 
 
 
#2
25.02.2020 07:11:03
Есть. Table.Join + Table.Group. Ваш вариант это самый тормозной из всех что можно было придумать. Но, если к правой таблице применить функцию Table.AddKey, то тормозов станет значительно меньше. Погуглите эту функцию есть пара статей с примерами то ли у Криса Вебба, то ли у Кена Пульса, сейчас точно не помню уже.
Вот горшок пустой, он предмет простой...
 
 
 
#3
27.02.2020 01:57:22
PooHkrd, спасибо Вам за подсказки.
Нашел статью Вебба https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/ - вроде понятно, завтра буду пробовать в рабочем файле.
Цитата
PooHkrd написал:
Table.Join + Table.Group
Речь про два разных шага? Или про Table.Group() как аргумент Table.Join?
Попробовал оба варианта: во втором ругается на повторяющиеся имена. Можете еще раз посмотреть пример?
 
 
 
#4
27.02.2020 09:33:36
Цитата
genosser написал:
Речь про два разных шага?
Без разницы. Можно сделать как здесь предложил один хороший человек. А можно взять по той же ссылке постом ниже мой файл-пример и посмотреть как реализовано в нем.
Вот горшок пустой, он предмет простой...
 
 
 
#5
27.02.2020 09:51:02
PooHkrd, благодарю
 
 
 
#6
27.02.2020 09:54:57
genosser, только обратите внимание, что в тех примерах по ссылке опущен 5-й параметр, т.е. по умолчанию используется ИннерДжойн. Для вашей задачи, нужно будет прописать JoinKind.LeftOuter
Вот горшок пустой, он предмет простой...
 
 
 
#7
27.02.2020 14:08:19
PooHkrd, обратил внимание, спасибо. В примерах не совсем то, что я хотел, но то, что хотел, смог получить вот такой конструкцией:
Код
TGroup = 
    Table.Group(
    Table.Join(
        #"Развернутый элемент PC",
        {"SKU", "PARID", "PARSTORE"},
        Table.RenameColumns(
            Receipt,
            {{"SKU", "SKU1"},{"ID", "ID1"},{"STORE","STORE1"},{"QUA","PARQUA"}}
            ),
        {"SKU1", "ID1", "STORE1"},
        JoinKind.LeftOuter
        ),
    {"SKU", "ID", "STORE","QUA"},
    {{"PARQUA", each List.Sum([PARQUA]), type number}, {"COS1", each List.Sum([COS1]), type number}},
    GroupKind.Local)

если честно, не ожидал, что получится, а теперь голова кипит.

Объясните, если не трудно, еще такие моменты:
Конструкция выше будет ускорена, если к правой таблице ("Receipt") применить Table.AddKey(), или это применимо только в связке с Table.AggregateTableColumn()?
Любую табличную функцию можно применить к любому объекту с типом table: неважно, является он шагом запроса либо вложенной таблицей в столбце?
Код, например мой, начинает отрабатываться с наиболее глубоко вложенной табличной функции, в том числе, если аргументом другой табличной функции на нижнем уровне является #table ()? Т.е. сначала вычисляются аргументы #table(), потом на уровень вложения выше и т.д.?
Изменено: genosser - 27.02.2020 14:11:46
 
 
 
#8
27.02.2020 14:27:24
Цитата
genosser написал:
Конструкция выше будет ускорена, если к правой таблице ("Receipt") применить Table.AddKey()
Фиг знает - надо тестировать.
Цитата
genosser написал:
Любую табличную функцию можно применить к любому объекту с типом table
Да.
Код выполняется условно говоря "в обратном порядке", это так называемые "ленивые вычисления" (lazy calculation). Т.е. сначала интерпретатор начиная с последнего шага выстраивает цепочку расчетов, которые нужно сделать, убирает все (по его мнению) "не нужные", и после этого уже начинает расчеты с начала выстроенной цепочки. С этим связано немало всяких интересностей, которые познаешь и учишься объезжать в процессе работы с этим чудным инструментом.
Изменено: PooHkrd - 27.02.2020 14:28:36
Вот горшок пустой, он предмет простой...
 
 
 
#9
27.02.2020 14:46:42
PooHkrd, благодарю за пояснения, нужно дальше грызть этот гранит
 
 
 
Страницы: 1
Наверх