Оконные функции: агрегация

Продолжаем разбираться с «окошками». В этой части посмотрим, как считать суммарные и средние показатели.

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

https://antonz.ru/window-aggregate/

Добавить или обновить записи одним запросом

Участник курса спрашивает:

Есть таблица, хочу ежедневно загружать в неё обновления из csv. Если по id запись уникальная — добавлять, а если запись существует — обновлять поля, которые отличаются, кроме id. Это можно как-то из коробки?

Да! Такой подход (добавить или обновить по необходимости) в мире SQL называется «upsert». В SQLite он реализуется через инструкцию on conflict.

Допустим, мы хотим добавить/обновить записи в таблице stats. Тогда запрос будет примерно такой:

insert into stats(id, last_updated, usage)
select ... from ...
on conflict(id) do update
set
last_updated = excluded.last_updated,
usage = usage + excluded.usage;

excluded — это та запись, которую мы пытаемся вставить (и она вызвала конфликт по id).

Что особенно приятно, такой же синтаксис сработает и в PostgreSQL (SQLite позаимствовал фичу у него).

P.S. Присоединяйтесь к чату «SQL на практике»! https://t.me/sqliter_chat

Оконные функции: скользящие агрегаты

Заканчиваем разбираться с оконными функциями. В этой части посмотрим, как считать показатели в динамике — скользящее среднее и сумму нарастающим итогом.

Это последняя статья серии! Гарантирую, что более понятного введения в «окошки» не найдете во всем интернете 💪

https://antonz.ru/window-rolling/

Курс по оконным функциям

Закончил курс по «окошкам»! 15 уроков, 39 задачек, бессчётное количество картинок и гифок.

Подходит для всех, кто знает обычные селекты и работает с MySQL, PostgreSQL или SQLite.

Стоит 500₽, для выпускников «SQLite для аналитики» — скидка 50%.

https://stepik.org/z/95367

🤔 Задачка о группах

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

Рассмотрим на конкретном примере. Есть таблица с атрибутами пользователей:

user_id, attr
1, alpha
1, beta
2, beta
2, gamma
3, delta
3, epsilon
4, delta
4, zeta
5, zeta
5, alpha
6, iota
7, iota
7, kappa
8, kappa
8, lambda

Хотим объединить пользователей в группы. При этом действуют правила:

— Если пользователи A и B обладают общим свойством → они входят в одну группу.
— Если A и B обладают общим свойством P1, B и C обладают общим свойством P2 → A, B, C входят в одну группу.
— Идентификатором группы считается минимальный идентификатор из входящих в нее пользователей.

Для каждой группы хотим еще определить список атрибутов ее пользователей.

В итоге должно получиться две группы:

group_id, user_id, attrs
1, 1, "alpha,beta,gamma,delta,epsilon,zeta"
1, 2, "alpha,beta,gamma,delta,epsilon,zeta"
1, 3, "alpha,beta,gamma,delta,epsilon,zeta"
1, 4, "alpha,beta,gamma,delta,epsilon,zeta"
1, 5, "alpha,beta,gamma,delta,epsilon,zeta"
6, 6, "iota,kappa,lambda"
6, 7, "iota,kappa,lambda"
6, 8, "iota,kappa,lambda”

За сколько запросов возьметесь решить задачу? Каждый CTE или подзапрос считается за отдельный запрос. Использовать процедурные расширения SQL вроде pl/sql и pl/pgSQL — нельзя.

Опрос следует.

Первому, кто решит задачу за минимальное количество запросов и сможет объяснить решение — любой мой курс в подарок ツ Пишите → @nalgeon

🤔 Задачка о группах — решение

Увидев задачку о группах, коллега написал мне:

> Человек, порочащий олимпиадное программирование, просит решить задачку ЗА ОДИН запрос. Зачем?

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

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

🌤️ SQLite-песочница в браузере

Чего мне всегда не хватало, так это аналога JSFiddle / CodePen для SQL. Онлайн-песочницы, в которой можно быстро проверить запрос и поделиться с другими.

Да, есть SQL Fiddle и DB Fiddle, но оба весьма неудобные, на мой взгляд.

Вот чего хотелось:

— Возможность загрузить готовую базу, а не писать SQL для создания таблиц.
— Подключать как локальные базы, так и удаленные (по url).
— Сохранять базу и запросы в облаке.
— Бесплатно и без регистрации.
— Свежайшая версия SQLite.
— Минимализм.

В общем, сделал сам:
sqlime.org

Написал на хабр, как устроена sqlite-песочница в браузере. Если вы немного знакомы с js, может быть интересно → https://habr.com/ru/post/580240/

Табличные выражения 👍

Прием №1, чтобы писать хорошие читаемые SQL-запросы — это табличные выражения (CTE). Люди их боятся, а зря.

Любой подзапрос:

select a, b, c
from (X)
where e = f

Механически превращается в CTE:

with cte_name as (X)
select a, b, c
from cte_name
where e = f

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

