Одноразовая загрузка данных с использованием встроенных внешних таблиц в Oracle Database

Этот запрос использует функцию EXTERNAL, содержащую определение внешней таблицы:

SELECT ... FROM EXTERNAL (
  COLUMNS ( ... )
  DEFAULT DIRECTORY ...
  LOCATION ( '<имя_файла>' )
);

То есть, вы можете выполнить одноразовую загрузку данных из файла, находящегося на сервере базы данных, без создания постоянной внешней таблицы — прямо в SQL-запросе 😍

👉 @SQLPortal

Глубокие SQL-вопросы, которые ты боялся задать

1. WHERE против HAVING

WHERE фильтрует строки до группировки,
а HAVING — после неё.

HAVING используют с агрегатами, например, SUM(), чтобы фильтровать уже сгруппированные данные.

2. Что такое "оконные функции" и чем они отличаются от групповых?

Оконные функции (ROW_NUMBER(), SUM() OVER() и др.) работают с набором строк, связанных с текущей, не объединяя их, как GROUP BY

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

3. Что такое CTE и чем они отличаются от подзапросов?

CTE (Common Table Expressions) — это временные именованные результаты, создаваемые с помощью WITH, которые можно использовать в основном запросе.

В отличие от подзапросов, CTE:
• читаются легче,
• могут быть рекурсивными,
• можно использовать несколько раз в одном запросе.

4. Что такое "взаимная блокировка" (Deadlock) и как её избежать?

Deadlock — это когда две транзакции блокируют друг друга, не давая завершиться

Избежать можно так:
• Держите транзакции короткими.
• Всегда обращайтесь к таблицам в одном порядке.
• Добавьте повторные попытки при сбоях.

5. Что такое "секционированные таблицы" и как они ускоряют запросы?

Секционирование делит большую таблицу на части (например, по дате или региону).

Если запрос фильтрует по секционированному столбцу, он пропустит нерелевантные части — это ускоряет выполнение.

6. Что такое "материализованные представления" и чем они отличаются от обычных?

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

Обычные представления данные не хранят, это просто сохранённые запросы

7. Что такое "покрывающий индекс" и зачем он нужен?

Покрывающий индекс содержит все столбцы, нужные для запроса

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

👉 @SQLPortal

Совет по psql: настройте файл psqlrc

Если вы часто работаете с psql, создайте файл-шаблон psqlrc с любимыми настройками форматирования и командами. В него можно даже сохранить часто используемые SQL-запросы как пользовательские команды.

Например, чтобы создать команду для просмотра медленных запросов:

\set long_running 'SELECT pid, now() - pg_stat_activity.xact_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.x...'

Теперь, когда этот запрос сохранён в psqlrc, достаточно ввести :long_running, чтобы выполнить его и сразу получить результат 😮

👉 @SQLPortal

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

Тонул в бенчмарках, блогах и маркетинговых обещаниях. 💩 Пока не нашёл эту простую блок-схему.

Она отсекает лишнее и помогает выбрать всего по двум вопросам:

> Какие у тебя данные?
> Что ты с ними делаешь?

Суть такая:

-> Структурированные + OLTP → реляционная БД
-> Структурированные + OLAP → колоночная БД
-> Неструктурированные → объектное хранилище

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

> В формате словаря? → Key-Value или In-memory
> Много связей? → графовая БД
> По времени? → time-series БД
> Геоданные? → геопространственная БД
> Вложенный JSON? → документная БД
> Много поиска? → поисковая БД

Эта схема сэкономила мне кучу времени. Возможно, поможет и тебе 💖

👉 @SQLPortal

Совет по SQL:

Всегда следи за индексами и поддерживай их в порядке.
Неиспользуемые или дублирующие индексы = медленные записи и зря занятое место.

Почему это важно?
– Чистые индексы = быстрее вставки и обновления
– Меньше I/O → выше производительность

Используй этот запрос, чтобы найти неиспользуемые индексы в SQL Server 🔍

👉 @SQLPortal

PostgreSQL в Kubernetes

