Обновить

Статистика в кармане: Портативная СУБД с базой Росстата для исследователей и не только

PythonPostgreSQLАдминистрирование баз данныхBig Data
image

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

Государственный статистический учет в России


Статистика вчера и сегодня


Статистические данные по нашей стране собираются довольно давно: экономические, социальные и иные показатели собирались и публиковались еще при царской власти, затем более масштабно — при советской власти и, наконец, в XXI веке (особенно с переходом на электронные базы данных) российская статистика окончательно приобрела свои современные очертания. В России сбором и обработкой статистических данных занимается Федеральная служба государственной статистики, она же просто Росстат. Своей основной целью эта служба называет «удовлетворение потребностей органов власти и управления, средств массовой информации, населения, научной общественности, коммерческих организаций и предпринимателей, международных организаций в разнообразной, объективной и полной статистической информации».

Вот как Росстат описывает становление современного статистического учета в Российской Федерации:
Начало ХХI века ознаменовалось для российской статистики принципиальными событиями. Прежде всего, в этот период были заложены единые основы осуществления официального статистического учета, которые были установлены Федеральным законом “Об официальном статистическом учете и системе государственной статистики в Российской Федерации”, принятым 29 ноября 2007 г. (далее – Закон о статистике).

Также в этот период были приняты Федеральный закон “Об информации, информационных технологиях и о защите информации” от 27 июля 2006 г.; Федеральный закон “О персональных данных” от 27 июля 2006г.; Кодекс Российской Федерации об административных правонарушениях от 30 декабря 2001 г. и другие законы и нормативные акты Правительства Российской Федерации, касающиеся правового регулирования официального статистического учета в Российской Федерации.

Основной целью Закона о статистике является создание правовых основ реализации единой государственной политики в области официального статистического учета, направленной на обеспечение информационных потребностей государства и общества в полной, объективной, научно-обоснованной и своевременной официальной статистической информации о социальных, экономических, демографических, экологических и других общественных явлениях в Российской Федерации.

С его принятием получили правовое регулирование многие направления деятельности в сфере официального статистического учета, осуществление которых ранее было затруднено. Установлены правила, соответствующие международным принципам официальной статистики, принятым Статистической комиссией ООН (1994 г.), и механизмы их реализации.

Полный текст исторический справки вы можете прочитать на сайте Росстата.

В 2019 г. Росстат утвердил «Стратегию развития Росстата и системы государственной статистики до 2024 года» (документ можно скачать по ссылке), в которой ставятся следующие задачи:

  1. Цифровизация предоставления статистических данных
  2. Снижение отчетной нагрузки на респондентов
  3. Усиление координации статистической деятельности
  4. Удовлетворение потребностей пользователей и повышение доверия к официальной статистике
  5. Совершенствование статистической методологии
  6. Активизация международного сотрудничества
  7. Внедрение новых методов управления кадрами
  8. Оптимизация административных процессов в Росстате
  9. Реализация проектов национального значения

В частности, за счет цифровизации планируется к 2024 г.:

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

Что касается задачи «удовлетворения потребностей пользователей и повышения доверия к официальной статистике», здесь немаловажно, что наши власти уже задумались о том, как повысить прозрачность и доступность официальных данных для широкой публики в классифицированном цифровом виде:
Разработчикам, общественным организациям, гражданам, журналистам, работающим в новом направлении – журналистике данных, в качестве основы для создания удобных и полезных приложений и сервисов необходима информация, размещаемая в форме открытых данных. Открытые данные, размещаемые в форме набора отдельных показателей, менее полезны и востребованы, чем логически увязанные последовательности (например, построенные в единой методологии временные ряды, ранжированные последовательности).
<...>
Потребуется обеспечить максимальную информационную открытость ведомства. Сегодня недостаточно производить и хранить имеющуюся статистическую информацию. Необходимо использовать все каналы взаимодействия с пользователями для популяризации статистики
и повышения статистической грамотности.

Отсюда постулируются и довольно-таки смелые плановые показатели 2024 г.:

  • уровень удовлетворенности потребителей официальной статистической информации – 90 процентов;
  • постоянное нахождение Росстата в ТОП-10 рейтинга Экспертного совета при Правительстве Российской Федерации по уровню развития механизмов (инструментов) открытости и направлений открытости;
  • доля общедоступных показателей, раскрываемых в формате открытых данных, – 100 процентов.

Единый статистический ресурс — ЕМИСС


Сегодня вся статистическая информация, собираемая Росстатом, хранится в единой базе статистических данных — ЕМИСС, доступной на сайте www.fedstat.ru. По состоянию на момент написания этой статьи, база ЕМИСС содержит 7230 показателей, собранных от 65 государственных ведомств. На главной странице есть диаграмма, показывающая, что ежемесячно обновляется более 1000 показателей.

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

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

(С сайта ЕМИСС)

Для удобства несколько прямых ссылок:


ЕМИСС — довольно гибкая система, которая позволяет скачивать, искать и визуализировать любые данные любым пользователям без наличия регистрации и с расширенными настройками представления (классификация, сортировка, фильтрация и т.д.), а также поддерживает запись статистических данных в базу для пользователей с правами «Эксперта». Но поскольку мы с вами явно не «эксперты», наш удел — скачивание и использование готовых данных :) О чем дальше и будем говорить.

Делаем свой «клон» ЕМИСС


При всех преимуществах «веб-морды» ЕМИСС (которая даже поддерживает контекстный поиск и позволяет выгружать датасеты в формате Excel), у нее есть несколько существенных недостатков, которые вообще присутствуют у подавляющего большинства открытых данных, доступных через веб:

  • Нет возможности составления сложных запросов к базе, в том числе, агрегатных запросов к нескольким датасетам (таблицам)
  • Нет четкого представления о структуре базы и иерархии таблиц, что очень важно для исследовательских целей (т.е. для ответа на вопрос «что я могу извлечь?»)
  • Работа с данными требует постоянного доступа к сети (к сайту ЕМИСС)
  • Возможности аналитики ограничены предоставленными веб-приложением инструментами (нет возможности конфигурации аналитических инструментов и визуализации графиков)

Поэтому у меня, конечно, возникла мысль автоматизировать получение данных с ЕМИСС, что в свою очередь привело к идее создания локальной базы данных в качестве «дубля» ЕМИСС. В сочетании с программным интерфейсом это решение лишено перечисленных выше недостатков. Совершенно инстинктивно мой выбор пал на решение на базе Python + PostgreSQL.

Структура базы данных


Я начал с того, что скачал с ЕМИСС полный перечень доступных датасетов (ссылку приводил выше), а также несколько самих датасетов в формате XML. Подробное описание данных ЕМИСС вы можете прочитать в вышеуказанном Руководстве пользователя, а я лишь приведу граф структуры БД, полученный на основе анализа этих XML (картинка кликабельна).



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

Собственно данные (т.е. численные наблюдения по годам, кварталам и т.д.) находятся в таблице obs ("observations"), которая связана внешними ключами с таблицей датасетов (datasets), классификаций (codevals), единиц измерений (units) и периодов наблюдений (periods).

Таблица датасетов, содержащая информацию о наборах данных, связана с таблицами служб (agencies), подразделений (departments), рубрик (classifier), классификаторов (codes), единиц измерений (units) и периодов наблюдений (periods).

Остальные таблицы являются служебными, и мы не будем на них подробно останавливаться.

Представления


Специально для удобства выгрузки данных в читаемом виде (с раскрытием зависимостей между таблицами) я создал несколько представлений:



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

