sqlportal | Unsorted

Telegram-канал sqlportal - SQL Portal | Базы Данных

14137

Присоединяйтесь к нашему каналу и погрузитесь в мир баз данных Связь: @devmangx РКН: https://clck.ru/3H4Wo3

Subscribe to a channel

SQL Portal | Базы Данных

Нужно быстро выгрузить данные? \copy записывает результат любого запроса в локальный файл.

\copy (SELECT * FROM users WHERE created_at > now() - interval '7 days') TO '~/new_users.csv' WITH CSV HEADER


Кейсы:

- Быстрый экспорт выборки без поднятия отдельного пайплайна или воркфлоу.
- Выгрузка данных для аналитики в CSV с последующей обработкой в pandas/BI.
- Отладка: снятие среза таблицы для локального воспроизведения бага.
- Миграции: подготовка данных перед переносом между окружениями.
- Интеграции: передача данных в сторонние системы через файл.
- Бэкап части данных по фильтру (например, последние 7 дней).
- Проверка гипотез: выгрузка подвыборки без нагрузки на основную систему.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

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

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

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


Почему так?

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

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

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Сравнение с предыдущим. Решение.

Решение: использование DATEDIFF

DATEDIFF
: эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.

Если сформулировать обычным языком следующий запрос, то окажется, что он выражает следующую идею: нужно выбрать такие идентификаторы, чтобы температура, соответствующая представляемым ими датам, была бы больше, чем температура на «вчерашние» по отношению к ним даты.

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1


👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Руководство по стилю SQL

Последовательное руководство по стилю кода SQL для обеспечения разборчивости и сопровождения проектов

⛓ Ссылка: тык

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Перенаправляете вывод psql куда-то ещё? В psql есть настройки, позволяющие сократить вывод до одиночных строк данных.

psql -qtA

- -q — тихий режим
- -t — только строки (tuples only)
- -A — без выравнивания (unaligned)

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Вчера мы говорили о функциях pg_get_*. Чтобы получить отформатированный SQL, передайте true вместо одной длинной нечитаемой строки.

pg_get_viewdef — представления
pg_get_indexdef — индексы
pg_get_constraintdef — ограничения

SELECT pg_get_viewdef('active_orders'::regclass, true);


👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Функции pg_get_* — это встроенные функции интроспекции в Postgres.

pg_get_viewdef — «Покажи SQL, лежащий за этим представлением»

pg_get_indexdef — «Покажи CREATE INDEX для этого индекса»

pg_get_constraintdef — «Покажи, как определён этот внешний ключ / check-ограничение»

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Вопрос по SQL

Нужно получить:

- все товары дешевле 100
- плюс товары из категории Electronics дешевле 500

Какое условие корректно вернет такой результат?

A)

price < 100 AND category = 'Electronics' OR price < 500


B)

price < 100 OR (category = 'Electronics' AND price < 500)


C)

(price < 100 OR category = 'Electronics') AND price < 500


👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Open-source инструмент Text2SQL, который превращает запросы на естественном языке в SQL, используя понимание схемы на базе графа. Задавай вопросы к базе простым языком, а QueryWeaver сам сделает всю склейку и соберет нужный SQL.

https://github.com/FalkorDB/QueryWeaver

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

😢😢😢

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Краткая шпаргалка по SQL с примерами запросов

https://github.com/enochtangg/quick-SQL-cheatsheet

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

JSONB-колонки Postgres и TOAST: гайд по производительности

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

Работа с API и массивами в типе jsonb в последнее время стала заметно популярнее, и хранить куски данных приложения в jsonb превратилось в распространённый паттерн.

Но зачем вообще “нарезать” JSON-объект на строки и колонки, а потом собирать его обратно, чтобы отправить клиенту?

Ответ: эффективность. Postgres быстрее всего работает со строками и колонками, а когда ты прячешь структуру данных внутрь JSON, движку сложнее разогнаться на максимум, как он мог бы.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

В Postgres есть unlogged-таблицы: для них отключается Write-Ahead Logging (WAL). За счёт этого запись становится заметно быстрее, но очевидная цена — такие таблицы не реплицируются через WAL и после крэша/перезапуска их содержимое не восстанавливается (по сути, данные считаются временными). В некоторых сценариях этот размен реально оправдан.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Какой SQL-запрос работает быстрее?

