Data Science. SQL hub

🖥 Что вы знаете о функции STUFF(Transact-SQL)?

Функция STUFF вставляет одну строку в другую, начиная с указанной позиции. При этом в исходной строке, начиная с указанной позиции, удаляются символы в количестве, определенном в параметре Length.

Другими словами STUFF вставляет одну строку в другую. Она удаляет указанное количество символов первой строки в начальной позиции и вставляет на их место вторую строку.

Синтаксис:

STUFF(String1, Position, Length, String2)

Здесь String1 — это строка, которая будет перезаписана. Position определяет стартовую позицию для перезаписи строки. Length — это длина подстроки, которая будет удалена из исходной строки. String2 — это строка, которая будет вставлена в String1.

Пример:

SELECT STUFF(‘SQL Tutorial’,1,3,’Python’)
В результате работы функции «SQL Tutorial» превратится в «Python Tutorial».

Результат:

Python Tutorial

@sqlhub

🖥 Гайд по оптимизации SQL запросов

Думаю, каждый хоть раз использовал команду explain или хотя бы слышал про нее. Эта команда демонстрирует план выполнения запроса, но как именно СУБД приходит к нему остается загадкой. Да и как вообще СУБД понимает, что выбранный запрос оптимален? Неужели она проверяет все возможные варианты?

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

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

➡️ Читать дальше

@sqlhub

🚀 SQL подзапросы: руководство по использованию

Есть задачи, которые нельзя решить с помощью одного обычного запроса. Пример такой задачи — выборка всех записей со значением больше среднего по всей таблице. Для одного запроса нельзя и выбрать значения, и посчитать агрегатную функцию по всей таблице. Чтобы решить такие задачи, используют подзапросы.

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

▪Статья

@sqlhub

🖥 Как упростить себе жизнь при разработке интерфейса взаимодействия приложения и БД?

же давно существуют ORM-фреймворки (англ. Object-Relational Mapping). ORM – это та самая прослойка между приложением и БД, с помощью которой можно, управляя объектами в приложении, синхронизировать их с объектами в БД, а также избавиться от необходимости вручную реализовывать DAL — то есть, не прописывать как должен выглядеть SQL-запрос на CRUD-операцию, не раскладывать переменные объекта по местам в запросе, не задавать приведение к типам/размерность, и т. д.

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

Для большинства языков существует масса ORM-фреймворков, например, SQLAlchemy для Python, Entity Framework для .NET, Hibernate для Java. Опираясь на свои предпочтения, я буду разбирать типовые фишки ORM с помощью .NET Core/ EF Core/MS SQL.

➡️ Читать дальше

@sqlhub

🔥 Гайд по оптимизации SQL запросов

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

В этой статье хочу рассказать о некоторых приемах, позволяющих значительно ускорить работу SQL.

▪Читать дальше

@sqlhub

🔥 Очень полезная статья по созданию разных типов индексов sql

https://data-flair.training/blogs/sql-index/

@sqlhub

🖥 Как обезопасить приложение от тяжёлых миграций, приводящих к блокированию запросов?

#PostgreSQL

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

Вначале каждой миграции, которая выполняется внутри транзакции, нужно изменить настройки конфигурации lock_timeout и statement_timeout и idle_in_transaction_session_timeout командой SET LOCAL. Действие SET LOCAL продолжается только до конца текущей транзакции, независимо от того, фиксируется она или нет. При выполнении такой команды вне блока транзакции выдаётся предупреждение и больше ничего не происходит.

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

BEGIN;
DO $$
DECLARE
exception_message text;
exception_context text;
BEGIN
/*
Задаёт максимальную длительность ожидания любым оператором получения блокировки таблицы, индекса, строки или другого объекта базы данных.
Если ожидание не закончилось за указанное время, оператор прерывается.
Это ограничение действует на каждую попытку получения блокировки по отдельности и применяется как к явным запросам блокировки
(например, LOCK TABLE или SELECT FOR UPDATE без NOWAIT), так и к неявным.
Если это значение задаётся без единиц измерения, оно считается заданным в миллисекундах.
*/
SET LOCAL lock_timeout TO '3s';
-- Максимальное время выполнения любого SQL запроса в этой транзакции. Если будет превышено, то транзакция откатится.
SET LOCAL statement_timeout TO '30min';
-- Максимальное время простаивания транзакции, PostgreSQL >= 10. Если будет превышено, то транзакция откатится.
SET LOCAL idle_in_transaction_session_timeout TO '10s';
EXCEPTION WHEN undefined_object THEN
GET STACKED DIAGNOSTICS
exception_message = MESSAGE_TEXT,
exception_context = PG_EXCEPTION_CONTEXT;
RAISE NOTICE '%', exception_context;
RAISE NOTICE '%', exception_message;
END $$;