SELECT * FROM all_datasets WHERE data_start > 0 LIMIT 10;

Результат запроса
select * from all_datasets
where data_start &gt; 0
limit 10
id classifier dataset updated prepared next_update description agency department data_start data_end prep_by prep_contact
1 526 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество должностных лиц, совершивших преступления, освобожденных от уголовной ответственности по нереабилитирующим основаниям 2020-08-27 15:57:49 2020-12-10 05:04:48 2021-03-31 21:00:00 В связи с внесением изменений в форму статистического наблюдения Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 018 2 020 Карпова Ю.А. 8(495)-982-76-43 gosstat.gprf@genproc.ru
1 479 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд 2020-08-27 15:50:08 2020-12-10 05:04:49 2021-03-31 21:00:00 В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru
1 419 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления 2020-08-27 15:48:12 2020-12-10 05:16:15 2021-03-31 21:00:00 Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 011 2 020 Карпова Ю.А. 8-495-982-76-43 gosstat.gprf@genproc.ru
1 335 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления занимающих должности в органах законодательной власти на уровне органа местного самоуправления 2020-08-27 15:48:57 2020-12-10 05:16:17 2021-03-31 21:00:00 Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 011 2 020 Карпова Ю.А. 8-495-982-76-43 gosstat.gprf@genproc.ru
1 172 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления занимающих должности в органах законодательной власти на уровне Российской Федерации 2020-08-27 15:48:39 2020-12-10 05:16:19 2021-03-31 21:00:00 Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 011 2 020 Карпова Ю.А. 8-495-982-76-43 gosstat.gprf@genproc.ru
1 540 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления занимающих должности в органах законодательной власти на уровне субъекта Российской Федерации 2020-08-27 15:48:48 2020-12-10 05:16:21 2021-03-31 21:00:00 Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 011 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru
1 520 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти (за исключением правоохранительных) на уровне органа местного самоуправления 2020-08-27 15:49:57 2020-12-10 05:16:28 2021-03-31 21:00:00 В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru
1 125 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти (за исключением правоохранительных) на уровне Российской Федерации 2020-08-27 15:49:40 2020-12-10 05:16:29 2021-03-31 21:00:00 В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru
1 272 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти (за исключением правоохранительных) на уровне субъекта Российской Федерации 2020-08-27 15:49:49 2020-12-10 05:16:23 2021-03-31 21:00:00 В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). Генеральная Прокуратура Российской Федерации Главное организационно-аналитическое управление ГП РФ 2 016 2 020 Боголюбов А.Е. (495)-982-76-55 kazimirov@genproc.gov.ru
1 167 По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Количество лиц, совершивших преступления занимающих должности в органах исполнительной власти на уровне органа местного самоуправления 2016-02-20 11:25:28 2020-12-10 05:16:40 2021-03-31 21:00:00 Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 011 2 015 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru


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

SQL код представления all_data (если кому интересно)
CREATE OR REPLACE VIEW public.all_data
AS SELECT obs.id,
    ds.id AS ds_id,
    ds.name AS dataset,
    cls.name AS classifier,
    ds.description,
    ds.prep_time AS prepared,
    ds.updated_time AS updated,
    ds.next_update_time AS next_update,
    ag.name AS agency,
    dept.name AS department,
    ds.range_start AS data_start,
    ds.range_end AS data_end,
    ds.prep_by,
    ds.prep_contact,
    obs.obs_year AS year,
    per.val AS release,
    units.val AS unit,
    codes.name AS code,
    codevals.name AS category,
    obs.obs_val AS value
   FROM obs
     JOIN datasets ds ON obs.dataset_id = ds.id
     JOIN classifier cls ON ds.class_id = cls.id
     JOIN agencies ag ON ds.agency_id = ag.id
     JOIN departments dept ON ds.dept_id = dept.id
     JOIN periods per ON obs.period_id = per.id
     JOIN units ON obs.unit_id = units.id
     JOIN codevals ON obs.code_id = codevals.id
     JOIN codes ON codevals.code_id = codes.id
  ORDER BY cls.name, ds.name, codes.name, codevals.name, obs.obs_year, per.val;


Первые 10 наблюдений:

SELECT * FROM all_data LIMIT 10;

Результат запроса
select * from all_data limit 10
id ds_id dataset classifier description prepared updated next_update agency department data_start data_end prep_by prep_contact year release unit code category value
1 752 008 1 526 Количество должностных лиц, совершивших преступления, освобожденных от уголовной ответственности по нереабилитирующим основаниям По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения 2020-12-10 05:04:48 2020-08-27 15:57:49 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 018 2 020 Карпова Ю.А. 8(495)-982-76-43 gosstat.gprf@genproc.ru 2 020 январь-июнь человек РФ Российская Федерация 2 099
1 743 580 1 479 Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). 2020-12-10 05:04:49 2020-08-27 15:50:08 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru 2 016 январь-декабрь единица РФ Российская Федерация 10 907
1 743 579 1 479 Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). 2020-12-10 05:04:49 2020-08-27 15:50:08 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru 2 016 январь-июнь единица РФ Российская Федерация 7 165
1 743 582 1 479 Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). 2020-12-10 05:04:49 2020-08-27 15:50:08 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru 2 017 январь-декабрь единица РФ Российская Федерация 13 122
1 743 581 1 479 Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). 2020-12-10 05:04:49 2020-08-27 15:50:08 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru 2 017 январь-июнь единица РФ Российская Федерация 7 179
1 743 583 1 479 Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). 2020-12-10 05:04:49 2020-08-27 15:50:08 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru 2 018 I полугодие единица РФ Российская Федерация 7 193
1 743 584 1 479 Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). 2020-12-10 05:04:49 2020-08-27 15:50:08 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru 2 018 январь-декабрь единица РФ Российская Федерация 12 770
1 743 585 1 479 Количество должностных лиц, совершивших преступления, уголовные дела и материалы по которым направлены в суд По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности В связи с внесением изменений в форму статистического наблюдения № 1-КОРР «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности» (приказ Генерального прокурора Российской Федерации от 09.03.2016 № 143). 2020-12-10 05:04:49 2020-08-27 15:50:08 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 016 2 020 Карпова Ю.А. (495)-982-76-43 gosstat.gprf@genproc.ru 2 018 январь-декабрь человек РФ Российская Федерация 12 770
1 676 937 1 419 Количество лиц, совершивших преступления По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. 2020-12-10 05:16:15 2020-08-27 15:48:12 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 011 2 020 Карпова Ю.А. 8-495-982-76-43 gosstat.gprf@genproc.ru 2 011 I полугодие человек РФ Российская Федерация 7 098
1 676 939 1 419 Количество лиц, совершивших преступления По федеральному плану статработ / Генеральная Прокуратура Российской Федерации / Информация о преступлениях коррупционной направленности Показатель формы «1-КОРР» (код 599) «Сведения о результатах работы правоохранительных (правоприменительных) органов по борьбе с преступлениями коррупционной направленности», утвержденной постановлением Федеральной службы государственной статистики от 04 мая 2011 г. № 233. Представляют информационные центры МВД, ГУВД, УВД субъектов РФ. 2020-12-10 05:16:15 2020-08-27 15:48:12 2021-03-31 21:00:00 Генеральная Прокуратура Российской Федерации Главное управление правовой статистики и информационных технологий 2 011 2 020 Карпова Ю.А. 8-495-982-76-43 gosstat.gprf@genproc.ru 2 011 январь-декабрь человек РФ Российская Федерация 11 978



