Onlayn kitobni bepul oʻqing: ta muallif  SQL для аналитика. 50 задач с решениями на PostgreSQL

Максим Чалышев

Оглавление

SQL для аналитика. 50 задач с решениями на PostgreSQL

ПОСВЯЩАЕТСЯ

моим коллегам-аналитикам

Тем, кто каждый день борется с хаосом данных

и не сдается

Введение

Для кого эта книга

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

Если вы уже знаете SQL, но не знаете, как реализовать воронку, посчитать retention, построить событийную аналитику, то в этой книге найдёте ответы.

Если вы почти не знаете SQL, то в начале книги несколько глав познакомят читателя с базовыми операторами языка SQL. Основные операторы языка кратко описаны в приложении в конце книги.

Чем поможет эта книга

Здесь почти нет теории, зато есть 50 реальных задач с решениями. И каждая задача — это настоящий отчёт, который аналитик применяет в работе.

Например:

— конверсия и LTV, воронка событий, отзывы с полнотекстовым поиском, аномалии в доставке и платежах

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

Что нужно, чтобы работать с книгой

— Установите PostgreSQL и pgAdmin (глава 1). Базу данных для примеров вы найдёте в репозитории: https://github.com/atvcross/postgresql_analyst

— Пишите запросы и проверяйте себя. Ответы к заданиям (для первых 15 глав) вы найдёте в книге. Остальные ответы — в репозитории на GitHub.

— Используйте главы книги как справочник. Если что-то забыли, например, как работает LAG или jsonb_set, открывайте соответствующую главу и применяйте

.

Данные для обучения

Все примеры основаны на единой схеме интернет-магазина. Скрипт для создания базы данных и заполнения тестовыми данными лежит в репозитории:

https://github.com/atvcross/postgresql_analyst

Скачайте файл market. sql и выполните его в pgAdmin (инструкция — в главе 2).

Вперёд, к задачам!

Если что пишите в VK https://vk.com/maxandmouse или Issues на GitHub. Удачи!

Часть 1. Установка. Повторение SQL

Глава 1. Устанавливаем PostgreSQL и pgAdmin

Готовим рабочее место

Чтобы выполнять примеры из книги, понадобится установить PostgreSQL на ваш компьютер.

Разберём установку PostgreSQL и pgAdmin — графического инструмента для работы с базами данных. Это несложно и недолго, если действовать по инструкции.

Подготовка к установке

Для установки потребуется:

— 64-разрядный компьютер или ноутбук

— оперативная память от 2 ГБ

— свободное место на диске от 2 ГБ

— операционная система Windows

Если у вас macOS или Linux — процесс установки будет отличаться. На официальном сайте есть инструкции для всех основных платформ.

Скачивание дистрибутива

Перейдите на официальный сайт PostgreSQL:

https://www.postgresql.org/download/

На странице выберите вашу операционную систему. Для Windows нажмите на значок Windows в верхней части страницы.

Вы попадёте на страницу загрузки для Windows. Нажмите Download the installer.

В списке версий выберите последнюю (на момент написания — 18.3). В колонке Windows x86—64 нажмите на значок скачивания.

Через несколько секунд начнётся загрузка. Если этого не случилось — нажмите Click me.

Установка PostgreSQL

Запустите скачанный файл. Обычно он лежит в папке «Загрузки» и называется вроде postgresql-18.3-1-windows-x64.exe.

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

После этого запустится мастер установки.

Шаг 1. Выбор папки установки. Оставьте настройки по умолчанию и нажмите Next.

Шаг 2. Выбор компонентов. Оставьте настройки по умолчанию и нажмите Next.

Шаг 3. Выбор папки для данных. Оставьте настройки по умолчанию и нажмите Next.

Шаг 4. Установка пароля администратора.

Важно! Задайте пароль для доступа к PostgreSQL.

Рекомендуем простой пароль вроде manager или admin. Можете придумать свой, но обязательно запишите его.

