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

Консоль 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 — напишите в комментариях, постараюсь учесть.

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;

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