SQLite на практике

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

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

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

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

https://stepik.org/z/95367

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

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

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

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

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

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

Есть таблица, хочу ежедневно загружать в неё обновления из 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-aggregate/

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

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

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

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

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

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

Недостающие функции 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-ranking/

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

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

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

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

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

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

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

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

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

Если вы настраивали кросс-запросы по нескольким базам в 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;

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

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

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

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

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

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

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

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

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

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

JSON → CSV

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что ждет нас в SQLite 3.35

В новых релизах разработчики SQLite часто перебирают движок так и сяк, а для внешнего наблюдателя ничего особо не меняется. 2020 год стал приятным исключнием — добавили кучу приятных фич для нас с вами, вроде вычисляемых столбцов, UPDATE FROM и великолепного .mode box в консоли.

Есть все шансы, что 2021 год продолжит традицию. Вот что ждет нас в релизе 3.35 (выходит 30 марта):

1. Математические функции (логарифм, квадратный корень, синусы-косинусы).
2. Удаление столбцов.
3. Возврат обработанных строк для DELETE, INSERT и UPDATE (выражение RETURNING).
4. Материализованные CTE.

И все это в одном релизе! Невероятно ツ

Подробности

Курс «SQLite для аналитики» опубликован на «Степике», и первый модуль можно пройти бесплатно и даже без регистрации 🎉 https://stepik.org/z/90778

Открыт пятый модуль — JSON. В нем научимся выбирать данные из json-файлов прямо в консоли SQLite, а также загружать данные и раскладывать по таблицам.

SQLite — самый удобный известный мне способ для работы с JSON. Поэтому я очень рад, что теперь и у вас будет такой инструмент!

https://stepik.org/lesson/495680/step/1?unit=487066