Существует миф, что «CTE медленные», но на современных СУБД это не так. Да и вообще — как с любым утверждением о медленности чего-либо, всегда можно сравнить два варианта (подзапрос и CTE), если сомневаешься.

Кроме обычных CTE бывают еще рекурсивные — вот это реально злые ребята. А обычные прекрасны, используйте их.

Мат. статистика в SQLite

Из коробки SQLite поддерживает только min(), max() и avg(). Но с помощью расширения stats легко посчитать медиану, моду, процентили и стандартное отклонение:

select
  median(num_pages),
  mode(num_pages),
  percentile_90(num_pages),
  percentile_95(num_pages),
  percentile_99(num_pages),
  stddev(num_pages)
from books;

Найти максимально похожую строку

Подписчик задал интересный вопрос:

Есть строка в переменной str1.
Есть sqlite3 и 600,000 строк внутри.
Как найти макимально приближенно похожую строку в базе? Точного сходства с str1 в базе нет!

Посмотрим на упрощенном примере. Допустим, у нас есть таблица employees, а в ней столбец name:

sqlite> select name from employees;
Дарья
Борис
Елена
Ксения
Леонид
Марина
Иван
Вероника
Григорий
Анна

Хотим найти сотрудника, чье имя больше всего похоже на Вера. Как это сделать?

Если бы в SQLite была функция, которая считает похожесть между двумя строками, все было бы просто:

select
  name,
  similarity(name, 'Вера') as sim
from employees
order by sim desc
limit 1

Но такой функции нет. Значит, либо искать расширение, в котором она найдется, либо подключить функцию из любимого языка программирования. На питоне, например, сделать это несложно:

import sqlite3
from difflib import SequenceMatcher

def similarity(a, b):
  ratio = SequenceMatcher(None, a.lower(), b.lower()).ratio()
  return round(ratio, 2)

db = sqlite3.connect("employees.db")
db.create_function("similarity", 2, similarity)

Теперь можно выполнить наш запрос:

cursor = db.execute(query)
result = cursor.fetchall()
print(result)

Он вернет самое похожее имя:

[('Вероника', 0.67)]

Ровно то, что мы хотели. Для 600К строк, правда, отработает не слишком быстро.

Вижу подзапрос → меняю на CTE

Многие студенты (да и не только студенты) обожают многоуровневые SQL-запросы. Пожалуйста, пожалуйста, пожалуйста, не пишите их. Используйте табличные выражения: https://antonz.ru/cte/

Нечеткое сравнение строк и фонетика

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

Сравнение строк по похожести:

— Расстояние Левенштейна
— Расстояние Дамерау — Левенштейна
— Расстояние Хэмминга
— Сходство Джаро — Винклера
— Оптимальное выравнивание строк
— Расстояние Spellcheck

Фонетические коды:

— Soundex
— Refined Soundex
— Spellcheck

И транслитерация (привет → privet).

https://github.com/nalgeon/sqlean/blob/main/docs/fuzzy.md

Быстрый поиск похожих слов на SQL
(готовое решение)

Я что-то закопался с подробной статьей про поиск похожих слов, так что вот пока готовое решение на SQLite.

Напомню, что задача в общем виде такая:

— Человек ввел произвольное слово.
— Мы хотим найти наиболее похожее слово из готового словаря.
— И сделать это быстро.

Полный перебор по словарю работает хорошо, но медленно.

Чтобы сделать быстро, понадобится расширение spellfix.

Подключаем расширение:

.load ./spellfix

Создаем таблицу для словаря:

create virtual table dictionary using spellfix1;

Загружаем в нее данные:

insert into dictionary(word)
select ... from ...;

И ищем похожие слова:

select word
from dictionary
where word match ?
limit 1

Например:

sqlite> select word from dictionary where word match 'абривиатура' limit 1;
аббревиатура

sqlite> select word from dictionary where word match 'рассчет' limit 1;
расчет

sqlite> select word from dictionary where word match 'дорга' limit 1;
дорога

Изи!

Позже отдельно распишу, как и почему все работает. И как сделать самостоятельно на любой базе.

500 учеников на курсе про «окошки»!

Зашел сегодня на «Степик» и увидел, что на курс по оконным функциям записались уже 500 человек. Когда его делал, совсем не рассчитывал на такую популярность.

Вообще, курс про «окошки» появился как спин-офф моего большого курса «SQLite для аналитики». Там был модуль «оконные функции», и он мне самому так понравился, что на его основе сделал отдельный курс.

Судя по всему, получилось неплохо, потому что студентов много и отзывы очень позитивные. Люди находят курс без всякой рекламы. Полностью прошли курс 40% участников — это очень недурно для онлайн-формата. Выпускники пишут, что стали регулярно использовать «окошки» в повседневной работе — лучший результат, что я могу представить.

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

«Родительскому» курсу про SQLite такая популярность и не снилась 🤷

SQLite 3.37

Тут у sqlite вышла новая версия! Да какая — теперь он научился проверять типы данных (двадцать лет ругали, что не проверяет).

Написал обзор основных изменений:
https://habr.com/ru/news/t/592085/