Страницы: 1
RSS
PowerQuery - подстановка значения по поиску в диапазоне дат - как оптимизировать
 
Добрый день!

Есть задача в таблицу с продажами подставить себестоимость.

Есть таблица с себестоимостью вида

ЦИФРОВОЙ АРТИКУЛ - СЕБЕСТОИМОСТЬ - ДАТА ДЕЙСТВИЯ С - ДАТА ДЕЙСТВИЯ ПО
(Внутренний артикул - Cебестоимость - ValidFrom - ValidTo)

В этой таблице около 5000 строк, она находится в отдельном файле.

Есть таблица с продажами в которой соответственно один из столбцов - ЦИФРОВОЙ АРТИКУЛ, а другой - ДАТА ЗАКАЗА.
(да, я знаю, что нужна дата закупки для подстановки, но таких данных нет). В этой таблице около 100 000 строк.

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

Я нашел решение на форумах, которое в целом довольно успешно работает.
Код
    #"Add prices" = Table.AddColumn(#"Removed Columns", "Price", 
                    (S)=> Table.SelectRows(Costs, (P) => P[#"Внутренний артикул"]=S[#"Внутренний артикул"] 
                    and P[ValidFrom]<=S[Дата заказа] and P[ValidTo]>S[Дата заказа])),
Таблица Costs - это и есть себестоимость из другого файла и перед этой строчкой я ее буферизирую
Код
Costs = Table.Buffer(#"SKU Costs"),
#SKU Costs - это отдельный запрос, который открывает файл, достает оттуда таблицу и удаляет пустые строки.

Однако, выполнение этого скрипта ну ООООЧЕНЬ медленное, а в статусе я вижу такое (см скриншот):

хххх MB from <внешний файл>

и "прокачать" он так может ни один гигабайт.

Это какая-то особенность PQ: почему он игнорирует буферизацию и все равно обращается к внешнему файлу? И откуда вообще такие дикие объемы данных?

И есть ли способ как-то оптимизировать скрипт?
Изменено: PrALX - 13.05.2024 10:26:38
 
PrALX, а пример данных-то где?
Можете просто поискать "интервальный поиск PQ" на форуме
 
PrALX, вы в продемонстрированном коде в польз. столбце не конечную цифру-значение получаете, а запись? а потом из записи вытаскиваете конечное значение в др. польз. столбце? может сразу вытаскивать конечное значение через обращение к полю записи? иначе у вас получается 100 тыс. вложенных в польз. столбец записей в промежуточном итоге, если я прав, и наверно это увеличивает расход памяти и быстродействие.
Изменено: voler83 - 13.05.2024 11:20:18
 
surkenny, Файлы анонимизировал. Оставил только часть таблицы с продажами, чтобы влезть в лимит.

[USER]voler83[/USER], Да, вытаскивается запись, потом раскрывается.

Попробовал сразу вытаскивать нужный столбец в нулевой строке через {0}[Себестоимость] в конце - вообще не ускоряет.

Кстати, есть ощущение, что проблема на самом деле кроется в запросе SKU Costs, который разбивает себестоимость по периодам. попробовал его просто выгрузить в таблицу на лист - выгружался несколько минут.  
Изменено: PrALX - 13.05.2024 12:41:38
 
PrALX, как вам советует surkenny, "интервальный поиск" будет оптимальным решением в этом случае. Только надо убедиться, что для каждого артикула в таблице продаж найдется себестоимость в таблице с себестоимостями и с датой <= дате продажи.
pq

изучайте темы форума
Пришелец-прораб.
 
AlienSx, Спасибо. Очень частно не хватает именно правильного названия того, что ты ищешь, чтобы найти решение )) Как я только не формулировал.

А проблема судя по всему реально в другом месте - в запросе SKU Costs.
Очень долго считаются даты окончания действия цены.
Буду переписывать сначала его.

или вопрос:

Если в списке себестоимости есть только дата начала действия, но нет даты конца (по умолчанию считаем что концом действия цены является новая дата начала) - возможно ли просто организовать подстановку?
Изменено: PrALX - 13.05.2024 12:56:25
 
Цитата
PrALX написал:
Буду переписывать сначала его.
хощяин-барин, конечно, но решение с интервальным поиском я вам в сообщение вложил. На плюсик нажмите - оно и откроется. Не подошло?
Пришелец-прораб.
 
AlienSx,

Разобрался. Собственно оно как раз и закрывает вообще в принципе потребность в каких либо расчетах в таблице себестоимости.
Очень элегантно. Спасибо!

