Dmitry Medvedev
167 lines
6.4 KiB
SQL
167 lines
6.4 KiB
SQL
# Задачи на отработку:
|
||
|
||
### **Задача 1.**
|
||
|
||
Таблица `products`
|
||
|
||
| product_id | name |
|
||
| --- | --- |
|
||
| 101 | iPhone 14 |
|
||
| 102 | Galaxy S22 |
|
||
| 103 | Pixel 7 |
|
||
|
||
Таблица `prices`
|
||
|
||
| product_id | price | region |
|
||
| --- | --- | --- |
|
||
| 101 | 999 | US |
|
||
| 101 | 1049 | EU |
|
||
| 102 | 850 | US |
|
||
| 102 | 899 | CA |
|
||
| 103 | 799 | JP |
|
||
1. Сделай `ANY LEFT JOIN`, чтобы получить цену **любой** из регионов.
|
||
SELECT
|
||
pr.product_id,
|
||
p.region,
|
||
pr.name,
|
||
p.price
|
||
FROM prices p
|
||
ANY LEFT JOIN products pr ON p.product_id = pr.product_id;
|
||
ЗДЕСЬ МЫ ПОЛУЧИМ НА ВЫХОДЕ 5 СТРОК.
|
||
А ЕСЛИ БЫ ДЖОЙНИЛИ ТАБЛИЦЫ НАОБОРОТ, ТО ПОЛУЧИЛИ БЫ 3 СТРОКИ.
|
||
|
||
2. Что будет, если в таблице `prices` несколько цен на один `product_id`?
|
||
ВСЕ БУДЕТ ЗАВИСЕТЬ ОТ СОЕДИНЕНИЯ ТАБЛИЦ. ЕСЛИ МЫ ДЖОЙНИМ К ТАБЛИЦЕ С product_id И НА КАЖДОЫЙ ЕСТЬ НЕСКОЛЬКО ЦЕН, ТО В ТАКОМ СЛУЧАЕ КЛИКХАУС ВОЗЬМЕТ ПЕРВОЕ ЗНАЧЕНИЕ С ДИСКА И ПРИСВОИТ ЕГО К product_id, А ОСТАЛЬНЫЕ ЗНАЧЕНИЯ ВЫБРОСИТ.
|
||
|
||
3. Как повлияет `ALL LEFT JOIN`? Почему в ClickHouse его избегают?
|
||
ИЗ-ЗА ПРИМЕНЕНИЯ ОЛЛ ЛЕФТ ДЖОЙН КЛИКХАУС НЕ СМОЖЕТ ВЫБРОСИТЬ НЕНУЖНЫЕ СТРОКИ И УЧТЕТ ВСЕ. В НАШЕМ ПРИМЕРЕ СТРОК СТАНЕТ 5.
|
||
ДАННЫЕ МОГУТ ПЕРЕГРУЖАТЬ ПАМЯТЬ И ТОРМОЗИТЬ СИСТЕМУ. МЕТРИКИ МОГУТ СЧИТАТЬСЯ ИСКАЖЕННЫМИ.
|
||
|
||
### **Задача 2.**
|
||
|
||
Таблица `ticks`
|
||
|
||
| time | symbol | price |
|
||
| --- | --- | --- |
|
||
| 2025-07-17 10:00:00 | AAPL | 190.5 |
|
||
| 2025-07-17 10:01:00 | AAPL | 191.2 |
|
||
| 2025-07-17 10:02:30 | AAPL | 192.0 |
|
||
| 2025-07-17 10:05:00 | GOOG | 2850.0 |
|
||
|
||
Таблица `orders`
|
||
|
||
| time | symbol | size |
|
||
| --- | --- | --- |
|
||
| 2025-07-17 10:00:30 | AAPL | 50 |
|
||
| 2025-07-17 10:02:00 | AAPL | 75 |
|
||
| 2025-07-17 10:03:00 | AAPL | 20 |
|
||
| 2025-07-17 10:04:00 | GOOG | 15 |
|
||
1. Сделай `ASOF JOIN`, чтобы каждой сделке найти **предыдущую цену**.
|
||
SELECT
|
||
o.symbol,
|
||
o.size,
|
||
t.price,
|
||
o.time AS order_time,
|
||
t.time AS price_time
|
||
FROM orders o
|
||
ASOF LEFT JOIN ticks t ON o.symbol = t.symbol AND o.time >= t.time;
|
||
|
||
2. Почему нельзя использовать обычный `JOIN` по `time = time`?
|
||
КАК МИНИМУМ ПОТОМУ ЧТО ВРЕМЯ ПО СДЕЛКЕ И ВРЕМЯ ПО ТИКЕТУ НЕ СОВПАДАЕТ.
|
||
|
||
3. Сделай выборку только по `symbol = 'AAPL'`.
|
||
SELECT
|
||
o.symbol,
|
||
o.size,
|
||
t.price,
|
||
o.time AS order_time,
|
||
t.time AS price_time
|
||
FROM orders o
|
||
ASOF LEFT JOIN ticks t ON o.symbol = t.symbol AND o.time >= t.time
|
||
WHERE o.symbol = 'AAPL';
|
||
|
||
ИЛИ
|
||
|
||
SELECT
|
||
o.symbol,
|
||
o.size,
|
||
t.price,
|
||
o.time AS order_time,
|
||
t.time AS price_time
|
||
FROM
|
||
(
|
||
SELECT * FROM orders WHERE o.symbol = 'AAPL'
|
||
) AS o
|
||
ASOF LEFT JOIN
|
||
(
|
||
SELECT * FROM ticks WHERE o.symbol = 'AAPL'
|
||
) AS t
|
||
ON o.symbol = t.symbol AND o.time >= t.time;
|
||
|
||
### **Задача 3.**
|
||
|
||
Таблица `users`
|
||
|
||
| user_id | name |
|
||
| --- | --- |
|
||
| 1 | Alice |
|
||
| 2 | Bob |
|
||
| 3 | Charlie |
|
||
| 4 | Diana |
|
||
|
||
Таблица `payments`
|
||
|
||
| payment_id | user_id | amount |
|
||
| --- | --- | --- |
|
||
| 1001 | 1 | 500 |
|
||
| 1002 | 1 | 1500 |
|
||
| 1003 | 3 | 1200 |
|
||
| 1004 | 4 | 900 |
|
||
1. Верни всех пользователей, у которых **есть платежи** (через `WHERE user_id IN (...)`).
|
||
SELECT
|
||
u.user_id
|
||
FROM users u
|
||
WHERE user_id IN (SELECT user_id FROM payments);
|
||
|
||
2. Добавь условие: сумма платежа больше 1000.
|
||
SELECT
|
||
u.user_id
|
||
FROM users u
|
||
WHERE user_id IN (SELECT user_id FROM payments WHERE amount > 1000);
|
||
|
||
3. Почему это называют `SEMI JOIN`? Как это оптимизируется в ClickHouse?
|
||
Потому что это неполноценное соединение , а только проверяющее факт наличия того или иного условия.
|
||
Вообще по идее нужно было бы написать LEFT SEMI JOIN , в кликхаус видя конструкцию IN (SELECT...) уже сам понимает что это SEMI JOIN.
|
||
|
||
### **Задача 4.**
|
||
|
||
Таблица `big_logs` (упрощённый фрагмент)
|
||
|
||
| user_id | event_time | event_type |
|
||
| --- | --- | --- |
|
||
| 1 | 2025-07-17 08:00:00 | login |
|
||
| 2 | 2025-07-17 08:05:00 | view |
|
||
| 3 | 2025-07-17 08:10:00 | purchase |
|
||
| 1 | 2025-07-17 08:20:00 | logout |
|
||
| 2 | 2025-07-17 08:30:00 | click |
|
||
|
||
Таблица `users`
|
||
|
||
| user_id | name |
|
||
| --- | --- |
|
||
| 1 | Alice |
|
||
| 2 | Bob |
|
||
| 3 | Charlie |
|
||
1. Сделай `JOIN`, чтобы добавить `name` к каждому логу.
|
||
SELECT
|
||
bl.user_id,
|
||
bl.event_time,
|
||
bl.event_type,
|
||
u.name
|
||
FROM big_logs bl ANY LEFT JOIN users u ON bl.user_id = u.user_id;
|
||
|
||
2. Почему `users` нужно ставить **в правую часть JOIN**?
|
||
ПОТОМУ ЧТО ЭТА ТАБЛИЦА ЯВЛЯЕТСЯ МАЛЕНЬКОЙ И МОЖНО СКАЗАТЬ СПРАВОЧНОЙ, А ЛОГИ БУДУТ РАСТИ И РАСТИ. И ЕСЛИ ЕЕ ЗАГРУЗИТЬ В ОПЕРАТИВНУЮ ПАМЯТЬ, ТО МЫ МОЖЕМ РАНО ИЛИ ПОЗДНО ПОЛУЧИТЬ ОШИБКУ.
|
||
|
||
3. Как выбрать `ANY` или `ALL` — если `user_id` уникален?
|
||
ЕСЛИ МЫ УВЕРЕНЫ НА 100% ЧТО ОН УНИКАЛЕН ТО НУЖНО ВАЫБИРАТЬ ANY , В ТАКОМ СЛУЧАЕ СОХРАНЯЮТСЯ РЕСУРСЫ ПАМЯТИ. ALL ЖЕ ВЫБИРАЕТСЯ КОГДА НЕОБХОДИМО ДЕКАРТОВО ПРОИЗВЕДЕНИЕ. |