Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Я новичок: сделать самый быстрый ВПР на PowerQuery, Сделал простой пример, прикрепил. При количестве строк Имен 6250 и строк Словаря 22000 запрос длится 40 минут!
 
#1
26.05.2024 13:28:48
Здравствуйте. Специально для специалистов форума я сделал пример, который вам понравится! Он очень простой! Возможно, вы даже будете смеяться!

Просто хочу сопоставить списку Имен - значения из Словаря слиянием. Или это делается массовой заменой по списку?

У меня запрос длится 40 минут, а мне сегодня хотелось бы выполнить 8 таких запросов до того как стемнеет и закончится рабочий день!  
 
 
 
#2
26.05.2024 13:48:53
Кажется, я начинаю догадываться! Нужно использовать List.Accumulate  !!!  Я успею сегодня разобраться? Что нужно изменить в моём примере?  
 
 
 
#3
26.05.2024 14:14:36
Ilya iatndiag, на таких данных все мгновенно должно выполниться при обычном Join'е.
А для чего шаг "Несвернутые столбцы" в запросе "Слияние Имен со Словарём (РЕЗУЛЬТАТ)", если результат тот же, что был бы и без него? это тестовые данные и этот шаг будет нужен для настоящих данных? может этот шаг тормозит все?
Изменено: voler83 - 26.05.2024 14:15:33
 
 
 
#4
26.05.2024 14:17:44
Я и сам не знаю! Когда этого шага нет - у меня Table. Когда я добавил этот шаг - я увидел то что мне нужно [Столбец2].

У меня там, наверное не обычный, а Nested Join.  И без указания Left / Right / Outer / Inner

А как нужно было?
Изменено: Ilya iatndiag - 26.05.2024 14:23:24
 
 
 
#5
26.05.2024 14:19:08
Прямо сейчас обрабатываю настоящие данные: на моем компьютере 10 минут. На слабом компьютере удаленно - 40 минут.

List.Accumulate я не смог сделать, лучше без него. Вот что я попытался сделать с лист.накопить:
Изменено: Ilya iatndiag - 26.05.2024 14:21:17
 
 
 
#6
26.05.2024 14:21:20
Ilya iatndiag, в заголовке столбца, в котором вложенные Table есть стрелки - нажмите на них и разверните. Т.е. вам надо оставить только шаг Источник и развернуть стрелками.
 
 
 
#7
26.05.2024 14:23:59
Может нужно было Left Join? Через запятую в конце вписать и всё заработает? Сейчас попробую как вы сказали!
Изменено: Ilya iatndiag - 26.05.2024 14:24:22
 
 
 
#8
26.05.2024 14:26:42
Цитата
Ilya iatndiag написал:
Может нужно было Left Join? Через запятую в конце вписать и всё заработает?