/*
Здесь SQL команды для наката (или отката) внутри транзакции
*/
COMMIT;

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

Если транзакция откатится, то есть 2 варианта: запустить повторно во время меньших нагрузок или оптимизировать код миграции, чтобы свести к минимуму блокировки.

@sqlhub

🖥 Вопросы с собеседования по анализу данных SQL в 2023 году.

Вы готовитесь к собеседованию по SQL для анализ данных? Тогда вы пришли в нужное место!

Это руководство поможет вам усовершенствовать свои навыки работы с SQL, вернуть уверенность в себе и быть готовым к работе!

Здесь вы найдёте подборку реальных вопросов для собеседований, задаваемых в таких компаниях, как Google, Oracle, Amazon, Microsoft и т.д. К каждому вопросу прилагается идеально написанный ответ, что экономит ваше время на подготовку к собеседованию.

➡️ Читать дальше

@sqlhub

🖥 Расскажите о видах индексов

Простые индексы. Создаются только для одного столбца таблицы.

Синтаксис:

CREATE INDEX index_name
ON table_name(column_name);

Составные индексы. Создаются для двух или большего количества столбцов таблицы.

Синтаксис:

CREATE INDEX index_name
ON table_name (column1, column2)

Уникальные индексы. Используются для поддержания целостности данных таблицы. Они не дают вставлять в таблицу несколько значений.

Синтаксис:

CREATE UNIQUE INDEX index
ON table_name(column_name)

@sqlhub

🖥 Как модифицировать данные в нескольких таблицах и вернуть id затронутых записей в одном запросе?

WITH
updated AS (
UPDATE table1
SET x = 5, y = 6 WHERE z > 7
RETURNING id
),
inserted AS (
INSERT INTO table2 (x, y, z) VALUES (5, 7, 10)
RETURNING id
)
SELECT 'table1_updated' AS action, id
FROM updated
UNION ALL
SELECT 'table2_inserted' AS action, id
FROM inserted;

@sqlhub

✔️ Особенности сравнения record и NULL

Testing a ROW expression with IS NULL only reports TRUE if every single column is NULL. Нужно об этом знать, чтобы на напороться на ошибки в своём коде.

SELECT
(NULL, NULL) IS NULL as "(NULL, NULL) IS NULL", --true
(NULL, NULL) IS NOT NULL as "(NULL, NULL) IS NOT NULL", --false
NOT (NULL, NULL) IS NULL as "NOT (NULL, NULL) IS NULL", --false

(1, NULL) IS NULL as "(1, NULL) IS NULL", --false
(1, NULL) IS NOT NULL as "(1, NULL) IS NOT NULL", --false --!!!
NOT (1, NULL) IS NULL as "NOT (1, NULL) IS NULL" --true --!!!

@sqlhub

🖥 Как откатить часть транзакции внутри функции или процедуры?
На SQL вы можете сделать так:

BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint; --rollback previous command
INSERT INTO table1 VALUES (3);
COMMIT;

Внутри функции или процедуры код выше завершится с ошибкой, например в is_sql.sql. Но вы можете откатить часть SQL команд в транзакции через подтранзакции:

DO $TEST$
BEGIN
-- here you can write DDL commands, for example, adding or deleting a table or its section
-- and/or
-- here you can write DML commands that modify data in tables and, thus, check the operation of triggers

-- rollback all test queries
raise exception using errcode = 'query_canceled';

EXCEPTION WHEN query_canceled THEN
--don't do anything
END
$TEST$;

@sqlhub

🖥 SQL: быстрое погружение 2022

Автор: Шилдс Уолтер

Книга идеальна для всех, кто ищет новые перспективы карьерного роста; для разработчиков, которые хотят расширить свои навыки и знания в программировании.

➡️ Книга

@sqlhub

Совет на 2023 год — выучите DevOps инструменты. DevOps на сегодня являются самыми высокооплачиваемыми и востребованными специалистами в ит-отрасли.

А для того, чтобы научиться за несколько месяцев, а не лет — держите в подписках канал DevOPs. Там на пальцах рассказывают что учить, на что забить и что актуально в 2023 году.

С этим каналом вы пройдете путь от "гугл - что такое Docker" до богатого DevOps специалиста.

Начните свой 2023 год продуктивно — школа DevOps.

🖥 SQL Задачи.

Даны 3 таблицы: таблица category с полями id и name, таблица sub_category с полями id и name и таблица page с полями id, name и sub_category_id. Достаньте одним запросом все страницы вместе с их подкатегориями и категориями.

SELECT * FROM page LEFT JOIN category ON page.catogory_id = category.id
LEFT JOIN sub_category ON category.id = sub_category.category_id

@sqlhub

PRQL — это простая и мощная конвейерная замена SQL

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

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

@sqlhub