digitalization-2025-10/postgres.md

# Работа с PostgreSQL

Минимальные команды для работы с одной таблицей: подключение, создание с первичным ключом, CRUD и основные команды ALTER.

## PostgreSQL with Docker

- **Установите Docker Desktop**: Убедитесь, что Docker установлен и запущен на вашем компьютере.

- **Скачать образ Postgres**:
  ```bash
  docker pull postgres:16
  ```

- **Запустить контейнер PostgreSQL**:
  ```bash
  docker run --name pg-local -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=appdb -p 5432:5432 -v pgdata:/var/lib/postgresql/data -d postgres:16
  ```

- **Проверить, что контейнер запущен**:
  ```bash
  docker ps
  ```

- **Подключение к PostgreSQL**:
  - Через psql внутри контейнера:
    ```bash
    docker exec -it pg-local psql -U postgres -d appdb
    ```
  - Параметры для GUI‑клиента (pgAdmin, DBeaver и др.):
    - Host: `localhost`
    - Port: `5432`
    - User: `postgres`
    - Password: `postgres`
    - Database: `appdb`
  - Быстрая проверка подключения (внутри psql):
    ```sql
    SELECT version();
    ```

- **Полезные команды**:
  ```bash
  # Остановить / запустить / удалить контейнер
  docker stop pg-local
  docker start pg-local
  docker rm -f pg-local

  # Удалить том с данными (необратимо)
  docker volume rm pgdata
  ```

## Подключение (контейнер Docker)

```bash
docker exec -it pg-local psql -U postgres -d appdb
```
## Важные slash-команды psql

```psql
\?            -- помощь по psql
\l            -- список баз данных
\c appdb      -- подключиться к базе appdb
\dt           -- список таблиц
\du           -- список ролей/пользователей
\dn           -- список схем
\d contacts   -- описание таблицы contacts
\q            -- выход
```
Выйти: `\q`.

## Рекомендации по именованию (best practices)

- Таблицы и столбцы: `snake_case`, английские названия; будьте последовательны (единственное или множественное число — выберите и придерживайтесь).
- Первичный ключ: как правило `id` типа `BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY`.
- Внешние ключи: `<referenced_table>_id`, тот же тип, что и PK ссылочной таблицы.
- Метки времени: `created_at`, `updated_at` типа `TIMESTAMPTZ`; задавайте `DEFAULT now()` и `NOT NULL`.
- Первичный ключ: `pk_<table>` (имя создаётся автоматически, но можно переопределить).
- Внешние ключи: `fk_<table>__<ref_table>`.

Пример (рекомендуемая версия таблиц):
```sql
CREATE TABLE contacts (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT,
  department_id BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_contacts__email UNIQUE (email)
);

CREATE TABLE departments (
  department_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  CONSTRAINT uq_departments__name UNIQUE (name)
);

ALTER TABLE contacts
  ADD CONSTRAINT fk_contacts__departments
  FOREIGN KEY (department_id)
  REFERENCES departments(department_id)
  ON UPDATE CASCADE
  ON DELETE SET NULL;


-- Пример наполнения данными (по 10 строк в каждую таблицу)
```sql
-- 10 отделов
INSERT INTO departments (name) VALUES
  ('Sales'),
  ('Engineering'),
  ('HR'),
  ('Marketing'),
  ('Finance'),
  ('Operations'),
  ('Support'),
  ('IT'),
  ('Legal'),
  ('R&D');

-- 10 контактов
INSERT INTO contacts (full_name, email, department_id) VALUES
  ('Alice Johnson',    'alice.johnson@example.com',     1),
  ('Bob Smith',        'bob.smith@example.com',         2),
  ('Carol Davis',      'carol.davis@example.com',       3),
  ('David Wilson',     'david.wilson@example.com',      4),
  ('Eva Brown',        'eva.brown@example.com',         5),
  ('Frank Miller',     'frank.miller@example.com',      6),
  ('Grace Lee',        'grace.lee@example.com',         7),
  ('Henry Taylor',     'henry.taylor@example.com',      8),
  ('Ivy Anderson',     'ivy.anderson@example.com',      9),
  ('Jack Thomas',      'jack.thomas@example.com',       10);
