ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Просьба помочь решить задачу
18 сообщений из 18, страница 1 из 1
Просьба помочь решить задачу
    #40070978
n_script
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
n_script
Гость
Добрый день!
Пожалуйста, помогите решить задачу.
Описание данных: таблица 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;
Просьба помочь вывести долю клиентов, которые «оттекли» в каждом месяце.
...
Рейтинг: 0 / 0
18.05.2021, 10:13
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40070983
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Выложите:

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

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

Да, не забудьте указать точную версию MySQL - для такой задачи это критично.
...
Рейтинг: 0 / 0
18.05.2021, 10:51
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40070986
n_script
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
n_script
Гость
Akina,

CREATE TABLE `active_clients` (
`REPORT_MONTH` date NOT NULL,
`CLIENT_ID` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_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
...
Рейтинг: 0 / 0
18.05.2021, 11:11
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40070988
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Akina
Да, не забудьте указать точную версию MySQL

n_script
MySQL Workbench 8.0 CE

...
Рейтинг: 0 / 0
18.05.2021, 11:20
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40070992
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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
...
Рейтинг: 0 / 0
18.05.2021, 11:37
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40070998
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
n_script
и не активен в месяцы N+1, N+2, N+3.

тогда, всё-таки, немного не так
... нуу и dateadd заменишь на MySQL-ий
court
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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

...
Рейтинг: 0 / 0
18.05.2021, 11:58
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071003
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
n_script
MySQL Workbench 8.0 CE
Это клиент, а не сервер.

И не вижу эталонного ответа для показанных данных.
...
Рейтинг: 0 / 0
18.05.2021, 12:07
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071004
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
court, ну уж если пишешь под 8 версию, то зачем вязать три копии, когда есть LEAD()?
...
Рейтинг: 0 / 0
18.05.2021, 12:08
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071009
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Akina
court, ну уж если пишешь под 8 версию, то зачем вязать три копии, когда есть LEAD()?
нуу, CTE можно заменить на derived table и будет для любой версии )
...
Рейтинг: 0 / 0
18.05.2021, 12:14
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071014
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
court
Akina
court, ну уж если пишешь под 8 версию, то зачем вязать три копии, когда есть LEAD()?
нуу, CTE можно заменить на derived table и будет для любой версии )

пс
но с LEAD() будет, конечно, на порядок лучше
я как-то и забыл, что в нём offset есть )) как-то никогда "не пригождался", а тут как раз в тему
...
Рейтинг: 0 / 0
18.05.2021, 12:37
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071025
n_script
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
n_script
Гость
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;
...
Рейтинг: 0 / 0
18.05.2021, 13:15
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071027
n_script
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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)
)
...
Рейтинг: 0 / 0
18.05.2021, 13:16
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071030
n_script
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
n_script
Гость
court, 8.0
я начинающий программист)
...
Рейтинг: 0 / 0
18.05.2021, 13:17
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071032
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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.
...
Рейтинг: 0 / 0
18.05.2021, 13:20
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071034
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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" - это признак того, что клиент "оттёк"
...
Рейтинг: 0 / 0
18.05.2021, 13:26
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071064
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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.
...
Рейтинг: 0 / 0
18.05.2021, 14:31
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071073
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Akina
Код: sql
1.
SUM(cnt < 3) / COUNT(*) * 100 percent_leaved


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

------------
с lead()
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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);


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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.0000002018-02-0140.0000002018-03-0150.2000002018-04-0130.3333332018-05-0131.000000
...
Рейтинг: 0 / 0
18.05.2021, 14:52
| Ответить | Цитировать | Написать  
Просьба помочь решить задачу
    #40071109
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
court
там клиент "оттёк" НЕ когда его не было хоть в одном из 3-х след.месяцах,
а когда его не было ВО ВСЕХ 3-х послед.месяцах
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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
...
Рейтинг: 0 / 0
18.05.2021, 16:55
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Просьба помочь решить задачу
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/search_topic.php?author=%D1%8A%D1%8A%D1%8A%D1%8A%D1%8A&author_mode=last_posts&do_search=1]:
 0ms
get settings:
 11ms
get forum list:
 15ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 167ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 57ms
get tp. blocked users:
 2ms
others: 852ms
total:  1149ms
созд. / загр.: 1149ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]