Эффективная работа приемной комиссии немыслима без надежной и быстрой базы данных. От качества составленных SQL-запросов зависит скорость обработки заявлений, корректность конкурсных списков и своевременность выдачи справок абитуриентам. В данной статье подробно разбираются основные типы запросов, необходимые для автоматизации процессов приемной кампании.
Проектирование структуры БД (Краткий обзор)
Перед написанием запросов необходимо понимать структуру таблиц. Типичная схема для приемной комиссии включает следующие сущности:
- applicants (Абитуриенты): id, last_name, first_name, middle_name, birth_date, passport_data, phone, email, address, benefits (льготы), target_organization (целевое обучение).
- education_programs (Образовательные программы): id, code, name, faculty, budget_places, paid_places, cost_per_year.
- entrance_exams (Вступительные испытания): id, program_id, subject_id, min_score, weight (коэффициент).
- subjects (Предметы): id, name (Русский язык, Математика, Информатика и т.д.).
- applications (Заявления): id, applicant_id, program_id, status (подано, рассмотрено, зачислен, отказ), submission_date, consent_original (согласие на зачисление), priority.
- exam_results (Результаты экзаменов): id, applicant_id, subject_id, score, exam_type (ЕГЭ, ВИ, ИД), certificate_number, year.
- documents (Документы): id, applicant_id, doc_type (паспорт, аттестат, медсправка, фото), status (оригинал/копия), received_date.
Базовые CRUD-операции для работы с абитуриентами
Добавление нового абитуриента
INSERT INTO applicants (last_name, first_name, middle_name, birth_date, passport_series, passport_number, phone, email, has_benefits)
VALUES ('Иванов', 'Иван', 'Иванович', '2006-05-15', '4500', '123456', '###', 'ivanov@mail.ru', FALSE); Обновление контактных данных
UPDATE applicants
SET phone = '###', email = 'new_email@mail.ru'
WHERE id = 1024; Поиск абитуриента по паспорту (проверка на дубликаты)
SELECT id, last_name, first_name, middle_name
FROM applicants
WHERE passport_series = '4500' AND passport_number = '123456'; Работа с заявлениями и приоритетами
Абитуриент может подать до 5 заявлений на разные программы. Важно учитывать приоритет при формировании рейтингов.
Подача заявления (транзакция)
BEGIN;
INSERT INTO applications (applicant_id, program_id, status, submission_date, priority)
VALUES (1024, 5, 'submitted', CURRENT_DATE, 1);
-- Фиксация факта подачи оригинала документа об образовании
UPDATE applicants SET has_original_diploma = TRUE WHERE id = 1024;
COMMIT; Получение списка заявлений абитуриента с названиями программ
SELECT a.id, ep.name AS program_name, ep.faculty, a.priority, a.status, a.submission_date
FROM applications a
JOIN education_programs ep ON a.program_id = ep.id
WHERE a.applicant_id = 1024
ORDER BY a.priority; Расчет конкурсных баллов (Ключевая логика)
Сумма баллов складывается из результатов ЕГЭ/ВИ с учетом весовых коэффициентов программы + индивидуальные достижения (ИД).
Запрос расчета суммы баллов для конкретного абитуриента на конкретную программу
SELECT
ap.id AS applicant_id,
ap.last_name,
ap.first_name,
ep.name AS program_name,
COALESCE(SUM(er.score * ee.weight), 0) + COALESCE(SUM(ia.bonus_points), 0) AS total_score
FROM applicants ap
JOIN applications a ON ap.id = a.applicant_id
JOIN education_programs ep ON a.program_id = ep.id
JOIN entrance_exams ee ON ep.id = ee.program_id
LEFT JOIN exam_results er ON ap.id = er.applicant_id AND ee.subject_id = er.subject_id
LEFT JOIN individual_achievements ia ON ap.id = ia.applicant_id AND ia.is_approved = TRUE
WHERE a.id = 5001 -- ID конкретного заявления
GROUP BY ap.id, ep.name; Массовый расчет рейтингов для всех поданных заявлений (для формирования списков)
WITH calculated_scores AS (
SELECT
a.id AS application_id,
a.applicant_id,
a.program_id,
a.priority,
a.consent_original,
COALESCE(SUM(er.score * ee.weight), 0) + COALESCE(SUM(ia.bonus_points), 0) AS total_score,
-- Проверка минимальных проходных баллов по каждому предмету
BOOL_AND(er.score >= ee.min_score OR er.score IS NULL) AS meets_min_requirements
FROM applications a
JOIN entrance_exams ee ON a.program_id = ee.program_id
LEFT JOIN exam_results er ON a.applicant_id = er.applicant_id AND ee.subject_id = er.subject_id
LEFT JOIN individual_achievements ia ON a.applicant_id = ia;applicant_id AND ia.is_approved = TRUE
WHERE a.status IN ('submitted', 'under_review')
GROUP BY a.id
)
SELECT
cs.*,
ap.last_name, ap.first_name, ap.middle_name,
ep.name AS program_name,
ep.budget_places
FROM calculated_scores cs
JOIN applicants ap ON cs.applicant_id = ap.id
JOIN education_programs ep ON cs.program_id = ep.id
WHERE cs.meets_min_requirements = TRUE
ORDER BY cs.program_id, cs.total_score DESC, cs.priority, ap.last_name; Формирование конкурсных списков и зачисление
Рейтинговый список на бюджетные места (с нумерацией строк)
SELECT
ROW_NUMBER OVER (PARTITION BY program_id ORDER BY total_score DESC, priority, last_name) AS rating_position,
applicant_id,
last_name,
first_name,
middle_name,
total_score,
consent_original,
program_id
FROM (
-- Подзапрос calculated_scores из примера выше
SELECT
a.id AS application_id,
a.applicant_id,
a.program_id,
a.priority,
a.consent_original,
COALESCE(SUM(er.score * ee.weight), 0) + COALESCE(SUM(ia.bonus_points), 0) AS total_score
FROM applications a
JOIN entrance_exams ee ON a.program_id = ee.program_id
LEFT JOIN exam_results er ON a.applicant_id = er.applicant_id AND ee.subject_id = er.subject_id
LEFT JOIN individual_achievements ia ON a.applicant_id = ia.applicant_id AND ia.is_approved = TRUE
WHERE a.status = 'submitted'
GROUP BY a.id
) scores
JOIN applicants ap ON scores.applicant_id = ap.id
WHERE total_score > 0
ORDER BY program_id, rating_position; Выявление абитуриентов, подлежащих зачислению (с учетом согласий и оригиналов)
Логика: берем топ-N по баллам, у которых подано согласие и (для бюджета) предоставлен оригинал аттестата.
WITH ranked_applicants AS (
SELECT
a.applicant_id,
a.program_id,
a.consent_original,
a.has_original_diploma,
COALESCE(SUM(er.score * ee.weight), 0) + COALESCE(SUM(ia.bonus_points), 0) AS total_score,
ROW_NUMBER OVER (PARTITION BY a.program_id ORDER BY
COALESCE(SUM(er.score * ee.weight), 0) + COALESCE(SUM(ia.bonus_points), 0) DESC,
a.priority, ap.last_name) as rn,
ep.budget_places
FROM applications a
JOIN applicants ap ON a.applicant_id = ap.id
JOIN education_programs ep ON a.program_id = ep.id
JOIN entrance_exams ee ON a.program_id = ee.program_id
LEFT JOIN exam_results er ON a.applicant_id = er.applicant_id AND ee.subject_id = er.subject_id
LEFT JOIN individual_achievements ia ON a.applicant_id = ia.applicant_id AND ia.is_approved = TRUE
WHERE a.status = 'submitted'
AND a.consent_original = TRUE
AND a.has_original_diploma = TRUE -- Для бюджета обязателен оригинал
GROUP BY a.id, ep.budget_places
)
SELECT applicant_id, program_id, total_score, rn AS place_in_rating
FROM ranked_applicants
WHERE rn <= budget_places; Обновление статусов заявлений при зачислении (Пример процедуры)
UPDATE applications
SET status = 'enrolled', enrollment_date = CURRENT_DATE
WHERE id IN (
SELECT application_id FROM ranked_applicants WHERE rn <= budget_places
); Отчетность и статистика
Статистика по конкурсу на каждую программу
SELECT
ep.code,
ep.name,
ep.budget_places,
COUNT(a.id) AS total_applications,
COUNT(CASE WHEN a.consent_original THEN 1 END) AS consents_submitted,
COUNT(CASE WHEN a.status = 'enrolled' THEN 1 END) AS enrolled_count,
ROUND(AVG(total_scores.total_score)::numeric, 2) AS avg_score,
MIN(total_scores.total_score) AS min_passing_score
FROM education_programs ep
LEFT JOIN applications a ON ep.id = a.program_id AND a.status IN ('submitted', 'enrolled')
LEFT JOIN (
SELECT a2.id AS app_id, COALESCE(SUM(er.score * ee.weight), 0) + COALESCE(SUM(ia.bonus_points), 0) AS total_score
FROM applications a2
JOIN entrance_exams ee ON a2.program_id = ee.program_id
LEFT JOIN exam_results er ON a2.applicant_id = er.applicant_id AND ee.subject_id = er.subject_id
LEFT JOIN individual_achievements ia ON a2.applicant_id = ia.applicant_id AND ia.is_approved = TRUE
GROUP BY a2.id
) total_scores ON a.id = total_scores.app_id
GROUP BY ep.id
ORDER BY ep.code; Список абитуриентов без результатов ЕГЭ (для напоминания)
SELECT DISTINCT ap.id, ap.last_name, ap.first_name, ap.phone, ap.email
FROM applicants ap
JOIN applications a ON ap.id = a.applicant_id
JOIN entrance_exams ee ON a.program_id = ee.program_id
LEFT JOIN exam_results er ON ap.id = er.applicant_id AND ee.subject_id = er.subject_id
WHERE a;status = 'submitted' AND er.id IS NULL; Контроль документов
Проверка комплекта документов абитуриента
SELECT
d.doc_type,
d.status,
d.received_date
FROM documents d
WHERE d.applicant_id = 1024
ORDER BY
CASE d.doc_type
WHEN 'passport' THEN 1
WHEN 'diploma' THEN 2
WHEN 'medical' THEN 3
WHEN 'photos' THEN 4
ELSE 5
END; Абитуриенты, не сдавшие оригинал аттестата к дедлайну
SELECT ap.id, ap.last_name, ap.first_name, ap.phone, a.program_id, ep.name
FROM applicants ap
JOIN applications a ON ap.id = a.applicant_id
JOIN education_programs ep ON a.program_id = ep.id
WHERE a.status = 'submitted'
AND a.consent_original = TRUE
AND ap.has_original_diploma = FALSE; Оптимизация и индексы
Для высокой производительности во время приемной кампании (пиковые нагрузки в июле-августе) критически важны правильные индексы:
- B-Tree индексы: На внешних ключах (
applications.applicant_id,applications.program_id,exam_results.applicant_id,exam_results.subject_id). - Составной индекс для рейтингов:
CREATE INDEX idx_app_prog_status ON applications(program_id, status); - Частичный индекс для активных заявлений:
CREATE INDEX idx_active_apps ON applications(program_id) WHERE status IN ('submitted', 'under_review'); - Индекс для поиска по ФИО:
CREATE INDEX idx_applicant_fio ON applicants(last_name, first_name, middle_name);
Безопасность и права доступа
База данных приемной комиссии содержит персональные данные. Необходимо разграничить доступ через роли (RBAC):
- role_operator (Оператор приемной комиссии):
SELECT, INSERT, UPDATEна таблицыapplicants, applications, documents, exam_results. Нет прав DELETE. - role_analyst (Аналитик/Статистик): Только
SELECTна все таблицы, доступ к представлениям (Views) для отчетов. - role_admin (Администратор БД): Полные права, управление структурой, бэкапы.
- role_api (Интеграция с порталом Госуслуг/Личным кабинетом): Ограниченный набор хранимых процедур для приема данных (предотвращение SQL-инъекций).
-- Пример создания роли и выдачи прав
CREATE ROLE role_operator;
GRANT SELECT, INSERT, UPDATE ON applicants, applications, documents, exam_results TO role_operator;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO role_operator;
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM role_operator; Использование представлений (Views) для упрощения
Создание представлений скрывает сложность джойнов от операторов и аналитиков.
CREATE VIEW v_applicant_full_info AS
SELECT
ap.id,
ap.last_name || ' ' || ap.first_name || ' ' || ap.middle_name AS fio,
ap.birth_date,
ap.phone,
ap.email,
a.id AS application_id,
ep.name AS program_name,
a.priority,
a.status,
a.consent_original,
ap.has_original_diploma,
COALESCE(SUM(er.score * ee.weight), 0) + COALESCE(SUM(ia.bonus_points), 0) AS total_score
FROM applicants ap
LEFT JOIN applications a ON ap.id = a.applicant_id
LEFT JOIN education_programs ep ON a;program_id = ep.id
LEFT JOIN entrance_exams ee ON ep.id = ee.program_id
LEFT JOIN exam_results er ON ap.id = er.applicant_id AND ee.subject_id = er.subject_id
LEFT JOIN individual_achievements ia ON ap.id = ia.applicant_id AND ia.is_approved = TRUE
GROUP BY ap.id, a.id, ep.name; Теперь оператор может просто выполнять: SELECT * FROM v_applicant_full_info WHERE program_name LIKE '%Информатика%';
Грамотная архитектура SQL-запросов — фундамент успешной приемной кампании. Ключевые принципы:
- Использование CTE (WITH) для разбиения сложной логики расчета баллов на читаемые этапы.
- Применение оконных функций (ROW_NUMBER, RANK) для автоматической нумерации в рейтинговых списках.
- Обязательное использование транзакций при изменении статусов заявлений и движении документов.
- Настройка индексов под типовые отчеты приемной комиссии.
- Разделение прав доступа через роли для защиты персональных данных абитуриентов.
Приведенные примеры покрывают 90% операционных потребностей приемной комиссии и могут быть адаптированы под конкретные правила зачисления вашего учебного заведения.