-- params set @projectName = 'Správa kontaktů a souvisejících zápisů (IBM) - Falsum'; set @iterationName = '2. iterace'; set @projectId = (select id from ppicha.project where name = (@projectName collate utf8_unicode_ci)); set @iterationId = (select id from ppicha.iteration where superProjectId = @projectId and name = (@iterationName collate utf8_unicode_ci)); set @daysLookahead = 3; set @daysLookbehind = 6; set @silenceSteepnes = 0.1; set @cliffSteepnes = 2; -- variables set @endDate = (select max(date_format(work_item.created, '%Y-%m-%d')) from ppicha.field_change, ppicha.work_item_change, ppicha.work_unit, ppicha.configuration_change, ppicha.work_item where field_change.name = 'status' and (field_change.newValue = 'Closed' or newValue = 'Invalid') and field_change.workItemChangeId = work_item_change.id and work_item_change.workItemId = work_unit.id and work_unit.iterationId = @iterationId and work_item_change.id = configuration_change.changeId and configuration_change.configurationId = work_item.id ); set @startDate = (select min(date_format(work_item.created, '%Y-%m-%d')) from ppicha.work_unit, ppicha.work_item where work_unit.id = work_item.id and work_unit.iterationId = @iterationId ); set @issuesCount = (select count(id) from ppicha.work_unit where work_unit.iterationId = @iterationId); set @duration = (select datediff(@endDate, @startDate) + 1); set @startDateFormatted = date_format(@startDate, '%Y-%m-%d'); set @endDateFormatted = date_format(@endDate, '%Y-%m-%d'); set @dailyIdeal = (select @issuesCount / @duration); set @silenceThreshold = @silenceSteepnes * @issuesCount; set @cliffThreshold = @cliffSteepnes * @daysLookahead * @dailyIdeal; select 'closedOn', 'lookbehind', 'silenceThreshold', 'lookahead', 'cliffThreshold', 'detected' union -- query select -- subdate(results.closedOn, interval (@daysLookbehind - 1) day) as intervalStart, -- adddate(results.closedOn, interval @daysLookahead day) as intervalEnd, results.closedOn, -- results.dayIndex, -- @daysLookbehind, results.lookbehind, @silenceThreshold, results.lookahead , @cliffThreshold, -- if(results.dayIndex >= @daysLookbehind, true, false) relevance, -- if(results.lookbehind <= @silenceThreshold, true, false) silence, -- if(results.lookahead >= @cliffThreshold, true, false) cliff, if(results.dayIndex >= @daysLookbehind and results.lookbehind <= @silenceThreshold and results.lookahead >= @cliffThreshold, true, false) detected from ( select @curRow := @curRow + 1 as dayIndex, days.selected_date as closedOn, ifnull(dataPoints.lookbehind, 0) as lookbehind, ifnull(dataPoints.lookahead, 0) as lookahead from -- all days beteween start and end to fill in the gaps ( select * from -- calendar ( select adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 ) v where v.selected_date between @startDateFormatted and @endDateFormatted ) days left join -- actual datapoints; full outer join on lookahead and lookbehind (lookahead LEFT JOIN lookbehind UNION lookahead RIGHT JOIN lookbehind) ( select ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint, ifnull(lookbehind.closedCount, 0) as lookbehind, ifnull(lookahead.closedCount, 0) as lookahead from -- look x days ahead (excluding current) ( select days.selected_date as dataPoint, count(distinct work_unit.id) as closedCount from -- all days beteween start and end to fill in the gaps ( select * from -- calendar ( select adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 ) v where v.selected_date between @startDateFormatted and @endDateFormatted ) days, ppicha.work_item, ppicha.configuration_change, ppicha.work_item_change, ppicha.work_unit, ppicha.field_change, ppicha.status, ppicha.project_instance, ppicha.project, ppicha.status_classification where date_format(work_item.created, '%Y-%m-%d') > days.selected_date and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day) and work_item.id = configuration_change.configurationId and configuration_change.changeId = work_item_change.id and work_item_change.workItemId = work_unit.id and work_unit.iterationId = @iterationId and work_item_change.id = field_change.workItemChangeId and field_change.name = 'status' and field_change.newValue = status.name and status.projectInstanceId = project_instance.id and project_instance.projectId = @projectId and status.classId = status_classification.id and status_classification.superClass = 'CLOSED' group by dataPoint ) lookahead left join -- look y days behind (including current) ( select days.selected_date as dataPoint, count(distinct work_unit.id) as closedCount from -- all days beteween start and end to fill in the gaps ( select * from -- calendar ( select adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 ) v where v.selected_date between @startDateFormatted and @endDateFormatted ) days, ppicha.work_item, ppicha.configuration_change, ppicha.work_item_change, ppicha.work_unit, ppicha.field_change, ppicha.status, ppicha.project_instance, ppicha.project, ppicha.status_classification where date_format(work_item.created, '%Y-%m-%d') <= days.selected_date and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day) and work_item.id = configuration_change.configurationId and configuration_change.changeId = work_item_change.id and work_item_change.workItemId = work_unit.id and work_unit.iterationId = @iterationId and work_item_change.id = field_change.workItemChangeId and field_change.name = 'status' and field_change.newValue = status.name and status.projectInstanceId = project_instance.id and project_instance.projectId = @projectId and status.classId = status_classification.id and status_classification.superClass = 'CLOSED' group by dataPoint ) lookbehind on lookahead.dataPoint = lookbehind.dataPoint union select ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint, ifnull(lookbehind.closedCount, 0) as lookbehind, ifnull(lookahead.closedCount, 0) as lookahead from -- look x days ahead (excluding current) ( select days.selected_date as dataPoint, count(distinct work_unit.id) as closedCount from -- all days beteween start and end to fill in the gaps ( select * from -- calendar ( select adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 ) v where v.selected_date between @startDateFormatted and @endDateFormatted ) days, ppicha.work_item, ppicha.configuration_change, ppicha.work_item_change, ppicha.work_unit, ppicha.field_change, ppicha.status, ppicha.project_instance, ppicha.project, ppicha.status_classification where date_format(work_item.created, '%Y-%m-%d') > days.selected_date and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day) and work_item.id = configuration_change.configurationId and configuration_change.changeId = work_item_change.id and work_item_change.workItemId = work_unit.id and work_unit.iterationId = @iterationId and work_item_change.id = field_change.workItemChangeId and field_change.name = 'status' and field_change.newValue = status.name and status.projectInstanceId = project_instance.id and project_instance.projectId = @projectId and status.classId = status_classification.id and status_classification.superClass = 'CLOSED' group by dataPoint ) lookahead right join ( -- look y days behind (including current) select days.selected_date as dataPoint, count(distinct work_unit.id) as closedCount from -- all days beteween start and end to fill in the gaps ( select * from -- calendar ( select adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3 ) v where v.selected_date between @startDateFormatted and @endDateFormatted ) days, ppicha.work_item, ppicha.configuration_change, ppicha.work_item_change, ppicha.work_unit, ppicha.field_change, ppicha.status, ppicha.project_instance, ppicha.project, ppicha.status_classification where date_format(work_item.created, '%Y-%m-%d') <= days.selected_date and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day) and work_item.id = configuration_change.configurationId and configuration_change.changeId = work_item_change.id and work_item_change.workItemId = work_unit.id and work_unit.iterationId = @iterationId and work_item_change.id = field_change.workItemChangeId and field_change.name = 'status' and field_change.newValue = status.name and status.projectInstanceId = project_instance.id and project_instance.projectId = @projectId and status.classId = status_classification.id and status_classification.superClass = 'CLOSED' group by dataPoint ) lookbehind on lookahead.dataPoint = lookbehind.dataPoint ) dataPoints on days.selected_date = dataPoints.dataPoint -- row index join (select @curRow := 0) r order by days.selected_date ) results -- actual detection -- where -- if(results.dayIndex >= @daysLookbehind and results.lookbehind <= @silenceThreshold and results.lookahead >= @cliffThreshold, true, false) = true