Projekt

Obecné

Profil

Collective Procrastination » Collective_Procrastination_(for_iteration)_-_SQL.sql

Pícha Petr, 05.04.2018 14:29

 
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
(1-1/3)