Глава 2. Расчёт динамики ВВП с использованием оконных функций

1. Методические указания (как делать)

1. Скачайте данные.
2. Откройте QGIS и загрузите данные из CSV-файла:
   • Слой → Добавить слой → Добавить слой из текста с разделителями;
   • формат файла → Другие разделители → точка с запятой;
   • имя слоя → ВВП_Регионы;
   • параметры записей — как на картинке; формат геометрии — без геометрии.

3. Зайдите в Базы данных → Менеджер БД.
4. Раскройте вашу БД и нажмите на public.
5. Затем — Импорт слоя/файла.
6. Параметры импорта настройте следующим образом:

7. С помощью модуля QuickOSM выгрузите административные границы субъектов РФ. Параметры (см. картинку): admin_level = 4, boundary = administrative, в «Россия».

8. Удалите лишние слои.
9. Удалите лишние атрибуты в admin_level_4_boundary_administrative_Россия. На этом шаге выпишите списком, какие атрибуты оказались лишними!

10. Переименуйте слой admin_level_4_boundary_administrative_Россия в Субъекты_РФ.
11. Импортируйте слой в БД; параметры на картинке:

12. Сделайте запрос к БД и сформируйте список регионов — соседей вашего выбранного региона:

SELECT сосед."name" AS соседний_регион, сосед.geom
FROM субъекты_РФ AS основной
JOIN субъекты_РФ AS сосед
ON ST_Touches(основной.geom, сосед.geom)
WHERE основной."name" = 'Ваша область';

13. Выгрузите слой и проверьте, все ли регионы-соседи были получены. Ниже пример для Нижегородской области (коричневая) и регионов-соседей (серые):

14. Выпишите регионы-соседей списком.
15. Соедините таблицы ВВП_Регионы и Субъекты_РФ по названию регионов. В запросе выберите регионы-соседи и ваш регион. Сформируйте статистику изменения динамики ВВП (год к году):

SELECT ВВП_Регионы."object_name" AS регион,
    субъекты_РФ.geom,
    ВВП_Регионы."year" AS год,
    ВВП_Регионы."indicator_value" AS ВВП,
    ВВП_Регионы."indicator_value" - LAG(ВВП_Регионы."indicator_value")
        OVER (PARTITION BY ВВП_Регионы."object_name"
              ORDER BY ВВП_Регионы."year") AS динамика_ВВП
FROM ВВП_Регионы
JOIN субъекты_РФ ON субъекты_РФ."name" = ВВП_Регионы."object_name"
WHERE ВВП_Регионы."object_name" IN ( 'Регион 1', 'Регион 2', 'Регион N' )
ORDER BY
    ВВП_Регионы."object_name",
    ВВП_Регионы."year";

Замените список ( 'Регион 1', 'Регион 2', 'Регион N' ) на интересующие вас регионы плюс ваш выбранный регион. Если в итоговом слое будет не хватать регионов — проверьте написание регионов в таблицах ВВП_Регионы и субъекты_РФ.

16. Выпишите названия регионов из Субъекты_РФ и вставьте в запрос к таблице ВВП_Регионы. Сравните результат. Например, для Нижегородской области:

SELECT DISTINCT(ВВП_Регионы."object_name") AS регион
FROM ВВП_Регионы
WHERE
ВВП_Регионы."object_name" ILIKE '%Ивановская область%' OR
ВВП_Регионы."object_name" ILIKE '%Костромская область%' OR
ВВП_Регионы."object_name" ILIKE '%Марий Эл%' OR
ВВП_Регионы."object_name" ILIKE '%Чувашия%' OR
ВВП_Регионы."object_name" ILIKE '%Мордовия%' OR
ВВП_Регионы."object_name" ILIKE '%Владимирская область%' OR
ВВП_Регионы."object_name" ILIKE '%Рязанская область%' OR
ВВП_Регионы."object_name" ILIKE '%Кировская область%' OR
ВВП_Регионы."object_name" ILIKE '%Нижегородская область%'
ORDER BY ВВП_Регионы."object_name";

Обратите внимание: для Чувашии ничего не высветится. Почему? Потому что полное название — «Чувашская республика», что не совпадает с запросом.

17. Используя результаты запроса, обновите данные в таблице ВВП. Например, для Нижегородской области пришлось переименовать 4 региона:

UPDATE ВВП_Регионы
SET "object_name" = CASE
    WHEN "object_name" = 'Республика Марий Эл' THEN 'Марий Эл'
    WHEN "object_name" = 'Республика Мордовия' THEN 'Мордовия'
    WHEN "object_name" = 'Рязанская обл.' THEN 'Рязанская область'
    WHEN "object_name" = 'Чувашская республика' THEN 'Чувашия'
    ELSE "object_name"
END;

18. Настройте стиль полученного слоя: ПКМ по слою → Стиль → Символизация по диапазонам значений (в старых версиях QGIS — «Градуированный знак») → значение Динамика_ВВП → режим (алгоритм) Равные интервалы → количество классов подбираете самостоятельно, руководствуясь своим эстетическим чувством. Симметричная классификация вокруг 0. Цветовой ряд — как на картинке.

19. Не забудьте настроить верную проекцию!
20. Используя модуль Time Manager, создайте GIF-изображение с иллюстрацией динамики ВВП.