Наконец, поскольку представление all_data довольно тяжеловесное, так как серверу необходимо выполнить множество вложенных запросов, я создал «облегченный» вариант — представление data_lite, в котором гораздо меньше выводимых полей.

SQL код представления data_lite (меньше полей = меньше подзапросов)
CREATE OR REPLACE VIEW public.data_lite
AS SELECT DISTINCT obs.id,
    ds.id AS ds_id,
    ds.name AS dataset,
    obs.obs_year AS year,
    per.val AS release,
    units.val AS unit,
    codes.name AS code,
    codevals.name AS category,
    obs.obs_val AS value
   FROM obs
     JOIN datasets ds ON obs.dataset_id = ds.id
     JOIN periods per ON obs.period_id = per.id
     JOIN units ON obs.unit_id = units.id
     JOIN codevals ON obs.code_id = codevals.id
     JOIN codes ON codevals.code_id = codes.id
  ORDER BY obs.obs_year, per.val, codes.name, codevals.name;


Результат запроса
select * from data_lite where year > 0 limit 10
id ds_id dataset year release unit code category value
3 568 133 3 637 Пассажирооборот по видам транспорта общего пользования 1 970 миллиард пассажиро-километров Вид транспорта Автобусный 100,09999847
364 291 307 Грузооборот по видам транспорта 1 970 миллиард тонно-километров Вид транспорта Автомобильный транспорт 116
2 441 822 2 689 Перевозки грузов по видам транспорта 1 970 миллион тонн Вид транспорта Автомобильный транспорт 7 853
364 292 307 Грузооборот по видам транспорта 1 970 миллиард тонно-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 116
2 441 823 2 689 Перевозки грузов по видам транспорта 1 970 миллион тонн Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 7 853
3 568 132 3 637 Пассажирооборот по видам транспорта общего пользования 1 970 миллиард пассажиро-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 100,09999847
3 568 135 3 637 Пассажирооборот по видам транспорта общего пользования 1 975 миллиард пассажиро-километров Вид транспорта Автобусный 157,30000305
364 293 307 Грузооборот по видам транспорта 1 975 миллиард тонно-километров Вид транспорта Автомобильный транспорт 184
2 441 824 2 689 Перевозки грузов по видам транспорта 1 975 миллион тонн Вид транспорта Автомобильный транспорт 11 650
364 294 307 Грузооборот по видам транспорта 1 975 миллиард тонно-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 184



Именно эти представления используются в клиентской части для получения данных из БД. Но для того, чтобы их получить, надо сначала данные загрузить :)

Импорт данных в БД


Для импорта данных в базу я решил нагрузить серверную часть. Поэтому были написаны две взаимосвязанные серверные функции:
  1. add_dataset — для загрузки датасетов (в таблицу datasets и, при необходимости, связанные с ней)
  2. add_data — для загрузки наблюдений (в таблицу obs и, при необходимости, связанные с ней)

Исходный код функции add_dataset
CREATE OR REPLACE FUNCTION public.add_dataset(
   prep_time timestamp with time zone,      -- дата/время выгрузки датасета
   last_updated timestamp with time zone,  -- дата/время последнего обновления датасета
   next_update timestamp with time zone,   -- дата/время следующего обновления датасета
   ds_id text,                   -- краткое условное обозначение в ЕМИСС
   fullname text,             -- полное название
   agency_id text,            -- код службы - источника данных (обычно число)
   agency_name text,      -- название службы - источника данных
   agency_dept text,       -- ответственное подразделение в службе
   codes jsonb,                -- классификаторы (в формате JSONB)
   unit text,                    -- основная единица измерения (например, "человек")
   periodicity text,          -- периодичность данных (например, "ежеквартально")
   this_release text,          -- период данного выпуска (например, "I-II квартал")
   data_range integer[],    -- начальный и конечный год наблюдений (2 значения)
   description text,            -- описание методологии датасета
   classifier_id text,          -- код рубрики
   classifier_path text,      -- полный путь рубрики
   prep_by text,                -- ФИО ответственного за подготовку 
   prep_contacts text)       -- контактные данные ответственного за подготовку 
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
	ag_id_ integer;
	dept_id_ integer;
	class_id_ integer;
	units_id_ integer;
	periods_id_ integer;
	codes_id_ integer;
	codename text;
	js_codeval jsonb;
	ds_id_ integer;
begin
	-- вставляем или обновляем данные в agencies (служба)
	insert into agencies(ag_id, name)
		values (agency_id, agency_name)
		on conflict on constraint agencies_unique1 do 
		update set name = excluded.name, ag_id = excluded.ag_id 
		returning id into ag_id_;	
	
	-- вставляем или обновляем данные в departments
	insert into departments(agency_id, name)
		values (ag_id_, agency_dept)
		on conflict on constraint depts_unique1 do
		update set name = excluded.name 
		returning id into dept_id_;
		
	-- вставляем или обновляем данные в classifier
	insert into classifier(class_id, name)
		values (classifier_id, classifier_path)
		on conflict on constraint classifier_unique1 do
		update set name = excluded.name 
		returning id into class_id_;
		
	-- вставляем или обновляем данные в units
	insert into units(val) values (unit)
		on conflict on constraint units_unique do
		update set val = excluded.val 
		returning id into units_id_;
		
	-- вставляем или обновляем данные в periods
	insert into periods(val) values (periodicity)
		on conflict on constraint periods_unique do 
		update set val = excluded.val 
		returning id into periods_id_;
		
	-- вставляем или обновляем данные в codes
	-- {'code_id': {'name': 'code_name', 'values': [['val_id', 'name'], [...], ...]}, {...}, ...}
	for codename in select * from jsonb_object_keys(codes)
	loop
		insert into codes(name) values (codes->codename->>'name')
			on conflict on constraint codes_unique1 do
			update set name = excluded.name 
			returning id into codes_id_;

		for js_codeval in select * from jsonb_array_elements(codes->codename->'values')
		loop
			insert into codevals(code_id, val_id, name) 
				values (codes_id_, js_codeval->>0, js_codeval->>1)
				on conflict on constraint codevals_unique do
				update set name = excluded.name;
		end loop;
	end loop;
	
	-- вставляем или обновляем данные в datasets
	insert into datasets(prep_time, updated_time, next_update_time,
						ds_id, agency_id, dept_id, name, period_id,
						unit_id, range_start, range_end, class_id,
						description, prep_by, prep_contact, code_id)
		values (prep_time, last_updated, next_update,
			   ds_id, ag_id_, dept_id_, fullname, periods_id_,
			   units_id_, data_range[1], data_range[2], class_id_,
			   description, prep_by, prep_contacts, codes_id_)
		on conflict on constraint datasets_unique1 do
		update set prep_time = excluded.prep_time, updated_time = excluded.updated_time, 
			next_update_time = excluded.next_update_time, ds_id = excluded.ds_id, 
			agency_id = excluded.agency_id, dept_id = excluded.dept_id,
			name = excluded.name, period_id = excluded.period_id, unit_id = excluded.unit_id,
			range_start = excluded.range_start, range_end = excluded.range_end,
			class_id = excluded.class_id, description = excluded.description, 
			prep_by = excluded.prep_by, prep_contact = excluded.prep_contact, code_id = excluded.code_id			
		returning id into ds_id_;
		
	return ds_id_;
	
end;
$function$
;


