SQL TO-DO LIST для аналитика данных

Создание таблицы

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary INT
);

Вставка данных в таблицу

INSERT INTO employees VALUES 
(1, 'John Doe', 'Finance', ...);

Базовый SELECT

SELECT name, salary 
FROM employees;

WHERE

SELECT * 
FROM employees 
WHERE salary > 40000;

ORDER BY

SELECT * 
FROM employees 
ORDER BY salary DESC;

LIMIT

SELECT * 
FROM employees 
LIMIT 5;

🔸Advanced SQL Tasks (общие)

Запуск дашборда в Power BI / Tableau на SQL-данных

Выполнение SQL-запросов на датасетах:

> Chinook Database: https://github.com/lerocha/chinook-database
> IMDB data: https://imdb.com/interfaces/

🔸Intermediate SQL Tasks

Агрегатные функции

SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

GROUP BY и HAVING

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 2;

JOIN

SELECT e.name, d.location 
FROM employees e 
JOIN departments d 
  ON e.department = d.name;

Подзапросы

SELECT name 
FROM employees 
WHERE salary > (
  SELECT AVG(salary) 
  FROM employees
);

🔸Advanced SQL Tasks (продвинутые)

Оконные функции (Window Functions)

SELECT name, salary, 
       RANK() OVER (
         PARTITION BY department 
         ORDER BY salary DESC
       ) AS dept_rank 
FROM employees;

CTE

WITH high_earners AS (
  SELECT * 
  FROM employees 
  WHERE salary > 50000
)
SELECT * 
FROM high_earners;

Очистка данных

SELECT TRIM(LOWER(name)) AS cleaned_name 
FROM employees;

👉 @SQLPortal

Postgres MERGE это давно ожидаемая фича, которая появилась в Postgres 15. MERGE используется для реализации upsert-логики (то есть insert или update).

Многие, кто работает с Postgres, хорошо знакомы с конструкцией INSERT ... ON CONFLICT. Но INSERT ... ON CONFLICT не подходит для сценариев с условной логикой, где нужно использовать WHERE, чтобы определить, что именно обновлять.

Читать: тык

👉 @SQLPortal

Ой... CPU забито на 100%... Что случилось? Это аналитик написал рекурсивный CTE и забыл лимит поставить? Жесть… что-то крутится, и это нужно срочно остановить. Давайте зайдём в Postgres, найдём и убьём runaway-транзакцию.

Для начала посмотрим на системную таблицу активности Postgres — pg_stat_activity. В ней отображаются все работающие backend-процессы (каждый со своим pid):

SELECT * FROM pg_stat_activity;

Важно: при запросе к pg_stat_activity нужно отфильтровать свою собственную сессию. Её PID можно получить через pg_backend_pid().
Так что запрос, который покажет все активные транзакции, кроме вашей:

SELECT * FROM pg_stat_activity WHERE pid <> pg_backend_pid();

Нашли "виновника"? Окей, теперь можно прибить процесс:

SELECT pg_terminate_backend(pid);

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

После того как ты спас базу от перегрева, застрахуйся от runaway-запросов в будущем, выставив statement_timeout

👉 @SQLPortal

SQL-вопрос на собеседовании для Data Engineer'ов (сможешь решить?)

Social Media PII

Социальная медиа-компания ведёт таблицу с информацией о пользователях, включая email-адреса и номера телефонов. Твоя задача написать функцию, которая обрабатывает эти данные для целей приватности и аналитики.

Твоя задача

—> Напиши функцию, которая выполняет следующие преобразования над входным DataFrame:

✅Извлечение домена из email: Из поля email извлеки доменное имя (текст после символа @).
✅Анонимизация номера телефона: Замаскируй первые 6 цифр номера телефона с помощью символов *, оставив только последние 4 цифры.

Результат должен включать следующие колонки:
anon_phone: Анонимизированный номер телефона (например, ******1234)
email_domain: Извлечённый домен (например, example.com)
user_id: Исходный user_id

Отсортируй результат по номеру телефона по возрастанию (до маскировки)

Схема входных данных

Имя DataFrame: input_df

| Название | Тип |
|------------------|------------|
| user_id | String |
| email | String |
| phone | Integer |

Схема выходных данных

| Название колонки | Тип |
|---------------------------|------------|
| anon_phone | String |
| email_domain | String |
| user_id | Integer |

Реши задачу здесь

👉 @SQLPortal

Data-engineer-handbook

Это репозиторий сo ссылками на всё, что вы когда-либо захотите изучить по data engineering

