SQLize Online / PHPize Online  /  SQLtest Online

create table #calendar (dt date, type varchar(10)) insert into #calendar (dt, type) values ('2021-04-30', 'work'), ('2021-05-01', 'hol'), ('2021-05-02', 'hol'), ('2021-05-03', 'hol'), ('2021-05-04', 'work'), ('2021-05-05', 'work'), ('2021-05-06', 'work'), ('2021-05-07', 'work'), ('2021-05-08', 'hol'), ('2021-05-09', 'hol'), ('2021-05-10', 'hol'), ('2021-05-11', 'work'), ('2021-05-12', 'work'), ('2021-05-13', 'work'), ('2021-05-14', 'work'), ('2021-05-15', 'hol'), ('2021-05-16', 'hol'), ('2021-05-17', 'work'), ('2021-05-18', 'work'), ('2021-05-19', 'work'), ('2021-05-20', 'work'), ('2021-05-21', 'work'), ('2021-05-22', 'hol'), ('2021-05-23', 'hol'), ('2021-05-24', 'work'), ('2021-05-25', 'work'), ('2021-05-26', 'work'), ('2021-05-27', 'work'), ('2021-05-28', 'work'), ('2021-05-29', 'hol'), ('2021-05-30', 'hol'), ('2021-05-31', 'work') create table #logs (deal_id int, status_id int, created_at datetime) insert into #logs (deal_id, status_id, created_at ) values (1, 1, '2021-05-12 18:54:00'), (1, 2, '2021-05-13 13:34:00'), (1, 3, '2021-05-17 14:39:00'), (1, 2, '2021-05-18 9:24:00'), (1, 3, '2021-05-18 9:24:00'), (1, 4, '2021-05-18 10:27:00'), (1, 5, '2021-05-19 13:00:00'), (1, 4, '2021-05-19 13:00:00'), (1, 5, '2021-05-24 8:06:00'), (2, 1, '2021-05-14 17:31:00'), (2, 2, '2021-05-17 14:39:00'), (3, 1, '2021-04-30 9:17:00'), (3, 2, '2021-05-11 15:21:00'), (3, 3, '2021-05-13 12:32:00'), (3, 2, '2021-05-13 12:33:00'), (3, 3, '2021-05-13 13:08:00'), (3, 4, '2021-05-14 6:31:00'), (3, 5, '2021-05-14 11:15:00'), (4, 1, '2021-05-14 12:05:00') /* Среднее время находения на каждом статусе по всем сделкам между отрезками без выходных дней (с учетом повторяющихся статусов), и количество повторов каждого статуса; */ -- посчитать кол-во минут между статусами внутри сделки исключая выходные -- попробовать через функцию lead -- получить среднее значение времени на каждом статусе -- посчитать количество повторов. -- Можно попробовать в отдельной таблице и приджойнить ее -- добавляю поля: -- next_created_at - дата создания следующего статуса в текущей сделке -- m_diff - время в минутах между статусами в текущей сделке -- результат записываю во временную таблицу #deals select *, lead(created_at) over(partition by deal_id order by created_at) as next_created_at, datediff(hour, created_at, lead(created_at) over(partition by deal_id order by created_at)) as m_diff into #deals from #logs -- определяю кол-во выходных дней между датой создания текущего статуса -- и датой создания следующего статуса -- результат записываю во временную таблицу #holidays select d.created_at, d.next_created_at, count(type) as hol_qty into #holidays from #deals d left join #calendar c on c.dt > cast(d.created_at as date) and c.dt < cast(next_created_at as date) and c.type = 'hol' group by d.created_at, d.next_created_at -- определяю время (в минутах) по каждому статусу (поле: mins_in_status) -- результат записываю во временную таблицу #deals_w_mins select d.deal_id, d.status_id, d.created_at, d.next_created_at, coalesce(d.m_diff - (h.hol_qty * 24), 0) as mins_in_status into #deals_w_mins from #deals d left join #holidays h on h.created_at = d.created_at and h.next_created_at = d.next_created_at -- вывожу среднее время в часах в статусах по всем сделкам -- вывожу кол-во повторов каждого статуса select sum(mins_in_status) as all_t, status_id group by status_id from deals_w_mins