SELECT * FROM orders
WHERE status = 'NEW'
OR status = 'PENDING'
OR status = 'PROCESSING';


vs

SELECT * FROM orders
WHERE status IN ('NEW', 'PENDING', 'PROCESSING');


👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

В Postgres можно делать функциональные индексы для запросов, которые приходят в конкретном формате. Например, для TIMESTAMP, когда в запросе фильтрация идет только по дате:

CREATE INDEX idx_orders_dt_only ON orders ((created_at::date)); 

SELECT count(*) FROM orders WHERE created_at::date = '2024-05-20';


👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

SQL-приём, о котором мало кто знает

Знал ли ты, что внутри транзакции можно создавать контрольные точки с помощью SAVEPOINT? Вместо сценария, где при ошибке теряется вся работа.

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

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

Следующий код откатывает всё:

BEGIN;

INSERT INTO orders VALUES (1, 'Laptop');
INSERT INTO payments VALUES (1, 'FAILED');

-- Здесь что-то пошло не так
ROLLBACK; -- Удалит И заказ, И платёж


Если добавить SAVEPOINT, можно откатиться только к последней контрольной точке:

BEGIN;

INSERT INTO orders VALUES (1, 'Laptop');
-- Создаём контрольную точку
SAVEPOINT before_payment;

INSERT INTO payments VALUES (1, 'FAILED');

-- Платёж не прошёл — откатываем только его
ROLLBACK TO SAVEPOINT before_payment;

-- Пробуем альтернативный способ оплаты
INSERT INTO payments VALUES (1, 'SUCCESS');

COMMIT;


SAVEPOINT полезен, когда стоимость полного отката слишком высокая и выгоднее переиграть только часть транзакции. Даёт более гибкое управление транзакциями, особенно в сложных сценариях.

SAVEPOINT не устраняет ошибку, а ограничивает её последствия.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

«Его открывал мой муж. Поэтому и покинул нас в 45», - добавила она

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

pgcli — интерфейс командной строки для PostgreSQL

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

Есть аналог для MySQL.

⛓ Ссылка: тык

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Как сгруппировать строки в пачки (batch), где сумма не превышает заданное значение — прямо в Oracle SQL

Используйте MATCH_RECOGNIZE:

SELECT ... FROM ... MATCH_RECOGNIZE (
ORDER BY ...
MEASURES ...
PATTERN ( total+ )
DEFINE total AS SUM ( ... ) <= ...
)


Например, можно разбить продажи по дате на группы, где суммарная выручка не превышает X.

Это похоже на "бин-пэкинг": ты просто собираешь строки в «ящики», пока сумма внутри ящика не превысит лимит

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Этот репозиторий содержит все, что вам нужно, чтобы прокачать свои навыки работы с SQL

Более 100 упражнений и примеров по SQL.

⛓ Ссылка: тык

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Руководство по SQL

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

Материал дополнен практическими примерами, которые способствуют лучшему усвоению информации

Сохраняйте, чтобы не потерять

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Если используешь COUNT вместе с CASE, не ставь ELSE 0

Пример:

SELECT 
Department,
COUNT(CASE WHEN Status = 'Active' THEN EmployeeID ELSE 0 END) AS ActiveCount
FROM Employees
GROUP BY Department;


Это неправильно и даст некорректный результат. Когда ты пишешь ELSE 0, COUNT начинает считать нули как значения. COUNT игнорирует только NULL, а ноль это не NULL. Поэтому если в отделе 10 активных и 5 неактивных сотрудников, запрос вернет 15 для ActiveCount, потому что посчитает все 15 строк (10 EmployeeID + 5 нулей).

Лучше так: убрать ELSE 0:

SELECT 
Department,
COUNT(CASE WHEN Status = 'Active' THEN EmployeeID END) AS ActiveCount
FROM Employees
GROUP BY Department;


ELSE тут не нужен. Если условие не выполняется, выражение вернет NULL, а COUNT NULL-ы не считает. Еще более явно можно написать так:

