🖥 Какая ошибка в этом запросе?

UPDATE books SET sales_1999 >
(SELECT SUM(qty * price)
FROM sales
WHERE sales.book_id = books.id
AND sales.date BETWEEN '01/01/1999' AND '12/31/1999')
Вы не можете использовать арифметические операторы для функции SUM() (например SUM(qty * price))

Ответ

Следует использовать знак (=) для подзапроса

@sqlhub

🖥 Подробная Шпаргалка SQL на 2023 год

Руководство о всех SQL-запросах и примерах их использования.

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

Ставьте ❤️, если хотите больше подобного материала.

✔️ Шпаргалка

@sqlhub

🖥 Назовите SQL-операторы для составления условий

GROUP BY: используется при агрегировании для объединения идентичных данных в группы, предложение GROUP BY следует за предложением WHERE в операторе SELECT. В примере ниже результатом запроса с использованием GROUP BY будет таблица, показывающая количество пользователей (Customers) в каждой стране (Country):

SELECT COUNT(CustomerID), Country
FROM Customers

GROUP BY Country;
HAVING: используется для указания условия поиска в предложении GROUP BY, HAVING может использоваться в отсутствие предложения GROUP BY с помощью предложения WHERE. В примере ниже результатом запроса с использованием HAVING будет таблица, показывающая количество пользователей (Customers) в каждой стране (Country) с числом пользователей больше 5.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

ORDER BY: сортирует набор результатов в порядке возрастания (по умолчанию) или убывания (при использовании ключевого слова DESC). В примере ниже результатом запроса будет таблица пользователей (Customers), отсортированная по полю «Страна» (Country):

SELECT * FROM Customers
ORDER BY Country;

WHERE: используется для определения условия извлечения записей». В примере ниже результатом запроса будет таблица пользователей (Customers) из Мексики:

SELECT * FROM Customers
WHERE Country='Mexico';

@sqlhub

🖥 Задача

Компания определяет своих суперпользователей как тех, кто совершил не менее двух транзакций. Из следующей таблицы (см картинку) напишите запрос, чтобы вернуть для каждого пользователя дату, когда он стал суперпользователем, сначала тех, кто стал раньше суперпользователем. Пользователи, которые не являются суперпользователями, также должны присутствовать в таблице.

Решение

WITH users (user_id, action, action_date)
AS (VALUES
(1, 'start', CAST('2-12-20' AS date)),
(1, 'cancel', CAST('2-13-20' AS date)),
(2, 'start', CAST('2-11-20' AS date)),
(2, 'publish', CAST('2-14-20' AS date)),
(3, 'start', CAST('2-15-20' AS date)),
(3, 'cancel', CAST('2-15-20' AS date)),
(4, 'start', CAST('2-18-20' AS date)),
(1, 'publish', CAST('2-19-20' AS date))),
-- create a date rank column, partitioned by user ID, using the ROW_NUMBER() window function
t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_date DESC) AS date_rank
FROM users ),
-- filter on date rank column to pull latest and next latest actions from this table
latest AS (
SELECT *
FROM t1
WHERE date_rank = 1 ),
next_latest AS (
SELECT *
FROM t1
WHERE date_rank = 2 )
-- left join these two tables, subtracting latest from second latest to get time elapsed
SELECT
l1.user_id,
l1.action_date - l2.action_date AS days_elapsed
FROM latest l1
LEFT JOIN next_latest l2
ON l1.user_id = l2.user_id
ORDER BY 1

@sqlhub

🖥 Задачи SQL с решениями. Десять практических упражнений SQL

Эти вопросы и примеры решений помогут Вам развить свои навыки в SQL.

➡️ Смотреть

@sqlhub

🖥 SQL Задачи.

--На входе у нас есть таблица
DECLARE @pays TABLE (
client_id int --Идентификатор клиента
,date datetime --Дата и время платежа
,pay_sum decimal(19,2) --Сумма платежа
)

-- Заполняем таблицу тестовыми данными

INSERT INTO @pays VALUES (1, '2022-01-01T16:11:11', 10)
INSERT INTO @pays VALUES (2, '2022-01-01T16:12:12', 20)
INSERT INTO @pays VALUES (3, '2022-01-01T16:13:13', 30)
INSERT INTO @pays VALUES (5, '2022-02-01T16:14:14', 50)
INSERT INTO @pays VALUES (3, '2022-02-01T16:15:15', 35)


Напишите запрос, который выбирал бы все данные из таблицы с добавлением 2 полей:
- сумма платежа нарастающим итогом
- количество записей выше текущей для записей с тем же идентификатором клиента
При этом записи упорядочиваются сначала по дате, потом по идентификатору клиента.


SELECT select_1.client_id, select_1.date, select_1.pay_sum, sm, cnt
FROM
(SELECT a.client_id, a.date, a.pay_sum, SUM(b.pay_sum) sm
FROM @pays a
LEFT OUTER JOIN @pays b
ON a.date >= b.date
GROUP BY a.client_id, a.date, a.pay_sum) select_1
INNER JOIN
(SELECT a.client_id, a.date, a.pay_sum, count(c.client_id) cnt
FROM @pays a
LEFT OUTER JOIN @pays c
on a.client_id = c.client_id
AND a.date > c.date
GROUP BY a.client_id, a.date, a.pay_sum,c.client_id) select_2
ON select_1.client_id = select_2.client_id
AND select_1.date = select_2.date
AND select_1.pay_sum = select_2.pay_sum
ORDER BY select_1.date, select_1.client_id

@sqlhub

🖥 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

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

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

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

➡️ Книга

@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

🖥 Как модифицировать данные в нескольких таблицах и вернуть 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

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

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

Синтаксис:

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

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

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

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

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

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

@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