Продолжение прошлого поста. Тезис: выбирайте данные, только если нужно передать их третьей стороне, например фронтенду. Для промежуточных вычислений это чаще всего не требуется. Достаточно сказать базе: обнови такие-то поля согласно такой-то логике, и она все сделает сама.

Обновление силами SQL особенно хорошо на больших данных. Не нужно гонять тысячи строк по сети. По компьютерным меркам сеть медленная, потому что это череда системных вызовов на обеих сторонах. Сеть требует сериализации данных. Даже в надежных сетях пакеты теряются, требуется повторная отправка и подтверждение.

Не нужно писать код на языке приложения. Код на условном Питоне или Джаве всегда длиннее и многословнее, чем на SQL. Нет кода – нет проблем.

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

create table users (
    id integer primary key,
    name text not null,
    points integer not null default 0
);

create table profiles (
    id integer primary key,
    user_id integer not null,
    avatar text null,
    del_requested boolean null default false
);

Кроме имени, в пользователе записано число баллов. Профиль ссылается на пользователя, и в нем два поля: аватарка (может быть пустой) и запрос на удаление (по умолчанию ложь). Вставим много записей:

insert into users
select
    x,
    format('user_%s', x),
    round(random() * 100000)
from
    generate_series(1, 10000) as seq(x);

insert into profiles
select
    x,
    x,
    null
    false
from
    generate_series(1, 10000) as seq(x);

Оператор INSERT принимает SELECT, который порождает данные. Не нужно генерить их на Питоне и передавать по сети, все происходит внутри базы.

Первая задача – поместить в отдельную таблицу топ-100 пользователей. Это делается запросом:

drop table if exists users_top_100;

create table users_top_100 as
select * from users
order by points desc
limit 100;

table users_top_100;

┌──────┬───────────┬────────┐
│  id  │   name    │ points │
├──────┼───────────┼────────┤
│ 6637 │ user_6637 │  99996 │
│ 8909 │ user_8909 │  99994 │
│ 9040 │ user_9040 │  99979 │
│ 2631 │ user_2631 │  99978 │
│ 7240 │ user_7240 │  99961 │
│  972 │ user_972  │  99958 │
│ 8698 │ user_8698 │  99924 │
│ 3850 │ user_3850 │  99914 │
│  382 │ user_382  │  99892 │
│ 2841 │ user_2841 │  99877 │
│ 2607 │ user_2607 │  99870 │
│ 5207 │ user_5207 │  99858 │

В приложении мы бы сначала прочитали данные, создали таблицу и записали данные в нее. Все это можно поручить базе. Таблица может быть временной для каких-то расчетов. Конечно, не следует создавать их в апишках, которые должны работать быстро. Но для других задач временные таблицы очень даже полезны.

Другой пример. Фирма проводит акцию: загрузи аватар и получи тысячу баллов. Предположим, некоторые пользователи добавили аватарку:

update profiles
set avatar = format('htts://path/to/file_%s', id)
where id between 500 and 600;
-- UPDATE 101

Вот как накинуть баллы тем, у кого она есть:

update users u
set points = points + 1000
from profiles p
where u.id = p.user_id
and p.avatar is not null;
-- UPDATE 101

Оператор UPDATE поддерживает FROM с произвольным запросом, таблицей и так далее. В условии проверяют совпадение строк по ключам. Обновятся только те пользователи, профили которых с аватаркой.

Третья задача: некоторые пользователи отметили в профиле значок “удалите меня”:

update profiles
set del_requested = true
where id between 666 and 999;
-- UPDATE 334

Вот как удалить такие профили и их пользователей одним запросом:

with del_profiles(user_id) as (
    delete from profiles
    where del_requested
    returning user_id
)
delete from users u
using del_profiles del
where u.id = del.user_id;
-- DELETE 334

Во всех случаях мы ничего не передавали клиенту.

Вставки могут быть с реакцией на конфликт (выражение ON CONFLICT). Есть команда MERGE, которая делит два набора данных на категории: только слева, пересечение, только справа. Для каждой категории можно задать реакцию: пропустить, удалить, записать куда-то и так далее.

Управлять данными при помощи SQL – это настоящий праздник. Нет зависимости от приложения и рантайма. База делает все сама: работает производительный код на Си, многократно проверенный и отлаженный. Не отбирайте у базы ее работу. Она сделает все лучше вас, достаточно базовых навыков SQL.

← Совет дня №27

Совет дня №29 →