Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Просьба помочь решить задачу  [new]
n_script
Member

Откуда:
Сообщений: 5
Добрый день!
Пожалуйста, помогите решить задачу.
Описание данных: таблица ACTIVE_CLIENTS содержит ежемесячный срез клиентов банка, которые совершали какие-либо транзакции в данном месяце. Атрибуты: отчетный месяц (report_month) и идентификатор клиента (client_id). Считаем, что клиент «оттек» из банка в месяце N, если в месяце N он активен (присутствует в таблице ACTIVE_CLIENTS) и не активен в месяцы N+1, N+2, N+3.
Таблица:
REPORT_MONTH CLIENT_ID
2018-01-01 1847982357
2018-01-01 938475
2018-02-01 1847982357
2018-02-01 6789998
2018-03-01 67900001
… …
Задание: вывести количество активных клиентов на каждый месяц; долю клиентов, которые «оттекли» в каждом месяце.
Количество активных клиентов на каждый месяц вывести получилось, код:
SELECT
MONTHNAME(REPORT_MONTH) AS MONTH,
COUNT(CLIENT_ID)
FROM active_clients
GROUP BY REPORT_MONTH;
Просьба помочь вывести долю клиентов, которые «оттекли» в каждом месяце.
18 май 21, 10:13    [22323441]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21180
Выложите:

- CREATE TABLE для таблицы
- INSERT INTO с тест-данными
- эталонный ответ для этих данных

Первые два пункта лучше сделать в виде online fiddle. Например, на dbfiddle.uk

Да, не забудьте указать точную версию MySQL - для такой задачи это критично.

Сообщение было отредактировано: 18 май 21, 10:44
18 май 21, 10:51    [22323447]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
n_script
Member

Откуда:
Сообщений: 5
Akina,

CREATE TABLE `active_clients` (
`REPORT_MONTH` date NOT NULL,
`CLIENT_ID` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `active_clients` VALUES ('2018-01-01',1847982357),('2018-01-01',938475),('2018-02-01',1847982357),('2018-02-01',6789998),('2018-03-01',67900001);

MySQL Workbench 8.0 CE
18 май 21, 11:11    [22323456]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
Akina
Да, не забудьте указать точную версию MySQL

n_script
MySQL Workbench 8.0 CE

Картинка с другого сайта.
18 май 21, 11:20    [22323459]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
with cte as (
	select
		t1.REPORT_MONTH
		,t1.CLIENT_ID
		,case when t2.CLIENT_ID is not null and t3.CLIENT_ID is not null then 1 else 0 end as flag
	from active_clients t1
	left join active_clients t2 on t1.CLIENT_ID=t2.CLIENT_ID and t2.REPORT_MONTH=dateadd(month,1,t1.REPORT_MONTH)
	left join active_clients t3 on t2.CLIENT_ID=t3.CLIENT_ID and t3.REPORT_MONTH=dateadd(month,1,t2.REPORT_MONTH)
	where dateadd(month,2,t1.REPORT_MONTH) <=  (select max(REPORT_MONTH) from active_clients)
)
select
	REPORT_MONTH
	,count(CLIENT_ID) as "количество активных клиентов на каждый месяц"
	,sum(case when flag=0 then 1.0 end) / count(CLIENT_ID) as "долю клиентов, которые «оттекли» в каждом месяце"
from cte 
group by
	REPORT_MONTH
18 май 21, 11:37    [22323477]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
n_script
и не активен в месяцы N+1, N+2, N+3.

тогда, всё-таки, немного не так
... нуу и dateadd заменишь на MySQL-ий
court
with cte as (
	select
		t1.REPORT_MONTH
		,t1.CLIENT_ID
		,case when t2.CLIENT_ID is null and t3.CLIENT_ID is null and t4.CLIENT_ID is null then 0 else 1 end as flag
	from active_clients t1
	left join active_clients t2 on t1.CLIENT_ID=t2.CLIENT_ID and t2.REPORT_MONTH=dateadd(month,1,t1.REPORT_MONTH)
	left join active_clients t3 on t1.CLIENT_ID=t3.CLIENT_ID and t3.REPORT_MONTH=dateadd(month,2,t1.REPORT_MONTH)
	left join active_clients t4 on t1.CLIENT_ID=t4.CLIENT_ID and t4.REPORT_MONTH=dateadd(month,3,t1.REPORT_MONTH)
	where dateadd(month,3,t1.REPORT_MONTH) <=  (select max(REPORT_MONTH) from active_clients)
)
select
	REPORT_MONTH
	,count(CLIENT_ID) as "количество активных клиентов на каждый месяц"
	,sum(case when flag=0 then 1.0 end) / count(CLIENT_ID) as "долю клиентов, которые «оттекли» в каждом месяце"
from cte 
group by
	REPORT_MONTH


Сообщение было отредактировано: 18 май 21, 11:50
18 май 21, 11:58    [22323502]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21180
n_script
MySQL Workbench 8.0 CE
Это клиент, а не сервер.

И не вижу эталонного ответа для показанных данных.
18 май 21, 12:07    [22323516]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21180
court, ну уж если пишешь под 8 версию, то зачем вязать три копии, когда есть LEAD()?
18 май 21, 12:08    [22323517]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
Akina
court, ну уж если пишешь под 8 версию, то зачем вязать три копии, когда есть LEAD()?
нуу, CTE можно заменить на derived table и будет для любой версии )
18 май 21, 12:14    [22323526]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
court
Akina
court, ну уж если пишешь под 8 версию, то зачем вязать три копии, когда есть LEAD()?
нуу, CTE можно заменить на derived table и будет для любой версии )

