---
# System prepended metadata

title: Поликлиника

---


# Поликлиника
# 1.Описание предметной области
Данная база данных моделирует систему управления медицинской клиникой. В ней содержатся следующие таблицы:

    "врачи" (doctors): Содержит информацию о врачах, включая их идентификатор, имя, специализацию, возраст и пол.

    "пациенты" (patients): Содержит информацию о пациентах, включая их идентификатор, имя, возраст, пол и контактный телефон.

    "записи на приём" (appointments): Связывает врачей и пациентов и содержит информацию о записях на прием, такую как идентификатор записи, идентификатор врача, идентификатор пациента, дату и время приема и примечание.

    "диагнозы" (diagnoses): Содержит информацию о диагнозах, включая идентификатор диагноза, название диагноза и описание.

    "результаты обследования" (test_results) содержит информацию о результатах обследования пациентов в рамках медицинской клиники"
    
# 2.Ожидаемые запросы к предметной области
1) Получить список всех врачей
2) Получить список всех пациентов
3) Количество пациентов, принятых каждым врачом поликлиники
4) Количество пациентов, принятых каждым специалистом (например, окулистом - 50 человек; из них Иванов принял 23, Петров - 27)
5) Сколько раз каждый пациент обращался в поликлинику в текущем году, в текущем месяце
6) К каким специалистам обращался пациент в прошлом году, месяце
# 3.Описание базы
В базе Поликлиника содержатся следующие таблицы:
![](https://hackmd.io/_uploads/rkXemFlwh.png)

Примечание: при построении ER диаграммы использовался инструмент DBDiagram (доступен
здесь https://dbdiagram.io)

## Таблица: doctors (врачи)
| Column Name   | Data Type    | Description                               |
|---------------|--------------|-------------------------------------------|
| id            | SERIAL       | Уникальный идентификатор врача             |
| name          | VARCHAR(50)  | Имя врача                                 |
| specialization| VARCHAR(50)  | Специализация врача                       |
| age           | INT          | Возраст врача                             |
| gender        | VARCHAR(10)  | Пол врача                                 |

## Таблица: patients (пациенты)
| Column Name      | Data Type    | Description                               |
|------------------|--------------|-------------------------------------------|
| id               | SERIAL       | Уникальный идентификатор пациента          |
| name             | VARCHAR(50)  | Имя пациента                              |
| age              | INT          | Возраст пациента                          |
| gender           | VARCHAR(10)  | Пол пациента                              |
| contact_phone    | VARCHAR(15)  | Контактный телефон пациента               |

## Таблица: appointments (записи на приём)
| Column Name        | Data Type    | Description                               |
|--------------------|--------------|-------------------------------------------|
| id                 | SERIAL       | Уникальный идентификатор записи на приём   |
| doctor_id          | INT          | Внешний ключ, ссылается на таблицу "doctors" |
| patient_id         | INT          | Внешний ключ, ссылается на таблицу "patients" |
| date_and_time      | TIMESTAMP    | Дата и время приёма                        |
| note               | TEXT         | Дополнительные заметки или комментарии к приёму |

## Таблица: diagnoses (диагнозы)
| Column Name   | Data Type    | Description                               |
|---------------|--------------|-------------------------------------------|
| id            | SERIAL       | Уникальный идентификатор диагноза          |
| name          | VARCHAR(100) | Название диагноза                         |
| description   | TEXT         | Описание диагноза                          |

## Таблица: test_results (результаты обследования)
| Column Name        | Data Type    | Description                               |
|--------------------|--------------|-------------------------------------------|
| id                 | SERIAL       | Уникальный идентификатор результата обследования |
| appointment_id     | INT          | Внешний ключ, ссылается на таблицу "appointments" |
| diagnosis_id       | INT          | Внешний ключ, ссылается на таблицу "diagnoses" |
| result             | TEXT         | Результат обследования                     |

# 4.Скрипт создания базы
```sql=

CREATE TABLE doctors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  specialization VARCHAR(50),
  age INT,
  gender VARCHAR(10)
);


CREATE TABLE patients (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  gender VARCHAR(10),
  contact_phone VARCHAR(15)
);


CREATE TABLE appointments (
  id SERIAL PRIMARY KEY,
  doctor_id INT REFERENCES doctors(id),
  patient_id INT REFERENCES patients(id),
  date_and_time TIMESTAMP,
  note TEXT
);


CREATE TABLE diagnoses (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  description TEXT
);


CREATE TABLE test_results (
  id SERIAL PRIMARY KEY,
  appointment_id INT REFERENCES appointments(id),
  diagnosis_id INT REFERENCES diagnoses(id),
  result TEXT
);

```
# 5.Скрипт заполнения базы
```sql=
CREATE OR REPLACE FUNCTION random_choice(anyarray)
  RETURNS anyelement AS
$$
BEGIN
  RETURN (SELECT $1[FLOOR(RANDOM() * ARRAY_LENGTH($1, 1) + 1)]);
END;
$$
LANGUAGE plpgsql;


-- Insert random data into the doctors table
INSERT INTO doctors (name, specialization, age, gender)
SELECT
  random_choice(ARRAY['Иван', 'Мария', 'Алексей', 'Екатерина', 'Дмитрий', 'Анна', 'Александр', 'Ольга', 'Николай', 'София', 'Михаил', 'Анастасия', 'Сергей', 'Виктория', 'Артем']),
  random_choice(ARRAY['Кардиолог', 'Дерматолог', 'Невролог', 'Офтальмолог', 'Педиатр', 'Психиатр', 'Хирург', 'Гинеколог', 'Ортопедический хирург', 'Уролог', 'Эндокринолог', 'Онколог', 'Радиолог', 'Анестезиолог', 'Патологоанатом']),
  floor(random() * 40) + 30,
  random_choice(ARRAY['Мужской', 'Женский'])
FROM generate_series(1, 100);

-- Insert random data into the patients table
INSERT INTO patients (name, age, gender, contact_phone)
SELECT
  random_choice(ARRAY['Алексей', 'Мария', 'Никита', 'Ольга', 'Иван', 'Екатерина', 'Александр', 'София', 'Даниил', 'Анна', 'Михаил', 'Анастасия', 'Максим', 'Виктория', 'Егор']),
  floor(random() * 80) + 1,
  random_choice(ARRAY['Мужской', 'Женский']),
  CONCAT('+7', floor(random() * 900) + 100, floor(random() * 9000) + 1000,  floor(random() * 9000) + 1000)
FROM generate_series(1, 100);

-- Insert random data into the appointments table
INSERT INTO appointments (doctor_id, patient_id, date_and_time, note)
SELECT
  (SELECT id FROM doctors ORDER BY random() LIMIT 1),
  (SELECT id FROM patients ORDER BY random() LIMIT 1),
  now() + (floor(random() * 30) || ' days')::interval + (floor(random() * 24) || ' hours')::interval,
  random_choice(ARRAY['Нет особых заметок', 'Контрольный приём', 'Необходимо лабораторное исследование', 'Подтверждение рецепта'])
FROM generate_series(1, 100);

-- Insert random data into the diagnoses table
INSERT INTO diagnoses (name, description)
SELECT
  random_choice(ARRAY['Гипертония', 'Диабет', 'Бронхит', 'Мигрень', 'Артрит', 'Пневмония', 'Депрессия', 'Тревожность', 'Аллергия', 'Гастрит', 'Бессонница', 'Остеопороз', 'Рак', 'Астма', 'Синусит']),
  random_choice(ARRAY['Описание диагноза 1', 'Описание диагноза 2', 'Описание диагноза 3', 'Описание диагноза 4', 'Описание диагноза 5'])
FROM generate_series(1, 100);

-- Insert random data into the test_results table
INSERT INTO test_results (appointment_id, diagnosis_id, result)
SELECT
  (SELECT id FROM appointments ORDER BY random() LIMIT 1),
  (SELECT id FROM diagnoses ORDER BY random() LIMIT 1),
  random_choice(ARRAY['Нормальный', 'Положительный', 'Отрицательный', 'Неоднозначный'])
FROM generate_series(1, 100);


```

# 6.Запросы к базе
1) Получить список всех врачей, у которых специализация "Кардиолог" и возраст младше 40 лет
```sql
SELECT * FROM doctors WHERE specialization = 'Кардиолог' AND age < 40;

```
2) Получить список пациентов, у которых контактный телефон начинается с кода "+79"
```sql
SELECT * FROM patients WHERE contact_phone LIKE '+79%';
```
3) Получить количество записей на приём для каждого врача

