Страницы: 1
RSS
Проход по цепочке с Power Query, Есть список повторных обращений, надо объединить все повторы в эпизоды
 
Добрый день!

Вот есть, например, такая таблица:
Номер обращения |Номер предыдущего обращения
21
3 2
16 15
17 16
18 17
Таблица отсортирована, повторы из одного эпизода всегда идут следом друг за другом.

Мне надо ее превратить в такую:
Номер обращения | Номер предыдущего обращения | Номер первого обращения в эпизоде
2 1 1
3 2 1
161515
171615
181715
И все это надо сделать в Power Query.
Такие деревья можно быстро разобрать в Power Pivot, но хотелось бы сделать это именно в PQ.
Конечно можно сделать это в VBA или на чистом Excel, который может смотреть на предыдущую строку, но это тоже не подходит.

Может есть какие-нибудь простые алгоритмы на этот счет? Или может я упустил какую формулу из справочника?
Изменено: vetrintsev - 16.05.2018 10:33:11
 
vetrintsev, а как определить то границу эпизода? ну и так трудно что ли сделать небольшой пример в виде файла Excel, чтобы помогающим не пришлось придумывать? А так сгруппировать по эпизодам, аггрегировать по мин, и потом джойнить к исходной
 
А почему в столбце с номером обращения отсутствуют номера, которые являются первыми в цепочке?
Вот горшок пустой, он предмет простой...
 
Цитата
StepanWolkoff написал:
а как определить то границу эпизода?
Я так понял, что в этом и состоит задача - развернуть цепочку с хвоста и определить все события относящиеся к одному эпизоду. Чую огромное количество переборов рекурсией, и PQ загнется на более менее серьёзном массиве.
Изменено: PooHkrd - 16.05.2018 10:48:07
Вот горшок пустой, он предмет простой...
 
PooHkrd, поэтому и нужен адекватный файл-пример, с максимально близкой структурой к оригиналу,  а автор ушел в подполье
 
Цитата
StepanWolkoff написал:
файл-пример
Как вариант файла примера.
Цитата
vetrintsev написал:
который может смотреть на предыдущую строку
А если кто-то "случайно" отсортировал по чему-нибудь - как вы будете определять эту самую предыдущую строку?
Изменено: Андрей VG - 16.05.2018 11:15:09
 
Ну, может быть как то поможет эта статья Unfold Child-Parent hierarchy in Power Query от Ивана Бондаренко
 
Простите, сижу на работе, загнался с примером, справедливое замечание.

Вот подготовил такой пример во вложении.
Изначально у меня нет даже сведений о повторах, просто список обращений (здесь я упростил исходник, там конечно еще идет джоин по клиенту и продукту)
Цитата
Андрей VG написал: А если кто-то "случайно" отсортировал по чему-нибудь - как вы будете определять эту самую предыдущую строку?
Если в чистом excele то никак, но я сейчас я сам сортирую данные в PQ. Поэтому не критично :)
 
И что вам не понятно по ссылке приведенной Степаном? По-моему - это как раз то, что вам нужно.
Вот горшок пустой, он предмет простой...
 
vetrintsev, я правильно понимаю, что "эпизод" определяется непрерывной цепочкой номеров обращений? Т.е. 1,2,3 =>эпизод1; 5,6,7,8,9=>эпизод2 и т.д.
 
Цитата
PooHkrd написал:
Я так понял, что в этом и состоит задача - развернуть цепочку с хвоста и определить все события относящиеся к одному эпизоду. Чую огромное количество переборов рекурсией, и PQ загнется на более менее серьёзном массиве.
итоговый массив около 50к записей.
Но нужна логика не разворачивания, и последовательного переходя по записям с просмотром/буферизацией предыдущей записи.
Но мне кажется я придумал:
Можно создать range на 50к индексов, и пробегаясь по нему брать его элементы за индексы строки, и потом этими индексами обращаться к строке i, смотреть в строку i-1 и смотреть есть ли в строке i-1 номер обращения, равный номеру предыдущего обращения в строке i, если есть то сохраняем в новом столбце строки i ROOT = ROOT из строки i-1, иначе ROOT в строке i равен номеру предыдущего обращения в этой же строке.
И рекурсии нет, а последовательность уже отсортирована, поэтому порядок не должен быть нарушен...
 
