Data Mining. Оптимизация заказов товаров в аптеке (аптечном пункте)

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

Мы живем в современном мире, где рутинные операции за нас выполняет компьютер. Поэтому Вы можете сказать: «Давайте используем компьютер, и он сделает всю черную работу за нас!». «У вас же есть база данных, содержащая статистику продаж различных лекарств?» – продолжите Вы – «Так почему же не использовать эту статистику для прогноза продаж и создания автоматической заявки на требуемые препараты?»

Да, в первом приближении вы будете правы. Такие решения есть в программных комплексах, автоматизирующих аптеки в России. Но есть одно очень большое «НО». Все эти решения не будут корректно работать, пока Вы не создадите группы товаров.

Объясню: Есть препарат: «Донормил 15мг Таб. Х30» производства Upsa Laboratoir Франция и «Донормил таб. 15 мг № 30» Aventis/Bristol-Myers Squibb – Франция. В базе данных это совершенно два разных препарата имеющих разный идентификатор и разное название, НО это одно и тоже. Если Вы будете учитывать статистику по этим двум разным товарам – то вы получите не правильный результат.

Для того чтобы получить достоверную информацию по движению и необходимости заказа товара необходимо создавать группы идентичных товаров. Как правило, создавать группы нужно вручную и обработать большое количество записей. Если есть выделенный сотрудник, который может в течение долгого периода времени работать над наполнением справочника «Группы товаров», то это более-менее реально (однако следует учитывать, что при поступлении новых товаров, справочник нужно постоянно дорабатывать). В рамках небольшого аптечного пункта, где обычно работают два-три человека за прилавком, такой возможности просто нет.

Встает вопрос: «Какие есть алгоритмы позволяющие находить одинаковые лекарственные препараты?». Первое, что приходит на ум — это расчет расстояния Левенштейна. Но тут мы сталкиваемся с ограничением данного алгоритма — расстояние Левенштейна для разных товаров «Линекс капс. х16» и «Линекс капс. х32» (расстояние равно двум), меньше чем у одинаковых товаров «Линекс капс. х16» и «Линекс N16 капс» (расстояние равно девяти). Проблема в том, что поставщики могут менять слова местами, заменять сокращения количества (кто-то пишет №, кто-то N, кто-то X), объема и т.п. Объединить одинаковые товары с помощью штрих кода невозможно. Одинаковые препараты, произведенные разными фабриками, имеют разный штрих код. Более того, один и тот же препарат, производимый на одном заводе, может иметь другой штрих код после прохождения перерегистрации.

После долгих поисков я пришел к следующему алгоритму поиска одинаковых препаратов:

1. Для первого приближения (поиска «похожих» препаратов) используется алгоритм N-Грамм. Этот алгоритм составляет все возможные комбинации подстрок, с длиной вплоть до указанной, и подсчитывает их совпадения. Количество совпадений, разделенное на число вариантов, объявляется коэффициентом схожести строк для фиксированного N (я выбрал значение 3) и выдается в качестве результата работы функции.

Например, для «Линекс капс N16» и «Линекс N16 капс», строки разбиваются на 3-граммы:

Сравниваемая подстрока Подстроки второй строки Совпадения Кол-во совпадений Кол-во вариантов Коэффициент похожести
Лин Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
ине Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
нек Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
eкс Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
кс Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да 11 13
с к Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Нет
ка Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
кап Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
апс Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
пс Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Нет
с N Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
N1 Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
N16 Лин, ине, нек, екс, кс, с N, N1,N16,16 ,6 к, ка, кап, апс Да
(11+12)/(13+13)=0,88
Лин Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
ине Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
нек Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
екс Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
кс Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
с N Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
N1 Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да 12 13
N16 Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
16 Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
6 к Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Нет
ка Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
кап Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да
апс Лин, ине, нек, екс, кс, с к, ка, кап, апс, пс, с N, N1, N16 Да


Результат равен 0,88.

Таким образом, мы объединяем препараты, для которых ключевые слова поменяны местами. Однако у данного алгоритма есть два недостатка:
а) Алгоритм объединяет препараты с «x10» и «x20», «г.» и «мг.» и т.д.;
б) Резко возрастает нагрузка на базу данных — словарь грамм занимает очень большой объем. Например, для справочника товарно-материальных ценностей объемом примерно 30 тысяч записей, словарь 3-грамм содержит 900 тысяч записей. Для прайса в 47 тысяч записей (объединенный прайс от нескольких поставщиков), словарь содержит уже 1,7 миллиона записей.

2. Для окончательного наполнения справочника «Группы товаров» (после которого требуется работа человека) исходный справочник материальных ценностей разбивается на словарь слов. Создается мета-справочник «ключевых» свойств препаратов:
а) Объем;
б) Количество;
в) Количество активного вещества;
г) Лекарственное вещество;
д) Цвет, вкус;
и т.д.

