Модуль 04 — Изменение данных: DML, подзапросы и CTE

С математической точки зрения INSERT, UPDATE и DELETE — это операции над множеством кортежей (строк). Понимание этого помогает писать безопасный код.

-- Одна строка с явными значениями
INSERT INTO posts (id, community_id, title, score)
VALUES (14, 2, 'challenge code sprint', 750);

-- Без жёстких id — используем подзапрос для автоматического id
INSERT INTO posts (id, community_id, title, score)
SELECT
    (SELECT MAX(id) + 1 FROM posts),
    (SELECT id FROM communities WHERE name = 'GamersHub'),
    'analysis esports stats',
    850;

-- INSERT ... SELECT — массовое добавление
-- Добавить лайк этого поста для каждого пользователя
INSERT INTO likes (id, user_id, post_id, liked_at)
SELECT
    (SELECT MAX(id) FROM likes) + ROW_NUMBER() OVER(),
    u.id,
    (SELECT id FROM posts WHERE title = 'challenge code sprint'),
    '2026-02-25'
FROM   users u;  -- лайк для каждого пользователя

UPDATE без WHERE изменяет ВСЕ строки таблицы. Всегда добавляйте условие фильтрации.

-- Поднять score поста 'challenge code sprint' на 10%
UPDATE posts
SET    score = score * 1.1
WHERE  title = 'challenge code sprint';

-- Снизить score всех tutorial-постов на 5%
UPDATE posts
SET    score = score * 0.95
WHERE  title LIKE '%tutorial%';

-- UPDATE с подзапросом: обновить через JOIN (PostgreSQL синтаксис)
UPDATE posts p
SET    score = score * 0.9
FROM   communities c
WHERE  c.id = p.community_id
  AND  c.name = 'GamersHub';

-- Удалить все лайки от 25 февраля
DELETE FROM likes
WHERE  liked_at = '2026-02-25';

-- Удалить пост 'challenge code sprint' из базы
DELETE FROM posts
WHERE  title = 'challenge code sprint';

-- TRUNCATE — быстрая очистка всей таблицы (без логирования)
TRUNCATE TABLE likes;

Подзапрос — это SELECT внутри другого SQL-выражения. Используется в WHERE, FROM, SELECT и других частях запроса.

-- Скалярный подзапрос в SELECT
SELECT
    title,
    score,
    score - (SELECT AVG(score) FROM posts) AS diff_from_avg
FROM   posts;

-- Подзапрос в WHERE: пользователи, лайкнувшие посты выше среднего score
SELECT DISTINCT u.name
FROM   users u
JOIN   likes l ON l.user_id = u.id
JOIN   posts p ON p.id = l.post_id
WHERE  p.score > (SELECT AVG(score) FROM posts);

-- Подзапрос в FROM (производная таблица)
SELECT sub.name, sub.total
FROM (
    SELECT u.name,
           COUNT(*) AS total
    FROM   likes l
    JOIN   users u ON u.id = l.user_id
    GROUP BY u.name
) sub
WHERE  sub.total > 3
ORDER BY sub.total DESC;

CTE — это именованный подзапрос, объявленный в начале запроса. Делает код более читаемым и позволяет переиспользовать вложенные результаты.

-- CTE для поиска пропущенных дней просмотров
WITH date_range AS (
    SELECT gs::date AS dt
    FROM   generate_series(
        '2026-01-01'::date,
        '2026-01-10'::date,
        '1 day'
    ) gs
),
visited AS (
    SELECT DISTINCT viewed_at
    FROM   views
    WHERE  user_id IN (1, 2)
)
SELECT dr.dt AS missing_date
FROM   date_range dr
LEFT JOIN visited v ON v.viewed_at = dr.dt
WHERE  v.viewed_at IS NULL
ORDER BY missing_date;

-- Несколько CTE: пользователь с наибольшим количеством лайков
WITH like_totals AS (
    SELECT
        u.name,
        l.liked_at,
        COUNT(*) AS total
    FROM   likes l
    JOIN   users u ON u.id = l.user_id
    WHERE  l.liked_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY u.name, l.liked_at
),
max_total AS (
    SELECT MAX(total) AS max_val FROM like_totals
)
SELECT lt.name, lt.liked_at, lt.total
FROM   like_totals lt
JOIN   max_total mt ON mt.max_val = lt.total;

Рекурсивный CTE решает задачи, которые классическими запросами не решить: задача коммивояжёра, поиск кратчайшего пути, иерархия подчинённости.

-- Подготовка: создать тестовую таблицу маршрутов
CREATE TEMP TABLE routes (
    point1 varchar, point2 varchar, cost integer
);
INSERT INTO routes VALUES
    ('a', 'b', 10), ('a', 'c', 15),
    ('b', 'c', 12), ('b', 'a', 10),
    ('c', 'a', 15), ('c', 'b', 12);

-- Задача: найти все туры из города 'a' с минимальной стоимостью
-- Таблица: routes(point1, point2, cost)

WITH RECURSIVE tours AS (
    -- Стартовая точка
    SELECT
        ARRAY['a', r.point2] AS tour,
        r.cost AS total_cost,
        r.point2 AS current_city
    FROM   routes r
    WHERE  r.point1 = 'a'

    UNION ALL

    -- Рекурсивный шаг
    SELECT
        t.tour || r.point2,
        t.total_cost + r.cost,
        r.point2
    FROM   tours t
    JOIN   routes r ON r.point1 = t.current_city
    WHERE  r.point2 <> ALL(t.tour)   -- не возвращаться в уже посещённый
)
SELECT total_cost, tour
FROM   tours
WHERE  array_length(tour, 1) = (SELECT COUNT(*) + 1 FROM routes WHERE point1 = 'a')
  AND  tour[array_upper(tour,1)] = 'a'   -- вернуться в начало
ORDER BY total_cost;