```


## Операции CRUD

```sql
-- Создать запись
INSERT INTO contacts (full_name, email)
VALUES ('Alice Johnson', 'alice@example.com');

-- Прочитать записи
SELECT id, full_name, email, created_at
FROM contacts
ORDER BY id
LIMIT 10;

-- Обновить запись
UPDATE contacts
SET email = 'alice@newmail.com'
WHERE id = 1;

-- Удалить запись
DELETE FROM contacts
WHERE id = 1;
```

### Работа с NULL

```sql
-- Вставить запись без email (NULL)
INSERT INTO contacts (full_name, email) VALUES ('No Email User', NULL);

-- Найти записи с отсутствующим email
SELECT id, full_name, email FROM contacts WHERE email IS NULL;

-- Найти записи, где email указан
SELECT id, full_name, email FROM contacts WHERE email IS NOT NULL;

-- Подставить значение по умолчанию, если email NULL
SELECT id, full_name, COALESCE(email, 'no-email@example.com') AS safe_email
FROM contacts;

```

## Основные команды ALTER TABLE

```sql
-- Добавить столбец
ALTER TABLE contacts ADD COLUMN phone TEXT;

-- Переименовать столбец
ALTER TABLE contacts RENAME COLUMN phone TO phone_number;

-- Удалить столбец
ALTER TABLE contacts DROP COLUMN phone_number;

-- Изменить тип столбца (пример: TEXT -> VARCHAR)
ALTER TABLE contacts ALTER COLUMN full_name TYPE VARCHAR(200);

-- Добавить первичный ключ в существующую таблицу (если отсутствует)
ALTER TABLE contacts ADD COLUMN id SERIAL;
ALTER TABLE contacts ADD PRIMARY KEY (id);

-- Удалить первичный ключ
ALTER TABLE contacts DROP CONSTRAINT contacts_pkey;

-- Переименовать таблицу
ALTER TABLE contacts RENAME TO people;
```

## Работа с несколькими таблицами (две)

Добавим таблицу `departments` и внешний ключ из `contacts` на `departments`.

```sql
-- Создать таблицу отделов
CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

-- Вернуть имя таблицы people обратно, если переименовывали ранее
ALTER TABLE IF EXISTS people RENAME TO contacts;

-- Добавить внешний ключ на departments
ALTER TABLE contacts ADD COLUMN department_id INT;
ALTER TABLE contacts
  ADD CONSTRAINT contacts_department_fk
  FOREIGN KEY (department_id)
  REFERENCES departments(department_id)
  ON UPDATE CASCADE
  ON DELETE SET NULL;
```

Пример данных:
```sql
INSERT INTO departments (name) VALUES ('Sales'), ('Engineering'), ('HR');

UPDATE contacts SET department_id = 1 WHERE id = 1; -- Alice -> Sales
-- При необходимости добавьте больше контактов аналогично
```

### Примеры JOIN

```sql
-- INNER JOIN: только совпадающие записи
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
JOIN departments d ON d.department_id = c.department_id;

-- LEFT JOIN: все контакты, даже без отдела
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
LEFT JOIN departments d ON d.department_id = c.department_id;

-- RIGHT JOIN: все отделы, даже без контактов
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
RIGHT JOIN departments d ON d.department_id = c.department_id;

-- FULL OUTER JOIN: объединение LEFT и RIGHT
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
FULL JOIN departments d ON d.department_id = c.department_id;
```

### UNION и INTERSECT

```sql
-- UNION: объединить результаты (уникальные строки)
SELECT full_name AS item FROM contacts
UNION
SELECT name AS item FROM departments;

