У нас регулярно возникает задача сопоставления списка телефонных номеров наших любимых клиентов с бездушными данными, лежащими в ненавистной CRM. Про два таких случая я уже писал (Как извлечь деньги из логов очередей телефонной станции Астериск с помощью QlikView и  Коллтрекинг и аллитерирующиеся продажи). CRM система крутится на боевом сервере и использовать его ресурсы для обсчета никак нельзя, а вдруг упадет? Поэтому задачи решаем с учетом ограничений наших ресурсов. Знакомый расклад?

Да, мы помним, что есть perl, регекспы, а первые компьютеры вообще имели 640КБ памяти. Но этот блог посвящен QlikView, поэтому постараемся показать подходы к решению этой задачи в QlikView.

Два об одном

Имеем две таблицы. В первой список телефонов клиентов с нумерацией вида:

Во второй, данные нашей CRM c идентификатором клиента и его номерами записанными в полном соответствии с фантазиями операторов, вида:

Коротко, стоит задача автоматически заджойнить эти таблички, чтобы затем произвести тонкий анализ телефонных звонков и сделать глубокомысленные выводы. Задача навести порядок в CRM тоже стоит, но не у нашего подразделения. Сроки, ресурсы и модели мотивации неизвестны. Риск допустим.

Я не знаю как идет сигнал

Медитировать над первой таблицей особого смысла нет. Телефонные номера всегда в закрытом плане (http://ru.wikipedia.org/wiki/Телефонный_номер) — нотации в которой номера всегда  начинаются с 7, с заграницы нам не звонят — у нас нет там клиентов. Короткие (либо длинные) номера  возникают в случае ошибки определителя номера, либо такой CallID отдает оператор связи, они не несут информации, их сравнительно мало и ими можно и нужно пренебречь.

Заготовка под загрузку будет выглядеть так:

Я не знаю принципа связи

Эта табличка заставляет задуматься, вспомнить добрым тихим словом разработчиков и вредненцев CRM. Можно написать регулярные выражения, но для QlikView они не родные и выполняться будут медленно. Поэтому воспользуемся магловской функцией KeepChar(Телефон,’0123456789,;’), которая оставит во второй таблице только цифры и пару разделителей. После её применения таблица будет иметь вид:

Слон, или цикл

Совсем простым INNER JOIN задача не решается — в специально подобранном примере не будет найдено ни одного совпадения (а они там точно есть).

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

Да, мы помним о ложном положительном риске! В нашем примере номер 7-952-959-05-19 может быть неверно привязан к клиенту. И да, «я готов пойти на эту жертву!» (Лорд Фаркуад)

Но как сделать полный перебор всех возможных комбинаций? Воспользуемся циклом! Можно перебирать телефоны из первой таблицы, можно из второй. Здравый смысл подсказывает, чтобы перебор шёл по меньшей таблице. В нашем примере, наоборот! Получилось что-то типа

В результате работы этого скрипта мы получили единственную запись в таблице result

И это действительно правильный ответ!

Преимущество такого скрипта в том, что он не требует много памяти и стабилен. Далее следуют недостатки: на тестовых объемах таблиц (30000 записей в каждой) он выполняется медленно. Действительно, если tmp заполняется 0.1 секунды, то цикл отработает за 3000 секунд, а это 50 минут. Несмотря на то, что QlikView выполняет запросы на всех ядрах процессора (LOAD * WHERE), обрамляющий скрипт (DO … WHILE) выполняется на одном ядре.

В 2015 году удалось поработать за компьютером с 3ГБ памяти под XP. Как вы понимаете, приложению выделялось не более 2ГБ, поэтому остро стоял вопрос экономии памяти. Данные разбивались на мелкие порции только чтобы впихнуть невпихуемое.

Кит или join

Как бы избавиться от цикла, и заставить работать все ядра процессора? Цикл решает задачу перебором. В том, чтобы составить все возможные комбинации нам поможет JOIN.

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

И опять получаем правильный ответ:

Этот скрипт является настоящим пожирателем памяти. На тесте (30000 записей в каждой из таблиц) приложение заняло 9ГБ памяти, зато исполнялось около 5 минут. К недостаткам скрипта можно отнести его нестабильность. Если в ваше отсутствие, в CRM подгрузят холодную клиентскую базу,  памяти может просто не хватить и скрипт рухнет.

Какая рыба в океане плавает быстрее всех

Если подумать, то в обоих скриптах QlikView занимается повторением пройденного, каждый раз, заново привязывая телефоны к клиентам. Если сделать промежуточную таблицу и хранить там уже распознанных клиентов, то объем используемых ресурсов (времени либо памяти) резко уменьшится. В этом нам поможет NOT Exist():

Если результаты того, что удалось распознать в очередной итерации сохранять, то объем работы еще немного сократиться. Мы будем анализировать только нераспознанных клиентов, заведенных в CRM:

Далее можно распознавать не все телефоны, а только звонки, совершенные за последние несколько месяцев… Все зависит от реальных задач стоящих перед вами.

Спасибо за рыбу!

История напоминает: победила Англия. Вот так и наш кит, при всех своих недостатках, ушел в продакшн.

Здравый смысл настаивает: в одних случаях важен код, который втискивается в жесткие рамки памяти, в других важна скорость, в третьих простота кода — залог беспроблемной поддержки.

Опыт упорствует: лучше иметь корявый код и решить реальную бизнес задачу, чем иметь офигительную идею, идеальный или совершенный код, через неделю после дедлайна. Не бойтесь писать код под QlikView. Реальные задачи имеют много способов решения. Не существует единственно правильного. Это жизнь, Карл!