На экране — живая база Postgres, счётчик строк в левом верхнем углу

Сначала мы останавливаем основной pod и удаляем primary — происходит автоматический failover, и выбирается новый лидер через Patroni. Затем мы удаляем каталог PGDATA у текущего Primary — и снова failover.

Хаоса явно недостаточно? 😈

На правой панели видно, как маршрутизация и сетевой трафик автоматически подстраиваются под сбои

👉 @SQLPortal

Чтобы найти самую длинную серию подряд идущих событий (например, дней входа в систему):

1. Убери дубликаты, сгруппировав по дате

2. Назначить одинаковую «группу» для последовательных дней:

dt - ROW_NUMBER() OVER (ORDER BY dt) как grp

3. Сгруппировать по этим grp

4. Отсортируй группы по длине в порядке убывания и возьми самую длинную

👉 @SQLPortal

Если ты используешь секционирование таблицы — есть ли у тебя секция по умолчанию?

Её можно задать так:

CREATE TABLE partman_test.time_taptest_table_default
PARTITION OF partman_test.time_taptest_table DEFAULT;

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

Можно настроить уведомления, если туда что-то попадает, или проверять через pg_partman check_default

> Больше деталей — в посте в блоге про default-секцию ❤️

👉 @SQLPortal

Для нагруженных приложений в Postgres есть трюк для повышения производительности — prepared statements (подготовленные выражения).

Prepared statements — это переиспользуемые SQL-запросы, которые пропускают этап планирования, выполняя тот же запрос с разными данными.

Полный запрос в Postgres проходит такие шаги:

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

Prepared statements сохраняют первые 3 шага — при следующем запуске остается только извлечь данные. Postgres может полностью пропустить планирование.

Пример:

Подготовка запроса и создание плана:

PREPARE get_user_by_email (text) AS
SELECT * FROM users WHERE email = $1;

Выполнение с одним параметром:

EXECUTE get_user_by_email('alice@test.com');

С другим параметром:

EXECUTE get_user_by_email('bob@test.com');

Удаление подготовленного выражения:

DEALLOCATE get_user_by_email;

ORM'ы умеют работать с prepared statements, и это важно для разрабов, которым нужна производительность. Со стороны приложения это называется параметризованный запрос — в запросе есть плейсхолдер, а значения подставляются позже. Такой параметризованный запрос превращается в prepared statement на уровне базы.

В Rails это выглядит так:

User.where("email = ?", "alice@example.com")

В Python:

cur.execute("SELECT * FROM users WHERE email = %s", ("alice@example.com",))

👉 @SQLPortal

IBM добавила поддержку PostgreSQL в COBOL для Linux

Да-да, не показалось — теперь в COBOL можно напрямую писать SQL-запросы к PostgreSQL.

— Встроенные SELECT, INSERT, UPDATE, DELETE прямо в COBOL
— Новый препроцессор на основе ECPG
— Работает на Linux x86

Старичок COBOL получил современную БД — корпораты в восторге. 😱

https://www.postgresql.org/about/news/new-postgresql-support-in-ibm-cobol-for-linux-on-x86-3074/

👉 @SQLPortal

SQL с нуля до профи — в одном PDF

Хочешь разобраться в SQL? Вот тебе шпаргалка, которая:

> Объясняет, что такое SQL
> Показывает разницу между DDL, DML, DQL
> Учит JOIN-ам, подзапросам и оконным функциям
> Даёт советы по оптимизации и автоматизации запросов

Идеально для новичков и тех, кто хочет подтянуть основы 😮

👉 @SQLPortal

В Postgres есть полезные функции AGE для работы с датами — особенно удобны, когда нужно вычитать и считать разницу во времени.

SELECT AGE(DATE '2025-05-08', DATE '2000-01-01');
          age           
------------------------
 25 years 4 mons 7 days
(1 row) 

Можно извлечь только количество лет:

SELECT EXTRACT(YEAR FROM AGE('2025-05-08', '2000-01-15')) AS years;
-- результат: 25
 years 
-------
    25
(1 row)

