Bonifatsiy
244 lines
7.4 KiB
SQL
244 lines
7.4 KiB
SQL
/* Запросы - Синтаксис*/
|
|
|
|
--! Задача 1 !--
|
|
|
|
DROP TABLE IF EXISTS dsolovianov.users ON CLUSTER clickhouse_cluster SYNC;
|
|
|
|
CREATE TABLE users
|
|
ON CLUSTER clickhouse_cluster
|
|
(
|
|
user_id UInt32,
|
|
name String,
|
|
age UInt8,
|
|
country String,
|
|
is_active Bool
|
|
) ENGINE = ReplicatedMergeTree(
|
|
'/clickhouse/tables/{shard}/dsolovianov/users_v2',
|
|
'{replica}'
|
|
)
|
|
ORDER BY user_id;
|
|
|
|
INSERT INTO users VALUES
|
|
(1, 'Ivan', 30, 'RU', 1),
|
|
(2, 'Anna', 25, 'RU', 1),
|
|
(3, 'Petr', 45, 'RU', 0),
|
|
(4, 'Olga', 35, 'RU', 1),
|
|
(5, 'John', 55, 'US', 1),
|
|
(6, 'Alice', 42, 'US', 0),
|
|
(7, 'Tom', 38, 'US', 1),
|
|
(8, 'Lucas', 28, 'BR', 1),
|
|
(9, 'Maria', 60, 'BR', 1),
|
|
(10, 'João', 27, 'BR', 0),
|
|
(11, 'Kenji', 33, 'JP', 1),
|
|
(12, 'Yuki', 29, 'JP', 0),
|
|
(13, 'Chen', 40, 'CN', 1),
|
|
(14, 'Li', 48, 'CN', 1),
|
|
(15, 'Fatima', 31, 'AE', 1),
|
|
(16, 'Ahmed', 36, 'AE', 1),
|
|
(17, 'George', 24, 'US', 1),
|
|
(18, 'Nina', 34, 'RU', 1),
|
|
(19, 'Max', 50, 'DE', 1),
|
|
(20, 'Sophie', 22, 'DE', 0);
|
|
|
|
--Получи 10 самых старших активных пользователей.
|
|
SELECT
|
|
user_id,
|
|
name,
|
|
age,
|
|
is_active
|
|
FROM users
|
|
WHERE is_active = true
|
|
ORDER BY age DESC
|
|
LIMIT 10;
|
|
|
|
--Выведи пользователей из country = 'RU', отсортированных по имени.
|
|
SELECT
|
|
user_id,
|
|
name,
|
|
country
|
|
FROM users
|
|
WHERE country = 'RU'
|
|
ORDER BY name;
|
|
|
|
--Что произойдёт, если написать LIMIT 10 без ORDER BY? Просто выведет 10 случайных активных прользователей без какой либо сортировки.
|
|
SELECT
|
|
user_id,
|
|
name,
|
|
age
|
|
FROM users
|
|
WHERE is_active = true
|
|
LIMIT 10;
|
|
|
|
--Посчитай количество пользователей по странам.
|
|
SELECT
|
|
country,
|
|
COUNT(*) as user_country
|
|
FROM users
|
|
GROUP BY country;
|
|
|
|
SELECT
|
|
country,
|
|
uniqExact(country) as unique_countries --возвращает точное количество уникальных значений в столбце country
|
|
FROM users
|
|
GROUP BY country;
|
|
|
|
--Для каждой страны выведи минимальный и максимальный возраст.
|
|
SELECT
|
|
country,
|
|
MIN(age) as min_age,
|
|
MAX(age) as max_age
|
|
FROM users
|
|
GROUP BY country
|
|
ORDER BY country;
|
|
|
|
--Отсортируй страны по убыванию количества пользователей.
|
|
SELECT
|
|
country,
|
|
COUNT(*) as user_country
|
|
FROM users
|
|
GROUP BY country
|
|
ORDER BY user_country DESC;
|
|
|
|
--Всё вместе
|
|
SELECT
|
|
country,
|
|
COUNT(*) as user_country,
|
|
SUM(is_active) as active_users,
|
|
MIN(age) as min_age,
|
|
MAX(age) as max_age,
|
|
uniqExact(country) as unique_countries
|
|
FROM users
|
|
GROUP BY country
|
|
ORDER BY country;
|
|
|
|
--! Задача 2 !--
|
|
|
|
DROP TABLE IF EXISTS dsolovianov.events ON CLUSTER clickhouse_cluster SYNC;
|
|
|
|
CREATE TABLE dsolovianov.events ON CLUSTER clickhouse_cluster
|
|
(
|
|
user_id UInt32,
|
|
event_time DateTime,
|
|
event_type String
|
|
)
|
|
ENGINE = ReplicatedMergeTree(
|
|
'/clickhouse/tables/{shard}/dsolovianov/events_v2',
|
|
'{replica}'
|
|
)
|
|
ORDER BY (user_id, event_time);
|
|
|
|
INSERT INTO dsolovianov.events (user_id, event_time, event_type) VALUES
|
|
(1, '2025-07-17 09:00:00', 'click'),
|
|
(1, '2025-07-17 09:05:00', 'view'),
|
|
(1, '2025-07-17 09:10:00', 'purchase'),
|
|
(2, '2025-07-17 10:00:00', 'click'),
|
|
(2, '2025-07-17 10:05:00', 'click'),
|
|
(2, '2025-07-17 11:00:00', 'view'),
|
|
(3, '2025-07-16 12:00:00', 'view'),
|
|
(3, '2025-07-16 12:30:00', 'click'),
|
|
(3, '2025-07-17 08:00:00', 'purchase'),
|
|
(4, '2025-07-17 13:00:00', 'click'),
|
|
(4, '2025-07-17 14:00:00', 'view'),
|
|
(4, '2025-07-17 15:00:00', 'view'),
|
|
(5, '2025-07-16 20:00:00', 'view'),
|
|
(5, '2025-07-17 01:00:00', 'click'),
|
|
(5, '2025-07-17 06:00:00', 'purchase'),
|
|
(6, '2025-07-17 07:30:00', 'view'),
|
|
(6, '2025-07-17 08:45:00', 'click'),
|
|
(7, '2025-07-16 23:59:59', 'click'),
|
|
(7, '2025-07-17 00:01:00', 'purchase'),
|
|
(7, '2025-07-17 10:10:00', 'view');
|
|
|
|
--1. Добавь колонку с кумулятивным количеством событий по `user_id` (используй `count() OVER ...`).
|
|
SELECT
|
|
user_id,
|
|
event_time,
|
|
event_type,
|
|
COUNT(*) OVER (PARTITION BY user_id ORDER BY event_time) AS cumulative_events
|
|
FROM dsolovianov.events
|
|
ORDER BY user_id, event_time;
|
|
|
|
--2. Сделай аналог ранжирования событий внутри пользователя: `row_number() OVER (PARTITION BY user_id ORDER BY event_time)`
|
|
SELECT
|
|
user_id,
|
|
event_time,
|
|
event_type,
|
|
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS event_rank
|
|
FROM dsolovianov.events
|
|
ORDER BY user_id, event_rank;
|
|
|
|
--3. Что будет, если ты явно задашь `ROWS BETWEEN`? Поддерживается ли?
|
|
--Да, `ROWS BETWEEN` поддерживается:
|
|
SELECT
|
|
user_id,
|
|
event_time,
|
|
event_type,
|
|
-- кумулятивное окно (все предыдущие строки)
|
|
COUNT(*) OVER (PARTITION BY user_id ORDER BY event_time
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_events,
|
|
-- скользящее окно: текущая + 2 предыдущих события
|
|
COUNT(*) OVER (PARTITION BY user_id ORDER BY event_time
|
|
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_3_events,
|
|
-- окно: предыдущая + текущая + следующая
|
|
COUNT(*) OVER (PARTITION BY user_id ORDER BY event_time
|
|
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS prev_curr_next
|
|
FROM dsolovianov.events
|
|
ORDER BY user_id, event_time;
|
|
|
|
--4. Сделай `WITH recent_events AS (...)` — все события за последние 24 часа.
|
|
WITH recent_events AS (
|
|
SELECT *
|
|
FROM dsolovianov.events
|
|
WHERE event_time >= '2025-07-16 15:00:00'
|
|
)
|
|
SELECT * FROM recent_events
|
|
ORDER BY event_time DESC;
|
|
|
|
--5. Используй CTE, чтобы посчитать топ-5 пользователей по числу этих событий.
|
|
WITH recent_events AS (
|
|
SELECT *
|
|
FROM dsolovianov.events
|
|
WHERE event_time >= '2025-07-16 15:00:00'
|
|
)
|
|
SELECT
|
|
user_id,
|
|
COUNT(*) AS event_count
|
|
FROM recent_events
|
|
GROUP BY user_id
|
|
ORDER BY event_count DESC
|
|
LIMIT 5;
|
|
|
|
--6. Добавь второй `WITH` — для маппинга `event_type → category` и используй его в основном запросе.
|
|
WITH
|
|
event_categories AS (
|
|
SELECT
|
|
event_type,
|
|
CASE
|
|
WHEN event_type = 'click' THEN 'engagement'
|
|
WHEN event_type = 'view' THEN 'engagement'
|
|
WHEN event_type = 'purchase' THEN 'conversion'
|
|
ELSE 'other'
|
|
END AS category
|
|
FROM (SELECT DISTINCT event_type FROM dsolovianov.events)
|
|
),
|
|
recent_events AS (
|
|
SELECT *
|
|
FROM dsolovianov.events
|
|
WHERE event_time >= '2025-07-16 15:00:00'
|
|
)
|
|
SELECT
|
|
re.user_id,
|
|
re.event_time,
|
|
re.event_type,
|
|
ec.category,
|
|
ROW_NUMBER() OVER (PARTITION BY re.user_id ORDER BY re.event_time) AS event_rank,
|
|
COUNT(*) OVER (PARTITION BY re.user_id ORDER BY re.event_time) AS cumulative_events
|
|
FROM recent_events re
|
|
LEFT JOIN event_categories ec ON re.event_type = ec.event_type
|
|
ORDER BY re.user_id, re.event_time;
|
|
|
|
/*
|
|
Вопрос: поддерживает ли ClickHouse многоуровневые или рекурсивные CTE?
|
|
многоуровневые CTE - да
|
|
рекурсивные CTE - нет в классическом понимании, а скорее в итеративном смысле.
|
|
*/ |