SELECT 
Department,
COUNT(CASE WHEN Status = 'Active' THEN 1 END) AS ActiveCount
FROM Employees
GROUP BY Department;


Единственный случай, когда ELSE 0 будет уместен, это если вместо COUNT ты используешь SUM.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Не используй CASE в WHERE

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

Классический пример:

SELECT * FROM products
WHERE
CASE WHEN category = 'Electronics' THEN price < 500 ELSE price < 100 END;


Работает, но часто убивает производительность, потому что CASE делает предикат не SARGable в большинстве популярных СУБД. Оптимизатор уже не может нормально сделать index seek по индексу на price (или по составному индексу (category, price)). В итоге нередко получаешь full table scan, даже если индексы отличные.

Лучше переписать так:

SELECT * FROM products
WHERE price < 100
OR (category = 'Electronics' AND price < 500);


Этот вариант лучше, потому что логика становится прямолинейной и без лишних условий. Запрос проще читать, проще поддерживать, и оптимизатору проще рассуждать о предикатах, потому что они “голые”, без обертки в логическое ветвление.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Поток данных в Postgres для чтения/записи:

Приложение --> (опционально) пулер --> отдельный backend/клиентское соединение --> shared buffers --> кэш ОС (page cache) --> физический диск.

При чтении к кэшу ОС или диску обращается только если нужных страниц нет в shared buffers.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

shivambhadani_/acid-properties-explained-a-complete-guide-with-postgresql-implementation-071afa6aaf8a">Cтатья про ACID-свойства.

Не только теория, но и практическая реализация на PostgreSQL с примерами из реальной жизни.

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

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Полезные Excel формулы

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Как выполнить поиск по нескольким условиям с помощью Xlookup

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

Перестань переписывать JOIN через ON; используй USING

Если ты давно пишешь JOIN’ы в SQL, ты наверняка привык к ON. Оно работает и делает свою работу. Но когда ты джоинишь две таблицы по колонкам с абсолютно одинаковым именем, есть более чистый и аккуратный вариант: USING.

Вот почему стоит подумать о переходе.

SELECT 
e.Employee_ID,
e.Employee_Name,
e.Department,
s.Net_Sales
FROM Employees e
JOIN sales s
ON e.Employee_ID = s.Employee_ID;


ON ок, но оно многословное. Приходится повторять и имена таблиц, и имена колонок. Если имя колонки одинаковое в обеих таблицах, ты по сути пишешь одно и то же дважды. Хуже того: когда выбираешь колонки, можешь получить два столбца Employee_ID, если явно не квалифицировать их.

USING это сокращение для join’ов по равенству, когда колонки называются одинаково. Та же самая выборка будет такой:

SELECT 
e.Employee_ID,
e.Employee_Name,
e.Department,
s.Net_Sales
FROM Employees e
JOIN sales s
USING (Employee_ID);


Намного чище, да? USING автоматически сопоставляет колонки с одинаковым именем и делает inner join (или left/right join, в зависимости от типа join). Ты указываешь имя колонки один раз.

Перейти на USING это маленькое изменение, которое делает SQL короче и более “самодокументируемым”. Меньше шансов ошибиться, и результирующий набор получается аккуратнее. В следующий раз, когда джоинишь по одинаково названным колонкам, выкинь ON и попробуй USING. Только проверь, что твоя СУБД поддерживает USING.

👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

В Postgres 18 появился новый метод клонирования для создания баз данных из шаблонных (template) БД, который работает заметно быстрее, чем другие способы. Это работает на конкретных файловых системах, которые поддерживают copy-on-write / reflink.


👉 @SQLPortal

Читать полностью…

SQL Portal | Базы Данных

В PostgreSQL 19 (dev) прилетели еще 2 полезных изменения:

1. Разрешили ALTER COLUMN SET EXPRESSION для виртуальных столбцов с CHECK-ограничениями

2. Запретили CR и LF в именах баз данных, ролей и табличных пространств

Небольшие изменения, но оба коммита полезные:
- первый про более гибкую работу с virtual columns
- второй про ужесточение/санитизацию имен и снижение странных кейсов

👉 @SQLPortal

Читать полностью…
Subscribe to a channel