Здесь все должно быть очевидным: на вход функции подаются значения для всех полей таблицы datasets (например, полное название датасета, дата последнего обновления, ФИО ответственного и т.д.) и связанных внешних таблиц (например, название ответственной службы, подразделения, рубрика и т.д.). СУБД последовательно обновляет сначала внешние таблицы, а затем таблицу datasets, добавляя в нее как исходные данные, так и новые значения внешних ключей. Функция возвращает ID добавленного или обновленного датасета.

А вот с функцией add_data немного по-другому: исходные данные в нее передаются как текст в формате JSON, а уже преобразование и извлечение полей данных происходит в самой функции. Это связано, с одной стороны, с желанием упростить вызов функции из клиента (любой клиент может сформировать исходные данные в виде JSON-строки), а с другой стороны, с богатыми возможностями непосредственной работы с JSON в самом Postgres (к слову, у меня версия 13 на локальном хосте).

Исходный код функции add_data
CREATE OR REPLACE FUNCTION public.add_data(
   dataset_json text, -- данные в формате JSON (текст)
   time_format text DEFAULT 'YYYY-MM-DD HH24-MI-SS'::text, -- исходный формат дат
   OUT n_added integer, -- ВОЗВРАЩАЕМ кол-во добавленных / обновленных записей
   OUT last_data_id bigint, -- ВОЗВРАЩАЕМ ID последней записи в obs
   OUT dataset_id integer) -- ВОЗВРАЩАЕМ ID соответствующего датасета в datasets
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
	data_json jsonb;
	prep_time timestamp with time zone;
	last_updated timestamp with time zone;
	next_update timestamp with time zone; 
	data_rec json;
	codes_id integer;
	codevals_id integer;
	units_id integer;
	periods_id integer;
	cnt1 bigint;
	cnt2 bigint;
	r text;
begin
	n_added := 0;
	last_data_id := -1;
	dataset_id := -1;
	
	data_json := dataset_json::jsonb;
	
	prep_time := to_timestamp(data_json->>'prepared', time_format);
	last_updated := to_timestamp(data_json->>'updated', time_format);
	next_update := to_timestamp(data_json->'periodicity'->>'next', time_format);
	
	-- добавить / обновить датасет	
	
	select into dataset_id public.add_dataset(prep_time, last_updated, next_update,
		data_json->>'id', data_json->>'full_name',
		data_json->>'agency_id', data_json->>'agency_name',
		data_json->>'agency_dept', data_json->'codes',
		data_json->>'unit', data_json->'periodicity'->>'value',
		data_json->'periodicity'->>'releases',
		array[cast(data_json->'data_range'->>0 as integer), 
		     cast(data_json->'data_range'->>1 as integer)], 
		data_json->>'methodology', 
		data_json->'classifier'->>'id',
		data_json->'classifier'->>'path',
		data_json->'prepared_by'->>'name',
		data_json->'prepared_by'->>'contacts');
	if dataset_id = -1 then
		raise notice '! Unable to add or update dataset!';
		return;
	end if;
	
	-- добавить наблюдения
	select count(*) into cnt1 from obs;
	
	for data_rec in select * from jsonb_array_elements(data_json->'data')
	loop
		-- code
		begin
			select id into strict codes_id from codes
			where lower(name) = lower(data_rec->>0) limit 1;
		exception
			when NO_DATA_FOUND then
			insert into codes(name) values (data_rec->>0)
				on conflict on constraint codes_unique1 do
				update set name = excluded.name 
				returning id into codes_id;
		end;
				
		-- codeval
		begin
			select id into strict codevals_id from codevals
			where code_id = codes_id and lower(name) = lower(data_rec->>1) limit 1;
		exception 
			when NO_DATA_FOUND then
			insert into codevals(code_id, name) values (codes_id, data_rec->>1)
				on conflict on constraint codevals_unique do
				update set code_id = excluded.code_id, name = excluded.name
				returning id into codevals_id;
		end;
				
		-- unit
		begin
			select id into strict units_id from units
			where lower(val) = lower(data_rec->>2) limit 1;
		exception
			when NO_DATA_FOUND then
			insert into units(val) values (data_rec->>2)
				on conflict on constraint units_unique do
				update set val = excluded.val 
				returning id into units_id;
		end;
				
		-- period
		begin
			select id into strict periods_id from periods
			where lower(val) = lower(data_rec->>3) limit 1;
		exception
			when NO_DATA_FOUND then
			insert into periods(val) values (data_rec->>3)
				on conflict on constraint periods_unique do
				update set val = excluded.val 
				returning id into periods_id;
		end;
				
		-- вставка / обновление записи
				
		insert into obs(dataset_id, code_id, unit_id, period_id, obs_year, obs_val) 
			values (dataset_id, codevals_id, units_id, periods_id, 
				cast(data_rec->>4 as integer), cast(data_rec->>5 as real))
			on conflict on constraint obs_unique1 do
			update set dataset_id = excluded.dataset_id, code_id = excluded.code_id,
				unit_id = excluded.unit_id, period_id = excluded.period_id,
				obs_year = excluded.obs_year, obs_val = excluded.obs_val 
			returning id into last_data_id;
				
	end loop;
	
	select count(*) into cnt2 from obs;
	n_added := cnt2 - cnt1;
	
end;
$function$
;


Итак, все, что теперь надо сделать в клиентской части, — это подготовить исходные данные в формате JSON-строки и передать эту строку на вход функции add_data (вместе с форматом дат, который можно оставить по умолчанию), а она уже сама добавит необходимые данные во все связанные таблицы БД и выдаст количество добавленных записей, ID датасета и ID последнего добавленного наблюдения. Cool! Но что у нас с поиском по базе?

Реализация поиска


Поиск по шаблону


Для обычного параметрического поиска используем стандартные SQL-запросы к представлениям и полагаемся на скорость движка Postgres. Например, посмотрим на пассажирооборот автобусов с 2010 по 2018 гг. по стране, выведя годовые показатели:

select * from data_lite 
where 
	dataset like 'Пассажирооборот автобусов%' and 
	category like 'Российская%' and
	release like '%за год' and
	year between 2010 and 2018
order by year;

Результат запроса
id ds_id dataset year release unit code category value
2 461 653 2 747 Пассажирооборот автобусов по маршрутам регулярных перевозок 2 014 значение показателя за год тысяча пассажиро-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 120 055 168
2 461 656 2 747 Пассажирооборот автобусов по маршрутам регулярных перевозок 2 015 значение показателя за год тысяча пассажиро-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 118 061 520
2 461 658 2 747 Пассажирооборот автобусов по маршрутам регулярных перевозок 2 016 значение показателя за год тысяча пассажиро-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 116 885 384
2 461 661 2 747 Пассажирооборот автобусов по маршрутам регулярных перевозок 2 017 значение показателя за год тысяча пассажиро-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 116 041 928
3 637 186 3 737 Пассажирооборот автобусов 2 017 значение показателя за год тысяча пассажиро-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 123 381 632
2 461 664 2 747 Пассажирооборот автобусов по маршрутам регулярных перевозок 2 018 значение показателя за год тысяча пассажиро-километров Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 114 817 920


Этот запрос у меня выполняется в среднем за 20 мс. Тот же запрос из представления all_data — за 20-30 мс (то есть примерно за такое же время). Более сложные запросы дают время выполнения все равно менее секунды, что очень хорошо для довольно объемной базы (на настоящее время у меня 1.4 млн. записей в obs).