21. Убедитесь, что данные года — в формате Date. Выполните обновление типа столбца (на всякий случай):

Выражение: to_date("Имя столбца с годами", 'yyyy')

2. Демонстрация: как работает LAG() OVER

Оконная функция не сворачивает строки, как агрегат с GROUP BY, а дописывает к каждой строке значение, вычисленное по её «окну». LAG(x) OVER (PARTITION BY регион ORDER BY год) берёт значение x из предыдущей строки того же региона в порядке лет. Нажимайте «шаг»: подсветка показывает окно текущей строки (фиолетовым — раздел PARTITION, оранжевым — текущая строка), а в колонку «динамика_ВВП» записывается разность текущего и предыдущего значений.

3. Состав отчёта

1. Используя файл description_regions_collection_102_v25062024.pdf, дайте описание данных.
2. Добавьте цитирование, как указано в файле и соблюдая ГОСТ (ссылка в список используемой литературы).
3. Приведите первые 4 строки из CSV-файла и дайте комментарий к каждой приведённой характеристике.
4. Покажите запрос для отображения (Листинг 6) всей таблицы с данными ВВП в БД.
5. Укажите источник данных для слоя с регионами РФ. Приведите процесс запроса (какой плагин использовался, с какими параметрами).
6. Укажите список атрибутов, которые вы удалили из данного слоя.
7. Покажите запрос для отображения (Листинг 7) всей таблицы с регионами в БД.
8. Приведите запрос из пункта 12 в исправленном виде для вашего варианта (Листинг 8). «Рис. 13. Результат данного запроса».
9. Дайте определение оконным функциям и объясните принцип их работы. По пункту 15 сформируйте Листинг 9; на «Рис. 14» приведите результат запроса.
10. Если возникли проблемы — приведите список субъектов, которые не вывелись на экран. Укажите причину. Дайте список изменений.
11. Покажите настройки стиля слоя и дайте комментарий к нему (что такое симметричная классификация в QGIS и почему «вокруг 0» и проч.). «Рис. 15. Результат применения стиля».
12. Расскажите про процесс построения картографической анимации для визуализации динамики изменения ВВП выбранных регионов (какой модуль используете, как настраиваете).
13. Дайте результаты анализа (какой из регионов показывает положительную динамику, какой — отрицательную и проч.). GIF-изображение приложите к отчёту.

Контрольные вопросы

• Что делает предикат ST_Touches и почему запрос соседей соединяет таблицу саму с собой?
  ST_Touches(a, b) истинен, когда геометрии касаются границами, но их внутренности не пересекаются — это и есть «соседство» регионов. Самосоединение (self join с алиасами «основной» и «сосед») нужно, чтобы сравнить каждый регион с каждым другим в одной таблице Субъекты_РФ.
• Дайте определение оконной функции. Чем она отличается от агрегата с GROUP BY?
  Оконная функция вычисляет значение по набору строк («окну»), определяемому предложением OVER, но не сворачивает строки: каждая строка результата сохраняется и получает своё вычисленное значение. GROUP BY, напротив, заменяет группу строк одной итоговой.
• Расшифруйте выражение LAG(v) OVER (PARTITION BY регион ORDER BY год).
  PARTITION BY регион делит строки на независимые разделы по регионам; ORDER BY год упорядочивает строки внутри раздела; LAG(v) возвращает значение v из предыдущей строки раздела (для первой строки — NULL). Разность v − LAG(v) даёт изменение показателя «год к году».
• Почему у первой строки каждого региона динамика_ВВП равна NULL?
  У первого года региона нет предыдущей строки в разделе, LAG возвращает NULL, а арифметика с NULL даёт NULL. Это корректное поведение: динамика «год к году» для первого наблюдения не определена.
• Зачем при поиске регионов используется ILIKE с процентами и какая ловушка у этого способа?
  ILIKE — регистронезависимый LIKE; шаблон '%Мордовия%' находит строку при любых префиксах («Республика Мордовия»). Ловушка: если официальное название вообще не содержит шаблона (Чувашия ↔ «Чувашская республика»), строка не найдётся — такие случаи исправляются UPDATE … CASE.
• Что такое симметричная классификация «вокруг 0» и почему она нужна для карты динамики?
  Границы классов располагаются симметрично относительно нуля (например, −40…−20, −20…0, 0…20, 20…40), а расходящийся цветовой ряд кодирует знак: красные оттенки — спад, синие/зелёные — рост. Так карта честно показывает направление динамики, а не только её величину.
• Какие требования к данным предъявляет Time Manager и как подготовить колонку года?
  Time Manager анимирует слои по колонке с типом даты/времени. Год-число нужно преобразовать в дату выражением to_date("колонка_года", 'yyyy') — тогда модуль сможет листать кадры по годам и собрать GIF динамики ВВП.

Источники

1. PostgreSQL 15 Documentation. Window Functions : [сайт]. — URL: https://www.postgresql.org/docs/15/tutorial-window.html (дата обращения: 18.08.2026).
2. PostGIS Documentation. ST_Touches : [сайт]. — URL: https://postgis.net/docs/ST_Touches.html (дата обращения: 18.08.2026).
3. TimeManager — QGIS Python Plugins Repository : [сайт]. — URL: https://plugins.qgis.org/plugins/timemanager/ (дата обращения: 18.08.2026).