StepanWolkoff, не совсем, судя по запросам в его примере эпизодом считаются обращения между которыми не более одного дня. Все остальное - это события без связей. Странная логика, конечно - но, мы ж не знаем почему именно так.
Вот горшок пустой, он предмет простой...
 
Цитата
vetrintsev написал:
И рекурсии нет
хотя как же я обращусь к предыдущей строке в которой сохранил root?

Нужно наверное сджоинить таблицу с собой же, и бегать по ней до тех пор, пока есть незаполненный ROOT. соответственно количество итераций пробега по всей таблице будет равно количеству повторных обращений в самом длинном эпизоде... блин...
 
Цитата
vetrintsev написал:
И рекурсии нет, а последовательность уже отсортирована, поэтому порядок не должен быть нарушен...
Ну, таким образом вы выдернете предка первого порядка, а с остальными как быть? Потому и нужна рекурсия, ибо вы не знаете сколько проходов нужно сделать.
С другой стороны Степан правильно копает. Если в качестве цепочки у вас выступают последовательности событий с разрывом между ними не более одних суток, то проще на этом же этапе их нумеровать и через группировку и List.Min выковыривать самого первого родителя в цепочке.
Короче говоря, самым оптимальным вариантом было бы дать исходные данные - алгоритм определения цепочек и нужный результат. А не справшивать как реализовать то решение, которое вы считаете верным.
Изменено: PooHkrd - 16.05.2018 13:22:52
Вот горшок пустой, он предмет простой...
 
Цитата
StepanWolkoff написал:
Ну, может быть как то поможет эта статья  Unfold Child-Parent hierarchy in Power Query  от Ивана Бондаренко
Спасибо, изучаю! Вероятно да, это то что надо.
Не обновился перед публикацией.
 
Цитата
PooHkrd написал:
Странная логика,
Это обычная логика подсчета повторных обращений, например для метрики 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 - 16.05.2018 14:00:13
 
Цитата
vetrintsev написал:
Нумерация не последовательная, между обращениями одного клиента могут обращаться другие, да в примере это не учел, но это так и есть.
Поэтому Степан и указал, что пример должен быть максимально приближенным к реальной структуре данных, чтобы не было потом такого, что для примера работает, а применить в жизни не выходит.
Вот горшок пустой, он предмет простой...
 
vetrintsev, ну вот блин, я уже покопал, сделал пример решения на ваших данных, а тут оказывается новые условия всплывают... Вот даже за это не буду выкладывать то, что сделал.
Цитата
vetrintsev написал:
Нумерация не последовательная, между обращениями одного клиента могут обращаться другие
Как вот это понимать? В исходных данных есть признак клиента? Или как вы там определяете, что это обращение должно быть в эпизоде, а это нет, если у вас идет клиент1:1,2,4; клиент2:3,5,6 - по вашей логике это будет два эпизода всего, или по два эпизода у каждого, а итого будет четыре?
 
Цитата
PooHkrd написал:
Потому и нужна рекурсия, ибо вы не знаете сколько проходов нужно сделать.
Алексей, в общем то на данном примере рекурсия не нужна. Порядок задаётся датами. То есть в локальной последовательности дат с разницей в один день корневым будет обращение с минимальной датой.
Вариант, для допиливания с не представленными данными и структурами.
В общем проще. Код по ссылке Степана тоже можно упростить, что то там перезамудрили обход дерева в ширину.
 