Но мы же все хорошо знаем ограничения оператора LIKE, не так ли? :) Зависимость от регистра букв, простота шаблонов поиска и т.п… Но у нас же Postgres! С такой-то современной СУБД можно и горы свернуть:

  • Чтобы искать без учета регистра, можно использовать оператор ILIKE (или его сокращенное обозначение — "~~*").
  • Чтобы искать по сложным шаблонам, можно использовать регулярные выражения и операторы поиска (NOT) SIMILAR TO, SUBSTRING, (!)~, (!)~*, REGEXP_MATCH(ES) и т.д.

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

Хорошо, с параметрическим поиском понятно. А как быть, если мы хотим по всей базе поискать что-то вроде «школы и детские сады» или «автобус трамвай поезд» (то есть так, как мы обычно «гуглим» в сети)? Для этого есть полнотекстовый поиск.

Полнотекстовый поиск


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

Я не буду здесь вдаваться в подробности и терминологию полнотекстового поиска в Postgres — можете углубиться в эту тему самостоятельно, если вы с ней не очень хорошо знакомы. Мы же сразу перейдем к практике.

Итак, для реализации полнотекстового поиска по базе (наподобие того, что делает Google, Yandex, Bing и иже с ними) во все таблицы, имеющие текстовые поля, добавлено поле search, содержащее поисковые индексированные данные для поиска. Тип данных этого поля — tsvector. Также для всех таких таблиц созданы соответствующие индексы в БД (в Postgres есть два вида поисковых индексов, я использую GIN как наиболее оптимальный). Например, создание индекса для таблицы agencies выглядит так:

-- индекс GIN в таблице agencies на поле search
CREATE INDEX agencies_search_idx ON public.agencies USING gin (search) 

Чтобы индексы постоянно обновлялись при обновлении данных в таблицах, используются триггерные функции для соответствующих таблиц, вызываемые при обновлении / добавлении данных. Например, для таблицы datasets определена функция datasets_update_insert:

CREATE OR REPLACE FUNCTION public.datasets_update_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
	new.description := public.entity2char(trim(replace(new.description, ' ', ' ')));
	new.search := setweight(to_tsvector('russian', coalesce(new.name, '')), 'A') ||
			      setweight(to_tsvector('russian', coalesce(new.description, '')), 'B');
	return new;
end;
$function$
;

Обратите внимание на этот пример: поле search обновляется из двух объединенных векторов (индексов) — название датасета и описание методологии, причем первый из них (название) имеет более высокий приоритет поиска (ему присваивается вес «A»). Таким образом, релевантность результатов поиска будет выше для совпадений в поле name, чем для совпадений в поле description. Также обратите внимание на явное указание локали «russian» для функции векторизации (Postgres и так бы справился, применив системную локаль, но так более наглядно и надежно). Внутренние механизмы морфологического парсера действуют исходя из заданной локали (языка), именно поэтому в БД с русскими текстами движок будет «понимать», что «трамвай» и «трамваями» имеют одну основу. А в английском языке таким же образом будут объединены, например, слова «John» и «John's» или «read» и «reading».

Для упрощения полнотекстовых запросов я реализовал также 4 вспомогательные функции-утилиты:

  1. search_datasets: ищет по датасетам с использованием функции to_tsquery, где ключевые слова объединяются вручную при помощи операторов & (И), | (ИЛИ),! (НЕ) и <-> (ПРЕДШЕСТВУЕТ) и группируются скобками
  2. search_datasets_web: ищет по датасетам с использованием функции websearch_to_tsquery, где ключевые слова объединяются интуитивно при помощи пробелов (И), а также оператора OR (ИЛИ) или могут группироваться кавычками для обозначения строгого порядка слов (как при поиске в Интернете)
  3. search_data: то же, что и search_datasets, но ищет по датасетам и наблюдениям
  4. search_data_web: то же, что и search_datasets_web, но ищет по датасетам и наблюдениям

Больше о различиях между этими и другими функциями поиска можете прочитать в справке.

Пара примеров:

1. Ищем «школы и детские сады» в датасетах (любые совпадения слов «школа» и словосочетания «детский сад»)

-- Вариант 1) "человеческий" запрос, как в Яндексе
select * from search_datasets_web('школы "детские сады"'); 
-- Вариант 2) ручное форматирование
select * from search_datasets('школы & (детские <-> сады)'); 

2. Ищем «уголовное дело» и «Верховный Суд» в наблюдениях за 2018 год (показатели за год)

-- Вариант 1) "человеческий" запрос, как в Яндексе
select * from search_data_web('"уголовное дело" "верховный суд"')
where obsyear = 2018 and obsperiod ~~* '%за год'; 
-- Вариант 2) ручное форматирование
select * from search_data('уголовное <-> дело & верховный <-> суд')
where obsyear = 2018 and obsperiod ~~* '%за год'

В каждом примере показаны оба варианта: «веб-поиск» (search_*_web) и поиск с ручным форматированием ключевых слов (search_*). Результаты поиска в обоих вариантах будут идентичными.

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

Конечно, можно обойтись и без этих самописных функций-утилит, выполняя запросы вручную. Это может пригодиться, если требуется дополнительные степени свободы, например, указать конкретно что и как должно быть найдено в определенных таблицах и как объединять и сортировать результаты. Например, выведем наблюдения, для которых в соответствующих датасетах содержится слово «стоимость» (естественно, со всеми возможными словоформами), причем только для Российской Федерации в целом (исключая фразы типа «столица Российской Федерации») и только за весь год:

select ds.name "Датасет", ds.description "Описание", obs.obs_year "Год", 
	periods.val "Период", units.val "Единица", codes.name "Рубрика", 
	codevals.name "Категория", obs.obs_val::numeric "Значение", 
	ts_rank(ds.search, to_tsquery('russian', 'стоимость')) "Ранг"
from datasets ds
	join obs on ds.id = obs.dataset_id
	join periods on obs.period_id = periods.id
	join units on obs.unit_id = units.id
	join codevals on obs.code_id = codevals.id
	join codes on codevals.code_id = codes.id
where 
	ds.search @@ to_tsquery('russian', 'стоимость') and
	codevals.search @@ websearch_to_tsquery('russian', '"Российская Федерация" -столица') and
	periods.search @@ to_tsquery('russian', 'год|январь-декабрь')
order by "Ранг" desc, "Датасет" asc, "Год" asc;