может быть...
= Table.NestedJoin(#"Измененный тип", {"Столбец1"}, #"Т из Словаря", {"Столбец1"}, "Т из Словаря", JoinKind.LeftOuter)
 
 
 
#9
26.05.2024 14:30:43
Сейчас попробую!!! Эксель вылетел (с моей формулой и со всеми данными) !!! Сейчас попробую с вашей!!!


Пробую, пробую, пробую ... пока не тот столбец загрузился ... ещё минуточку


С малыми данными работает!  


Сейчас проверю с БОЛЬШИМи данными !!! Ждите! Это ужасно интересно !!!
Изменено: Ilya iatndiag - 26.05.2024 14:42:17
 
 
 
#10
26.05.2024 15:02:17
Проверил с большими данными, у меня там 8 одинаковых таблиц из Имён, на примере таблицы номер шесть:


= Table.NestedJoin(#"Т из Имен", {"Столбец1"}, #"Т из Словаря", {"Столбец1"}, "Т из Словаря", JoinKind.LeftOuter)

= Table.NestedJoin(#"Т(8x8)_6",{"Столбец1"},#"Таблица из Словаря", {"Столбец1"}, "Таблица из Словаря", JoinKind.LeftOuter)

Получилось неправильное соответствие! И я понял почему! В результате Словарь отсортировался в Столбце1.

А мне нужно, чтобы каждая строка стояла НА СВОЕМ месте! Недопустима перестановка строк словаря или перестановка строк имен! Каждая строка жестко закреплена на своейй позиции! Первая во всех таблицах должна оставаться - первой. Вторая - второй.

Наверное в моем примере словарь отсортирован!!! Надо перемешать!  1 9 3 2 5
Изменено: Ilya iatndiag - 26.05.2024 15:05:12
 
 
 
#11
26.05.2024 15:04:10
Ilya iatndiag, составьте руками пример и прикрепите сюда: левая таблица/правая таблица (словарь) / таблица-результат с комментами по позициям как надо чтобы получилось - ничего не понятно.
сначала попробуйте до Join добавить столбец индекса в таблицу-словарь (просто, чтобы был)
Изменено: voler83 - 26.05.2024 15:05:43
 
 
 
#12
26.05.2024 15:05:27
Хорошо.

В вашем примере всё работает.

Наверное, я напутал в таблицах. Спасибо.

Индекс! Есть такая тема! Вот это очень может быть!

В общем, заплатили мне за мою работу: "вот тебе мальчик 500р. на мороженое". Даже не интересно дальше разбираться. Я чуточку попозже еще раз проверю как всё это работает с большими данными, тогда здесь отпишусь. Спасибо еще раз!
Изменено: Ilya iatndiag - 26.05.2024 19:22:41
 
 
 
#13
27.05.2024 08:51:53
Отчитываюсь о результате: одинаково работают мой и ваш варианты. Скорость работы НЕ изменилась. На этом тему ЗАКРЫВАЕМ.

Мой вариант с отменой разворачивания столбцов:
Код
let
    Источник = Table.NestedJoin(#"ТаблицаИсточн",{"Столбец1"},#"Таблица из Словаря",{"Столбец1"},"NewColumn"),
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(Источник, {"Столбец1"}, "Атрибут", "Значение"),
    #"Развернутый элемент Значение" = Table.ExpandTableColumn(#"Несвернутые столбцы", "Значение", {"Столбец2"}, {"Значение.Столбец2"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Развернутый элемент Значение",{"Атрибут", "Столбец1"})
in
    #"Удаленные столбцы"
Ваш вариант с индексом (если НЕ использовть индекс, то результат получится неправильный, похожий на сортировку):
Код
let
    Источник = Table.NestedJoin(#"ТаблицаИсточн",{"Столбец1"},#"Таблица из Словаря", {"Столбец1"}, "Таблица из Словаря", JoinKind.LeftOuter),
    #"Добавлен индекс"   = Table.AddIndexColumn(Источник, "Индекс", 0, 1),
    #"Развернутый элемент Таблица из Словаря" = Table.ExpandTableColumn(#"Добавлен индекс", "Таблица из Словаря", {"Столбец2"}, {"Таблица из Словаря.Столбец2"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Развернутый элемент Таблица из Словаря",{"Столбец1", "Индекс"})
in
    #"Удаленные столбцы"

Оба варианта работают одинаково долго при больших объемах данных. На этом тему ЗАКРЫВАЕМ. Ещё раз спасибо.
 
 
 
#14
27.05.2024 09:14:31
Ilya iatndiag, на таких данных все должно работать мгновенно. значит у вас проблемы  либо в источнике данных (тянет по сети? ...??), либо есть еще обработки, либо др.
 
 
 
#15
27.05.2024 15:57:14
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    zam = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    nms = Table.ColumnNames(from)&List.Skip(Table.ColumnNames(zam)),
    dict = Record.FromList(Table.ToList(zam,(x)=>List.Skip(x)),Table.ToColumns(zam){0}),
    tbl = Table.FromList(Table.ToRows(from),(x)=>x&Record.FieldOrDefault(dict,x{0},{}),nms),
    to = Table.SelectColumns(tbl,List.Skip(Table.ColumnNames(tbl)))
in
    to
Изменено: sotnikov - 27.05.2024 16:08:22
 
 
 
#16
28.05.2024 08:59:07
Скажу вам сразу, ничего не изменилось! Читайте всё!

Это потрясающе! Не знаю как вы до этого догадались или с какого американского форума скопировали, но я бы не то что не додумался, я даже это на Stackowerflow не нашёл! Я теперь неделю буду разбираться как это работает! Я, кстати, продолжу работать с заказчиком, может ещё на шоколадное мороженое денег заработаю!

Всего одна ошибка: Expression.Error: Поле "Имя... какое-нибудь N-ное" уже существует в записи. Т.е. он реагирует на ПОВТОРЯЮЩИЕСЯ имена.

По объектам Table, Record, List и методам FromList, ToList, ToColumns, ToRows, Skip можно помотреть справку - это более менее понятно.

Что означает &    (предположу, что это конкатенация)  ?
Что означает {}    (продположу, что индекс элемента списка, начинающийся с нуля)  ?
Почему сразу написано Table   (что это, это объект/экземпляр класса)  ?
Почему сразу написано &List   (что это, если это не метод и не свойство)  ?
Почему сразу написано Record (с какого момента у нас появился этот объект)  ?

предположу, что нужно смотреть в предпросмотр, там эти объекты появляются с каждым новым шагом

Почему у метода FieldOrDefault третий аргумент {}  ?

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

Не знаю, можно я сюда прикреплю файл с рабочими данными. Все равно же только СВОИ видят. К сожалению файл не прикрепился - он слишком велик.

Запустил Гугл-секундомер.

Извините...10000 строк обработались за 6 минут 40 секунд (признаюсь, терпения не хватило дождаться окончания)
Всего строк 22000. Получаются примерно те же 10 минут! Процессор intel i5 64bit, 2 ядра, индекс производительности примерно 6500.

Извините, но стоило ли так усложнять код! А если попробовать сделать SQL в Access и подключаться как к базе, то что-то изменится? Я, наверное, попробую! Я уже думаю про макрос с циклом и запрос к базе данных SELECT .... FROM .... WHERE ....

Если количество строк 300 x 50, то работает мгновенно!
Но если их 22000 на 10000, то работает 10 минут!

Дополнительных обработок нет, файл локальный.
Я прихожу к выводу, что то что я хочу - ФИЗИЧЕСКИ НЕВОЗМОЖНО. Может факториал нужно посчитать?  1*2*3*4*5*...20000 и получим количество возможных комбинаций строк и их все нужно перебрать.
Изменено: Ilya iatndiag - 28.05.2024 09:27:49
 
 
 
#17
28.05.2024 11:02:10
Ilya iatndiag, обезличьте данные, удалите часть данных, слелайте архив или выложите обезличенный файл на файлообменник.
Пришелец-прораб.
 
 
 
#18
28.05.2024 11:38:27
Чтоб меня заблокировали за сторонние ссылки. На вашей совести.
Изменено: Ilya iatndiag - 28.05.2024 11:49:42
 
 
 
#19
28.05.2024 12:04:49
Ilya iatndiag, данные грязноваты (пробелы в конце, например). И еще кое-что: Titan_180x1500_microprism.png в именах отсутствует в таком виде в словаре. В словаре есть 1Titan_180x1500_microprism.png. А так, без усложнений всяких тот самый словарь на записях. Немного "полечил" - убрал пробелы в конце и начале, но более ничего не делал. Можно еще регистр везде поднять, но это уж вам решать.
код
Пришелец-прораб.
 
 
 
#20
28.05.2024 12:11:58
Обработка за 1 секунду ???? Всего одна секунда ???

В чём прикол ??? Кажется всё работает ... а в чём прикол ???

Да, вы колдун. Это какое-то черодейство.

Я бы денег отправил уже за это, только мне за это заплатили всего 500 рублей. На двоих не делится. Человек в воскресение сидел на работе до 23:00 перед следующим понедельником только чтобы дождаться, когда же эта обработка завершится.

А почему так сложно и ответ не лежит на поверхности? Вот это, наверное и есть хеш-таблица, которую я так хотел получить.
Изменено: Ilya iatndiag - 28.05.2024 12:18:51
 
 
 
#21
28.05.2024 12:31:05
Цитата
Ilya iatndiag написал:
а в чём прикол ???
приемчик известный. Ознакомьтесь. Там и видосик прилагается. "Прикол" в том, что вы "долбите" ваш словарь много-много раз (можно было бы его Table.Buffer перед Table.NestedJoin - веселее должно дело пойти). А тут мы его упаковали в record один только раз (record остается "стабильной" в запросе) и уже ее используем.
Еще один вариант ускорения: использовать не Table.NestedJoin, а Table.Join с параметром JoinAlgorithm.SortMerge, но обе таблицы придется отсортировать по ключу:

Да и Table.Buffer словаря, скорее всего, тоже надо будет сделать. Но поиск ускорится.
Изменено: AlienSx - 28.05.2024 12:48:53
Пришелец-прораб.
 
 
 
#22
30.05.2024 09:56:39
Цитата
Ilya iatndiag написал:
Обработка за 1 секунду ???? Всего одна секунда ???
Обычный левый join и так 1 секунду делается с учетом разворота, и на таких данных он изначально 1 секунду и должен был делаться,  и на 100 тыс строк х 100 тыс строк также будет делаться быстро, проблемы изначально не должно было быть, у вас что-то с компьютером явно, например, вся оперативка и процессор заняты.
Единственно, ни одного совпадения по имени со словарем нет.

upd: я скопировал имена в словарь несколько раз чтобы для каждого имени нашлось совпадение в словаре - даже с учетом дублей все обновляется мгновенно, в модель данных загружается 5 секунд 260 тыс строк (размножились после joina с учетом дублей)

п.с.: либо это один сплошной троллинг
Изменено: voler83 - 30.05.2024 10:33:15
 
 
 
#23
30.05.2024 12:35:29
А у вас, наверное Excel 2019 или новее! У меня Excel 2016 года. Скорее всего, есть отличия. Хотя, постойте ... на втором компьютере Эксель 2019 года и всё то же самое. Так что никакой это не троллинг, это Правда Жизни! Офис 2019 я могу установить, но у меня телеметрия не отключается (телеметрия - это как распределённые вычисления по производству криптовалюты от Майкрософт среди всех компьютеров Земли, ничего хорошего кроме перегрева).

Вы сказали, что загрузили в модель данных. А я не загружал! Я даже не знаю для чего эта функции. Без добавления в модель данных данные не обрабатываются движком как база данных?
Изменено: Ilya iatndiag - 30.05.2024 13:24:35
 
 
 
Страницы: 1
Наверх