```sql
SELECT doctors.name, COUNT(appointments.id) AS appointment_count
FROM doctors
LEFT JOIN appointments ON doctors.id = appointments.doctor_id
GROUP BY doctors.id, doctors.name;

```
4) Получить список пациентов, у которых были назначены приёмы на следующей неделе
```sql
SELECT patients.*
FROM patients
INNER JOIN appointments ON patients.id = appointments.patient_id
WHERE appointments.date_and_time BETWEEN CURRENT_DATE + INTERVAL '1 week' AND CURRENT_DATE + INTERVAL '1 week' + INTERVAL '6 days';

```
5) Получить список диагнозов, у которых количество результатов обследований больше 10
```sql
SELECT DISTINCT diagnoses.name, COUNT(test_results.id) AS result_count
FROM diagnoses
INNER JOIN test_results ON diagnoses.id = test_results.diagnosis_id
GROUP BY diagnoses.id, diagnoses.name
HAVING COUNT(test_results.id) > 10;

```
6) Количество пациентов, принятых каждым специалистом
```sql
SELECT
  doctors.specialization,
  doctors.name AS doctor_name,
  COUNT(DISTINCT appointments.patient_id) AS total_patients_count
FROM
  doctors
LEFT JOIN
  appointments ON doctors.id = appointments.doctor_id
GROUP BY
  doctors.specialization, doctors.name
ORDER BY
  doctors.specialization, doctors.name;

```