Github : https://github.com/DataExpert-io/data-engineer-handbook

😳😳😳

👉 @SQLPortal

Новичкам в Postgres — разберитесь с основами создания таблиц: первичные ключи, внешние ключи и типы данных, всё это в oбучающем гайде

-- https://www.crunchydata.com/developers/playground/postgres-tutorial-create-tables

👉 @SQLPortal

Бомбический инструмент для работы с SQL-базами данных

Исследуй, работай с таблицами и получай аналитику прямо из них.

Поддерживает MySQL, PostgreSQL, SQLite и другие.

Бесплатный и с открытым исходным кодом:

→ http://github.com/frectonz/sql-studio

👉 @SQLPortal

Совет по работе с JSON в Postgres: используйте jsonb_pretty для форматирования JSON-поля в читаемый и структурированный вид с отступами.

SELECT
    id,
    username,
    jsonb_pretty(profile) AS pretty_profile
FROM
    users;

Результат запроса будет выглядеть так:

 id | username | pretty_profile
----+----------+------------------------------------------------
  1 | jdoe     | {
    |          |     "age": 30,
    |          |     "name": "John Doe",
    |          |     "address": {
    |          |         "city": "Anytown",
    |          |         "street": "123 Main St"
    |          |     },
    |          |     "courses": [
    |          |         {
    |          |             "title": "History",
    |          |             "credits": 3
    |          |         },
    |          |         {
    |          |             "title": "Math",
    |          |             "credits": 4
    |          |         }
    |          |     ],
    |          |     "isStudent": false
    |          | }

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

👉 @SQLPortal

Когда-нибудь случайно удалял не те данные?

и закоммитил?

в проде? 😱

С помощью Oracle Flashback Query можно посмотреть, как данные выглядели в прошлом:

SELECT ... FROM ... AS OF TIMESTAMP <время>

Это позволяет найти строки, которых больше нет в таблице, и восстановить их обратно.

👉 @SQLPortal

ХОСТИНГИ ДЛЯ БАЗ ДАННЫХ — БЕСПЛАТНО
Актуальные варианты на сегодня ↓

🔸NEON TECH
PostgreSQL, 500 МБ, 190 часов вычислений
→ neon.tech

🔸MONGODB ATLAS
MongoDB, 512 МБ, автоматические бэкапы
→ mongodb.com

🔸TURSO
SQLite, 5 ГБ, 1 миллиард чтений
→ turso.com

🔸XATA
PostgreSQL, 15 ГБ, неограниченный трафик
→ lite.xata.io

🔸SUPABASE
PostgreSQL, 500 МБ, 5 ГБ трафика
→ supabase.com

🔸COCKROACHDB
10 ГБ хранилища, 50 млн запросов
→ cockroachlabs.com

🔸KOYEB
PostgreSQL, 1 ГБ, 5 часов вычислений
→ koyeb.com

🔸FREEDB TECH
MySQL, 25 МБ, максимум 200 подключений
→ freedb.tech

Если знаешь ещё варианты — делись

👉 @SQLPortal

Чтобы сгруппировать данные по году/дню/часу/... в Oracle SQL, используйте TRUNC:

TRUNC(<dt>, '<fmt>')

А как быть с кратными интервалами — например, 4 часа или 2 дня?

Раньше это было непросто.

Но Laurent Schneider показал, что начиная с версии 23.7 это стало проще с помощью:

TIME_BUCKET(<dt>, <interval>, <start_date>)

https://laurentschneider.com/wordpress/2025/03/time_bucket-group-by-time-period.html

👉 @SQLPortal

Поиск по эмбеддингам в YDB

Векторный поиск в СУБД Яндекса позволяет искать данные (текст, изображения, аудио, видео) по смыслу, а не по точному совпадению.

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

Хабр: https://habr.com/ru/companies/yandex/articles/926724/

👉 @SQLPortal

Кешируй результаты PL/SQL-функций, пометив их как:

CREATE FUNCTION ... RETURN ... RESULT_CACHE

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

Killianlynchh показывает, как это использовать и мониторить — ccылка 👍

👉 @SQLPortal

Мы все когда-то пытались использовать агрегатную функцию в WHERE

SELECT department, SUM(salary)
FROM employees
WHERE SUM(salary) > 100000 
GROUP BY department;

Но SQL так не работает 😉

Для фильтрации по агрегатным значениям используется HAVING:

SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

В чём разница между WHERE, HAVING и FILTER?

