Регулярные выражения в 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

SQL-шпаргалка

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

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

https://antonz.ru/sql-cheatsheet/

Выпуск «Подлодки» про SQL

Вышел новый эпизод лучшего айтишного подкаста Podlodka, полностью посвященный SQL! Вместе с ведущими Катей и Стасом обсудили широкий спектр тем, посвященный языку — от самых основ до новейших фич 2023 года.

Получился очень насыщенный тур по SQL. Даже если отлично знаете его — наверняка найдете для себя что-нибудь новенькое. Мне очень понравилось (но я тут, конечно, не объективен).

Выпуск на 2+ часа, так что вот темы с таймкодами, чтобы лучше ориентироваться:

0:00 Что такое SQL. Почему SQL прекрасен. Зачем SQL разработчику. Стандарт SQL.

15:20 Базовый SQL. DDL и DML. Джойны.

22:12 Транзакции. Что это и зачем. ACID. Изоляция. Уровни изоляции.

45:53 Быстродействие. Фулсканы и индексы. Статистика. Пакетные операции. Длинные транзакции.

1:01:18 Расширенные возможности. Представления. Ограничения целостности. Триггеры. Хранимые процедуры и функции.

1:14:51 SQL для анализа данных. Описательная статистика. OLAP-кубы. Оконные функции. Кластеризация. Очистка данных.

1:27:47 Безопасность. Авторизация и права. Инъекции и динамический SQL. Шифрование. Идентификаторы. Выстрел в ногу.

1:37:56 Современный SQL. Массивы. JSON. Рекурсия. Темпоральные таблицы. Графы.

1:59:22 Как прокачаться в SQL. Курсы. Книги. Статьи.

Надеюсь, вам пригодится! И подписывайтесь на «Подлодку», она крутая.

SQL-рецепт: Сегментация данных

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

Решение — использовать функцию ntile() поверх SQL-окна.

Пример

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

— высокооплачиваемые,
— средние,
— низкооплачиваемые.

select
  ntile(3) over w as tile,
  name, salary
from employees
window w as (order by salary desc)
order by salary desc, id;

t  sal  name
1  120  Иван
1  104  Леонид
1  104  Марина
1  100  Анна
2  96   Вероника
2  96   Григорий
2  90   Ксения
3  84   Елена
3  78   Борис
3  70   Дарья

ntile(n) разбивает все записи на n групп и возвращает номер группы для каждой записи. Если общее количество записей (10 в нашем случае) не делится на размер группы (3), то первые группы будут крупнее последних.

Нюансы

— ntile() всегда старается разбить данные так, чтобы группы были одинакового размера. Поэтому записи с одинаковым значением з/п вполне могут попасть в разные (соседние) группы.

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

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

Неподдерживаемые фичи

Некоторым фичам SQL-стандарта уже больше 30 лет, а они до сих пор не реализованы (и, вероятно, никогда не будут).

Например, стандарт разрешает накладывать условия на таблицу целиком, но СУБД это не поддерживают.

Есть таблица сотрудников:

create table employees (
  id integer primary key,
  name varchar(50),
  salary integer
);

Накладываем условие «зарплатный фонд должен быть меньше 3000»:

create assertion salary_fund
check (
  3000 > (select sum(salary) from employees)
);

И получаем:

error: CREATE ASSERTION is not yet implemented

🤷‍♀️

Кодирование данных в SQLite

Все знают про шестнадцатеричное кодирование (hex):

select hex('hello');
-- 68656C6C6F

select unhex('68656C6C6F');
-- hello

Но есть и другие способы.

Base32:

select encode('hello', 'base32');
-- NBSWY3DP

select decode('NBSWY3DP', 'base32');
-- hello

Base64:

select encode('hello', 'base64');
-- aGVsbG8=

select decode('aGVsbG8=', 'base64');
-- hello

Base85:

select encode('hello', 'base85');
-- BOu!rDZ

select decode('BOu!rDZ', 'base85');
-- hello

URL-кодирование:

select encode('hel lo!', 'url');
-- hel%20lo%21

select decode('hel%20lo%21', 'url');
-- hel lo!

И все они теперь доступны в SQLite с помощью расширения crypto.

SQL-рецепт: Сравнение с соседями

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

Решение — использовать функцию lag() поверх SQL-окна.

Пример

Сравним расходы компании от месяца к месяцу в абсолютном выражении:

select
  year, month,
  expense,
  expense - lag(expense) over w as diff
from expenses
window w as (
  order by year, month
)
order by year, month;

year  mon exp  dif
2020  1    82  
2020  2    75   -7
2020  3   104   29
2020  4    94  -10
2020  5    99    5
2020  6   105    6
2020  7    95  -10
2020  8   110   15
2020  9   104   -6

lag(value, offset) возвращает значение value из строки, отстоящей на offset строк назад от текущей.

По умолчанию offset равно 1, указывать его не обязательно.

Нюансы

— Еще есть функция lead(). Работает в точности как lag(), только смотрит вперед, а не назад.

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

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

25 текстовых функций в SQLite

С помощью стандартных функций instr, substr и replace можно много чего сделать. Но мне хотелось более полного набора возможностей, как в PostgreSQL, Python или Go. Пришлось добавить.

Новое расширение text содержит 25 функций для работы со строками: от slice, contains и count до split_part, trim и repeat. И многие из них совместимы с PostgreSQL!

А если добавить функции работы с регулярками из regexp, вас и вовсе будет не остановить ツ

Некоторые из них в SQLite уже были, но с полным набором удобнее