Bonifatsiy
117 lines
4.5 KiB
SQL
117 lines
4.5 KiB
SQL
--Типы данных
|
||
|
||
--Задача 1
|
||
CREATE TABLE raw_data
|
||
ON CLUSTER clickhouse_cluster
|
||
(
|
||
user_id String,
|
||
balance String,
|
||
created_at String
|
||
) ENGINE = MergeTree ORDER BY tuple();
|
||
|
||
SELECT DISTINCT
|
||
toDecimal64OrNull(balance, 2) AS balance_decimal,
|
||
toDateTime64OrNull(created_at, 3) AS created_at_date
|
||
FROM raw_data
|
||
WHERE
|
||
toDecimal64OrNull(balance, 2) IS NOT NULL
|
||
AND toDateTime64OrNull(created_at, 3) IS NOT NULL
|
||
|
||
/*
|
||
CAST(balance AS Decimal(10, 2)) - этом случае будет ошибка, так как данный метод не поддеживает NULL
|
||
|
||
Следующие два метода по сути своей преобразование типа данных с условием ИЛИ, то есть:
|
||
toDecimal64OrNull(balance, 2) - в этом методе на выходе будут корректные данные, в противном случае NULL
|
||
toDecimal64OrZero(balance, 2) - аналогично с NULL, только будет ноль.
|
||
*/
|
||
|
||
--Задача 2
|
||
CREATE TABLE events
|
||
ON CLUSTER clickhouse_cluster
|
||
(
|
||
event_id String,
|
||
event_date String, -- форматы: "2023-01-01", "01.02.2023", "invalid"
|
||
event_time String -- форматы: "12:30:45", "25:99:99"
|
||
)
|
||
ENGINE = MergeTree
|
||
ORDER BY event_id;
|
||
|
||
--Заполнил данными для проверки
|
||
INSERT INTO events (event_id, event_date, event_time) VALUES
|
||
('1', '2023-01-01', '12:30:45'),
|
||
('2', '01.02.2023', '08:15:00'),
|
||
('3', 'invalid', '25:99:99'),
|
||
('4', '2023-05-20', 'invalid'),
|
||
('5', '31.12.2023', '23:59:59');
|
||
|
||
SELECT * FROM events;
|
||
|
||
SELECT DISTINCT
|
||
toDate(DateTime_Valid) AS Event_Date,
|
||
toHour(DateTime_Valid) AS Event_Hour,
|
||
DateTime_Valid
|
||
FROM (
|
||
SELECT
|
||
parseDateTimeBestEffortOrNull(concat(event_date, ' ', event_time)) AS DateTime_Valid
|
||
FROM events
|
||
)
|
||
WHERE DateTime_Valid IS NOT NULL;
|
||
|
||
--Задача 3
|
||
DROP TABLE IF EXISTS user_activity ON CLUSTER clickhouse_cluster;
|
||
|
||
CREATE TABLE user_activity
|
||
ON CLUSTER clickhouse_cluster
|
||
(
|
||
user_id UInt32,
|
||
actions String -- JSON: ["login", "purchase", "logout"]
|
||
) ENGINE = MergeTree ORDER BY tuple();
|
||
|
||
--Тест данные
|
||
INSERT INTO user_activity (user_id, actions) VALUES
|
||
(1, '["login", "purchase", "logout"]'),
|
||
(2, '["login", "logout"]'),
|
||
(3, '["login"]'),
|
||
(4, '["login","purchase"]');
|
||
|
||
SELECT
|
||
user_id,
|
||
-- Сначала реобразовал записи из набора "каких-то" букв (String) в массив строк Array(String). Потом считваем длину массива и дальше суммируем длину всех массивов действий для каждого пользователя
|
||
sum(length(JSONExtract(actions, 'Array(String)'))) AS count_actions
|
||
FROM user_activity
|
||
WHERE
|
||
-- Проверяем наличие 'purchase' в массиве
|
||
has(JSONExtract(actions, 'Array(String)'), 'purchase')
|
||
GROUP BY
|
||
user_id;
|
||
/*
|
||
Преобразовал записи из набора "каких-то" букв (String) в массив строк Array(String). Нашел пользователей в новом массиве Array(String), совершивших действие "purchase".
|
||
Просто написать actions = 'purchase' не получится, так как изначально actions просто (String) и соответсвенно "["login", "purchase", "logout"]" нельзя сравнить с "purchase". Поэтому сначала преобразовываем в массив, а потом с функцией has(array, elem) ищем то, что нужно.
|
||
То есть менять бы не пришлось, если бы в actions было изначально разные значения в каждой строке ("login", "purchase" или "logout")
|
||
*/
|
||
|
||
--Задача 4
|
||
DROP TABLE IF EXISTS sensor_data ON CLUSTER clickhouse_cluster;
|
||
|
||
CREATE TABLE sensor_data
|
||
ON CLUSTER clickhouse_cluster
|
||
(
|
||
sensor_id UInt32,
|
||
value Float64,
|
||
status Enum('ok' = 1, 'error' = 2)
|
||
) ORDER BY sensor_id;
|
||
|
||
--Тест данные
|
||
INSERT INTO sensor_data (sensor_id, value, status) VALUES
|
||
(1, 25.5, 'ok'),
|
||
(2, 120.75, 'error'),
|
||
(3, 15.0, 1),
|
||
(4, 9999999.99, 'ok'),
|
||
(5, 0.001, 'ok');
|
||
|
||
SELECT
|
||
toString(status) AS status_string,
|
||
avg(value) AS avg_value --Среднее значение value для каждого status
|
||
FROM sensor_data
|
||
WHERE accurateCastOrNull(value, 'Decimal(10,2)') IS NOT NULL --Фильтруем строки, где value нельзя преобразовать в Decimal(10,2)
|
||
GROUP BY status_string; |