Введите пароль дважды и нажмите Next.

Шаг 5. Выбор порта. Оставьте порт по умолчанию (5432) и нажмите Next.

Шаг 6. Выбор локали. Оставьте значение по умолчанию (обычно DEFAULT) и нажмите Next.

Шаг 7. Просмотр настроек. Проверьте параметры и нажмите Next.

Шаг 8. Установка. Нажмите Next. Процесс займёт от 5 до 15 минут.

Шаг 9. Завершение. Снимите галочку Launch Stack Builder and Exit и нажмите Finish.

Если вы забыли пароль — процедура сброса описана в шаге 88 (раздел «Сброс забытого пароля пользователя PostgreSQL»).

Проверка установки

В меню Пуск найдите и запустите pgAdmin 4.

В левой части окна раскройте Servers → PostgreSQL 18 (версия может отличаться).

Введите пароль, который задали при установке. Рекомендуем снять галочку Store password, чтобы не вводить пароль каждый раз.

Если всё прошло успешно — вы увидите структуру сервера в левой панели. Иногда требуется повторить подключение дважды: первый запуск может быть долгим.

Первое знакомство с pgAdmin

Для работы с запросами используйте Query Tool.

Выберите в меню Tools → Query Tool.

Откроется окно, где можно писать и выполнять SQL-запросы.

pgAdmin — это графический интерфейс для PostgreSQL. Он позволяет:

— просматривать структуру баз данных

— выполнять SQL-запросы

— редактировать данные в таблицах

— управлять серверами и пользователями

Простыми словами: pgAdmin — это окно в ваш PostgreSQL.

В левой панели pgAdmin находится дерево объектов. Разберитесь с иерархией:

— Servers — подключения к серверам PostgreSQL

— PostgreSQL 18 (или ваша версия) — ваш локальный сервер

— Databases — список баз данных

— Schemas → public — здесь находятся таблицы, представления, функции

— Tables — список таблиц в схеме public

Совет: чтобы быстро найти нужную таблицу, используйте поиск (Ctrl + G).

Подключение к серверу

В левой панели pgAdmin найдите Servers и раскройте его. Вы увидите сервер PostgreSQL 18 (или вашу версию).

Нажмите на сервер левой кнопкой мыши. Откроется окно ввода пароля.

Введите пароль, который задали при установке. Рекомендуем снять галочку Store password.

После правильного ввода сервер станет активным — зелёная иконка загорится, и вы увидите структуру баз данных.

Инструменты pgAdmin

В верхнем меню есть раздел Tools. Здесь находятся основные инструменты:

— Query Tool — главное место для написания и выполнения SQL-запросов. Именно его мы будем использовать на протяжении всей книги.

— Search Objects (Ctrl + G) — поиск по объектам базы данных.

— Import/Export — импорт и экспорт данных (например, из CSV).

Query Tool

Это главный элемент pgAdmin. Здесь пишутся запросы.

Как открыть:

— Нажмите правой кнопкой на базе данных → Query Tool

— Или выберите базу → Tools → Query Tool

Интерфейс:

— Верхняя часть — редактор кода (пишете запрос)

— Нижняя часть — результат выполнения (таблицы, сообщения, время выполнения)

— Кнопка Execute (Play) или клавиша F5 — выполнить запрос

Пример кода:

text

SELECT current_user;

Напишите этот запрос в редакторе, нажмите Execute (как Play) — и увидите данные из таблицы.

Что в итоге

Если всё сделали правильно — переходите к главе 2. Если нет — перечитайте шаг 4 про пароль, чаще всего ошибка там.

Глава 2. Устанавливаем схему. Что есть в нашей схеме

Для дальнейшей работы нам нужно создать демо-базу данных и заполнить её информацией.

Что скачиваем

Вам понадобится загрузить файл market. sql. Он находится в специальном репозитории на GitHub.

Ссылка на репозиторий: https://github.com/atvcross/postgresql_analyst

