Разработчики старой школы, новой школы, вайбкодеры — все обожают regex. Это же идеальный инструмент для работы с текстом и переменными. В Postgres отличная поддержка регулярных выражений. Вот примеры запросов на поиск и замену текста.

👉 @SQLPortal

Оконные функции в Postgres — это супер

С помощью SQL можно быстро:

🔸Считать накопительные суммы
🔸Строить агрегаты по группам / партициям
🔸Создавать рейтинги и ранжирование
🔸Делать lag/lead-анализ — сравнивать значения из разных строк
🔸Вычислять скользящие (moving/rolling) средние

Вот практический туториал

👉 @SQLPortal

pg_eventserv: передача событий Postgres LISTEN/NOTIFY в WebSocket-соединения.

https://github.com/crunchydata/pg_eventserv

👉 @SQLPortal

Отслеживай отношения родитель–потомок в Oracle SQL с помощью CONNECT BY:

SELECT ...
FROM ...
START WITH <корневая_запись>
CONNECT BY PRIOR parent_row = child_row

Полезные вспомогательные функции:

🔸LEVEL — глубина текущей строки в иерархии
🔸CONNECT_BY_ISLEAF — возвращает 1, если у строки нет потомков
🔸SYS_CONNECT_BY_PATH — путь от корня до текущей строки

👉 @SQLPortal

Почему Postgres становится таким популярным для AI-нагрузок?

Нам не раз задавали этот вопрос — и мы понимаем почему: 30-летняя реляционная СУБД на первый взгляд не выглядит как инструмент, созданный для мира ИИ.

Но вот почему Postgres активно набирает популярность в AI-сфере:

➤ Интеграция AI с существующей или новой data-инфраструктурой
AI-приложения, как правило, работают с уже существующими структурированными данными — пользователи, клиенты, продукты, транзакции и т.д.
Postgres позволяет объединять эмбеддинги и выходные данные моделей с реляционными таблицами, чтобы строить AI-инструменты поверх существующих систем.

➤ Поддержка векторных типов данных
Расширение pgvector добавляет хранение эмбеддингов прямо в Postgres. Это зрелое, проверенное решение, поддерживаемое большинством поставщиков и пользователей Postgres.
pgvector также предоставляет продвинутые стратегии индексации (например, HNSW), которые ускоряют поиск и работу с векторами.

➤ Готовность к продакшену
Postgres покрывает все требования бизнеса: бэкапы, connection pooling, высокая доступность.
Это значит, что на нём можно запускать cutting-edge AI-проекты на стабильной, зрелой платформе, готовой к реальной эксплуатации.

👉 @SQLPortal

Материализованные представления (Materialized Views, MVs) хранят результаты запроса.

Основная проблема — поддержание их в актуальном состоянии.

Killianlynchh показывает, как материализованные представления в реальном времени (с параметром ON QUERY COMPUTATION в Oracle Database) позволяют получать актуальные данные, применяя изменения к устаревшим представлениям.

👉 @SQLPortal

Вы можете использовать ORDER BY по столбцам, которых нет в SELECT-списке запроса.

...за исключением случая:

SELECT DISTINCT this FROM t ORDER BY that  --  некорректно!

Почему так?

DISTINCT удаляет дубликаты из выбранных значений и формирует новую таблицу,
поэтому доступны только те столбцы, что явно указаны в SELECT

Объяснение от Lukas Eder — blog.jooq.org

👉 @SQLPortal

Типы индексов в базах данных, которые должен знать каждый разработчик

👉 @SQLPortal

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