WHERE фильтрует строки до группировки и агрегации:

SELECT department, SUM(salary)
FROM employees
WHERE hire_date >= DATE '2020-01-01'
GROUP BY department;

HAVING фильтрует после агрегации, работает в связке с GROUP BY (или хотя бы с агрегатным контекстом).
С помощью HAVING можно применять условия к агрегатам, таким как SUM, AVG и т.д.:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Этот запрос вернёт только тех клиентов, у которых больше 5 заказов.

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

Пример, который не сработает:

SELECT
  department,
  SUM(salary) FILTER (WHERE SUM(salary) > 100000) AS total_salary
FROM employees
GROUP BY department;

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

Правильный способ — использовать HAVING для фильтрации агрегатов, а FILTER — для условий внутри агрегатной функции:

SELECT
  department,
  SUM(salary) AS total_salary,
  SUM(salary) FILTER (WHERE employment_type = 'full-time') AS full_time_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

👉 @SQLPortal

Как масштабировать базы данных?

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

Это позволяет выполнять запросы только к нужным фрагментам данных, ускоряя загрузку.

В целом, существует два основных типа партиционирования:

1. Горизонтальное партиционирование (шардинг)

Таблицы разбиваются по строкам: каждая партиция имеет одинаковую схему, но содержит разные записи.

Это особенно полезно для multi-tenant приложений, где данные можно разделить по пользователям/клиентам,
либо когда объём данных слишком велик для одной базы данных.

Варианты шардинга:

- Шардинг по ключу (hash-based)

Шард определяется с помощью хеш-функции, применяемой к определённому ключевому столбцу записи.
Хеш-функция равномерно распределяет данные по шардам.

✅Преимущество — сбалансированная нагрузка и объём данных
Недостатки — сложные кросс-шардовые запросы и трудности при переразбиении (re-sharding)

- Шардинг по диапазонам (range-based)
Данные распределяются по диапазонам ключей.

Например: customer_id от 1 до 1000 — в одном шарде, от 1001 до 2000 — в другом.

✅Удобен для запросов с операциями над диапазонами

Может привести к перегрузке отдельных шардов (hotspots), если распределение неравномерное

- Шардинг по справочнику (directory-based)
Используется таблица соответствий (lookup table), которая мапит ключи на конкретные шарды.

✅Гибкость: легко добавлять/удалять шарды и делать переразбиение

> При шардинге все экземпляры БД должны быть структурно идентичны, а данные — равномерно распределены.

2. Вертикальное партиционирование

Таблицы разбиваются по столбцам: часто используемые колонки отделяются от редко используемых.
Это повышает эффективность доступа и кеширования.

Например, таблицы можно разнести по разным базам данных.

Почему шардинг — это сложно?

Шардинг требует значительных усилий: нужно перераспределять данные, адаптировать запросы,
обрабатывать пересекающиеся данные.

Это долго, дорого и усложняет поддержку системы.

Что делать, когда БД начинает тормозить?

1. Вертикальное масштабирование — добавить ресурсов на сервер БД (CPU, память и т.д.)
2. Репликация — создать реплику для чтения, чтобы разгрузить основной экземпляр

✅Репликация эффективна только при наличии кеширования

Когда применяют партиционирование?

- Горизонтальное партиционирование — когда нужно масштабировать большие таблицы
и повысить производительность для определённых типов запросов

- Вертикальное партиционирование — если в таблицах много колонок, но не все используются одновременно

👉 @SQLPortal

В PostgreSQL больше нет разделения на пользователей и группы — теперь есть только роли.

Индивидуальный пользователь — это просто роль с правом входа (LOGIN), которой можно назначить одну или несколько других ролей — таким образом и создаются группы пользователей.

Как создать нового пользователя в Postgres:

1️⃣Создать роль с правом входа (пользователя):

CREATE ROLE simon;

2️⃣Установить пароль:

\password simon

21654641seeswf!2@
21654641seeswf!2@

\password — это специальная команда Postgres, которая устанавливает пароль в виде уже хэшированного значения.

3️⃣Чтобы создать роль-группу (без возможности входа), используйте NOLOGIN. Затем выдайте нужные права:

CREATE ROLE accounting_ro NOLOGIN;

GRANT CONNECT ON DATABASE finance TO accounting_ro;
GRANT USAGE ON SCHEMA accounting TO accounting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO accounting_ro;

4️⃣Привязать пользователя к роли-группе:

GRANT accounting_ro TO simon;

👉 @SQLPortal