пс
но с LEAD() будет, конечно, на порядок лучше
я как-то и забыл, что в нём offset есть )) как-то никогда "не пригождался", а тут как раз в тему
18 май 21, 12:37    [22323542]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
n_script
Member

Откуда:
Сообщений: 5
court, спасибо.
Оптимизировала под свою версию (8.0), сработало:
WITH cte AS (
SELECT
t1.REPORT_MONTH,
t1.CLIENT_ID,
CASE WHEN t2.CLIENT_ID IS NULL AND t3.CLIENT_ID IS NULL AND t4.CLIENT_ID IS NULL THEN 0 ELSE 1 END AS flag
FROM ACTIVE_CLIENTS t1
LEFT JOIN ACTIVE_CLIENTS t2 on t1.CLIENT_ID=t2.CLIENT_ID and t2.REPORT_MONTH=DATE_ADD(t1.REPORT_MONTH, INTERVAL 1 month)
LEFT JOIN ACTIVE_CLIENTS t3 on t1.CLIENT_ID=t3.CLIENT_ID and t3.REPORT_MONTH=DATE_ADD(t1.REPORT_MONTH, INTERVAL 1 month)
LEFT JOIN ACTIVE_CLIENTS t4 on t1.CLIENT_ID=t4.CLIENT_ID and t4.REPORT_MONTH=DATE_ADD(t1.REPORT_MONTH, INTERVAL 1 month)
WHERE DATE_ADD(t1.REPORT_MONTH, INTERVAL 1 month) <= (SELECT MAX(REPORT_MONTH) FROM ACTIVE_CLIENTS)
)
SELECT
MONTHNAME(REPORT_MONTH) AS MONTH,
COUNT(CLIENT_ID) AS QUANTITY_OF_ACTIVE_CLIENTS,
SUM(CASE WHEN flag=0 THEN 1.0 END)/COUNT(CLIENT_ID) AS SHARE_OF_OUTFLOW
FROM cte
GROUP BY
REPORT_MONTH;
18 май 21, 13:15    [22323561]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
n_script
Member

Откуда:
Сообщений: 5
court, пожалуйста, объясните эту часть кода:
with cte as (
select
t1.REPORT_MONTH
,t1.CLIENT_ID
,case when t2.CLIENT_ID is null and t3.CLIENT_ID is null and t4.CLIENT_ID is null then 0 else 1 end as flag
from active_clients t1
left join active_clients t2 on t1.CLIENT_ID=t2.CLIENT_ID and t2.REPORT_MONTH=dateadd(month,1,t1.REPORT_MONTH)
left join active_clients t3 on t1.CLIENT_ID=t3.CLIENT_ID and t3.REPORT_MONTH=dateadd(month,2,t1.REPORT_MONTH)
left join active_clients t4 on t1.CLIENT_ID=t4.CLIENT_ID and t4.REPORT_MONTH=dateadd(month,3,t1.REPORT_MONTH)
where dateadd(month,3,t1.REPORT_MONTH) <= (select max(REPORT_MONTH) from active_clients)
)
18 май 21, 13:16    [22323563]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
n_script
Member

Откуда:
Сообщений: 5
court, 8.0
я начинающий программист)
18 май 21, 13:17    [22323567]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
n_script
FROM ACTIVE_CLIENTS t1
LEFT JOIN ACTIVE_CLIENTS t2 on t1.CLIENT_ID=t2.CLIENT_ID and t2.REPORT_MONTH=DATE_ADD(t1.REPORT_MONTH, INTERVAL 1 month)
LEFT JOIN ACTIVE_CLIENTS t3 on t1.CLIENT_ID=t3.CLIENT_ID and t3.REPORT_MONTH=DATE_ADD(t1.REPORT_MONTH, INTERVAL 1 month)
LEFT JOIN ACTIVE_CLIENTS t4 on t1.CLIENT_ID=t4.CLIENT_ID and t4.REPORT_MONTH=DATE_ADD(t1.REPORT_MONTH, INTERVAL 1 month)

почему везде INTERVAL 1 ?
должно быть 1,2,3 как в ТЗ
автор
не активен в месяцы N+1, N+2, N+3.


