Страницы: 1
RSS
Расчет изменений цены для большой таблицы.
 
Здравствуйте.
Есть большая таблица более 1 млн.строк. поэтому работал в PQ
Попробовал высчитать изменение цены исключая колебания (если цена изменилась и вернулась обратно такое движение исключаем) при помощи объединения таблиц со сдвинутым индексом. Но получается не то что нужно (Съедается часть строк, а это потеря информации + ооооочень долго + на выходе все равно не те цифры (( ).
Решил попробовать перейти в Power Pivot (первый раз, до этого с ним еще не работал, поэтому сильно не ругайте).
Нашел там интересную функцию SUBSTITUTEWITHINDEX
Но не разобрался в синтексисе, а русскоязычные сайты примеров использования не дали.
Прошу вашей помощи.
 
Доброе время суток
Цитата
kolyaya написал:
если цена изменилась и вернулась обратно такое движение исключаем
Есть возможность составить пример входной таблицы и пример выходной таблицы с учётом этого момента? Пока приведено как бы следующее
Для каждого товара на порядке возрастающих дат, начиная с первой строки порядка
1. в вывод выводим первую строку данных, считая изменение равным 0. Считаем её последней
2. ищем строку по порядку такую, что абсолютное изменение цены от последней строки вывода будет равно или больше некоторого заданного порога.
3. в вывод выводим такую строку и в изменение выводим разность с последней строкой и считаем её последней
4. повторяем 2, 3 пока не обойдём весь диапазон дат.

Непонятно два момента, Как учитывать требование
Цитата
kolyaya написал:
если цена изменилась и вернулась обратно такое движение исключаем
Почему для яблок порог изменения больше 1, а для груш равен 1? Или такой порог задаётся для каждого товара отдельно? Если да, то почему не приведена таблица для учёта?

kolyaya, вы не стесняйтесь описывать алгоритм расчётов, подобное
Цитата
kolyaya написал:
Решил попробовать..
с ним еще не работал...
не разобрался в синтексисе
и т. д. никак не помогают понять - как рассчитывать вообще, не опираясь на функции и средства того или иного языка программирования. Форум всё жк для решения задач, а не для того чтобы было бы где пожаловаться.
 
На втором листе попытался описать построчно, может так понятней будет+ в первом файл одну строку пропустил в итоговой таблице может эта ошибка неясность внесла.
Яблоко1104.янв1
Цитата
Андрей VG написал:
Почему для яблок порог изменения больше 1, а для груш равен 1? Или такой порог задаётся для каждого товара отдельно? Если да, то почему не приведена таблица для учёта?
Порога нет цена может увеличиться и на 1 и вернуться, а может и на 5 и вернуться. Нужно отфильтровать эту ненужную информацию, когда движение +- равнозначное.
Цитата
Андрей VG написал:
и т. д. никак не помогают понять - как рассчитывать вообще, не опираясь на функции и средства того или иного языка программирования. Форум всё жк для решения задач, а не для того чтобы было бы где пожаловаться.
Если можно привидите пример использования данной функции. Как я понял из описания она и является аналогом NestedJoin и возможно ускорит обработку информации и уменьшит нагрузку. Есть ли смысл запрос который написан для примера обрабатывать в power pivot, увеличит ли это скорость?
Изменено: kolyaya - 03.06.2020 09:50:47
 
Долго и мучительно я наконец-то добился правильного выполнения на power query.
Но в оригинальном файле запрос съедает все ресурсы и не может его обработать.
Помогите пожалуйста облегчить запрос.
Возможно ли написать аналог в power pivot или power bi? Увеличит ли это скорость выполнения?

Подскажите пожалуйста имеет ли смысл создавать пробежуточные столбцы как в примере ( "CH", "CHP", "FiltrCH", "FiltrC" ) или лучше прописать все условия в условном столбце? Если кто сталкивался как это влияет на скорость.

Заранее спасибо.
Изменено: kolyaya - 04.06.2020 23:24:02
 
kolyaya, а такой вариант подойдет?
Код
let Источник = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Remove = Table.Distinct(Источник, {"Товар", "Цена"}),
    Group = Table.Group(Remove, {"Товар"}, {{"a", each let 
    a=Table.ToColumns(_),b=Table.FromColumns(a&{{0}&List.RemoveLastN(a{1})})
    in Table.AddColumn(b,"b", each [Column2]-[Column4])}})
in  Table.ExpandTableColumn(Group[[a]],"a",{"Column1","Column3","Column2","b"},{"a","b","c","d"})
 
Цитата
Михаил Л написал:
й вариант подойдет?
Классно, быстро но полностью исключаются возврат цены. т.е Если цена в начале была 10 и в  конце вернулась к 10 возврат не учитывается.(( А так спасибо интересный запрос.
Можно прокоментировать как работает эта часть запроса?  
a=Table.ToColumns(_),b=Table.FromColumns(a&{{0}&List.RemoveLastN(a{1})})
in Table.AddColumn(b,"b", each [Column2]-[Column4])}})

А можно сделать чтобы было 2 столбца цены? (они не зависимы поэтому пересчитать не получаеться). Думал сам смогу под 2 цены изменить но для меня этот код не полностью понятен ((
Изменено: kolyaya - 05.06.2020 14:51:04
 
Цитата
kolyaya написал:
как работает эта часть запроса
Эта часть нужна только для вычисления разницы цены. Так, наверное, понятнее будет
Чет Сайт с ГуглХром общаться не хочет
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Remove = Table.Distinct(Источник, {"Товар", "Цена"}),
    #"Filtered Rows" = Table.SelectRows(Remove, each ([Товар] = "Яблоко")),
     
    a=Table.ToColumns(#"Filtered Rows"),a2={0} & List.RemoveLastN(a{1}),b=Table.FromColumns(a & {a2}),
    c= Table.AddColumn(b,"b", each [Column2]-[Column4])
in c
Изменено: Михаил Л - 05.06.2020 15:22:00
 
Спасибо. Теперь понятнее. Но я читал где то на форуме что такой способ работает медленнее чем join. Попробую так. О результатах отпишусь.
 
Круто! большое спасибо. Все получилось. Мне сначала показалось что это (a{1}) обращение к ячейке (где-то видел подобное, даже пробовал, было очень медленно), но ваш метод оказался довольно быстрым. Обработка 3 цен по более сложному алгаритму с фильтрами (Table.Distinct мне не подошел), 600000 строк обработал за 5 минут.

Подскажите пожалуйста, а как применить Table.Distinct ко всем строкам за исключением первой и последней?

Еще раз спасибо.
 
Цитата
kolyaya написал:
как применить Table.Distinct ко всем строкам за исключением первой и последней?
Если ваши данные упорядочены по товаре и дате, то первая строка товара остаётся всегда. Вопрос, учитывая алгоритм Михаил Л - что если начальная и конечная цена товара на порядке дат совпадают? Каков должен быть результат?
В примере id - даты одного товара, val - цены. Что должно получиться в результате с учётом требования по оставлению первого последнего и задачи собственно темы?
Изменено: Андрей VG - 15.06.2020 22:22:03
 
 1 1
2 2
9 1
насчет первой строки согласен, а последняя с наиболшьим id или последняя по времени (это в принципе одно и тоже)
 
Вариант подзадачи с сохранением первой и последней записи товара в не зависимости от уникальности. Как-то не очень красиво получилось. Может коллеги подсобят.
Думаю, уже смещение для вычисления разницы в Table.Group на финальном этапе сделаете самостоятельно.
Изменено: Андрей VG - 16.06.2020 08:05:43 (Чуть подрихтовал)
Страницы: 1
Наверх