💅 InterDB — сайт про внутреннее устройство PostgreSQL!

Если работаешь с базами и хочешь понимать, как Postgres устроен изнутри, этот сайт точно стоит внимания. Здесь подробно объясняется, как хранятся данные, работают индексы, транзакции и репликация. Всё показано на схемах и описано простым языком.

📌 Оставляю ссылочку: interdb.jp

➡️ SQL Ready | #ресурс

RANK vs DENSE_RANK: точное ранжирование в SQL!

При создании рейтингов или аналитических сводок часто нужно определить позиции записей по показателю — с учётом повторов. Для этого используются оконные функции RANK() и DENSE_RANK().

Создадим таблицу с продажами:

CREATE TABLE sales (
  employee TEXT,
  total_sales INT
);

INSERT INTO sales VALUES
('Alice', 500),
('Bob', 700),
('Charlie', 700),
('Diana', 400);

Выполним ранжирование по сумме продаж:

SELECT employee,
       total_sales,
       RANK() OVER (ORDER BY total_sales DESC) AS rank_pos,
       DENSE_RANK() OVER (ORDER BY total_sales DESC) AS dense_rank_pos
FROM sales;

Результат:

employee | total_sales | rank_pos | dense_rank_pos
---------+--------------+----------+----------------
Bob      | 700          | 1        | 1
Charlie  | 700          | 1        | 1
Alice    | 500          | 3        | 2
Diana    | 400          | 4        | 3

🔥 Так что, RANK() пропускает позиции после повторов, а DENSE_RANK() идёт подряд, без «дыр» в нумерации.

➡️ SQL Ready | #практика

Ускоряй отчёты с материализованными представлениями!

Когда отчёты считаются минутами, а данные почти не меняются, решение может быть в кэшировании результата.

Используй материализованное представление (как VIEW, но результат хранится физически в таблице):

CREATE MATERIALIZED VIEW daily_stats AS
SELECT date(created_at) AS day,
       COUNT(*) AS total_orders
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_stats(day);  -- нужно для CONCURRENTLY

Обновлять можно вручную или по расписанию (cron, pg_cron):

REFRESH MATERIALIZED VIEW_stats;

А если нужно без блокировок чтения:

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats;

🔥 Помни: concurrently медленнее и требует индекс, но позволяет читать данные во время обновления.

➡️ SQL Ready | #совет

Выбираем уникальные записи с приоритетом по дате!

Когда нужно взять по одной записи на пользователя, DISTINCT не всегда подходит: он не умеет выбирать, какая строка «главная». В PostgreSQL есть мощный приём — DISTINCT ON.

Создадим таблицу:

CREATE TABLE users (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL,
    login_count INT,
    created_at TIMESTAMP DEFAULT now()
);

Добавим данные:

INSERT INTO users (email, login_count, created_at) VALUES
('alice@mail.com', 5, '2024-01-01'),
('alice@mail.com', 8, '2024-03-01'),
('bob@mail.com',   2, '2024-02-01');

Теперь выберем только последнюю запись на каждый email:

SELECT DISTINCT ON (email)
       email, login_count, created_at
FROM users
ORDER BY email, created_at DESC, id DESC;

Результат:

email           | login_count | created_at
----------------+--------------+----------
alice@mail.com  | 8            | 2024-03-01
bob@mail.com    | 2            | 2024-02-01

🔥 DISTINCT ON берёт по одной строке на группу, а ORDER BY задаёт, какая именно — последняя, первая или с нужным приоритетом.

➡️ SQL Ready | #практика