Открыт шестой (предпоследний) модуль курса — Большие наборы данных. В нем вы научитесь работать с увесистыми таблицами так же легко, как с мелкими. Освоите, как загружать и выгружать большие датасеты. И конечно, поймете, за счет чего запросы работают быстро (или небыстро).

Эти знания пригодятся с любой современной СУБД, будь то Oracle, SQL Server или PostgreSQL. Все они устроены примерно одинаково, когда дело доходит до выполнения селектов.

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

https://stepik.org/lesson/475989/step/1?unit=466901

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

Нет более обманчивого раздела SQL, чем «оконные функции». Когда слышишь эти слова, думаешь «наверно, просто придумали какие-то дополнительные функции». Бросаешь взгляд в доку, видишь row_number(), думаешь «ну точно, так и есть».

А вот и нет! «Оконные функции» — это отдельный доменный язык, встроенный в обычный SQL. И он сложнее, чем все, что вы знали о селектах, вместе взятое (включая фильтрацию, группировку и сортировку).

Чтобы полноценно объяснить оконные функции, по ним надо писать книгу или делать отдельный курс. Поэтому вот что я планирую:

1) Объяснить, что такое «окно» в SQL и как оно работает.

2) Показать некоторые частые задачи, которые решают с помощью оконных функций.

Освоив эти два пункта, вы сможете «добрать» недостающее из документации СУБД (а вот начинать с документации не советую, она суховата).

Если у вас есть конкретные вопросы по «окошкам» в SQL — напишите в комментариях, постараюсь учесть.

JSON → CSV

Я не очень люблю игрушечные примеры, поэтому вот вам настоящий.

У HeadHunter есть замечательное API станций метро по городам. Оно отдает данные в иерархическом JSON — использовать «как есть» такое неудобно. Хорошо, что с помощью SQLite любой JSON можно превратить в набор CSV-шек.

А автоматизировать процесс удобно через GitHub Actions. Описал, как это работает все вместе (+ исходники):
https://antonz.ru/github-actions-scraping/

Если что непонятно, спрашивайте в комментариях ツ

Зачем нужны оконные функции

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

Я бы даже начал не со «что это», а «зачем оно». Поэтому на конкретных примерах показал, чем могут быть полезны «окошки» в SQL:

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

​Окошки на курсе

Кто-то из великих сказал, что если освоить оконные функции в SQL — жизнь уже никогда не будет прежней. В хорошем смысле ツ

Надеюсь, со свежим модулем курса вы преодолеете этот важный рубеж. У вас будет много помощников: Дарья, Борис, Елена, Ксения, Марина, Леонид, Иван, Вероника, Григорий и Анна. Особенно Марина, конечно.

Чтобы узнать, причем тут все эти люди, переходите к седьмому и последнему модулю курса — Оконные функции!

P.S. Для тех, кто пройдет все модули, откроется Финал. Он пока еще запекается, но скоро будет готов.

Сводный запрос по нескольким базам

Если вы настраивали кросс-запросы по нескольким базам в PostgreSQL или Oracle, то знаете, какая эта боль. А в SQLite вот так:

attach database 'moscow.db' as moscow;
attach database 'samara.db' as samara;

select * from moscow.employees
union all
select * from samara.employees;

Удобно, чтобы не переливать данные в одну базу, если они уже лежат в разных.

SQLite для аналитики

Итак, спустя три месяца курс полностью закончен 🎉

В итоге получилось семь модулей:

1. Основы SQLite.
2. Очистка данных.
3. Связи в данных.
4. Данные → знания.
5. JSON.
6. Большие наборы.
7. Оконные функции.

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

47 уроков и 130 практических заданий, сертификат для выпускников.

Огромное спасибо бета-тестерам и всем участникам за критику и комментарии! Ваша обратная связь бесценна 🙏

https://antonz.ru/sqlite-course/

Оконные функции: ранжирование

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

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

Недостающие функции SQLite

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

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

В программировании на C я не силен, так что дело продвигается не очень быстро. Но кое-что уже удалось сделать:

— математические функции (sqrt, pow, log)
— мат. статистика (median, percentile, stddev)
— строковые функции (reverse, split_part)
— регулярные выражения (like, substr, replace)
— поддержка юникода
— работа с csv как с таблицей

https://github.com/nalgeon/sqlean

Оконные функции: смещение

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

Это одна из неочевидных тем, так что пришлось изготовить особенно много картинок и гифок.

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

Помните, я восторгался количеством новых функций в SQLite 3.35? За все надо платить: разработчики выпустили уже пятый патч-релиз (впервые в истории SQLite — раньше было не больше четырех).

Не знаю, какой урок можно из этого извлечь. Разве что «не пихайте много новых фич в один релиз». Хотя звучит довольно очевидно, мы никогда не устаем наступать на эти грабли ツ

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

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

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

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://news.1rj.ru/str/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/