Мета-справочник, содержит данные о синонимах, соответствиях (например: г = 1000 мг) и способе поиска свойств в словаре. Исключив среди «похожих» препаратов (результат работы первого алгоритма) препараты для которых различаются «ключевые» свойства мы получим справочник «Группы товаров».

Указанный алгоритм позволяет автоматически наполнить справочник «Группы товаров», который уже в дальнейшем редактируется пользователем.

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

Формулы выглядят так:




где и принимают значения из диапазона [0;1]
y — реальное количество продаж;




Для прогнозирования следующего значения используется формула:

Для прогнозирования нескольких значений:


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

В школе, когда я только изучал Fortram, Папа купил книгу Дж. Форсайта «Машинные методы математических вычислений». Помню свое удивление от первого знакомства с вычислением с плавающей точкой, понятие «Машинное эпсилон». Вспомнив, про эту книгу, я нашел в ней алгоритм поиска минимума, но только для функции одной переменной. Для функции нескольких переменных автор отсылал читателя к недоделанному (1977 год, на момент написания книги) пакету MINPACK из Национальной лаборатории в Аргоне. Представляете мое удивление, когда я нашел данный пакет, написанный на Fortran'е, нашел пакет MINPACK C/C++ и пообщался с автором «перевода» с Fortran'а на C.

На сегодняшний момент для прогноза продаж лекарств, мною реализован программный комплекс, состоящий:
а) Библиотеки для MS SQL сервера (dll), реализованной в виде расширенных хранимых процедур, написанных на C++ и реализующих расчет прогноза;
б) Базы данных MS SQL сервера, содержащей справочники словарей, метаданные и хранимые процедуры: расчета прогноза, сопоставления товарно-материальных ценностей и прайс-листа и другие.
в) Клиента, в котором пользователь делает прогноз, работает со справочниками и прайс-листами.

Внедрение программного комплекса в разы ускорило работу по заказу товаров и повысило эффективность работы аптеки. И что более главное — вернуло жену в семью!

Реализованный мною программный комплекс жестко привязан к одному вендору, автоматизирующему аптечный бизнес на территории России. Используя универсальность подхода, можно реализовать аналогичное решение и для других программных продуктов. Указанный алгоритм можно применить не только в аптечном бизнесе, но и в любом другом, где есть большая номенклатура однотипных товаров, и есть необходимость в объединении справочника товарно-материальных ценностей и прогнозе продаж в условиях жесткой конкуренции и постоянного изменения рынка.
Поделиться публикацией

