🖐 Знакомство

Привет! Я Антон Жиянов. Разрабатываю опенсорс, веду курсы, пишу про облачные сервисы, открытые данные и программирование (вот все проекты).

«SQLite на практике» — канал о работе с данными на SQL. Здесь не бывает новостей, копипасты, переводов и рекламы. Бывают анонсы моих проектов, в том числе платных.

Приходите в чат

Готов второй модуль — Очистка данных! В нем «причесываем» проблемные данные, чтобы можно было нормально их проанализировать.

Доступ открыт для бета-тестеров: https://stepik.org/lesson/469032/step/1?unit=459852

Зачем использовать консоль

Курс учит работать с sqlite в «родной» консоли. И вот почему.

Консоль — это режим максимального фокуса. Есть данные, есть инструмент (сам sqlite), и больше нет ничего. Нет развесистого UI с его багами, нет устаревших драйверов, нет возюканья мышкой. Только вы и данные, наедине — что может быть лучше ツ

Если консоль вам категорически не подходит — можно использовать и графические средства, конечно. Рекомендую dbeaver.io — он работает с любыми СУБД и обладает нормальным интерфейсом. Не рекомендую «DB Browser for SQLite» и «SQLiteStudio» — оба ужасны.

Но лучше начните с консоли.

Открыт третий модуль — Связи в данных! В нем соединяем таблицы, разворачиваем иерархии и разбираемся с отношением «многие ко многим».

https://stepik.org/lesson/475718/step/1?unit=466630

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

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

https://antonz.ru/random-table/

Скачать и загрузить датасет прямо из консоли

Консоль SQLite умеет выполнять системные утилиты, вот так:

.shell команда параметры

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

sqlite> .shell wget https://github.com/nalgeon/sqliter/raw/main/city.csv
sqlite> .import --csv city.csv city
sqlite> select count(*) from city;
1117

Работает на маке и линуксе. На винде тоже работает, но поскольку системных утилит там почти нет — толку от этого немного.

В третьем модуле открыты еще два урока — «Множества» и «Представления».

Хоть курс и не про SQL как таковой, но если пройдете третий модуль целиком — здорово прокачаете SQL-навыки, гарантирую.

Причем не на уровне синтаксиса, а с пониманием зачем, что и как.

https://stepik.org/lesson/479467/step/1?unit=470443

Открыт четвертый модуль — Данные → знания! В нем узнаем, как описать весь набор данных двумя показателями — «средним представителем» и «большинством». И внезапно разберемся в современной моде (так уж вышло).

Изучим тот самый 1% матстатистики, которого достаточно для 90% случаев. Конечно, без жести. Никаких трехэтажных формул, никаких F-критериев Фишера и хи-квадратов Пирсона.

А еще финалисты модуля войдут в элитарный 0,0001% людей, которые понимают, что такое «корреляция» ツ

https://stepik.org/lesson/479468/step/1?unit=470444

Как не забросить курс

Вижу, что некоторые участники начали проходить курс, а потом остановились. Это распространенная ситуация для онлайн-курсов, я и сам не раз попадал в печальную статистику.

Если узнали себя, вот что хочу сказать:

1. Это нормально (вы и сами, наверно, знаете, но на всякий случай).
2. Сейчас лучший момент, чтобы возобновить уроки.
3. Не пытайтесь пройти целый модуль за раз, это сложно. Хороший темп — один урок каждые 1–3 дня.
4. Если что-то совсем не получается — приходите в личку, постараюсь помочь.

Есть еще бета-тестеры (получили курс бесплатно с условием, чтобы будут проходить и регулярно давать обратную связь). Некоторые из них не начали курс или начали и забросили. Таких я удалю, не обижайтесь.

Основная часть курса готова

Открыт последний урок четвертого модуля — Анализ текста (+ резюме по модулю). Хотел еще сделать отдельный урок по регулярным выражениям, но испытал унизительное поражение в борьбе с ними, поэтому пришлось отложить.

Итого, готовы четыре модуля:

1. Основы SQLite. Загружаем датасет, вспоминаем SQL, выгружаем в CSV.
2. Очистка данных. «Причесываем» проблемные данные и разбираемся с типами.
3. Связи в данных. Соединяем, объединяем и пересекаем таблицы в наборе данных.
4. Анализ показателей. Считаем среднее, процентили, распределение и корреляцию. Анализируем текст.

Если пройдете их — научитесь уверенно работать с датасетами в SQLite и формулировать вопросы к данным в виде SQL.

Что дальше

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

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

Большинство уверенно выбрало JSON. Полностью поддерживаю! Это и правда намного интереснее, чем регулярки.

Модуль уже запекается и будет готов на следующей неделе, а пока вот небольшой тизер — селектим json прямо из файла ツ

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

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

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

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

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

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

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

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

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

Подробности

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

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

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

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

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

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

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

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

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

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

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

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