Создание таблицы ost_ticket, где хранятся заявки на ипотеку:
create table ost_ticket (
ticket_id int primary key,
created timestamp,
staff bool,
status varchar(30),
city_id int
);
Создание таблицы ost_ticket_action_history, где хранятся пройденные этапы по заявкам на ипотеку:
create table ost_ticket_action_history (
ticket_id int,
action_id int,
action_name varchar(30),
started timestamp,
foreign key (ticket_id) references ost_ticket (ticket_id)
)
Заполнение первой таблицы данными:
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (1, "2021-09-22", 1, "closed", 1);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (2, "2022-03-31", 1, "open", 2);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (3, "2019-07-13", 1, "open", 3);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (4, "2021-10-21", 1, "open", 4);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (5, "2022-03-31", 1, "open", 5);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (6, "2021-06-21", 1, "open", 1);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (7, "2020-10-23", 1, "open", 2);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (8, "2020-01-08", 1, "open", 3);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (9, "2019-09-01", 1, "closed", 4);
insert into ost_ticket (ticket_id, created, staff, status, city_id) values (10, "2020-05-04", 1, "open", 5);
Заполнение второй таблицы данными:
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (1, 1, "Консультация", "2021-09-22");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (2, 2, "Заявка в банк", "2022-03-31");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (3, 2, "Заявка в банк", "2019-07-13");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (4, 2, "Заявка в банк", "2021-10-21");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (5, 1, "Консультация", "2022-03-31");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (6, 1, "Консультация", "2021-06-21");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (7, 1, "Консультация", "2020-10-23");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (8, 2, "Заявка в банк", "2020-01-08");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (9, 1, "Консультация", "2019-09-01");
insert into ost_ticket_action_history (ticket_id, action_id, action_name, started) values (10, 2, "Заявка в банк", "2020-05-04");
Нужно из таблицы ost_ticket_action_history, столбец action_name, сделать конверсию данных "Консультация" и "Заявка в банк" по формуле "Консультация"/"заявка в банк"*100%
Ума хватило только на это:
select (select (count(if(action_name='Заявка в банк')))/select (count(if(action_name='Консультация')))*100%;
Соответственно, это не правильно.
Сильно не бейте, просто уже голова пухнет, а результатов 0
P.S. Разобрался с помощью небольших костылей.
