Страницы: 1
RSS
Контроль уникальности по двум независимым столбцам в Power Query
 
Всем привет!

Сначала сам вопрос:
как из выборки:
left_id right_id
1 1
2 1
1 3
2 3
3 3
Оставить только строки:
Left_id right_id
1 1
2 3
А вот зачем это:


Есть таблица с событиями.
мне надо связать два типа события.

В одном отчетном периоде клиент может отключить старую услугу и подключить новую, вот надо в строку вывести код событий отключения и подключения, чтобы потом можно было сравнить сами услуги (старую и новую)

первый уровень усложнения: у клиента может быть несколько услуг, причём отключить он может две, а подключить одну. Благо требования достаточно мягкие, чтобы можно было просто сцепить события в порядке их возникновения: это я решил SQL запросом пронумеровал ссобытия попарно и сджоинил по его номеру.

но такой механизм не отвечает реалиям бизнесзадачи.

Второе усложнение: отчетный период закрывается до 15 числа месяца, следующего за отчетным. Такая мелочь привела к тому, что события отключения обычно в конце месяца, а подключения в начале следующего, и аналитики вручную сцепляют эти события.

в попытках решить задачу я смог составить декартово произведение всех возможных событий в рамках одного клиента, если она попадают в один отчётный период. Только сложность в том, что событие в числах месяца с 1 по 15 могут попасть сразу в два отчетных периода (прошлый и текущий).

Данную задачу можно было бы решить, если можно бы было создать таблицу с двумя столбцами с констрейнтамии на уникальность в каждомъ. Тогда, полученный запрос просто бы пытался вставить в эту таблицу данные, и игнорировал бы ошибки, на выходе таблица содержала бы только корректные связи событий.
НО. Create table нельзя.

Соответственно, надо каким либо образом симулировать проверку на уникальность по каждому из двух столбцов.

Сам запрос вызывается из MS SSAS 2017 Tabular к хранилищу на postgresql 8.2, поэтому из инструментов только M и SQL...

 
 
Добрый день Table.Distinct
 
И что он вернет? Distinct может вернуть либо уникальные значения по одному столбу, либо уникальные сочетания по нескольким.

Здесь скриптом я бы реализовал такой алгоритм:
Код
инициализируем переменные max_left_id=-1,  max_right_id=-1
Цикл по всем записям, в цикле:
    если left_id>max_left_id и right_id>max_right_id, то:
        max_left_id=left_id
        max_right_id=right_id
        и выбрать текущую запись
    иначе пропускаем текущую запись
но скрипт запустить не где, кроме SQL и M.
Distinct выдаёт другой результат.  
 
Доброе время суток.
Цитата
vetrintsev написал:
такой алгоритм
Используйте над списком записей List.Accumulate, где в качестве начального состояния запись [result = {}, max_left_id = -1, max_right_id = -1] на каждой итерации если условие выполняется, то пополняете список result записью и задаёте новые максимумы.
Подобное можно сделать и рекурсивной функцией с доступом списку записей по текущему номеру и функцией List.Generate - в этих случаях потребуется дополнительная фильтрация результата, но на большом количестве записей List.Generate будет предпочтительнее, так как сцепление списков в List.Accumulate - очень не спешная задача. Это не push для массивов javascript, увы.
Изменено: Андрей VG - 23.06.2019 19:02:52
 
Андрей VG, огромное спасибо за направление!

Про Generator логику понял (правда не понял зачем тут рекурсивная функция...), но у меня тогда вопрос, если каждый раз будет осуществляться индексированное обращение к источнику по номеру строки, не увеличит ли это нагрузку на источник в N раз, где N - количество записей (против последовательного прохода по строкам)? Или PQ увидит что обращения идут последовательно и будет также последовательно фетчить данные? Или PQ загонит таблицу в память? Или ему нужно об это сказать через Table.Buffer? Там и 100 метров не будет, таблица хоть и длинная, но узкая и на числах...
Изменено: vetrintsev - 23.06.2019 20:55:16
 
Цитата
vetrintsev написал:
правда не понял зачем тут рекурсивная функция.
Описывалось три разных подхода решения задачи. То есть рекурсивная функция - это самостоятельное решение, хотя и подобное List.Generate. Очевидный вариант на List.Accumulate. Требуемую предварительную сортировку, думаю, сможете добавить самостоятельно.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    rowList = Table.ToRecords(Source),
    selector = List.Accumulate(
        rowList, [result = {}, max_left = -1, max_right = -1], (acc, next) =>
        if next[left_id] > acc[max_left] and next[right_id] > acc[max_right] then
            [result = acc[result] & {next}, max_left = next[left_id], max_right = next[right_id]]
        else
            acc
    )
in
    Table.FromRecords(selector[result])

Цитата
vetrintsev написал:
не увеличит ли это нагрузку на источник в N раз, где N - количество записей (против последовательного прохода по строкам
Увеличит. Но если добавить List.Buffer, то соотношение будет приблизительно такое как и для коллекции VBA - в сравнении циклов For i = 1 To Collection.Count и For Each item In Collection. Разница заметная, но повторюсь, стоимость слияния списков выше.
Версия для T-SQL
Скрытый текст
Изменено: Андрей VG - 23.06.2019 22:18:04
 
Цитата
Андрей VG написал:
Очевидный вариант на List.Accumulate.
На локальном компе отработал на 3К строках, а на сервере ошибка:
Цитата
Expression.Error: При вычислении возникло переполнение стека. Продолжение невозможно.
Про WITH RECURSIVE было бы идеально, если бы не postgresql 8.2 :(

С List.Generate получилось, на сервере успешно отработало...
В коде ниже left и right поменяны на drop и make, и еще в процессе пришлось добавить значения null в left_id
Скрытый текст
 
Андрей VG, Спасибо, Вам, за помощь!
 
Чисто в целях тренировки сделал
два варианта на List.Generate
Почему-то думаю, что второй побыстрее
F1 творит чудеса
Страницы: 1
Наверх