Не бейте сильно, если где-то уже данный вопрос обсуждался, но я курил и Chris Webb и все такое, прямого решения н нашел, хотя много полезных идей около моей проблемы прочитал. Итак есть таблица по сути три столбца Index - просто порядковый номер продажи в рамках системы, ID товара, Сумма продажи, необходимо на каждый товар, на каждую продажу подсчитать накопленную по нему сумму, по сути простой нарастающий итог, но посегментно, для каждого товара свой итог, а не общую сумму продаж. При этом желательно не использовать операции прерывающие Query Folding, по сути, ка кодин из вариантов решения, я сджойнил таблицу саму на себя и новом столбце получил вложенную таблицу с продажами по данному товару, но теперь вот незадача, надо отфильтровать вложенную таблицу, т.е. убрать все продажи с индексом (порядковым номером, Index из начала постановки задачи) больше чем у текущей строки, т.е. убрать все более поздние продажи, чем в текущей строке
Я пробовал Table.TransformColumns но она не принимает значение Index из внешнего окружения, моего понимания принципов работы PQ пока не хватает что-бы либо изобрести другое решение не прерывающее Query Folding либо довести это решение до ума, помогите плиз!
Константин Иванов, для такой задачи фолдинг поломается в любом случае. Если надо чтобы быстро, то лучше скулем на стороне БД решать, вьюху сделать. А если все же надо через PQ, то какой объем строк?
написал: Константин Иванов, для такой задачи фолдинг поломается в любом случае. Если надо чтобы быстро, то лучше скулем на стороне БД решать, вьюху сделать. А если все же надо через PQ, то какой объем строк?
Ну пока документов\строк немного, где-то 150.000 тысяч , те которые требуется проанализировать таким образом, но количество постоянно растет. Жаль, что сворачивание придется потерять, но если его терять, то какие варианты Вы бы считали наиболее оптимальными? в целом я могу это сделать отдельной табличкой и при момощи связей потом подцепить ее уже в Power BI к основной для анализа и построения отчетности. Наверное это не столь ужасно будет для производительности, поэтому за варианты с потерей Query Folding тоже буду благодарен! На стороне БД можно сделать, есть у нас девелопер, но я просто не хотел его дергать и мне самому эта задача представляется интересной, т.к. она в том или ином виде встречается в моей практике довольно часто и каждый раз приходится изобретать велосипед)) Мне вообще интересно именно решение с попыткой передать тем или иным способом внешний параметр внутрь функции - в даннном случае передать исходный Index в функцию, которая обрабатывает вложенную табличку. Видел решения через построение функции, но как добавить в нее изначальный Индекс не допёр ((( Так же есть решение через без передачи внешних данных внутрь функции, например List.Accumulate но мне кажется это медленное решение - тупо в изначалной табличке, опираясь на исходный индекс, перебирать по условию и ссумировать, если его же применять ко вложенным таблицам это вообще мне кажется очень долгим и неуклюжим решением, хотя я может чего-то не догоняю.
Спасибо за отклик ! я посмотрел КОД ночестно признаться решения своего вопроса тут не увидел, скачал , сейчас еще раз проанализирую но что-то пока не увидел тут решения своего вопроса ))
Вобщем пока нарастающий итог удалось сделать несколькими способами, но все они помимо того, что убивают Query Folding весьма медленные, самое быстрое на 150.000 тысяч записей получилось в районе 12-15 минут, думал List.Generate или List.Accumulate будут быстрее, но они по всей видимости работают быстро в рамках подсчета нарастающего итога вообще, а когда 150.000 строк еще дополнительно имеют разбивку по ID продукта, то что мне удалось построить на их основе уходит по времени в бесконечность ((
Итак способы, которые я использовал
1. Прямая фильтрация исходной таблицы по индексу и ID продукта, это примрено на 4-5 часов обновления 2. Создание вложенных табличек по ID продукта и отдельная функция, которая каждую вложенную табличку в каждой строке фильтрует, что-бы индекс было до или равно текущей продажи и подсчитывает накопленную сумму это тоже на часы обновление.... 3. Заранее таблицу сортируем - что бы продажи сначала были отсортированы по ID продукта, как бы блоками получились внутри одной большой таблицы, затем внутри каждого продукта сортируем продажи по индексу , что бы они были в хронологическом порядке и затем добавив новый индекс уже в таком положении таблицы считаем накопительный итог для каждой продажи, 15 минут,пока это лучший результат. Вариантов с List.Accumulate или List.Generate пока построить не удалось, возможно там надо цикл в цикле делать - то есть общий цикл идет по табличке, а для каждой строки запускается собственный цикл который за счет индексов знает сколько строк надо проссумировать назад, что-бы получить нарастающий итог по текущему продукту
Константин Иванов, вот вместо Вашей рукописи лучше бы сделали то, что Msi2102 попросил. И отговорка, что Вы данные из БД тащите, не принимается Мы от Вас как раз данные и не хотим, а нужен пример этих данных. Конечно, если хотите решение получить, а не просто выговориться
написал: Константин Иванов, вот вместо Вашей рукописи лучше бы сделали то, что Msi2102 попросил. И отговорка, что Вы данные из БД тащите, не принимается Мы от Вас как раз данные и не хотим, а нужен пример этих данных. Конечно, если хотите решение получить, а не просто выговориться
Прошу прощения был в отъезде. PooHkrd, surkenny , Msi2102 Вот файл , еще раз постараюсь кратко, с этим у меня проблемы. Есть файл с продажами, каждая продажа содержит ID продукта, задача посчитать в Power Query нарастающий итог, на каждую продажу по этому конкретному продукту. P.S. Попробовал считать его сгруппировав продукты блоками т.е. отсортировав входную таблицу так что продажи кадого продукта идут строго подряд по возрастанию , затем по следующему продукту, и так далее. Применил List.Generate, на 150.000 продаж думаю будет обновление около часа - но генерально работает. P.P.S в DAX за счет связей между таблицами вобщем-то тоже легче посчитать, интересно решить эту задачу именно в Power Query в том числе что бы не нагружать отчет, одно дело он на минуту дольше будет обновляться зато потом работать быстро, другое дело он будет это считать и хранить в самом Power BI мне показалось это не оптимально, но может я ошибаюсь, читал тут мнение, что DAX лучше приспособлен к подсчетам и делает это быстрее, а Power Query скорее предназначен для подготовки сырых данных, а не для подсчетов.
surkenny, спасибо! пытаюсь разобраться как это работает - если яправильно понимаю внутрь функции группировки, вставляем List.Generate в функцию агрегации, я не столь искушенный юзер или кодер в данном случае, мне потребуется время для осознания )) сегодня или макс завтра вернусь с результатом ) еще раз благодарность за ответ. P.S. думаю тема ушла в сторону от фильтрации вложенных таблиц, это уже нарастающий итог по продукту с учетом даты или порядкового номера продажи) видимо я неверным путем изначально пошел и отсюда неверная тема.
let
Source = Sql.Database("Server", "Database"),
sql = Value.NativeQuery(
Source,
"SELECT *,
SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date) as RT
FROM
dbo.Table",
null,
[EnableFolding = true]
)
in
sql
Server, Database, Table (и схему, если не dbo) подставляете свои. Вместо * можете выбрать только интересующие столбцы. Потестил с SQL Server 2019, работает. Если в Вашей базе данных нет оконок, можете посмотреть альтернативы.
[URL=#]?[/URL] 1 2 3 4 5 6 7 8 9 10 11 12 13 let Source = Sql.Database( "Server" , "Database" ), sql = Value.NativeQuery( Source, "SELECT *, SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date ) as RT FROM dbo.Table", null, [EnableFolding = true] ) in sql
Server, Database, Table (и схему, если не dbo ) подставляете свои. Вместо * можете выбрать только интересующие столбцы. Потестил с SQL Server 2019, работает. Если в Вашей базе данных нет оконок, можете посмотреть альтернативы .
Спасибо громадное, я не очень силен в SQL, но я примерно понял как это работает, но только не вижу отбора на каждую продажу, что-бы получить все продажи ДО и ВКЛЮЧАЯ эту продажу, но не позже, тут по-моему алгоритм считает все продажи по продукту, а у меня задача посчитать все продажи по продукту на момент этой конкретной продажи, когда встретится следующая продажа то нарастающий итог подрастет. Т.е. для каждой продажу нужен именно нарастающий итог до и включая эту самую продажу, а не общая сумма по продукту. Или я неверно понял то, что Вы написали ?
написал: Или я неверно понял то, что Вы написали ?
Похоже на то Впрочем, я тоже немного ошибся - особенно не смотрел Ваши данные, думал, дата и время будет уникальным идентификатором. Если уникальный идентификатор у Вас в столбце RN, то нужно добавить его в ORDER BY:
Код
let
Source = Sql.Database("Server", "Database"),
sql = Value.NativeQuery(
Source,
"SELECT *,
SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date, RN) as RT
FROM
dbo.Table",
null,
[EnableFolding = true]
)
in
sql
Ну или сделать более универсально (т.е. даже если уникального идентификатора строки нет):
Код
let
Source = Sql.Database("Server", "Database"),
sql = Value.NativeQuery(
Source,
"SELECT *,
SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RT
FROM
dbo.Table",
null,
[EnableFolding = true]
)
in
sql
В целом, код выполняет ровно то, что Вы и хотели: 1. Добавляет столбец с накопительным итогом по каждому продукту 2. Делает это в PQ 3. Так, чтобы query folding не поломался.
[URL=#]?[/URL] 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 let data = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content], typed = Table.TransformColumnTypes ( data, { { "Date" , type datetime }, { "Price" , type number }, { "RN" , Int64.Type } } ), sort = Table.Sort ( typed, { { "ProductID" , Order.Ascending }, { "Date" , Order.Ascending } } ), group = Table.Group ( sort, { "ProductID" }, { { "recs" , ( t ) => [ recs = List.Buffer ( Table.ToRecords ( t ) ), generate = List.Generate ( () => [ i = 0, sum = recs{i}[Price], result = Record.AddField ( recs{i}, "Sum" , sum ) ], each [i] < List.Count ( recs ), each [ i = [i] + 1, sum = [sum] + recs{i}[Price], result = Record.AddField ( recs{i}, "Sum" , sum ) ], each [result] ) ][generate], type {record} } }, GroupKind.Local ), toTable = Table.FromRecords ( List.Combine ( group[recs] ) ) in toTable
surkenny, работает очень быстро, как работает я тоже вобщем-то разобрался, но только делает не совсем то, что я описывал, данный код считает нарастающий итог по продукту, а мне нужен нарастающий итог по продукту с учетом даты или индекса продажи т.е. мне нужны все продажи до и включая текущую, не не включая последующие, можно опираться на дату но лучше на столбец RN что бы можно было точно понимать какие продажи были до, а какие после текущей.
surkenny, да, приношу свои извинения, код работает как надо и если отсортировать затем продажи обратно, все будет гут. Но я вот написал, что понял как код работает, при ближайшем рассмотрении, вынужен признать, нет )) прошу Вашей помощи: то, что Table.Group передает кусок таблицы сгруппированный по столбцам (в нашем случае по одному столбцу "ProductID") это я понял и легкомысленно решил, что далее все просто, но вот что я не понял это 1. Верно ли я понимаю, что далее после столбца группировки объявляется функция ( t ) => , разве нельзя просто там разместить List.Generate? 2. Если я все верно прочитал, то там после функции стоит [generate], вот этого я не понял совсем )) 3. Самое, пожалуй главное, как мы указываем функции List.Generate, что именно этот сгруппированный, отфильтрованный кусок таблицы, верно ли я понимаю, что это происходит на моменте recs = List.Buffer ( Table.ToRecords ( t ) ) т.е. на моменте объявления функции собственно мы и получаем что t = кусок этой исходной таблицы ?
Я очень прше прощения за вопросы, елси они покажутся дурацкими, но мне действительно непонятно )) заранее спасибо за потраченное на меня время!
Константин Иванов написал: 1. Верно ли я понимаю, что далее после столбца группировки объявляется функция ( t ) => , разве нельзя просто там разместить List.Generate?
Так нам бы в List.Generate в каждом шаге обращаться к новой "строке". Быстро и просто - создать из таблицы список записей, в каждом шаге List.Generate увеличивать какой-то счетчик на 1 и обращаться к соответствующему элементу списка записей ( ИмяШагаСпискаЗаписей{n} ).
Цитата
Константин Иванов написал: 2. Если я все верно прочитал, то там после функции стоит [generate], вот этого я не понял совсем ))
Конструкция [a=..., b=..., c=...][b] эквивалентна let a=..., b=..., c=... in b. По сути это вложенный запрос. Если понятнее, то можно код переписать так:
Код
...
group = Table.Group (
sort,
{ "ProductID" },
{
{
"recs",
( t ) =>
let
recs = List.Buffer ( Table.ToRecords ( t ) ),
generate = List.Generate (
() => [ i = 0, sum = recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
each [i] < List.Count ( recs ),
each [ i = [i] + 1, sum = [sum] + recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
each [result]
)
in
generate,
type {record}
}
},
GroupKind.Local
),
...
Цитата
Константин Иванов написал: на моменте объявления функции собственно мы и получаем что t = кусок этой исходной таблицы ?
Все верно. Мы объявили функцию от одной переменной t. Для каждой группы t - это часть таблицы из предыдущего шага sort. И вот с этой таблицей можете делать все, что угодно . Можно было бы сделать по-другому: each перед дальнейшим кодом, а вместо t использовать _. Но тогда рискуете нарваться на сложности при вложенных each внутри. Лучше объявить имя этой таблицы.
P.S. Возможно для понимания будет полезен чуть измененный код (сортируем уже таблицу t внутри группировки; группировка глобальная, а не локальная - четвертый аргумент функции Table.Group по умолчанию GroupKind.Global. Скорее всего, такой вариант будет медленнее. Потестите на реальных данных:
Скрытый текст
Код
let
data = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
typed = Table.TransformColumnTypes ( data, { { "Date", type datetime }, { "Price", type number }, { "RN", Int64.Type } } ),
group = Table.Group (
typed,
{ "ProductID" },
{
{
"recs",
( t ) =>
[
sort = Table.Sort ( t, { { "ProductID", Order.Ascending }, { "Date", Order.Ascending } } ),
recs = List.Buffer ( Table.ToRecords ( sort ) ),
generate = List.Generate (
() => [ i = 0, sum = recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
each [i] < List.Count ( recs ),
each [ i = [i] + 1, sum = [sum] + recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
each [result]
)
][generate],
type {record}
}
}
),
toTable = Table.FromRecords ( List.Combine ( group[recs] ) )
in
toTable
Михаил Л, я это для "красоты" что-ли кода использую Ну и по аналогии с переменными DAX у Феррари/Руссо не очень люблю код вида
Код
let
a=1,
b=2
in
a+b
Вот так как-то лучше для меня :
Код
let
a=1,
b=2,
res = a+b
in
res
И более удобно для отладки кода вложенного запроса на части данных.
P.S. Пора Вам перенимать эстафету PooHkrd совсем редко заходит (остальные M/DAX специалисты тоже ), а у меня сейчас все время уходит на FineBI. Ближайшее время вообще особо не смогу помогать
surkenny написал: более удобно для отладки кода вложенного запроса
Я не программист), поэтому мне удобнее накликать сгенерированный запрос и изменить только необходимое.
Цитата
surkenny написал: Пора Вам перенимать эстафету PooHkrd совсем редко заходит (остальные M/DAX специалисты тоже )
Заходят наверное все (только Андрея VG не видно). А вот решений-ответов да, мало. Я бы и рад чаще помогать, только у меня работа много времени занимает. Да и башка часто занята другим)