С меньшими единицами времени всё не так просто:

SELECT EXTRACT(MONTH FROM AGE('2025-05-08', '2000-01-15')) AS months;
-- результат: 3

С 2000 года прошло явно больше трёх месяцев... Чтобы получить точное значение в месяцах, надо учесть количество лет * 12 + месяцев:

SELECT 
  EXTRACT(YEAR FROM AGE('2025-05-08', '2000-01-01')) * 12 +
  EXTRACT(MONTH FROM AGE('2025-05-08', '2000-01-01')) AS total_months;
 total_months 
--------------
          304
(1 row)

👉 @SQLPortal

На одной картинке — как выглядят стандартные SQL-запросы против аналогов в MongoDB

Удобная шпаргалка, если переходишь с реляционных БД на документо-ориентированные ❤️

👉 @SQLPortal

Используйте квалифицированные выражения (type constructors), чтобы упростить присваивание переменным записей и массивов в PL/SQL:

rec_var := rec_type('v1', 'v2', ...);
arr_var := arr_type(1, 2, ...);

Можно использовать позиционную или именованную нотацию:

> для массивов — индекс и значение,

> для записей — имя атрибута

👉 @SQLPortal

Следишь за новостями в мире UUID?

За последние годы многое изменилось — особенно с появлением UUIDv7.

UUIDv7 сочетает в себе:

> префикс с Unix-временем
> суффикс как у случайного UUID

Если ты раньше использовал UUIDv4, то знаешь — он хорошо подходит для приватности, но плохо сортируется и индексируется. UUIDv7 решает это, добавляя временную метку в начало — это делает его дружелюбнее к базам данных и производительности.

В Postgres уже есть расширение для использования UUIDv7, а официальная поддержка планируется в Postgres 18 💖

👉 @SQLPortal

Термин дня в Postgres: кардинальность

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

> Высокая кардинальность — много уникальных значений. Индексы полезны для поиска и сортировки.

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

Можно узнать кардинальность через pg_stats:

SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'storm_events';

Пример:

attname    | yearmonth
n_distinct | 12

attname    | event_id
n_distinct | -1

> Положительное значение, как 12, — значит 12 уникальных значений.
> Отрицательное, как -1, — все строки уникальны (обычно id).

Кардинальность влияет и на селективность, и на стратегии джойнов в плане выполнения запроса ❤️

👉 @SQLPortal

Ищешь инструмент для рисования схем баз данных прямо в браузере?

Посмотри на drawdb — это бесплатный и open source-проект.

Можно импортировать существующие схемы, экспортировать в разных форматах и делиться диаграммами 😮

👉 @SQLPortal

SQL-псевдоним позволяет присвоить таблице или столбцу временное имя во время выполнения запроса

Псевдонимы особенно полезны при работе со сложными запросами, соединениями (JOIN), или когда нужно переименовать столбцы в результате

Псевдоним существует только на время выполнения этого запроса

Псевдонимы создаются с помощью ключевого слова AS

Пример:

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

👉 @SQLPortal

Какой вариант правильный?

A. Общие продажи, сгруппированные по названию книги.
B. Общее количество проданных экземпляров для каждого жанра.
C. Общая выручка, сгруппированная по жанру.
D. Список жанров с их книгами.

Правильный вариант — B

Запрос делает JOIN между таблицами Books и Sales, группирует по b.Genre и считает сумму s.Quantity, то есть — общее количество продаж по жанрам

👉 @SQLPortal

Можешь найти ошибку и поправить запрос?

Запрос ниже должен возвращать общий объём продаж для каждой категории товаров из таблицы Products. Но он выбрасывает ошибку:

SELECT category_id, SUM(price * quantity) 
FROM Products 
WHERE category_id IS NOT NULL;

👉 Ошибка в том, что используется агрегат SUM(...), но нет GROUP BY.

Вот исправленный вариант:

SELECT category_id, SUM(price * quantity) 
FROM Products 
WHERE category_id IS NOT NULL
GROUP BY category_id;

👉 @SQLPortal