|
1
|
-- params
|
|
2
|
set @projectName = 'Správa kontaktů a souvisejících zápisů (IBM) - Falsum';
|
|
3
|
set @iterationName = '2. iterace';
|
|
4
|
set @projectId = (select id from ppicha.project where name = (@projectName collate utf8_unicode_ci));
|
|
5
|
set @iterationId = (select id from ppicha.iteration where superProjectId = @projectId and name = (@iterationName collate utf8_unicode_ci));
|
|
6
|
set @daysLookahead = 3;
|
|
7
|
set @daysLookbehind = 6;
|
|
8
|
set @silenceSteepnes = 0.1;
|
|
9
|
set @cliffSteepnes = 2;
|
|
10
|
|
|
11
|
|
|
12
|
-- variables
|
|
13
|
set @endDate = (select
|
|
14
|
max(date_format(work_item.created, '%Y-%m-%d'))
|
|
15
|
from
|
|
16
|
ppicha.field_change,
|
|
17
|
ppicha.work_item_change,
|
|
18
|
ppicha.work_unit,
|
|
19
|
ppicha.configuration_change,
|
|
20
|
ppicha.work_item
|
|
21
|
where
|
|
22
|
field_change.name = 'status'
|
|
23
|
and (field_change.newValue = 'Closed' or newValue = 'Invalid')
|
|
24
|
and field_change.workItemChangeId = work_item_change.id
|
|
25
|
and work_item_change.workItemId = work_unit.id
|
|
26
|
and work_unit.iterationId = @iterationId
|
|
27
|
and work_item_change.id = configuration_change.changeId
|
|
28
|
and configuration_change.configurationId = work_item.id
|
|
29
|
);
|
|
30
|
|
|
31
|
set @startDate = (select
|
|
32
|
min(date_format(work_item.created, '%Y-%m-%d'))
|
|
33
|
from
|
|
34
|
ppicha.work_unit,
|
|
35
|
ppicha.work_item
|
|
36
|
where
|
|
37
|
work_unit.id = work_item.id
|
|
38
|
and work_unit.iterationId = @iterationId
|
|
39
|
);
|
|
40
|
set @issuesCount = (select count(id) from ppicha.work_unit where work_unit.iterationId = @iterationId);
|
|
41
|
set @duration = (select datediff(@endDate, @startDate) + 1);
|
|
42
|
set @startDateFormatted = date_format(@startDate, '%Y-%m-%d');
|
|
43
|
set @endDateFormatted = date_format(@endDate, '%Y-%m-%d');
|
|
44
|
set @dailyIdeal = (select @issuesCount / @duration);
|
|
45
|
set @silenceThreshold = @silenceSteepnes * @issuesCount;
|
|
46
|
set @cliffThreshold = @cliffSteepnes * @daysLookahead * @dailyIdeal;
|
|
47
|
|
|
48
|
select
|
|
49
|
'closedOn',
|
|
50
|
'lookbehind',
|
|
51
|
'silenceThreshold',
|
|
52
|
'lookahead',
|
|
53
|
'cliffThreshold',
|
|
54
|
'detected'
|
|
55
|
|
|
56
|
union
|
|
57
|
|
|
58
|
-- query
|
|
59
|
select
|
|
60
|
-- subdate(results.closedOn, interval (@daysLookbehind - 1) day) as intervalStart,
|
|
61
|
-- adddate(results.closedOn, interval @daysLookahead day) as intervalEnd,
|
|
62
|
results.closedOn,
|
|
63
|
-- results.dayIndex,
|
|
64
|
-- @daysLookbehind,
|
|
65
|
results.lookbehind,
|
|
66
|
@silenceThreshold,
|
|
67
|
results.lookahead ,
|
|
68
|
@cliffThreshold,
|
|
69
|
-- if(results.dayIndex >= @daysLookbehind, true, false) relevance,
|
|
70
|
-- if(results.lookbehind <= @silenceThreshold, true, false) silence,
|
|
71
|
-- if(results.lookahead >= @cliffThreshold, true, false) cliff,
|
|
72
|
if(results.dayIndex >= @daysLookbehind and results.lookbehind <= @silenceThreshold and results.lookahead >= @cliffThreshold, true, false) detected
|
|
73
|
from
|
|
74
|
(
|
|
75
|
select
|
|
76
|
@curRow := @curRow + 1 as dayIndex,
|
|
77
|
days.selected_date as closedOn,
|
|
78
|
ifnull(dataPoints.lookbehind, 0) as lookbehind,
|
|
79
|
ifnull(dataPoints.lookahead, 0) as lookahead
|
|
80
|
from
|
|
81
|
-- all days beteween start and end to fill in the gaps
|
|
82
|
(
|
|
83
|
select
|
|
84
|
*
|
|
85
|
from
|
|
86
|
-- calendar
|
|
87
|
(
|
|
88
|
select
|
|
89
|
adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
|
|
90
|
from
|
|
91
|
(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,
|
|
92
|
(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,
|
|
93
|
(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,
|
|
94
|
(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
|
|
95
|
)
|
|
96
|
v
|
|
97
|
where
|
|
98
|
v.selected_date between @startDateFormatted and @endDateFormatted
|
|
99
|
)
|
|
100
|
days
|
|
101
|
left join
|
|
102
|
-- actual datapoints; full outer join on lookahead and lookbehind (lookahead LEFT JOIN lookbehind UNION lookahead RIGHT JOIN lookbehind)
|
|
103
|
(
|
|
104
|
select
|
|
105
|
ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint,
|
|
106
|
ifnull(lookbehind.closedCount, 0) as lookbehind,
|
|
107
|
ifnull(lookahead.closedCount, 0) as lookahead
|
|
108
|
from
|
|
109
|
-- look x days ahead (excluding current)
|
|
110
|
(
|
|
111
|
select
|
|
112
|
days.selected_date as dataPoint,
|
|
113
|
count(distinct work_unit.id) as closedCount
|
|
114
|
from
|
|
115
|
-- all days beteween start and end to fill in the gaps
|
|
116
|
(
|
|
117
|
select
|
|
118
|
*
|
|
119
|
from
|
|
120
|
-- calendar
|
|
121
|
(
|
|
122
|
select
|
|
123
|
adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
|
|
124
|
from
|
|
125
|
(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,
|
|
126
|
(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,
|
|
127
|
(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,
|
|
128
|
(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
|
|
129
|
)
|
|
130
|
v
|
|
131
|
where
|
|
132
|
v.selected_date between @startDateFormatted and @endDateFormatted
|
|
133
|
)
|
|
134
|
days,
|
|
135
|
ppicha.work_item,
|
|
136
|
ppicha.configuration_change,
|
|
137
|
ppicha.work_item_change,
|
|
138
|
ppicha.work_unit,
|
|
139
|
ppicha.field_change,
|
|
140
|
ppicha.status,
|
|
141
|
ppicha.project_instance,
|
|
142
|
ppicha.project,
|
|
143
|
ppicha.status_classification
|
|
144
|
where
|
|
145
|
date_format(work_item.created, '%Y-%m-%d') > days.selected_date
|
|
146
|
and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day)
|
|
147
|
and work_item.id = configuration_change.configurationId
|
|
148
|
and configuration_change.changeId = work_item_change.id
|
|
149
|
and work_item_change.workItemId = work_unit.id
|
|
150
|
and work_unit.iterationId = @iterationId
|
|
151
|
and work_item_change.id = field_change.workItemChangeId
|
|
152
|
and field_change.name = 'status'
|
|
153
|
and field_change.newValue = status.name
|
|
154
|
and status.projectInstanceId = project_instance.id
|
|
155
|
and project_instance.projectId = @projectId
|
|
156
|
and status.classId = status_classification.id
|
|
157
|
and status_classification.superClass = 'CLOSED'
|
|
158
|
group by
|
|
159
|
dataPoint
|
|
160
|
)
|
|
161
|
lookahead
|
|
162
|
left join
|
|
163
|
-- look y days behind (including current)
|
|
164
|
(
|
|
165
|
select
|
|
166
|
days.selected_date as dataPoint,
|
|
167
|
count(distinct work_unit.id) as closedCount
|
|
168
|
from
|
|
169
|
-- all days beteween start and end to fill in the gaps
|
|
170
|
(
|
|
171
|
select
|
|
172
|
*
|
|
173
|
from
|
|
174
|
-- calendar
|
|
175
|
(
|
|
176
|
select
|
|
177
|
adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
|
|
178
|
from
|
|
179
|
(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,
|
|
180
|
(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,
|
|
181
|
(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,
|
|
182
|
(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
|
|
183
|
)
|
|
184
|
v
|
|
185
|
where
|
|
186
|
v.selected_date between @startDateFormatted and @endDateFormatted
|
|
187
|
)
|
|
188
|
days,
|
|
189
|
ppicha.work_item,
|
|
190
|
ppicha.configuration_change,
|
|
191
|
ppicha.work_item_change,
|
|
192
|
ppicha.work_unit,
|
|
193
|
ppicha.field_change,
|
|
194
|
ppicha.status,
|
|
195
|
ppicha.project_instance,
|
|
196
|
ppicha.project,
|
|
197
|
ppicha.status_classification
|
|
198
|
where
|
|
199
|
date_format(work_item.created, '%Y-%m-%d') <= days.selected_date
|
|
200
|
and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day)
|
|
201
|
and work_item.id = configuration_change.configurationId
|
|
202
|
and configuration_change.changeId = work_item_change.id
|
|
203
|
and work_item_change.workItemId = work_unit.id
|
|
204
|
and work_unit.iterationId = @iterationId
|
|
205
|
and work_item_change.id = field_change.workItemChangeId
|
|
206
|
and field_change.name = 'status'
|
|
207
|
and field_change.newValue = status.name
|
|
208
|
and status.projectInstanceId = project_instance.id
|
|
209
|
and project_instance.projectId = @projectId
|
|
210
|
and status.classId = status_classification.id
|
|
211
|
and status_classification.superClass = 'CLOSED'
|
|
212
|
group by
|
|
213
|
dataPoint
|
|
214
|
)
|
|
215
|
lookbehind
|
|
216
|
on lookahead.dataPoint = lookbehind.dataPoint
|
|
217
|
|
|
218
|
union
|
|
219
|
|
|
220
|
select
|
|
221
|
ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint,
|
|
222
|
ifnull(lookbehind.closedCount, 0) as lookbehind,
|
|
223
|
ifnull(lookahead.closedCount, 0) as lookahead
|
|
224
|
from
|
|
225
|
-- look x days ahead (excluding current)
|
|
226
|
(
|
|
227
|
select
|
|
228
|
days.selected_date as dataPoint,
|
|
229
|
count(distinct work_unit.id) as closedCount
|
|
230
|
from
|
|
231
|
-- all days beteween start and end to fill in the gaps
|
|
232
|
(
|
|
233
|
select
|
|
234
|
*
|
|
235
|
from
|
|
236
|
-- calendar
|
|
237
|
(
|
|
238
|
select
|
|
239
|
adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
|
|
240
|
from
|
|
241
|
(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,
|
|
242
|
(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,
|
|
243
|
(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,
|
|
244
|
(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
|
|
245
|
)
|
|
246
|
v
|
|
247
|
where
|
|
248
|
v.selected_date between @startDateFormatted and @endDateFormatted
|
|
249
|
)
|
|
250
|
days,
|
|
251
|
ppicha.work_item,
|
|
252
|
ppicha.configuration_change,
|
|
253
|
ppicha.work_item_change,
|
|
254
|
ppicha.work_unit,
|
|
255
|
ppicha.field_change,
|
|
256
|
ppicha.status,
|
|
257
|
ppicha.project_instance,
|
|
258
|
ppicha.project,
|
|
259
|
ppicha.status_classification
|
|
260
|
where
|
|
261
|
date_format(work_item.created, '%Y-%m-%d') > days.selected_date
|
|
262
|
and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day)
|
|
263
|
and work_item.id = configuration_change.configurationId
|
|
264
|
and configuration_change.changeId = work_item_change.id
|
|
265
|
and work_item_change.workItemId = work_unit.id
|
|
266
|
and work_unit.iterationId = @iterationId
|
|
267
|
and work_item_change.id = field_change.workItemChangeId
|
|
268
|
and field_change.name = 'status'
|
|
269
|
and field_change.newValue = status.name
|
|
270
|
and status.projectInstanceId = project_instance.id
|
|
271
|
and project_instance.projectId = @projectId
|
|
272
|
and status.classId = status_classification.id
|
|
273
|
and status_classification.superClass = 'CLOSED'
|
|
274
|
group by
|
|
275
|
dataPoint
|
|
276
|
)
|
|
277
|
lookahead
|
|
278
|
right join
|
|
279
|
(
|
|
280
|
-- look y days behind (including current)
|
|
281
|
select
|
|
282
|
days.selected_date as dataPoint,
|
|
283
|
count(distinct work_unit.id) as closedCount
|
|
284
|
from
|
|
285
|
-- all days beteween start and end to fill in the gaps
|
|
286
|
(
|
|
287
|
select
|
|
288
|
*
|
|
289
|
from
|
|
290
|
-- calendar
|
|
291
|
(
|
|
292
|
select
|
|
293
|
adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
|
|
294
|
from
|
|
295
|
(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,
|
|
296
|
(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,
|
|
297
|
(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,
|
|
298
|
(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
|
|
299
|
)
|
|
300
|
v
|
|
301
|
where
|
|
302
|
v.selected_date between @startDateFormatted and @endDateFormatted
|
|
303
|
)
|
|
304
|
days,
|
|
305
|
ppicha.work_item,
|
|
306
|
ppicha.configuration_change,
|
|
307
|
ppicha.work_item_change,
|
|
308
|
ppicha.work_unit,
|
|
309
|
ppicha.field_change,
|
|
310
|
ppicha.status,
|
|
311
|
ppicha.project_instance,
|
|
312
|
ppicha.project,
|
|
313
|
ppicha.status_classification
|
|
314
|
where
|
|
315
|
date_format(work_item.created, '%Y-%m-%d') <= days.selected_date
|
|
316
|
and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day)
|
|
317
|
and work_item.id = configuration_change.configurationId
|
|
318
|
and configuration_change.changeId = work_item_change.id
|
|
319
|
and work_item_change.workItemId = work_unit.id
|
|
320
|
and work_unit.iterationId = @iterationId
|
|
321
|
and work_item_change.id = field_change.workItemChangeId
|
|
322
|
and field_change.name = 'status'
|
|
323
|
and field_change.newValue = status.name
|
|
324
|
and status.projectInstanceId = project_instance.id
|
|
325
|
and project_instance.projectId = @projectId
|
|
326
|
and status.classId = status_classification.id
|
|
327
|
and status_classification.superClass = 'CLOSED'
|
|
328
|
group by
|
|
329
|
dataPoint
|
|
330
|
)
|
|
331
|
lookbehind
|
|
332
|
on lookahead.dataPoint = lookbehind.dataPoint
|
|
333
|
)
|
|
334
|
dataPoints
|
|
335
|
on days.selected_date = dataPoints.dataPoint
|
|
336
|
-- row index
|
|
337
|
join
|
|
338
|
(select @curRow := 0) r
|
|
339
|
order by
|
|
340
|
days.selected_date
|
|
341
|
)
|
|
342
|
results
|
|
343
|
-- actual detection
|
|
344
|
-- where
|
|
345
|
-- if(results.dayIndex >= @daysLookbehind and results.lookbehind <= @silenceThreshold and results.lookahead >= @cliffThreshold, true, false) = true
|