Результат запроса (первые 10 записей)
Датасет Описание Год Период Единица Рубрика Категория Значение Ранг
Изменение остаточной балансовой стоимости контрактов, договоров аренды, лицензий, маркетинговых активов и гудвилла за счет переоценки (+, -) или обесценения(-) по текущей рыночной стоимости по 2016 г. Учитывается изменение остаточной балансовой стоимости контрактов, договоров аренды, лицензий, маркетинговых активов и гудвилла за счет их переоценки по текущей рыночной стоимости (+, -) и обесценения (-), проведенных в течение отчетного года. 2 013 значение показателя за год тысяча рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 0 0,80212605
Изменение остаточной балансовой стоимости контрактов, договоров аренды, лицензий, маркетинговых активов и гудвилла за счет переоценки (+, -) или обесценения(-) по текущей рыночной стоимости по 2016 г. Учитывается изменение остаточной балансовой стоимости контрактов, договоров аренды, лицензий, маркетинговых активов и гудвилла за счет их переоценки по текущей рыночной стоимости (+, -) и обесценения (-), проведенных в течение отчетного года. 2 016 значение показателя за год тысяча рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 0 0,80212605
Доля стоимости государственных (муниципальных) контрактов, заключенных по результатам несостоявшихся торгов и запросов котировок у единственного поставщика (исполнителя, подрядчика), в общей стоимости заключенных государственных (муниципальных) контрактов Расчетный показатель.Методология заполнения показателя относится к компетенции Минэкономразвития России и будет сообщена после получения от Министерства алгоритма расчета. 2 010 значение показателя за год процент Территории Российская Федерация 34,3 0,75990885
Текущая рыночная стоимость основного капитала на конец года с 2017 г. Текущая рыночная стоимость основного капитала — это стоимость, по которой он может быть реализован на открытом рынке в условиях конкуренции, когда стороны сделки действуют разумно, располагая достаточной информацией, а на величине сделки не отражаются какие-либо чрезвычайные обстоятельства. Для расчёта текущей рыночной стоимости запасов основного капитала используются следующие подходы: 1. Затратный подход, при котором стоимость основного капитала на некоторую дату определяется текущей ценой приобретения эквивалентного нового актива, за вычетом накопленной суммы снижения его стоимости (накопленного потребления). Полная восстановительная стоимость основного капитала на начало или конец отчетного года, учтенная в текущих ценах (из баланса основных фондов по полной восстановительной стоимости), уменьшается на величину их накопленного потребления к этой дате, исходя из среднего прошедшего срока службы (возраста), среднего предстоящего срока службы, по данному виду основного капитала, и закономерностей снижения текущей рыночной стоимости основного капитала. Разновидность затратного подхода — метод непрерывной инвентаризации (МНИ) при котором используются данные о погодовых инвестициях в основной капитал за период, соответствующий среднему сроку службы данного вида основного капитала, с учётом их ликвидации (исходя из сроков их службы, возраста и закономерностей выбытия), накопленного потребления и изменения цен к началу или концу отчетного года. Применяется также смешанный метод, при котором используются данные о полной восстановительной стоимости основного капитала на базовую дату, а последующие изменения стоимости (за счет приобретения, выбытия, потребления основного капитала, изменения цен) учитываются в соответствии с МНИ. 2. Сравнительный подход основан на данных ежегодного обследования сделок по основным фондам по форме федерального статистического наблюдения № 11(сделка). По данным этой формы определяются соотношения фактической цены продажи основных фондов к их полной восстановительной стоимости, которые умножаются на полную восстановительную стоимость всех имеющихся основных фондов. Для определения текущей рыночной стоимости сравнительно однородных видов основного капитала (скота, жилых зданий) расчёт осуществляется путём перемножения натуральных показателей наличия актива (поголовья скота, площади жилья) и цен (соответственно, на голову скота, на продажу единицы площади различных типов жилья на вторичном рынке). В связи с несовпадением структуры продаваемого и имеющегося жилья расчёты проводятся по достаточно дифференцированным типам жилья и группам жилых зданий. 3. Доходный подход состоит в оценке дисконтированной стоимости будущих экономических выгод, ожидаемых от оцениваемого основного капитала. По некоторым видам основного капитала возможен расчёт чистых доходов на основе показателей величины денежного потока от этих активов, с последующим дисконтированием. В остальных случаях используются данные формы федерального статистического наблюдения № 11(сделка). Расчёт доходов от основного капитала осуществляется на основе коэффициентов, определяемых соотношениями величины арендной платы по основным фондам к полной восстановительной стоимости арендованных (сданных в аренду) основных фондов. Далее полная восстановительная стоимость всего имеющегося основного капитала умножается на эти коэффициенты, а также на корректирующие коэффициенты, отражающие дисконтирование предстоящих доходов, исходя из оставшихся сроков службы соответствующих видов основного капитала (разницы полного срока службы и возраста). 2 017 значение показателя за год миллион рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 350 039 000 0,75086457
Текущая рыночная стоимость основного капитала на конец года с 2017 г. Текущая рыночная стоимость основного капитала — это стоимость, по которой он может быть реализован на открытом рынке в условиях конкуренции, когда стороны сделки действуют разумно, располагая достаточной информацией, а на величине сделки не отражаются какие-либо чрезвычайные обстоятельства. Для расчёта текущей рыночной стоимости запасов основного капитала используются следующие подходы: 1. Затратный подход, при котором стоимость основного капитала на некоторую дату определяется текущей ценой приобретения эквивалентного нового актива, за вычетом накопленной суммы снижения его стоимости (накопленного потребления). Полная восстановительная стоимость основного капитала на начало или конец отчетного года, учтенная в текущих ценах (из баланса основных фондов по полной восстановительной стоимости), уменьшается на величину их накопленного потребления к этой дате, исходя из среднего прошедшего срока службы (возраста), среднего предстоящего срока службы, по данному виду основного капитала, и закономерностей снижения текущей рыночной стоимости основного капитала. Разновидность затратного подхода — метод непрерывной инвентаризации (МНИ) при котором используются данные о погодовых инвестициях в основной капитал за период, соответствующий среднему сроку службы данного вида основного капитала, с учётом их ликвидации (исходя из сроков их службы, возраста и закономерностей выбытия), накопленного потребления и изменения цен к началу или концу отчетного года. Применяется также смешанный метод, при котором используются данные о полной восстановительной стоимости основного капитала на базовую дату, а последующие изменения стоимости (за счет приобретения, выбытия, потребления основного капитала, изменения цен) учитываются в соответствии с МНИ. 2. Сравнительный подход основан на данных ежегодного обследования сделок по основным фондам по форме федерального статистического наблюдения № 11(сделка). По данным этой формы определяются соотношения фактической цены продажи основных фондов к их полной восстановительной стоимости, которые умножаются на полную восстановительную стоимость всех имеющихся основных фондов. Для определения текущей рыночной стоимости сравнительно однородных видов основного капитала (скота, жилых зданий) расчёт осуществляется путём перемножения натуральных показателей наличия актива (поголовья скота, площади жилья) и цен (соответственно, на голову скота, на продажу единицы площади различных типов жилья на вторичном рынке). В связи с несовпадением структуры продаваемого и имеющегося жилья расчёты проводятся по достаточно дифференцированным типам жилья и группам жилых зданий. 3. Доходный подход состоит в оценке дисконтированной стоимости будущих экономических выгод, ожидаемых от оцениваемого основного капитала. По некоторым видам основного капитала возможен расчёт чистых доходов на основе показателей величины денежного потока от этих активов, с последующим дисконтированием. В остальных случаях используются данные формы федерального статистического наблюдения № 11(сделка). Расчёт доходов от основного капитала осуществляется на основе коэффициентов, определяемых соотношениями величины арендной платы по основным фондам к полной восстановительной стоимости арендованных (сданных в аренду) основных фондов. Далее полная восстановительная стоимость всего имеющегося основного капитала умножается на эти коэффициенты, а также на корректирующие коэффициенты, отражающие дисконтирование предстоящих доходов, исходя из оставшихся сроков службы соответствующих видов основного капитала (разницы полного срока службы и возраста). 2 018 значение показателя за год миллион рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 370 957 000 0,75086457
Текущая рыночная стоимость жилых зданий Расчет текущей рыночной стоимости жилых зданий в разрезе субъектов Российской Федерации выполнен в соответствии с рекомендациями Системы национальных счетов (СНС)* по построению баланса активов и пассивов. Жилые здания трактуются как нефинансовый экономический актив и отражаются по текущей рыночной стоимости на дату составления баланса (конец года). Данные о текущей рыночной стоимости жилых зданий используются также в расчетах стоимости выпуска жилищных услуг, производимых и потребляемых владельцами жилья, методом пользовательских расходов, и включаются в оценки валового внутреннего продукта и валового регионального продукта. Данный расчет носит макроэкономический характер. В отличие от инвентаризационной и кадастровой стоимостей жилья, не применяется для целей налогообложения, а также отличается от рыночной стоимости жилья, определяемой на микроуровне при совершении конкретной сделки. Расчет текущей рыночной стоимости жилых зданий основан на данных о площади имеющегося жилья различных типов, расположенного в городской и сельской местности, и ценах продаж различных типов жилья на вторичном рынке (Методологические положения, утвержденные приказом Росстата от 22.08.2012 г. № 459). Расчеты выполнены по дифференцированным типам и группам жилых зданий; для оценки стоимости жилья с учетом его территориального расположения использованы соотношения кадастровой стоимости жилья, расположенного в административных центрах регионов и других населенных пунктах данного региона. *) Система национальных счетов 2008, ЕК, МВФ, ОЭСР, ООН, ВБ, Нью-Йорк, 2012 год 2 011 значение показателя за год миллион рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 103 364 000 0,74161458
Текущая рыночная стоимость жилых зданий Расчет текущей рыночной стоимости жилых зданий в разрезе субъектов Российской Федерации выполнен в соответствии с рекомендациями Системы национальных счетов (СНС)* по построению баланса активов и пассивов. Жилые здания трактуются как нефинансовый экономический актив и отражаются по текущей рыночной стоимости на дату составления баланса (конец года). Данные о текущей рыночной стоимости жилых зданий используются также в расчетах стоимости выпуска жилищных услуг, производимых и потребляемых владельцами жилья, методом пользовательских расходов, и включаются в оценки валового внутреннего продукта и валового регионального продукта. Данный расчет носит макроэкономический характер. В отличие от инвентаризационной и кадастровой стоимостей жилья, не применяется для целей налогообложения, а также отличается от рыночной стоимости жилья, определяемой на микроуровне при совершении конкретной сделки. Расчет текущей рыночной стоимости жилых зданий основан на данных о площади имеющегося жилья различных типов, расположенного в городской и сельской местности, и ценах продаж различных типов жилья на вторичном рынке (Методологические положения, утвержденные приказом Росстата от 22.08.2012 г. № 459). Расчеты выполнены по дифференцированным типам и группам жилых зданий; для оценки стоимости жилья с учетом его территориального расположения использованы соотношения кадастровой стоимости жилья, расположенного в административных центрах регионов и других населенных пунктах данного региона. *) Система национальных счетов 2008, ЕК, МВФ, ОЭСР, ООН, ВБ, Нью-Йорк, 2012 год 2 012 значение показателя за год миллион рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 118 524 000 0,74161458
Текущая рыночная стоимость жилых зданий Расчет текущей рыночной стоимости жилых зданий в разрезе субъектов Российской Федерации выполнен в соответствии с рекомендациями Системы национальных счетов (СНС)* по построению баланса активов и пассивов. Жилые здания трактуются как нефинансовый экономический актив и отражаются по текущей рыночной стоимости на дату составления баланса (конец года). Данные о текущей рыночной стоимости жилых зданий используются также в расчетах стоимости выпуска жилищных услуг, производимых и потребляемых владельцами жилья, методом пользовательских расходов, и включаются в оценки валового внутреннего продукта и валового регионального продукта. Данный расчет носит макроэкономический характер. В отличие от инвентаризационной и кадастровой стоимостей жилья, не применяется для целей налогообложения, а также отличается от рыночной стоимости жилья, определяемой на микроуровне при совершении конкретной сделки. Расчет текущей рыночной стоимости жилых зданий основан на данных о площади имеющегося жилья различных типов, расположенного в городской и сельской местности, и ценах продаж различных типов жилья на вторичном рынке (Методологические положения, утвержденные приказом Росстата от 22.08.2012 г. № 459). Расчеты выполнены по дифференцированным типам и группам жилых зданий; для оценки стоимости жилья с учетом его территориального расположения использованы соотношения кадастровой стоимости жилья, расположенного в административных центрах регионов и других населенных пунктах данного региона. *) Система национальных счетов 2008, ЕК, МВФ, ОЭСР, ООН, ВБ, Нью-Йорк, 2012 год 2 013 значение показателя за год миллион рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 120 155 000 0,74161458
Текущая рыночная стоимость жилых зданий Расчет текущей рыночной стоимости жилых зданий в разрезе субъектов Российской Федерации выполнен в соответствии с рекомендациями Системы национальных счетов (СНС)* по построению баланса активов и пассивов. Жилые здания трактуются как нефинансовый экономический актив и отражаются по текущей рыночной стоимости на дату составления баланса (конец года). Данные о текущей рыночной стоимости жилых зданий используются также в расчетах стоимости выпуска жилищных услуг, производимых и потребляемых владельцами жилья, методом пользовательских расходов, и включаются в оценки валового внутреннего продукта и валового регионального продукта. Данный расчет носит макроэкономический характер. В отличие от инвентаризационной и кадастровой стоимостей жилья, не применяется для целей налогообложения, а также отличается от рыночной стоимости жилья, определяемой на микроуровне при совершении конкретной сделки. Расчет текущей рыночной стоимости жилых зданий основан на данных о площади имеющегося жилья различных типов, расположенного в городской и сельской местности, и ценах продаж различных типов жилья на вторичном рынке (Методологические положения, утвержденные приказом Росстата от 22.08.2012 г. № 459). Расчеты выполнены по дифференцированным типам и группам жилых зданий; для оценки стоимости жилья с учетом его территориального расположения использованы соотношения кадастровой стоимости жилья, расположенного в административных центрах регионов и других населенных пунктах данного региона. *) Система национальных счетов 2008, ЕК, МВФ, ОЭСР, ООН, ВБ, Нью-Йорк, 2012 год 2 014 значение показателя за год миллион рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 131 437 000 0,74161458
Текущая рыночная стоимость жилых зданий Расчет текущей рыночной стоимости жилых зданий в разрезе субъектов Российской Федерации выполнен в соответствии с рекомендациями Системы национальных счетов (СНС)* по построению баланса активов и пассивов. Жилые здания трактуются как нефинансовый экономический актив и отражаются по текущей рыночной стоимости на дату составления баланса (конец года). Данные о текущей рыночной стоимости жилых зданий используются также в расчетах стоимости выпуска жилищных услуг, производимых и потребляемых владельцами жилья, методом пользовательских расходов, и включаются в оценки валового внутреннего продукта и валового регионального продукта. Данный расчет носит макроэкономический характер. В отличие от инвентаризационной и кадастровой стоимостей жилья, не применяется для целей налогообложения, а также отличается от рыночной стоимости жилья, определяемой на микроуровне при совершении конкретной сделки. Расчет текущей рыночной стоимости жилых зданий основан на данных о площади имеющегося жилья различных типов, расположенного в городской и сельской местности, и ценах продаж различных типов жилья на вторичном рынке (Методологические положения, утвержденные приказом Росстата от 22.08.2012 г. № 459). Расчеты выполнены по дифференцированным типам и группам жилых зданий; для оценки стоимости жилья с учетом его территориального расположения использованы соотношения кадастровой стоимости жилья, расположенного в административных центрах регионов и других населенных пунктах данного региона. *) Система национальных счетов 2008, ЕК, МВФ, ОЭСР, ООН, ВБ, Нью-Йорк, 2012 год 2 015 значение показателя за год миллион рублей Классификатор объектов административно-территориального деления (ОКАТО) Российская Федерация 138 764 000 0,74161458


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

