Таблица отсортирована, повторы из одного эпизода всегда идут следом друг за другом.
Мне надо ее превратить в такую:
Номер обращения |
Номер предыдущего обращения |
Номер первого обращения в эпизоде
2
1
1
3
2
1
16
15
15
17
16
15
18
17
15
И все это надо сделать в Power Query. Такие деревья можно быстро разобрать в Power Pivot, но хотелось бы сделать это именно в PQ. Конечно можно сделать это в VBA или на чистом Excel, который может смотреть на предыдущую строку, но это тоже не подходит.
Может есть какие-нибудь простые алгоритмы на этот счет? Или может я упустил какую формулу из справочника?
vetrintsev, а как определить то границу эпизода? ну и так трудно что ли сделать небольшой пример в виде файла Excel, чтобы помогающим не пришлось придумывать? А так сгруппировать по эпизодам, аггрегировать по мин, и потом джойнить к исходной
StepanWolkoff написал: а как определить то границу эпизода?
Я так понял, что в этом и состоит задача - развернуть цепочку с хвоста и определить все события относящиеся к одному эпизоду. Чую огромное количество переборов рекурсией, и PQ загнется на более менее серьёзном массиве.
Простите, сижу на работе, загнался с примером, справедливое замечание.
Вот подготовил такой пример во вложении. Изначально у меня нет даже сведений о повторах, просто список обращений (здесь я упростил исходник, там конечно еще идет джоин по клиенту и продукту)
Цитата
Андрей VG написал: А если кто-то "случайно" отсортировал по чему-нибудь - как вы будете определять эту самую предыдущую строку?
Если в чистом excele то никак, но я сейчас я сам сортирую данные в PQ. Поэтому не критично
PooHkrd написал: Я так понял, что в этом и состоит задача - развернуть цепочку с хвоста и определить все события относящиеся к одному эпизоду. Чую огромное количество переборов рекурсией, и PQ загнется на более менее серьёзном массиве.
итоговый массив около 50к записей. Но нужна логика не разворачивания, и последовательного переходя по записям с просмотром/буферизацией предыдущей записи. Но мне кажется я придумал: Можно создать range на 50к индексов, и пробегаясь по нему брать его элементы за индексы строки, и потом этими индексами обращаться к строке i, смотреть в строку i-1 и смотреть есть ли в строке i-1номер обращения, равный номеру предыдущего обращения в строке i, если есть то сохраняем в новом столбце строки i ROOT = ROOT из строки i-1, иначе ROOT в строке i равен номеру предыдущего обращения в этой же строке. И рекурсии нет, а последовательность уже отсортирована, поэтому порядок не должен быть нарушен...
StepanWolkoff, не совсем, судя по запросам в его примере эпизодом считаются обращения между которыми не более одного дня. Все остальное - это события без связей. Странная логика, конечно - но, мы ж не знаем почему именно так.
хотя как же я обращусь к предыдущей строке в которой сохранил root?
Нужно наверное сджоинить таблицу с собой же, и бегать по ней до тех пор, пока есть незаполненный ROOT. соответственно количество итераций пробега по всей таблице будет равно количеству повторных обращений в самом длинном эпизоде... блин...
vetrintsev написал: И рекурсии нет, а последовательность уже отсортирована, поэтому порядок не должен быть нарушен...
Ну, таким образом вы выдернете предка первого порядка, а с остальными как быть? Потому и нужна рекурсия, ибо вы не знаете сколько проходов нужно сделать. С другой стороны Степан правильно копает. Если в качестве цепочки у вас выступают последовательности событий с разрывом между ними не более одних суток, то проще на этом же этапе их нумеровать и через группировку и List.Min выковыривать самого первого родителя в цепочке. Короче говоря, самым оптимальным вариантом было бы дать исходные данные - алгоритм определения цепочек и нужный результат. А не справшивать как реализовать то решение, которое вы считаете верным.
Это обычная логика подсчета повторных обращений, например для метрики FCR, а мне нужно их связать в эпизоды, чтобы определить не просто факт повтора, а еще и весь контекст и путь клиента.
Нумерация не последовательная, между обращениями одного клиента могут обращаться другие, да в примере это не учел, но это так и есть. Однако, так как строки идут последовательно, можно
Цитата
PooHkrd написал: С другой стороны Степан правильно копает. Если в качестве цепочки у вас выступают последовательности событий с разрывом между ними не более одних суток, то проще на этом же этапе их нумеровать и через группировку и List.Min выковыривать самого первого родителя в цепочке.
их проиндексировать. А вот как их группировать? По какому признаку? все строки отличаются друг от друга.
Цитата
PooHkrd написал: Короче говоря, самым оптимальным вариантом было бы дать исходные данные - алгоритм определения цепочек и нужный результат. А не справшивать как реализовать то решение, которое вы считаете верным
я же выложил пример) Там и надо добавить 3-й столбец с ссылкой на самого главного родителя.
==== можно так же итеративно (напишу здесь, чтобы не забыть): Н - номер обращения НП - номер предыдущего обращения ROOT - искомое значение
1 блок: 1) Делаем антисоединение слева left[НП] = right[Н] - так мы находим все первые повторки в каждом эпизоде. 2) Добавляем столбец ROOT = [НП]
потом итеративная функция FIND_ROOTS( T1 as table)=> 0) принимаем таблицу T1, в которой заполнены ROOT только для повторок 1 уровня (по антисоединению) 1) T2 копируем и удаляем все строки где не пустой ROOT 2) T3 копируем и удаляем все пустые строки, где пустой ROOT 3) Делаем внешнее соединение слева T2[НП] = Т3[Н] 4) удаляем столбец T2[ROOT] 5) разворачиваем T3[ROOT] - так мы присвоили ROOT следующему уровню. 6) если T2[ROOT] содержит null, то 6.1) T4 = вызываем функцию @FIND_ROOTS( T2 ) 6.2) T2 удаляем строки с пустым ROOT 6.3) возвращаем Table.Combine({T2, T4})
примерно так должно работать. Думаю что 20 join'ов по таблицам, которые с каждым уровнем уменьшаются в размерах (причем почти в геометрической прогрессии), будет быстрее, чем для каждой строки итерировать с поиском по значению в строках.
Вечером попробую сделать, должно работать, и всего 20-30 итераций с джоинами на уровне таблиц без построкового поиска для каждой строки.
vetrintsev написал: Нумерация не последовательная, между обращениями одного клиента могут обращаться другие, да в примере это не учел, но это так и есть.
Поэтому Степан и указал, что пример должен быть максимально приближенным к реальной структуре данных, чтобы не было потом такого, что для примера работает, а применить в жизни не выходит.
vetrintsev, ну вот блин, я уже покопал, сделал пример решения на ваших данных, а тут оказывается новые условия всплывают... Вот даже за это не буду выкладывать то, что сделал.
Цитата
vetrintsev написал: Нумерация не последовательная, между обращениями одного клиента могут обращаться другие
Как вот это понимать? В исходных данных есть признак клиента? Или как вы там определяете, что это обращение должно быть в эпизоде, а это нет, если у вас идет клиент1:1,2,4; клиент2:3,5,6 - по вашей логике это будет два эпизода всего, или по два эпизода у каждого, а итого будет четыре?
PooHkrd написал: Потому и нужна рекурсия, ибо вы не знаете сколько проходов нужно сделать.
Алексей, в общем то на данном примере рекурсия не нужна. Порядок задаётся датами. То есть в локальной последовательности дат с разницей в один день корневым будет обращение с минимальной датой. Вариант, для допиливания с не представленными данными и структурами. В общем проще. Код по ссылке Степана тоже можно упростить, что то там перезамудрили обход дерева в ширину.
StepanWolkoff написал: Как вот это понимать? В исходных данных есть признак клиента? Или как вы там определяете, что это обращение должно быть в эпизоде, а это нет, если у вас идет клиент1:1,2,4; клиент2:3,5,6 - по вашей логике это будет два эпизода всего, или по два эпизода у каждого, а итого будет четыре?
у меня все обращения по одному клиенту сгруппированы, отсортированы по дате, поэтому не принципиально - последовательность можно восстановить проиндексировав таблицу, и не важно в таком случае какой был номер у обращения. Даже связи между повторками есть в последовательных индексах (см. пример, второй запрос).
если у одного клиента по одной и той же тематике за последние 48 часов было зафиксировано обращение, то текущее обращение считается повторным. Но еще раз говорю, последовательная нумерация легко делается, причем даже без группировки - сортируем по клиенту, потом по дате, потом индексируем, и все соединено последовательно.
блин пока сообщение напишешь, светлые умы еще новый текст накидают) Спасибо Вам всем за отзывчивость! Вернусь на форум через часа 2.
Да я уже понял, что сам всех запутал своим "простым" примером) хотел как лучше. Но Ваши идеи я понял, если получится - выложу, может еще кому пригодится разворачивать цепочки...
Степан, спасибо за ссылку. Боюсь тот код повеситься на больших объёмах при построении иерархии. Вариант от предложенного мной примера. на 99 корневых 99 дочерних элементов - выполняется за 3,7 секунды. При 999 корневых 99 дочерних за 275 секунд. При 99 корневых 999 дочерних за 440 секунд.
Скрытый текст
Код
let
Source = Excel.CurrentWorkbook(){[Name="Обращения"]}[Content],
preRootDef = Table.RenameColumns(Source, {{"Номер обращения", "Child"}, {"Номер предшествующего", "Parent"}}),
rootDef = Table.Join(preRootDef, {"Parent"}, Source, {"Номер обращения"}, JoinKind.LeftAnti)[[Child], [Parent]],
addRoot = Table.AddColumn(rootDef, "Root", each [Parent]),
addLevel = Table.AddColumn(addRoot, "Level", each 2),
renamed = Table.RenameColumns(Source, {{"Номер обращения", "c"}, {"Номер предшествующего", "p"}}),
buffered = Table.Buffer(renamed),
generator = List.Generate(
() => [step = addLevel, count = Table.RowCount(addLevel)],
each [count] > 0,
(prev) =>
let
next = Table.Join(buffered, {"p"}, prev[step], {"Child"}),
result = if Table.RowCount(next) = 0 then
[step = next, count = 0]
else
let
needed = Table.RenameColumns(next[[c], [p], [Root], [Level]], {{"c", "Child"}, {"p", "Parent"}})
in
[step = Table.Buffer(Table.TransformColumns(needed, {"Level", each _ + 1})), count = Table.RowCount(step)]
in
result,
each [step]
),
result = Table.Combine(generator),
return = Table.Sort(result,{{"Root", Order.Ascending}, {"Level", Order.Ascending}})
in
return