ПРИМЕНЕНИЕ МЕТОДА АГРЕГИРОВАНИЯ, ГРУППИРОВКИ И ОБЪЕДИНЕНИЯ ДАННЫХ С ПОМОЩЬЮ ЯЗЫКА ЗАПРОСОВ SQL В ЦЕЛЯХ АНАЛИЗА ПОВЕДЕНИЯ ПОЛЬЗОВАТЕЛЕЙ

АННОТАЦИЯ

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

ABSTRACT

This article describes data aggregation, grouping and union method using the SQL structured query language in order to analyze the behavior of users using the example of phone duration data. The result includes the analysis of the features of the SQL query language and a detailed description of the algorithm for finding all users that meet predefined criteria. The SQL language is used to create, modify and manage data in relational databases, so the method described in this article can easily be adapted for a wide range of such tasks.

Ключевые слова: агрегирование данных; группировка данных; анализ данных; язык структурированных запросов; sql; база данных; система управления базами данными; алгоритм; реляционная база данных; анализ поведения.

Keywords: data aggregation; data grouping; data analysis; structured query language; sql; database; database management system; algorithm; relational database; behavior analysis.

Актуальность проблемы

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

Сегодня на помощь решения таких задач призваны прийти системы управления базами данных СУБД – «совокупность программных и лингвистических средств общего или специального назначения, обеспечивающих управление созданием и использованием баз данных» [1].

Для манипуляции данными в таких системах необходимо использовать специальный язык запросов SQL. Язык SQL является инструментом, предназначенным для организации, управления, выборки и обработки информации, содержащейся в базе данных [2]. Его освоение – задача не всегда нетривиальная, но зная особенности построения блоков запросов данного языка, эта задача сильно упрощается.

Описание предложенного решения

Рассмотрим особенности построения блоков запросов языка SQL и применения метода агрегирования и группировки данных на примере двух таблиц данных:

• phones (name, phone_number)
• calls (id, caller, callee, duration)

Рисунок 1. Описание схемы таблиц базы данных

Каждая строка в таблице данных phones содержит информацию о клиенте: его имя и уникальный номер телефона. У каждого клиента может быть только один телефонный номер.

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

Чтобы найти всех клиентов (агрегировать данные по заранее заданным критериям), разговор которых с другими абонентами составил не менее определенного количества времени в минутах, необходимо воспользоваться методом группировки и объедения данных.

• Для группировки данные воспользуемся таким оператором SQL как GROUP BY, а для соединения данные воспользуемся таким оператором SQL как UNION ALL [3].
• Для объединения данные воспользуемся таким оператором SQL как INNER JOIN _ ON _ [4].

В результате предложенный алгоритм соединения, группировки и объединения данных в одной из самых распространенных СУБД PostgreSQL [5] может выглядеть следующим образом:

Рисунок 2. Предложенный алгоритм агрегирования и группировки данных

Рассмотрим выполнение и работу алгоритма на примере следующих тестовых данных:

Рисунок 3. Тестовые данные таблиц базы данных

Алекс говорил четыре раза, и общая продолжительность его звонков составила 7+1+1+1 = 10 минут. Лера говорила четыре раза, и общая продолжительность ее звонков 4+3+1+1 = 9 минут. Джен говорил дважды и общая продолжительность его звонков 1+4 = 5 минут. Диана говорила четыре раза и общая продолжительность ее звонков 7+1+3+1 = 12 минут.

Рисунок 4. Результат работы алгоритма на примере тестовых данных

Легко увидеть, что Алекс и Диана говорили по крайней мере 10 минут.

Вывод

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

Список литературы:

1. ГОСТ Р ИСО МЭК ТО 10032-2007: Эталонная модель управления данными. // Стандартимформ. 2009 – С. 3.
2. Джеймс Р. Грофф, Пол Н. Вайнберг, Эндрю Дж. Оппель. SQL: полное руководство, 3-е издание.  // М.: «Вильямс», 2014. – С. 30.
3. К. Дж. Дейт. Введение в системы баз данных. // М.: Вильямс, 2005. – С. 315–330.
4. Крис Фиайли. SQL: Руководство по изучению языка. // М.: Peachpit Press, 2003. – С. 241–264.
5. PostgreSQL. Powerful, open source object-relational database system. / [Электронный ресурс]. – Режим доступа: https://www.postgresql.org/ (дата обращения: 05.11.2022).