Вычисляемые столбцы

Иногда поле в запросе рассчитывают на основе других столбцов таблицы.

Например, есть столбец income с годовым доходом и tax_rate с налоговой ставкой. Тогда можно посчитать годовой налог:

select
id,
income * tax_rate as tax
from people;

А чтобы не таскать везде эти вычисления, удобно создать виртуальный столбец.

В общем виде синтаксис такой:

alter table ТАБЛИЦА
add column СТОЛБЕЦ ТИП as (ВЫРАЖЕНИЕ);

Для налогов будет так:

alter table people
add column tax real as (
income * tax_rate
);

После этого столбец можно использовать в запросах точно так же, как обычные столбцы:

select id, tax
from people;

Виртуальные столбцы не хранятся в базе и рассчитываются «на лету». Но по ним вполне можно построить индекс, если захотите ускорить выборку.

Строго говоря, в SQLite есть виртуальные (virtual) вычисляемые столбцы и хранимые (stored). Хранимые сохраняются на диске, но создать их через alter table невозможно, поэтому в основном пользуются виртуальными.

Вычисляемые столбцы могут использовать любые столбцы таблицы, но не другие таблицы и не результаты подзапросов. Оно и к лучшему: для более сложных комбинаций есть представления (views) и временные таблицы (temp tables). Но о них как-нибудь в другой раз.

JSON и виртуальные столбцы

Допустим, вы решили вести журнал событий, которые происходят в системе. События бывают разных типов, у каждого свой набор полей. Например, вход в систему:

{
  "timestamp": 1652614531,
  "object": "user",
  "object_id": 11,
  "action": "login",
  "details": {
    "ip": "192.168.0.1"
  }
}

Или пополнение счета:

{
  "timestamp": 1652614584,
  "object": "account",
  "object_id": 12,
  "action": "deposit",
  "details": {
    "amount": "1000",
    "currency": "USD"
  }
}

Вы решаете не заниматься нормализацией по таблицам, а хранить прямо в JSON. Заводите таблицу events с единственным полем value:

select value from events;

{"timestamp":1652614531,...
{"timestamp":1652614584,...
{"timestamp":1652614644,...

И выбираете события по конкретному объекту:

select
  json_extract(value, '$.object'),
  json_extract(value, '$.action')
from events
where json_extract(value, '$.object_id') = 11;

┌────────┬────────┐
│ object │ action │
├────────┼────────┤
│ user   │ login  │
└────────┴────────┘

Все здорово, но json_extract() при вызове каждый раз парсит текст, так что на сотне тысяч записей запрос будет работать медленно. Что делать?

Создать виртуальные столбцы:

alter table events
add column object_id integer
as (json_extract(value, '$.object_id'));

Построить индекс:

create index events_object_id on events(object_id);

Теперь запрос работает моментально:

select object, action
from events
where object_id = 11;

Благодаря виртуальным столбцам получилась практически NoSQL база данных ツ

песочница

Временные таблицы

Бывает, хочется собрать данные из нескольких таблиц в одну, и дальше с ними поработать. Например, для удобства выбрать вакансии вместе с работодателями и регионами:

select v.*, e.name, a.name
from vacancy as v
  join employer as e on e.id = v.employer_id
  join area as a on a.id = v.area_id

Теперь хочется запускать запросы по сводным данным. Есть три способа это сделать:

1) Common Table Expression (CTE)
2) Представление (view)
3) Временная таблица (temporary table)

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

Представление — это такой именованный селект, к которому можно обращаться как к обычной таблице:

create view vacancy_view as
select ... from ... join ...;

select * from vacancy_view;

Очень похоже на CTE, только не приходится каждый раз повторять селект в запросе — достаточно написать название представления. Как и CTE, представление тоже вычисляется каждый раз.

Во многих СУБД есть материализованные представления, которые сохраняют данные на диск — но не в SQLite.

Временная таблица — точно такая же, как настоящая: хранит данные на диске, можно построить индексы для быстрой выборки. Но существует она, только пока открыто соединение с базой данных.

create temporary table vacancy_temp as
select ... from ... join ...;

select * from vacancy_temp;

Временные таблицы отлично подходят для экспериментов, когда вы только знакомитесь с данными. Можно позволить себе любые безумства — ведь после отключения от базы все будет забыто ツ

песочница

Про курсы

Как знают давние подписчики канала, у меня есть три курса на Степике:

— Оконные функции SQL
— SQLite на практике
— SQLite для аналитики

Первые два частично охватывают материал третьего. Поэтому я решил, что будет правильно давать скидку на «SQLite для аналитики» для выпускников «окошек» и «на практике».

Так что если вы:

— успешно решили все задачки в «SQLite на практике» (скидка 500₽),
— или получили сертификат с отличием в «Оконные функции SQL» (скидка 500₽),
— и хотите пройти «SQLite для аналитики»,

