CC BY
21УДК 004.421: 339.5
АВТОМАТИЗАЦИЯ РЕШЕНИЯ ЗАДАЧ ДЛЯ ИНФОРМАЦИОННО-АНАЛИТИЧЕСКОГО ОБЕСПЕЧЕНИЯ ДЕЯТЕЛЬНОСТИ ТАМОЖЕННЫХ ОРГАНОВ С ИСПОЛЬЗОВАНИЕМ SQL-ЗАПРОСОВ
Сальников В.И.
Санкт-Петербургский имени В.Б. Бобкова филиал Российской таможенной академии
AUTOMATION OF PROBLEM SOLVING FOR INFORMATION AND ANALYTICAL SUPPORT OF CUSTOMS AUTHORITIES USING
SQL QUERIES
Salnikov V.I.
St. Petersburg named after V.B. Bobkova branch of the Russian Customs Academy
Аннотация
В статье представлен алгоритм, разработанный для анализа данных таможенных деклараций на основе построения запросов, сформулированных на языке SQL. Алгоритм предназначен для построения списка деклараций, вместе с которыми подано или недостаточное количество документов или документы, названия которых отличаются от установленных.
Данный алгоритм позволяет автоматизировать и ускорить контроль деклараций на товары, на которые установлены ограничения и требуется предоставление определенной разрешительной документации.
Ключевые слова: таможенное дело, анализ данных, база данных, SQL-запросы.
Abstract
The article presents an algorithm developed for the analysis of customs declaration data based on the construction of queries formulated in SQL. The algorithm is designed to build a list of declarations, along with which filed or insufficient number of documents or documents whose names differ from the established.
This algorithm allows you to automate and speed up the control of declarations for goods that are subject to restrictions and require the provision of certain permits.
Keywords: customs affairs, data analysis, database, SQL queries.
Важным направлением деятельности таможенных органов является анализ информации, содержащейся в базах данных таможенных деклараций, с целью обеспечения соблюдения мер таможенно-тарифного регулирования. Для этого необходимы знание основ работы с электронными таблицами, системами управления базами данных и умение строить поисковые запросы на выборку данных для последующего анализа. Технология использования электронных таблиц для автоматизации расчета таможенных платежей рассмотрена в работах [1,2], технология построения автоматизированных систем принятия решений представлена в работе [3]. В работе [4] разработана технология анализа данных таможенной статистики.
Однако поиск данных не всегда ограничивается простыми запросами на выборку данных. Для выполнения эффективного
анализа необходимо уметь строить сложные запросы на основе подзапросов, запросы с вычислениями и группировкой, использовать в запросах логические и текстовые функции. Это необходимо для автоматизации работы с большими объемами информации, поскольку анализ данных вручную требует значительных временных и трудовых затрат. Изучению практических приемов построения и анализа реляционных баз данных посвящена работа [5].
Должностные лица таможенных органов получают исходные данные для анализа в результате выполнения запросов к базе данных деклараций на товары. Для выполнения анализа полученные данные необходимо подвергнуть предварительной обработке, группировке, сводке и выборке. Некоторые классы аналитических задач можно решить, используя средства систем управления базами данных MS Access, для
ЭКОНОМИЧЕСКИЕ НАУКИ
(А] А - Поиск деклараций с неправиль.. - п
I Главная Создание Внешние данные Работа с базами н Поля Таблица
W &
декларации
номер строки - №_,цеил - товар - кат_товара - Пред_,цок - Ц
1 111 А К1 Д1от
2 111 А К1 Д2от
3 111 А К1 ДЗ №
4 111 Б К2 Д1от
5 111 Б К2 Д5от
6 111 В КЗ Д4от
7 222 А К1 Д1 №
В 222 А hi Д2 №
9 222 Б К2 Д5 №
10 333 А hi Д5№
11 444 Б К2 Д1 №
12 444 Б К2 Д4№
* (№)
Запись: и
1 из 12
► ПК
VI Нет фильтра Поиск
Режим таблицы
Num Lock Е Л
Рис. 1. Таблица, отображающая данные деклараций
чего достаточно выполнить импорт таблиц в данное приложение и построить необходимые запросы. Преимущество данного способа решения аналитических задач объясняется поддержкой MS Access средств графического интерфейса, что делает построение запросов наглядным процессом.
Другой, универсальный способ решения аналитических задач основан на построении запросов непосредственно к базе данных деклараций с использованием языка SQL. Решение о выборе того или иного способа должно приниматься исходя из принципа минимизации трудоемкости работы.
В ходе обучения должностных лиц таможенных органов приемам работы с MS Access и SQL, автором проведен опрос обучающихся, в результате которого были выявлены классы сложных и трудоемких задач, с которыми приходится сталкиваться таможенникам, занимающимся информационно-аналитической работой. Наиболее актуальной из этих задач представляется задача поиска сведений о товарах, на которые предоставлены не все разрешительные товаросопроводительные документы.
При осуществлении выборки из базы данных деклараций по каждому предоставленному документу формируется отдельная запись, поэтому данные о номере де-
кларации и о наименовании товара повторяются в нескольких записях. В декларации могут быть заявлены несколько товаров, для которых предоставляются разные разрешительные документы. Для решения задачи требуется составить, по крайней мере, две связанные таблицы.
В первую таблицу помещается выборка из базы данных деклараций, по которой необходимо выполнить анализ. Для выборки необходимы такие данные, как номер декларации, наименование товара, категория товара, предоставленный документ и др. Категория товара требуется как признак классификации, исходя из которого формируются требования к количеству и составу разрешительных документов. В качестве категории может выступать, например, код ТНВЭД.
Пример таблицы с условными данными в формате MS Access показан на рис. 1.
Во вторую таблицу записывается количество и наименования документов по каждой категории товара. Вторая таблица должна быть заполнена постоянными данными, поскольку список документов для каждой категории остается постоянным. Количество полей таблицы должно быть больше максимально возможного количества документов на два-три поля.
Пример второй таблицы с условными данными показан на рис. 2.
Рис. 2. Таблица, отображающая данные о количестве и видах документов
по категориям товаров
Ш |
^ - Поиск деклараций с неправил,..
I Главная Создание Внешние данные Работа с базами данных ч? 0
Количество документов го категориям
111 HI
№_декл - кат_товара - товар - Соип1-Пред_док -
А 3
Б 2
В 1
А 2
Б 1
А 1
Б 2
111 И 2
111 ИЗ
222 HI 222 И 2 333 HI 444 К 2
Запись: м 1 из 7
Я Нет фильтра |
Режим таблицы
Nurn Lock
. SQL %
Рис. 3. Результат выполнения запроса, подсчитывающего количество фактически предоставленных документов по каждой декларации, категории товара и товару
В первую таблицу данные будут подгружаться путем импорта. Вопрос разбивки по категориям тоже представляет некоторые трудности, поскольку описывать список документов для каждого кода ТНВЭД является трудоемкой процедурой. Целесообразно выбрать лишь некоторые группы или позиции, по которым проводится проверка. Соответственно и выборка, подгружаемая в первую таблицу, должна содержать только указанные позиции. Это первое ограничение данного метода. Связь должна быть установлена между полем «Код» таблицы «Категории» и полем «Кат_товара» таблицы «Декларации».
При анализе теоретически возможны две ситуации: представлено документов меньше, чем требуется, или представлены
не все те документы, которые требуются. Сначала на основе таблицы «Декларации», необходимо создать запрос с группировкой по декларациям, категориям и товарам, подсчитывающий количество предоставленных документов. Для такого подсчета используется групповая операция Count в поле «Пред_док». В режиме SQL запрос записывается следующим образом:
SELECT декларации.[№_декл], декла-рации.кат_товара, декларации.товар, Count(декларации.Пред_док) AS [Count-Пред_док]
FROM декларации
GROUP BY декларации.[№_декл], де-кларации.кат_товара, декларации.товар;
Результат выполнения запроса представлен на рис 3.
[A] Microsoft Access - Поиск деклараций с неправильными и не.,. _ п
» I ^р Недостающие доку менты X
X S №_декл - категория - товар - Соип1:-Пред_док кол_док -
ZT Е ДЭЗ Ki А 2 3
X m ч in кг Б 1 2
X J 333 К1 А 1 3
с
ё Запись: н 1 из 3 ► м Нет фильтра Поиск
Режим таблицы | Nurm Lock i В ffi Ш SOL Ы
|
Рис. 4. Результат выборки деклараций с недостающими документами
На основе указанного запроса и таблицы «Категория» создается запрос «Недостающие документы» на выборку деклараций, по которым количество документов меньше установленного. В запросе устанавливается связь между полем «Код» таблицы «Категории» и полем «Кат_товара» из предыдущего запроса. В условии отбора для поля, в котором подсчитано количество представленных документов, необходимо вписать условие меньше значения поля «Кол_док» из таблицы Категория.
В режиме SQL данный запрос имеет
вид:
SELECT [Количество документов по ка-тегориям].[№_декл], Категории.категория, [Количество документов по категориям].то-вар, [Количество документов по категориям]. [Сои^-Пред_док], Категории.кол_док
FROM [Количество документов по категориям] INNER JOIN Категории ON [Количество документов по категориям].кат_товара = Категории.код
WHERE ((([Количество документов по категориям].[Сои^-ред_док])<[Кол_док]));
В результате выполнения запроса будут отобраны записи, для которых документов не хватает по количеству.
Результат выборки представлен на рис. 4. Как видно из рисунка, в данном примере найдены 3 записи: в одной декларации найдены 2 товара и еще в одной декларации - один товар.
Далее необходимо отобрать записи, для которых представлены не те документы, которые требуются. Для этого формируется запрос на основе таблиц Декларации и Категории, из которых выбираются те же поля, что и в предыдущем запросе. В запрос добавляется вычисляемое поле, ука-
зывающее правильный ли документ представлен или нет. Для вычисления используется логическое выражение:
Правильный: ПЩПред_док] In ([Док1];[Док2];[Док3]);1;0)
Смысл выражения заключается в следующем: если предоставленный документ соответствует одному из документов, указанных в полях таблицы Категория, то выводится 1 (истина), если не соответствует, то 0 (ложь). В данном случае представлен массив из 3-х документов.
Следует отметить, что полное название документа не будет соответствовать его названию в таблице «Категории», если полное название содержит дату и номер документа. Поэтому для сравнения можно взять часть названия документа, используя строковые функции Left и Instr. Данные функции позволяют выделить из строки часть названия документа до первого пробела, поэтому следующее выражение будет работать, если в полном наименовании документа есть пробел:
Правильный: М^еА([Пред_док];^г(1;[Пред_док];" ")) In ([Док1];[Док2];[Док3]);1;0)
Однако при этом в названии вида документа не должно быть пробелов, чтобы можно было гарантированно отделить название вида документа от его остальных реквизитов. Это второе ограничение данного метода. Частично эта проблема может быть решена использованием другого разделителя, отличного от пробела, или их сочетанием. Также удобным для сравнения является использование кода видов документов вместо их названий.
После записи формулы в указанном вычисляемом поле необходимо установить
® Microsoft Access - Поиск деклараций с неправильными ... - п
» I Неправильные документы X
X X №_декл - категория - товар Пред_док - Правильный -
Е 333 К1 А Д5 № 0
X ffl ig 444 Н2 Б Д4№ 0
X J Ж
Б ■g Запись: H 1 из 2 ► M ► Нет фильтра Поиск
Режим таблицы Num Lock M А 1й
Рис. 5. Результат выборки декларации с несоответствующими документами
SI Microsoft Access - Поиск деклараций с неправильны
» < JH Декларации с неправильными и недостающими X
X №_декл - Категория товар
ZT ■g И К1 А
X m 222 Н2 Б
ig X 333 К1 А
j Б 444 Н2 Б
ig ё
о Запись: И 1 из 4 ► И А ч Нет фильтра Поиск
Режим таблицы Num Lock (MJffl (Ш, ï» .:
Рис. 6. Результат выполнения запроса на объединение данных из двух запросов
условие отбора - 0, чтобы выбрать декларации, где обнаружены несоответствия. В режиме SQL запрос имеет вид:
SELECT декларации.[№_декл], Катего-рии.категория, декларации.товар, деклара-ции.Пред_док,
М^еА([Пред_док],^г(1,[Пред_док]," ")) In ([док1],[док2],[док3]),1,0) AS Правильный
FROM декларации INNER JOIN Категории ON декларации.[кат_товара] = Катего-рии.код WHERE
(((И^еАаПред_док],1п^г(1,[Пред_док]," ")) In ([док1],[док2],[док3]),1,0))=0))
ORDER BY декларации.[№_декл], Кате-гории.категория;
Результат выполнения запроса представлен на рис. 5.
На рис. 5 отображены сведения о двух декларациях, вместе с которыми предоставлены несоответствующие документы.
Таким образом, получены два списка деклараций, в которых обнаружены несоответствия. Их можно объединить, используя запрос на объединение. Запишем данный запрос на языке SQL:
Select [Номер декларации], Категория, Товар from [Недостающие документы] UNION select [Номер декларации], Категория, Товар from [Неправильные документы].
В результате выполнения данного запроса получим полный список деклараций, в которых обнаружены несоответствия. Если несоответствия обнаружены как по количеству, так и по виду документа, то соответствующая запись будет выведена однократно (без дублирования).
Результат выполнения запроса представлен на рис. 6.
Таким образом, представленный алгоритм построения запросов к базе данных позволил построить список деклараций, вместе с которыми подано или недостаточное количество документов или документы, названия которых отличаются от установленных. Данный алгоритм позволяет автоматизировать и ускорить контроль деклараций на товары, на которые установлены ограничения и требуется предоставление определенной разрешительной документации.
ЭКОНОМИЧЕСКИЕ НАУКИ
Список литературы
1. Сальников В.И. Методика формирования общих профессиональных компетенций студентов на примере алгоритмизации расчета таможенных платежей // Ученые записки Санкт-Петербургского имени В.Б. Бобкова филиала РТА. 2016. № 4 (60). С. 109-117.
2. Сальников И.А., Сальников В.И. Алгоритмизация расчета таможенных платежей при ввозе товаров на таможенную территорию Таможенного союза физическими лицами // Ученые записки Санкт-Петербургского имени В.Б. Бобкова филиала Российской таможенной академии. 2018. № 2 (66). С 102-108.
3. Сальников В.И. Построение систем принятия решений на основе операций алгебры логики // Ученые записки Санкт-Петербургского имени В.Б. Бобкова филиала Российской таможенной академии. 2019. № 2 (70). С. 101-107.
4. Сальников В.И. Прогнозирование на основе мультипликативной и множественной регрессионной модели сезонности // Бюллетень инновационных технологий. 2019, № 3 (11). С. 50-55.
5. Сальников В.И. Построение баз данных Microsoft Access: учебное пособие. Санкт-Петербург, Российская таможенная академия: РИО Санкт-Петербургского имени В.Б. Бобкова филиала, 2018. 114 с.
Поступила в редакцию 07.11.2019
Сведения об авторе:
Сальников Владислав Игоревич - старший преподаватель кафедры таможенного дела Санкт-Петербургского имени В.Б. Бобкова филиала Российской таможенной академии, кандидат экономических наук, e- mail: visrta@yandex.ru
Электронный научно-практический журнал "Бюллетень инновационных технологий" (ISSN 2520-2839) является сетевым средством массовой информации регистрационный номер Эл № ФС77-73203 по вопросам публикации в Журнале обращайтесь по адресу bitjournal@yandex.ru
