Доброго времени суток! Уважаемые форумчани! Это мой первый пост на форумt, не судите строго. Помогите в решении одной небольшой задачи в Power Query. первое, есть справочная таблица с указанием сделки (идентификатора сделки) и периоды (начальная и конечная дата) действия процентной ставки по сделке. втрое, есть таблица с данными "Сделки" и "Дата" Необходимо, вытащить действующий процент на дату который попадает в период.
Shuhrat, Думаю, что интервальный поиск тут будет самым быстрым вариантом. Поищите на форуме, мне лень в 100 раз делать Только справочник нужно будет переделать немного в PQ. Вот такой вариант попробуйте:
Код
let
sprData = Excel.CurrentWorkbook(){[ Name = "Сравочник" ]}[Content],
spr = Table.Buffer ( Table.TransformColumnTypes ( sprData, { { "НачалДата", type date }, { "КонечДата", type date } } ) ),
lst = List.Buffer ( spr[Процент] ),
data = Excel.CurrentWorkbook(){[ Name = "Сделки" ]}[Content],
typed = Table.TransformColumnTypes ( data, { { "Сумма", type number }, { "Дата", type date } } ),
addPerc = Table.AddColumn (
typed,
"Процент",
( r ) =>
try
lst{Table.PositionOf ( spr, r, Occurrence.First, ( x, y ) => y[Дата] >= x[НачалДата] and y[Дата] <= x[КонечДата] and x[Сделка] = y[Сделка] )}
otherwise
null,
type number
)
in
addPerc
С одной колонкой с процентами работает норм. У моей таблицы ("Сделки" и "Дата") больше 2 млн.строк (Вариант с создание новой таблицы не очень подходить) а "справочник" содержит более 400 строк и проценты даны в 4 колонках.
Shuhrat, я за интервальный поиск. Если не получается самостоятельно, то попросите модераторов перенести тему в раздел работа. Смогу взяться за реализацию. По цене написал в ЛС.
[QUOTE]Михаил Л написал: не шустро из 11 строк справочника получается 1875 строк по всем датам. Тут и вариант из #2 можно переделать. Но самым шустрым будет интервальный поиск
Shuhrat, даже если возьмете решение Михаил Л, если не сложно, дайте мне в ЛС (или в tg) реальные данные (я их использовать и распространять не буду ) - я покажу сравнительное время вычисления результатов по разным методам.
Михаил Л, кстати, важный момент Вы не учли: что произойдет, если вдруг в справочнике будут пересекающиеся даты для одной сделки? К примеру, как на рисунке:
Михаил Л написал: Скорость не будет ниже чем в два раза.
Уверены? Что готовы поставить? На больших объемах (типа справочник (уже разделенный на строки) 1kk, данные 1kkk+) Ваш запрос далеко не на каждой машинке вообще когда-либо выполнится
surkenny написал: что произойдет, если вдруг в справочнике будут пересекающиеся даты для одной сделки?
В изначальных данных этого нет. Если бы были пересекающие даты, то рано вообще что-либо решать. Сначала надо определить какие данные показывать, с первой строки, со второй или с обеих строк данные.
Цитата
surkenny написал: исключительно для Вас и Вашего развития
Мне уже за сорок. Куда развиваться) Завязывать уже пора, это да) Мой запрос на миллионе строк и 400+ строк отработал за 20сек. Пример весил более 8мб, да и на работе уже остался ps/ Вот пример по быстрому на млн строк и справочнике 400строк. Сначала обновить запрос Сделки, а потом result
Уважаемые форумчане! Я рад что моя задача вызвала столько дискуссий. Решение Михаила на примере сработала на ура. Но на реальных данных я ещё не с интегрировал. Так как помимо этого там ещё куча вычислений, типа "тут считай, а тута нет". В примере я сильно упростил задачу (выделил только один шаг). Как только все сделаю обязательно сообщу результат. отвечая на возникшие вопросы: общий объем данный 120 м. точное кол-во строк: 2 003 384, "нахлёста" дат не может быть, за это отвечает другая программа. Компьютер: Intel® Core i5-9500 CPU @ 3.00GHz 3.00 GHz RAM 8 gB Без указанного функционала вычисляется примерно 3 минуты (точно не засекал).
Моя работа не связана с вычислениями - это хобби. Помогаю коллегам с вычислениями. Немного о себе Power Query я занимаюсь всего месяц. Но на Excel работаю аж с версии виндовса win3.1 (до него работал на Quattro Pro под DOS, а до него SuperCalc если помните такие ).