Смутил только фильтр на последнем шаге. Такой синтаксис дает какие-то преимущества против стандартного SelectRows(down, each ([Продавец] <> null))?

Осталось продумать и прописать возможные ошибки, хотя вроде тут или значение или null будет если я правильно вижу.
 
Цитата
PrALX написал:
Такой синтаксис дает какие-то преимущества против стандартного SelectRows(down, each ([Продавец] <> null))?
позволяет не заблудиться в 2х и более соснах. В данном случае сосна одна, поэтому преимущества никакого нет.
Цитата
PrALX написал:
хотя вроде тут или значение или null будет если я правильно вижу.
ну если в столбце Продавец таблицы с продажами всегда что-то есть (<> null), то должно работать. Это может быть любой другой столбец, в котором что-то есть (ну кроме себестоимости, даты заказа и артикула).
P.S. ну или, чтобы не полагаться на отсутствие null в каком-либо столбце таблицы с продажами, то можно предварительно создать такой столбец с любым значением и потом фильтроваться по этому столбцу.
Изменено: AlienSx - 13.05.2024 13:50:04
Пришелец-прораб.
 
AlienSx, Да, поле продавец есть всегда где есть какие либо данные по заказам.

Я больше думаю вот об этом комментарии "Только надо убедиться, что для каждого артикула в таблице продаж найдется себестоимость в таблице с себестоимостями и с датой <= дате продажи"

Проблема может быть именно тут ибо тут чистый человеческий фактор.
Причем она 100% будет - может не быть артикула в таблице. Если артикул будет - то первая строчка всегда заполняется как 2010 год - то есть даже есть себестоимость будет null или 0 - это не страшно. Хотя конечно тоже возможна ситуация, в которой будет только строчка с датой позже даты продаж.

Можно ли сделать Fill.down, но ограничить его только рамками номера артикула? Если артикул меняется, а данных нет - то null.
Изменено: PrALX - 13.05.2024 14:09:49
 
Цитата
PrALX написал:
Причем она 100% будет - может не быть артикула в таблице. ... Хотя конечно тоже возможна ситуация, в которой будет только строчка с датой позже даты продаж.
тогда это все развалится. При отсутствии артикула и/или более ранней даты в себестоимостях данные других артикулов залезут на чужую территорию. Метод борьбы (я б так делал) - добавить в таблицу с себестоимостями строки со ВСЕМИ уникальными артикулами из таблицы продаж , датой, например, 01.01.2000 и "нет данных" в колонке с себестоимостью. Все это можно сделать в power query, конечно.
Пришелец-прораб.
 
AlienSx,

Я как раз думал, что Чисто теоретически я могу формировать список артикулов, которые есть в выгрузке. Смотреть те, по которым нет себестоимости. И добавлять в таблицу себестоимости со значением null и датой 2000 года.
Значит пойду по этому пути
 
Цитата
PrALX написал:
Можно ли сделать Fill.down, но ограничить его только рамками номера артикула?
сгруппировать по артикулу и делать это в группе, но это лишнее imho (см мое замечание выше)
Пришелец-прораб.
 
Цитата
PrALX написал:
И добавлять в таблицу себестоимости со значением null
нееет! Только не null!!! Их Table.FillDown затрет. "Нет данных" надо, а не null.
p.s. и дата 01.01.2001 не нужна - вот там может быть null, он встанет первым при сортировке по возрастанию по дате.
Изменено: AlienSx - 13.05.2024 14:22:34 (ps)
Пришелец-прораб.
 
AlienSx,
так просто в группировке по скю делать заполнение вниз :)

Вот моя тема старая
Для продаж ранее назначенной цены я еще заполнение вверх делал.
 
Цитата
surkenny написал:
так просто в группировке по скю делать заполнение вниз
само собой, о чем и речь в #13. Я без группировки хотел обойтись. Но, может быть, вы правы и лучше группироваться. Рассудить может замер скоростей  :D
Пришелец-прораб.
 
AlienSx, Да, верно. Поправил.

Перенес все в полноценный файл. Работает отлично!
Спасибо огромное!

Замер скоростей теперь уперся в скорость выгрузки нескольких десятков запросов из API и в одновременной обработке нескольких запросов PowerQuery.
Но однозначно стало быстрее и теперь вместо 3 гигабайт всего 10 мегабайт из внешнего файла подгружается.
Изменено: PrALX - 13.05.2024 15:20:57
Страницы: 1
Наверх