Как скачать файл:

— Нажмите на зелёную кнопку Code

— Выберите Download ZIP

— Распакуйте архив в любую папку

— Внутри найдите файл market. sql

Устанавливаем схему через pgAdmin

— Откройте pgAdmin.

— Создайте новую базу данных.

В дереве слева нажмите правой кнопкой на Databases → Create → Database.

В поле Database введите book_analytics. Остальные поля менять не нужно. Нажмите Save.

— Откройте Query Tool. (Главное меню -> Tools -> Query Tool)

Нажмите правой кнопкой на book_analytics → Query Tool.

— Загрузите файл market. sql.

В редакторе Query Tool нажмите на значок папки (Open File). Выберите скачанный файл market. sql.

Также можно открыть файл в текстовом редакторе (например, Блокнот) и перенести текст в Query Tool через буфер обмена.

— Выполните скрипт.

Нажмите кнопку Execute (или клавишу F5).

Будут созданы нужные таблицы и заполнены данными.

Проверка установки

В левой панели (дерево объектов) нажмите правой кнопкой на book_analytics → Refresh.

Раскройте Schemas → public → Tables. Здесь должны отобразиться все восемь таблиц:

— users

— categories

— products

— orders

— order_items

— payments

— users_log

— user_comments

Если таблиц нет или их меньше — схема установилась не полностью, или произошла ошибка. Попробуйте выполнить скрипт заново.

Если всё хорошо, выполните проверочный запрос в Query Tool:

text

SELECT COUNT (*) FROM users;

Должно вернуться 15.

Что есть в нашей схеме

Это схема интернет-магазина, который торгует разными товарами. Здесь есть товары, заказы, пользователи, платежи. Давайте подробнее о каждой таблице.

users — пользователи.

Колонки: user_id, user_name, registered_at, country, is_active.

Хранит идентификатор, имя, дату регистрации, страну, активен ли пользователь.

categories — категории товаров.

Колонки: category_id, name, parent_id.

Построена как дерево: у каждой категории может быть родитель (parent_id). Это позволяет делать иерархические запросы.

products — товары.

Колонки: product_id, name, category_id, price.

Название, цена, привязка к категории.

orders — заказы.

Колонки: order_id, user_id, order_date, shipped_date, delivered_date, status.

Кто заказал, когда, статус заказа, даты отгрузки и доставки.

order_items — состав заказа.

Колонки: order_id, product_id, quantity, price_per_unit.

Какие товары и в каком количестве купили. Цена за единицу фиксируется на момент покупки.

payments — платежи.

Колонки: payment_id, order_id, amount, payment_date, payment_method.

Сумма, дата, способ оплаты. Привязан к заказу.

users_log — лог событий.

Колонки: log_id, user_id, action, dt_tm, log_data.

Действия пользователя: enter, show, add_to_cart, buy, exit, error. Плюс время события и дополнительные данные в формате JSONB.

user_comments — отзывы.

Колонки: comment_id, user_id, product_id, rating, comment_text, comment_date.

Текст отзыва, оценка от 1 до 5, дата. Привязан к пользователю и товару.

Вопросы и ответы. А вдруг что-то пошло не так

В: Ошибка: база book_analytics уже существует

О: Удалите старую: нажмите правой кнопкой на book_analytics → Delete/Drop. Затем создайте заново.

В: Ошибка: duplicate key violates unique constraint

О: Та же проблема. Удалите старую базу и повторите установку.

В: Ошибка: permission denied

О: Запустите pgAdmin от имени администратора.

В: Пустой результат при проверке

О: Выполнили SELECT COUNT (*) FROM users, а вернулся 0? Значит, данные не загрузились. Запустите market. sql ещё раз.

Что в итоге

Если всё сделали правильно — переходите к главе 3. Если таблиц нет или запрос вернул не 15 — перечитайте раздел «Устанавливаем схему через pgAdmin’ и выполните скрипт заново.