Клиентская часть


Как я уже говорил, клиент написан на Python (Python 3 или, если быть точнее, Python 3.9). Исходный код (включающий, кстати, и актуальный бэкап БД) доступен на Github.

Фронтэнд


Сразу оговорюсь, что работа над клиентом в части фронтэнда еще ведется. Окончательный вариант пользовательского интерфейса как таковой отсутствует на данный момент. Я пытался реализовать веб-приложение на Flask, но пока мои потуги уперлись в отсутствие опыта веб-разработки :) Тем не менее, наработки доступны в ветке webapp, к которой, мне кажется, я еще вернусь. В идеале я планировал опубликовать полноценное веб-приложение на Heroku или Google App Engine, чтобы люди могли пользоваться без необходимости локального приложения и установки PostgreSQL. Как говорится, мне надо еще поучить «матчасть». Возможно, кто-то из вас сможет предложить какую-то другую идею или самостоятельно форкнуть проект и допилить интерфейс — смотрите сами!

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

В текущей реализации REPL основан на библиотеке Python Prompt Toolkit, которая поддерживает цветовые схемы консоли (на базе CSS и не только), отображение прогресс-баров, всплывающие диалоги, подсказки в нижней и правой панели консоли и 100500 разных других «плюшек», нужных и не очень. На сегодняшний день REPL работает довольно топорно: логика работы программы основана на иерархическом меню с односимвольными командами, которые заносятся в стек и выполняются на основе гигантского if… elif… else. Пример работы текущей версии консольного интерфейса показан ниже (терминал — ConEmu-Maximus 5 для Windows 64-bit).