Комментарии 25

    +3
    MS SQL Data Quality Services не помогли в разборе и дедупликации?
      +6
      Не хочу вас расстраивать, но все препараты давно уже отсортированы по МНН в достаточно известных справочниках (доступных как базы данных).
      Надо было начать с матчасти, прежде чем городить свои велосипеды :)
        +1
        Поддерживаю, но объединять по МНН тоже не серебряная пуля.
        Ну вот например МНН Дротаверина Гидрохлорид.
        Под него попадет как собственно сам Дротаверина Гидрохлорид, так и Но-Шпа а продаются они ой как по разному.
          +1
          Думаю стоит взять справочник типа Видаль, построить словарь наименований с формами но без дозировок.
          И сводить отплясывая от него, это возможно будет понадежнее чем N-граммы или расстояние редактирования.
            0
            Хотя я наверно не прав, без дозировок тоже не обойтись
            +4
            Соглашусь с Вами, что я не большой специалист в Фармацевтике. Я как, Вы говорите, начал с поиска международных баз данных, содержащих МНН, начал изучать SPARQL. Но потом столкнулся с жестокой реальностью: в нашей покупной базе МНН проставлен только для 70% товаров.
            К тому же примерно треть продаж в аптеке приходится на не лекарственные препараты (БАДы, косметика и т.д.) которые просто не имеют МНН.
            Буду признателен, если приведете пример «известных справочников (доступных как базы данных)», за исключением ГРЛС.
              0
              На самом деле у этой задачи нет простого и дешевого решения.
              Без ручного сведения все равно не обойтись, можно только сократить объем этой ручной работы.

              Можно распердоливать сокращения, сводить по МНН Видалю и всему такому с применением LDA итд — но счастья не будет.
                0
                Так я и не говорю, что это 100% решение. После работы алгоритма — провизор все равно руками работает со справочником «Группы товаров», добавляя новое или удаляя лишнее (что напортачил алгоритм :)). НО это лучше чем работать с нуля.
                  0
                  Простое и дешевое решение есть.
                  Берете полный ассортиментный план какого-нибудь федерального дистрибьютора (например Протека). Каждая позиция его АП содержит код — принимаете его основным.
                  АП каждого последующего поставщика привязываете к кодам прайса ранее определенного основным. Делаете это через таблички соответствия кодов которые есть практически у каждого поставщика или дистрибьютора*. В результате — у вас привязаны и Донормил 15мг Таб. Х30» производства Upsa Laboratoir Франция и «Донормил таб. 15 мг № 30» Aventis/Bristol-Myers Squibb – Франция к одному и тому же коду. Сравнение цены у разных поставщиков таким образом становится тривиальной задачей.

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

                  *Не факт что вам их дадут сразу, но всегда есть варианты.
                    0
                    Не подскажете, как обычная аптека может выбросить у того же протека инструмент, который позволит аптеке всегда заказывать препараты по самой низкой цене, а не у протека? При том, что фармдистрибьютеры отлично понимают, что именно системы электронного заказа уронили маржу из-за безумно жёсткой ценовой конкуренции. Особенно в протеке, который этого джинна и выпустил из бутылки.
                      0
                      Кстати, имеет смысл поинтересоваться ценами и условиями у Пермского QWERTY
                      Возможно это будет оптимальным решением.
                        +1
                        Протек приведен лишь в качестве примера. А вообще, есть универсальный алгоритм: если с вами не хочет работать организация — работайте с конкретным человеком в организации.
                  0
                  А что за справочник?
                  +2
                  я правильно понимаю, что для справочника групп товаров по сути был переписан стандартный модуль триграмм pgtrgm для MS SQL?

                  www.postgresql.org/docs/9.1/static/pgtrgm.html
                    0
                    Нужен еще справочник синонимов.
                    Решения подобного типа есть уже лет так 15, а вы опять с нуля…
                      0
                      Решения подобного типа есть уже лет так 15, а вы опять с нуля…

                      Может решение где-то и есть, но на фармацевтическом рынке России, в программных комплексах: «е-Фарма» (Спарго Технологии), «Эприка», «Юнико», «1С-Рарус» я этого не нашел :(
                        0
                        А не смотрели в сторону онлайн-сервисов, например inpos.ru/? Они активно работают с аптеками — т.е. ассортимент и связки у них уже есть.
                        Я ковырял немного — кассовая часть сыровата, но активно допиливается, аналитическая часть уже работает хорошо.
                      0
                      Я не знаю предметную область, но для группировки помимо уже названных способов первое, что приходит в голову — взять состав двух ЛС, отсортировать и «выровнять» как выравниваются ДНК. Иначе говоря, использовать алгоритм сравнения ДНК, где вместо оснований — составляющие ЛС. Это, конечно, относится только к тем товарам, что имеют состав.
                        0
                        Подход интересный, но в прайсах не указывается состав ЛС.
                        +1
                        Для решения поставленной задачи есть dqs в ms sql. Даже если он не подходит по причине лицензий, то можно воспользоваться fts, который есть даже в express. Можно банально вытащить термы в таблицу и по совпадениям термов находить похожие препараты. Вообще стоит изучить возможности text mining в ms sql, а потом уже лепить свои велосипеды.

                        Тоже самое касается и предметной области. Для лекарств уже есть готовые справочники и «инвариантные» наименования.

                        Описание алгоритмов у вас хорошее, но предметная и техническая область выбраны неверно, ибо в них проблема давно решена.
                        +3
                        Для сравнения строк, в которых меняются не отдельные символы, а сразу токены, хорошо подходит Расстояние Жаккара.
                        image
                        Сначала бьем две строки на токены, а потом делим количество совпавших на количество уникальных токенов в обеих строках.
                          +1
                          >В базе данных это совершенно два разных препарата имеющих разный идентификатор и разное название, НО это одно и тоже.
                          У вас ассортиментный план кривой, собственно и все. Обычно все живут через привязки того же Протека.
                            0
                            все живут через привязки того же Протека

                            Но не все же работают в еФарма :) И заказывают товар только через Эприку.
                            Мы заказываем товары не только в Протеке – в Катрене, Роста, СИА, Пульс, Альянс Хелскеа, Vichy(Мир лечебной косметики), Стелмас, Omron, и т.д.
                            Как вы видите из списка, так же как и все остальные аптеки мы торгуем, не только фармацевтическими препаратами, но и косметикой, медицинским оборудованием и т.п.

                            При заказе из сводного прайс листа нужно выбрать товар с минимальной ценой и при этом с подходящим срокам годности.

                            О том как работает еФарма знаю только из презентаций. Буду признателен, если расскажите как в ней происходит создание автоматического заказа?

                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                            Самое читаемое