то присылайте ссылку на свой Степик-профиль мне @nalgeon, чтобы получить промо-код.

Расширения SQLite для процессоров Apple

Наконец дошли руки настроить сборку расширений для ARM-процессоров Apple (M1, M2).

Плюс вместо кучи мелких файлов сделал по архиву для каждой операционной системы.

Заодно напомню, какие сейчас есть расширения:

— crypto: криптографические хеш-функции вроде SHA-1 и SHA-256.
— fileio: работа с файловой системой — чтение и запись файлов, создание каталогов.
— fuzzy: нечеткое сравнение строк, фонетические алгоритмы, транслитерация.
— ipaddr: манипуляция IP-адресами и подсетями.
— json1: работа с JSON.
— math: математические функции.
— re: регулярные выражения.
— stats: статистика — медиана, процентили, стандартное отклонение.
— text: работа со строками.
— unicode: поддержка юникода для функций upper(), lower() и LIKE.
— uuid: генерация уникальных идентификаторов.
— vsv: работа с CSV-файлами как с таблицами базы.

И еще вагон расширений в инкубаторе (для них сборку под ARM пока не делал).

https://github.com/nalgeon/sqlean/releases/latest

Пользовательские функции в SQLite

Вообще-то их нет. Да, можно написать функцию на С или Python, и зарегистрировать в SQLite. Но не прямо в SQL.

Но все меняется, если подключить расширение define! С ним можно писать функции на чистом SQL.

С define несложно определить собственную функцию:

select define('sumn', ':n * (:n + 1) / 2');

А затем использовать, как будто встроенную:

> select sumn(5);
15

Пользовательские функции могут принимать несколько параметров и вызывать другие функции.

Сгенерировать случайное N, такое что a ≤ N ≤ b:

> select define('randint', ':a + abs(random()) % (:b - :a + 1)');

> select randint(10, 99);
42

Можно даже задать функцию, которая возвращает несколько значений!

Подробности в документации

Выполнить произвольный SQL из строки

Задавать функции прямо в SQL — это здорово. Но что делать, если хочется динамически составить SQL-запрос и выполнить его?

Поможет функция eval():

> select eval('select 10 + 32');
42

Можно выполнять любые DDL и DML запросы:

select eval('create table tmp(value int)');
select eval('insert into tmp(value) values (1), (2), (3)');
select eval('select value from tmp');
select eval('drop table tmp');

Функция входит в расширение define.

SQLite в 2022 году

Вот заметные фичи, которые появились в SQLite в прошедшем году:

— Операторы -> и ->> в JSON (3.38)
— RIGHT и FULL JOIN (3.39)
— Функция unixepoch() (3.38)
— Оператор IS DISTINCT FROM для совместимости со стандартом (3.39)
— Расширение recovery для восстановления битых баз
— SQLite теперь компилируют в WASM (3.40)

Попробовать новые фичи всегда можно в песочнице sqlime

Регулярные выражения в SQLite

Регулярные выражения — пожалуй, самый мощный инструмент обработки текста без программирования.

По умолчанию «регулярки» отсутствуют в SQLite, но вы легко можете их добавить с новым расширением regexp.

В комплекте:

— проверка строки на соответствие шаблону;
— извлечение фрагмента строки по шаблону;
— замена по шаблону.

Поддерживается довольно навороченный синтаксис, включая всякие разные группы, ленивые квантификаторы и look-around.

Unicode тоже поддерживается, что для русского особенно приятно.

Главное, не злоупотребляйте :)

Сводные таблицы в SQLite

Сводные таблицы — популярный инструмент отчетности.

Допустим, у нас есть таблица продаж продуктов по годам:

— продукт,
— год,
— выручка.

И мы хотим преобразовать ее в сводную:

— по строкам продукты,
— по столбцам годы,
— на пересечении строки и столбца — выручка.

У SQLite нет встроенного оператора PIVOT. Но все же существует несколько способов решить задачу:

1. Фильтр по итогам.
2. Динамический SQL.
3. Специальное расширение.

Подробности в отдельной статье, а то такие толстые запросы в телеграм плохо ложатся.

https://antonz.ru/sqlite-pivot-table/

Индексы

Лучшее, что можно сделать после того, как вы освоили основы SQL (select, where, join, group by итп) — разобраться, как работают индексы.

Именно это позволит вам писать эффективные запросы.

Люди часто думают, что индексы — это какая-то ускорительная магия. На самом деле, нет.

Индекс — это отсортированный набор значений, каждое из которых указывает обратно на запись таблицы, из которой его взяли.

Индекс организован в виде дерева. Это позволяет движку БД моментально находить нужное значение и вытаскивать связанную запись из таблицы (см. упрощенную картинку).