Ввод пароля БД при старте приложения


Ввод команд и панель подсказок внизу окна


Последовательный вывод результатов поиска с форматированием


Отображение рубрик датасетов в иерархическом порядке

REPL пока также не завершен и еще допиливается (пока нет импорта / экспорта данных, реализации SQL-запросов и много еще чего), но концепция должна быть понятна.

Бэкэнд


Бэкэнд состоит из 3 модулей:

  1. rsengine — скачивание, парсинг и конвертирование данных ЕМИСС
  2. psdb — работа с базой данных PostgreSQL, в т.ч. поиск и вывод результатов в различных форматах
  3. russtat — основная входная точка программы, реализующая функции импорта данных из ЕМИСС в локальную БД и различные тесты

Также в состав приложения входит модуль globs.py с глобальными переменными и утилитами. В нем достойна упоминания переменная булевая DEBUGGING, которую можно использовать как флаг для включения и отключения подробных логов.

Модуль rsengine


В модуле rsengine определен единственный класс — Russtat, в задачи которого входит загрузка исходных датасетов и их реестра в формате XML с сайта ЕМИСС, обновление локального реестра датасетов в формате JSON, парсинг и конвертация данных в JSON с опциональным сохранением их в директории проекта (для последующего переноса в БД или иных целей).

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

Модуль psdb


В модуле psdb определены два класса: класс Psdb, отвечающий за «низкоуровневую» работу с БД PostgreSQL, и унаследованный от него класс Russtatdb, который более конкретно заточен на работу с нашей локальной БД с учетом ее специфики, в частности, определенных в ней серверных функций.

В качестве движка для PostgreSQL используется psycopg2 (де-факто стандартный Python клиент для этой СУБД). В качестве альтернативы я пробовал также подход на основе ORM (PonyORM), но уперся в определенные трудности, связанные с отсутствием поддержки типа tsvector (напомню, это для полнотекстового поиска) в ORM. Любопытные могут посмотреть на мои «старания» в ветке ponyorm. Вообще же я для себя понял, что ORM — это не мое :) Достаточно знания SQL и стандартного питоновского подхода к управлению БД (см. PEP 249, он же DB-API).

В классе Psdb определены, в частности, методы sqlquery и fetch, которые я постарался сделать достаточно мощными для любых запросов и возврата результатов в виде итератора («сырого» курсора БД), списка, словаря или датафрейма pandas.

В классе Russtatdb определены, в основном, методы поиска (по датасетам и наблюдениям), форматированного вывода результатов (в т.ч. для нужд REPL), а также импорта данных в БД (метод add_data). Этот последний метод, по сути, является оберткой вокруг серверной функции с тем же названием, о который мы уже говорили выше. Точно такими же обертками являются методы findin_datasets и findin_data, которые вызывают соответствующие функции поиска по шаблону в БД.

Модуль russtat


В модуле russtat, который содержит точку входа в клиентское приложение (вернее, одну из точек входа, так как другой является repl.py), основной функцией является update_db. Как следует из ее названия, она обновляет данные в БД: сначала при необходимости обновляется реестр датасетов с сайта ЕМИСС, затем скачиваются сами датасеты (можно указать диапазон и возможность пропуска уже существующих датасетов) и импортируются в БД при помощи метода add_data. Это делается, как я уже говорил, в мультипроцессовом режиме, с выводом промежуточных и финального логов (в консоль или указанный файл).

Таким образом, если у вас чистая (только что созданная) БД, то начать необходимо с вызова russtat.py (в аргументы командной строки можно передавать параметры обновления БД, но для чистой БД лучше все оставить по умолчанию, т.е. не передавать параметры).

Итак...


Итак, мы завершили обзор решения, которое позволяет пользоваться официальными данными Росстата (покрывающими сотни различных аспектов жизнедеятельности нашей страны) без постоянного доступа к Интернету, «без email и регистрации», самостоятельно анализируя и препарируя данные в любом объеме и любыми инструментами. А поскольку рассмотренное решение на базе Python + PostgreSQL можно назвать
переносимым
По крайней мере, с точки зрения кроссплатформенности. Но есть также решения, позволяющие запускать и пользовать PostgreSQL без установки. Я уж не говорю про различные дистрибутивы Python, которые также работают без классической инсталляции.
— название статьи нельзя ругать за чистый кликбейт :)

Напоследок еще раз добавлю, что в исходниках присутствует полный бэкап БД со всеми данными (dbbackup.backup), а также SQL-код для создания чистой БД (dbcreate.sql). Вы можете воспользоваться любой из этих опций через стандартный админский интерфейс Postgres (pgAdmin), либо в другом любимом клиенте. В pgAdmin надо создать новую БД (с любым именем), затем вызвать на новой БД контекстное меню и выбрать Restore… Далее указать dbbackup.backup для БД с данными или dbcreate.sql для чистой БД (в этом случае во вкладке Restore Options надо установить флажок «Only schema», т.е. не копировать данные):



Еще раз ссылка на проект (Github)


Внимание: блокировки!


Апдейт
Поскольку Росстат мой IP заблокировал после обнаружения активности «бота» (хотя нигде на сайте ЕМИСС положений о таком ограничении нет), предупреждение всем, кто хочет использовать этот или иной подобный скрипт. Используйте соответствующие методы обхода IP блокировки.
Теги:big datapythonpostgresqlросстатстатистикасубд
Хабы: Python PostgreSQL Администрирование баз данных Big Data
Рейтинг +6
Количество просмотров 3,4k Добавить в закладки 37
Комментарии
Комментарии 9

Похожие публикации

Факультет Python-разработки
12 марта 2021180 000 ₽GeekBrains
Факультет аналитики Big Data
15 марта 2021270 000 ₽GeekBrains
Python QA Engineer
16 марта 202160 000 ₽OTUS
SQL и получение данных
18 марта 202117 900 ₽Нетология
Python-разработчик с нуля
22 марта 202169 900 ₽Нетология

Лучшие публикации за сутки