Цитата
StepanWolkoff написал:
Как вот это понимать? В исходных данных есть признак клиента? Или как вы там определяете, что это обращение должно быть в эпизоде, а это нет, если у вас идет клиент1:1,2,4; клиент2:3,5,6 - по вашей логике это будет два эпизода всего, или по два эпизода у каждого, а итого будет четыре?
у меня все обращения по одному клиенту сгруппированы, отсортированы по дате, поэтому не принципиально - последовательность можно восстановить проиндексировав таблицу, и не важно в таком случае какой был номер у обращения. Даже связи между повторками есть в последовательных индексах (см. пример, второй запрос).

если у одного клиента по одной и той же тематике за последние 48 часов было зафиксировано обращение, то текущее обращение считается повторным. Но еще раз говорю, последовательная нумерация легко делается, причем даже без группировки - сортируем по клиенту, потом по дате, потом индексируем, и все соединено последовательно.

блин пока сообщение напишешь, светлые умы еще новый текст накидают) Спасибо Вам всем за отзывчивость!
Вернусь на форум через часа 2.
Изменено: vetrintsev - 16.05.2018 14:24:14
 
Цитата
Андрей VG написал:
Алексей, в общем то на данном примере рекурсия не нужна.
Ну, после того как ТС выложил пример, это стало и так понятно. Изначально то было 2 столбца и все.  :D
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
это стало и так понятно
Как бы снова нет
Цитата
PooHkrd написал:
за последние 28 часов было зафиксировано обращение, то текущее обращение считается повторным
:D
Всё, я пас.
 
Цитата
Андрей VG написал:
28 часов
опечатался - 48 часов, исправил.
Цитата
Андрей VG написал:
Всё, я пас.
Да я уже понял, что сам всех запутал своим "простым" примером) хотел как лучше.
Но Ваши идеи я понял, если получится - выложу, может еще кому пригодится разворачивать цепочки...
 
Вот вариант с тем же алгоритмом что у Андрея, но в кнопочном режиме. Так красиво как он, я пока не могу.
Вот горшок пустой, он предмет простой...
 
Степан, спасибо за ссылку. Боюсь тот код повеситься на больших объёмах при построении иерархии. Вариант от предложенного мной примера.
на 99 корневых 99 дочерних элементов - выполняется за 3,7 секунды. При 999 корневых 99 дочерних за 275 секунд. При 99 корневых 999 дочерних за 440 секунд.
Скрытый текст

Может кому и пригодится.
 
Я как-то обленился и практически на кнопочках собрал (немного в другом виде, наверное?):

Код
// Цепочка
let
    Source = let
    Source = Excel.CurrentWorkbook(){[Name="Обращения"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Номер обращения", Int64.Type}, {"Дата обращения", type datetime}})
in
    #"Changed Type",

    AddIndex1 = Table.AddIndexColumn(Source, "idx1", 1, 1),
    AddIndex0 = Table.AddIndexColumn(Source, "idx0", 0, 1),
    Renamed = Table.RenameColumns(AddIndex0,{{"Номер обращения", "Номер"}, {"Дата обращения", "Дата"}}),
    Merged = Table.Join(Renamed,{"idx0"},AddIndex1,{"idx1"},JoinKind.LeftOuter),
    DateDiff = Table.AddColumn(Merged, "Вычитание", each Duration.TotalHours([Дата]-[Дата обращения]), Int64.Type),
    AddLink = Table.AddColumn(DateDiff, "Цепочка", each if [Вычитание] = null then [Номер] else if [Вычитание] <=48 then null else [Номер], Int64.Type),
    SortedRows = Table.Sort(AddLink,{{"Номер обращения", Order.Ascending}}),
    FilledDown = Table.FillDown(SortedRows,{"Цепочка"}),
    Removed = Table.SelectColumns(FilledDown,{"Номер", "Дата", "Цепочка"})
in
    Removed
Изменено: Максим Зеленский - 16.05.2018 17:17:58
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Я как-то обленился и практически на кнопочках собрал (немного в другом виде, наверное?):
Table.FillDown - век живи, век учись! То что надо! Спасибо!
и никаких иттераций +3 строчки в запросе.
Страницы: 1
Наверх