Лучший материал, чтобы освоить индексы и быстродействие SQL в целом — Use The Index, Luke Маркуса Винанда. Его книга выводит понимание баз данных на новый уровень.

Доступна бесплатно на сайте: https://use-the-index-luke.com/

Ну очень грубая (примерно как π = 3) иллюстрация того, как работает поиск по индексу

Работа с файлами в SQLite

У SQLite есть расширение для работы с файлами с такими функциями:

— readfile(path)
— writefile(path, data)

Они читают или пишут файл одним куском, что плохо подходит для больших файлов.

Чтобы работать с файлами было удобнее, я сделал расширение fileio с парой дополнительных функций:

fileio_scan(path)

Построчно читает файл:

select rowid, value, name
from fileio_scan('hello.txt');

fileio_append(path, str)

Дописывает строку в файл:

create table hello(value text);
-- insert some data

select sum(
  fileio_append(
    'hello.txt',
    value||char(10)
  )
) from hello;

Теперь можно работать с файлами построчно, экономя память!

Импорт-экспорт в SQLite

Написал небольшое руководство по импорту и экспорту данных в SQLite:

— Загрузить JSON-документ из файла.
— Построчно прочитать текстовый файл.
— Построчно записать результаты выполнения запроса в файл.
— Импортировать файлы из каталога.

https://antonz.ru/sqlean-fileio/

Новые уроки по оконным функциям

Для выпускников курса по окошкам — вышли три новых урока:

— Мат. статистика. Изучаем относительный ранг, медиану и процентили.

— Острова и кластеры. Объединяем похожие значения в группы.

— Очистка данных. Удаляем дубликаты и заполняем пропуски.

Приглашаю попробовать!

Интерактивные SQL-примеры

Есть новость для всех поклонников SQL 🙂

1.5 года назад я сделал онлайн-песочницу для отладки и шеринга SQL-запросов. С тех пор постоянно использую ее, для обучения и преподавания.

Но кое-чего не хватало.

Я хотел, чтобы была возможность превратить любую статью или заметку в блоге в интерактивную SQL-песочницу.

Чтобы читатели могли отредактировать запрос и посмотреть, как изменится результат. Или даже попробовать собственные запросы.

Я хотел интерактивные SQL-примеры в браузере.

И в итоге их сделал!

Теперь каждый может превратить статичные SQL-запросы в статье, заметке или веб-странице — в интерактивные примеры.

Что особенно приятно, это требует минимальных изменений в HTML-коде.

Судите сами: https://sqlime.org/examples.html

regexp_capture

Частая задача в регулярках — извлечь конкретную группу вместо всего совпадения.

Например:

— строка = 'abc'
— паттерн = 'a(.)c'
— группа 1 = 'b'

regexp_substr в Oracle и MySQL так не умеет, а Postgres требует указывать 4 дополнительных параметра.

Поэтому для SQLite я запилил отдельную функцию regexp_capture, которая делает ровно это — извлекает конкретную группу из совпадения:

select regexp_capture('abc', 'a(.)c', 1);
-- b

SQL-консультант в песочнице

Кому-то нравится заставлять искусственный интеллект делать всякую дичь, ну а я предпочитаю более практичное и полезное применение.

Поэтому с сегодняшнего дня в SQLite-песочнице заработала функция «Ask AI».

ИИ может объяснять, исправлять и комментировать ваш SQL — не покидая песочницы:

➊ Напишите вопрос в виде комментария перед SQL-запросом.

➋ Нажмите кнопку «Ask AI».

➌ Получите не очень быстрый, но часто полезный ответ от ИИ.

Попробуйте! Все, что нужно — API-ключ для OpenAI.

https://sqlime.org

P.S. Если API-ключа у вас нет, задать вопрос всегда можно в нашем чате @sqliter_chat. ИИ там тоже иногда отвечает, ну и не только он ツ

SQL-рецепт: Ранжирование строк

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

Решение — использовать функцию rank() поверх SQL-окна, упорядоченного по целевым столбцам.

Пример

Отранжируем сотрудников из таблицы employees по зарплате:

select
  rank() over w as "rank",
  name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;

r  sal  name
1  120  Иван
2  104  Леонид
2  104  Марина
4  100  Анна
5   96  Вероника
5   96  Григорий
7   90  Ксения

Функция rank() присваивает каждому сотруднику ранг в соответствии с его зарплатой (order by salary desc).

Обратите внимание, что сотрудники с одинаковой зарплатой получили одинаковые ранги (Леонид и Марина, Вероника и Григорий).

Нюансы

— Можно использовать dense_rank() вместо rank(), чтобы избежать «дыр» в рангах.

— Если СУБД не поддерживает инструкцию window, можно встроить определение окна: rank over (order by ...)

→ Оконные функции SQL