-- INTERSECT: пересечение множеств
-- (искусственный пример: найдём такие строки, где имя контакта совпадает с названием отдела)
SELECT full_name FROM contacts
INTERSECT
SELECT name FROM departments;
```

### Оконные функции (window functions) на примере компаний нефти и газа

Создадим отдельную таблицу `companies` с повторяющимися данными для примеров.

```sql
CREATE TABLE companies (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  company_name TEXT NOT NULL,
  sector TEXT NOT NULL,                    -- 'Oil' | 'Gas'
  country TEXT NOT NULL,                   -- страна регистрации
  revenue_usd NUMERIC(14,2) NOT NULL,      -- выручка
  employees INT NOT NULL,                  -- число сотрудников
  founded_year INT,                        -- год основания (может повторяться)
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO companies (company_name, sector, country, revenue_usd, employees, founded_year) VALUES
  ('PetroOne',  'Oil', 'USA',  1200000.00, 5000, 1980),
  ('GasPrime',  'Gas', 'USA',   800000.00, 3200, 1990),
  ('NordOil',   'Oil', 'Norway',950000.00, 2100, 1975),
  ('EuroGas',   'Gas', 'Norway',400000.00, 1500, 2001),
  ('OilAsia',   'Oil', 'UAE',  2200000.00, 9000, 1970),
  ('GasEast',   'Gas', 'UAE',  1300000.00, 4800, 1988),
  ('LatOil',    'Oil', 'Brazil', 500000.00, 1200, 2005),
  ('SouthGas',  'Gas', 'Brazil', 350000.00,  900, 2005),
  ('OilAsia-2', 'Oil', 'UAE',  1600000.00, 6000, 1970), -- повторяющиеся страна/год/sector
  ('PacificGas','Gas', 'Australia', 300000.00, 800, 1998);
```

Группировки, фильтрация (HAVING), сортировка и LIMIT:

```sql
-- Выручка и среднее число сотрудников по сектору
SELECT sector,
       SUM(revenue_usd) AS total_revenue,
       AVG(employees)::INT AS avg_employees
FROM companies
GROUP BY sector
HAVING SUM(revenue_usd) > 1000000 -- оставить только сектора с суммарной выручкой > 1 млн
ORDER BY total_revenue DESC
LIMIT 10;

-- По стране и сектору
SELECT country, sector,
       COUNT(*) AS num_companies,
       SUM(revenue_usd) AS total_revenue
FROM companies
GROUP BY country, sector
ORDER BY total_revenue DESC, country;
```

Примеры оконных функций:

```sql
-- Нумерация компаний внутри сектора по выручке (от большей к меньшей)
SELECT company_name, sector, revenue_usd,
       ROW_NUMBER() OVER (PARTITION BY sector ORDER BY revenue_usd DESC) AS rn_in_sector
FROM companies
ORDER BY sector, rn_in_sector;

-- Ранги: RANK (с пропусками), DENSE_RANK (без пропусков) внутри сектора
SELECT company_name, sector, revenue_usd,
       RANK()       OVER (PARTITION BY sector ORDER BY revenue_usd DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY sector ORDER BY revenue_usd DESC) AS drnk
FROM companies
ORDER BY sector, rnk;

-- Сумма и среднее по стране (окно PARTITION BY)
SELECT company_name, country, revenue_usd,
       SUM(revenue_usd) OVER (PARTITION BY country)            AS country_revenue_total,
       AVG(revenue_usd) OVER (PARTITION BY country)            AS country_revenue_avg
FROM companies
ORDER BY country, revenue_usd DESC;

-- Скользящее среднее по выручке внутри сектора (окно 1 предыдущая, текущая, 1 следующая)
SELECT company_name, sector, revenue_usd,
       AVG(revenue_usd) OVER (
         PARTITION BY sector
         ORDER BY revenue_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS moving_avg_in_sector
FROM companies
ORDER BY sector, revenue_usd;

-- Процентильные ранги и квартиль (NTILE)
SELECT company_name, sector, revenue_usd,
       PERCENT_RANK() OVER (PARTITION BY sector ORDER BY revenue_usd) AS percent_rank_in_sector,
       CUME_DIST()    OVER (PARTITION BY sector ORDER BY revenue_usd) AS cume_dist_in_sector,
       NTILE(4)       OVER (PARTITION BY sector ORDER BY revenue_usd) AS quartile_in_sector
FROM companies
ORDER BY sector, revenue_usd;

-- LAG/LEAD: сравнение с соседями по выручке внутри сектора
SELECT company_name, sector, revenue_usd,
       LAG(revenue_usd)  OVER (PARTITION BY sector ORDER BY revenue_usd) AS prev_revenue,
       LEAD(revenue_usd) OVER (PARTITION BY sector ORDER BY revenue_usd) AS next_revenue
FROM companies
ORDER BY sector, revenue_usd;
```