Сообщение было отредактировано: 18 май 21, 13:13
18 май 21, 13:20    [22323570]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
n_script
court, пожалуйста, объясните эту часть кода:
with cte as (
select
t1.REPORT_MONTH
,t1.CLIENT_ID
,case when t2.CLIENT_ID is null and t3.CLIENT_ID is null and t4.CLIENT_ID is null then 0 else 1 end as flag
from active_clients t1
left join active_clients t2 on t1.CLIENT_ID=t2.CLIENT_ID and t2.REPORT_MONTH=dateadd(month,1,t1.REPORT_MONTH)
left join active_clients t3 on t1.CLIENT_ID=t3.CLIENT_ID and t3.REPORT_MONTH=dateadd(month,2,t1.REPORT_MONTH)
left join active_clients t4 on t1.CLIENT_ID=t4.CLIENT_ID and t4.REPORT_MONTH=dateadd(month,3,t1.REPORT_MONTH)
where dateadd(month,3,t1.REPORT_MONTH) <= (select max(REPORT_MONTH) from active_clients)
)

зафиксировали месяц/клиента в t1 и джойним к нему месяц+1, месяц+2, месяц+3 того же клиента
а в case - проверяем есть ли он в этих (месяц+1, месяц+2, месяц+3)
Если во всех месяцах его нет, то установили флаг "0" - это признак того, что клиент "оттёк"
18 май 21, 13:26    [22323576]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21180
WITH 
cte1 AS ( SELECT t1.report_month, t1.client_id, COUNT(*) cnt
          FROM active_clients t1
          LEFT JOIN active_clients t2 ON t1.client_id = t2.client_id
                                     AND t2.report_month BETWEEN t1.report_month + INTERVAL 1 MONTH
                                                             AND t1.report_month + INTERVAL 3 MONTH
          GROUP BY t1.report_month, t1.client_id )
SELECT report_month, 
       COUNT(*) active,
       SUM(cnt < 3) / COUNT(*) * 100 percent_leaved
FROM cte1
GROUP BY report_month ;

fiddle

В данных - 5 клиентов. Последние три месяца, само собой, считается 100% отток, ибо дальше данные отсутствуют.

Обошлось и без оконных функций. CTE легко превращается в подзапрос. Так что будет работать и на 5.x.

Сообщение было отредактировано: 18 май 21, 14:24
18 май 21, 14:31    [22323633]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
court
Member

Откуда:
Сообщений: 2335
Akina
SUM(cnt < 3) / COUNT(*) * 100 percent_leaved

неее, там клиент "оттёк" НЕ когда его не было хоть в одном из 3-х след.месяцах,
а когда его не было ВО ВСЕХ 3-х послед.месяцах
автор
если в месяце N он активен (присутствует в таблице ACTIVE_CLIENTS) и не активен в месяцы N+1, N+2, N+3


------------
с lead()
+
INSERT INTO active_clients VALUES 
('2018-01-01',1),  
('2018-02-01',1),
('2018-03-01',1),
('2018-04-01',1),
('2018-05-01',1),
('2018-01-01',2),
('2018-02-01',2),
('2018-03-01',2),
('2018-05-01',2),
('2018-01-01',3),
('2018-03-01',3),
('2018-04-01',3),
('2018-02-01',4),
('2018-03-01',4),
('2018-04-01',4),
('2018-05-01',4),
('2018-02-01',5),
('2018-03-01',5);

with cte as (
	select
		t1.report_month
		,t1.client_id
		,case when lead(t1.report_month)over(partition by t1.client_id order by t1.report_month) 
                       between dateadd(month,1,t1.REPORT_MONTH) and dateadd(month,3,t1.REPORT_MONTH) then 1 else 0 
                end as flag
	from active_clients t1
)
select
	REPORT_MONTH
	,count(CLIENT_ID) as "количество активных клиентов на каждый месяц"
	,sum(case when flag=0 then 1.0 else 0 end) / count(CLIENT_ID) as "долю клиентов, которые «оттекли» в каждом месяце"
from cte 
group by
	REPORT_MONTH

REPORT_MONTHколичество активных клиентов на каждый месяцдолю клиентов, которые «оттекли» в каждом месяце
2018-01-0130.000000
2018-02-0140.000000
2018-03-0150.200000
2018-04-0130.333333
2018-05-0131.000000
18 май 21, 14:52    [22323646]     Ответить | Цитировать Сообщить модератору
 Re: Просьба помочь решить задачу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21180
court
там клиент "оттёк" НЕ когда его не было хоть в одном из 3-х след.месяцах,
а когда его не было ВО ВСЕХ 3-х послед.месяцах
WITH 
cte1 AS ( SELECT t1.report_month, t1.client_id, COUNT(t2.client_id) cnt
          FROM active_clients t1
          LEFT JOIN active_clients t2 ON t1.client_id = t2.client_id
                                     AND t2.report_month BETWEEN t1.report_month + INTERVAL 1 MONTH
                                                             AND t1.report_month + INTERVAL 3 MONTH
          GROUP BY t1.report_month, t1.client_id )
SELECT report_month, 
       COUNT(*) active,
       SUM(!cnt) / COUNT(*) * 100 percent_leaved
FROM cte1
GROUP BY report_month ;
fiddle

Сообщение было отредактировано: 18 май 21, 16:47
18 май 21, 16:55    [22323728]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить