· 8 years ago · May 20, 2018, 09:34 AM
1DROP TABLE IF EXISTS NewRadioClicks;
2CREATE TABLE `NewRadioClicks` (
3 `Person_id` INT (11) NOT NULL,
4 `Question_id` INT (11) NOT NULL,
5 `ClickCount` INT (11) NOT NULL,
6 `WrongClicks` INT (11) NOT NULL,
7 `LastEndValue` INT (11) NOT NULL,
8 `TargetValue` INT (11) NOT NULL,
9 `EndValueCorrect` BOOLEAN NOT NULL,
10 `LastEndTime` BIGINT (20) NOT NULL,
11 `TotalInteractionTime` INT (20),
12 `IndexOfDifficulty` INT (11) NOT NULL
13);
14
15SET @rank=0;
16DROP TABLE IF EXISTS tmp_LastClickValues_1526803507;
17CREATE TABLE tmp_LastClickValues_1526803507 (
18 SELECT
19 @rank:=@rank+1 AS rank,
20 data.Person_id,
21 data.Question_id,
22 data.LastEndValue,
23 data.LastEndTime
24 FROM (
25 SELECT
26 MAXID,
27 Person_id,
28 Question_id,
29 EndValue as LastEndValue,
30 LET as LastEndTime
31 FROM RadioClicks as lrc
32 LEFT JOIN (
33 SELECT
34 MAX(Id) as MAXID,
35 MAX(EndT) as LET
36 FROM RadioClicks
37 GROUP BY Person_id, Question_id
38 ) as LETtable ON LETtable.MAXID = lrc.Id
39 WHERE
40 LET IS NOT NULL AND
41 ClickType != "Other"
42 ORDER BY Person_id, CONVERT(SUBSTR(Question_id, 2), INTEGER)
43 ) as data
44 ORDER BY
45 data.MAXID ASC
46);
47
48INSERT INTO NewRadioClicks
49(
50 SELECT
51 nrc.Person_id,
52 CONVERT(SUBSTR(nrc.Question_id, 2), INTEGER) - 2,
53 COUNT(CONCAT(nrc.Person_id, nrc.Question_id)) as Clicks,
54 wc.WrongClicks,
55 LAST.LastEndValue,
56 nrc.Target,
57 IF (LAST.LastEndValue = nrc.Target, 1, 0),
58 LAST.LastEndTime,
59 LAST.TimePassed,
60 nrc.IndexOfDifficulty
61 FROM RadioClicks as nrc
62 LEFT JOIN (
63 SELECT
64 wrc.Person_id,
65 wrc.Question_id,
66 COUNT(wct.Id) as WrongClicks
67 FROM RadioClicks as wrc
68 LEFT JOIN (
69 SELECT
70 Id,
71 Question_id,
72 Person_id
73 FROM RadioClicks
74 WHERE
75 ClickType IN ("Missclick", "Missdrag") OR
76 EndValue != Target OR
77 CorrectResult = 0 AND
78 ClickType != "Other"
79 ) as wct ON wrc.Id = wct.Id
80 WHERE wrc.ClickType != "Other"
81 GROUP BY wrc.Person_id, wrc.Question_id
82 ORDER BY Person_id ASC, Question_id ASC
83 ) as wc ON (nrc.Person_id = wc.Person_id AND nrc.Question_id = wc.Question_id)
84 LEFT JOIN (
85 SELECT
86 LCV1.*,
87 COUNT(LCV2.rank) as BOGUSFIELD, -- We need this row to include rows that don't exist in our join
88 -- MySQL for one or another reason removes NULL rows on a left join
89 -- if and only if it concerns a self join (there's a bug report pending)
90 -- This field however will then account as "0" instead of NULL,
91 -- Resulting in the missing rows being added
92 IF (LCV2.LastEndTime IS NOT NULL, LCV1.LastEndTime - LCV2.LastEndTime, NULL) as TimePassed
93 FROM tmp_LastClickValues_1526803507 AS LCV1
94 LEFT JOIN tmp_LastClickValues_1526803507 AS LCV2
95 ON LCV1.rank = LCV2.rank + 1 AND
96 LCV1.Person_id = LCV2.Person_id
97 GROUP BY LCV1.Rank
98 ) as LAST ON (nrc.Person_id = LAST.Person_id AND nrc.Question_id = LAST.Question_id)
99 WHERE
100 nrc.ClickType != "Other" AND
101 nrc.Question_id NOT LIKE "%BOGUS%"
102 GROUP BY nrc.Person_id, nrc.Question_id
103 ORDER BY nrc.Person_id ASC, CONVERT(SUBSTR(nrc.Question_id, 2), INTEGER) ASC
104);
105
106
107DROP TABLE tmp_LastClickValues_1526803507;
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124DROP TABLE IF EXISTS NewSliderClicks;
125CREATE TABLE `NewSliderClicks` (
126 `Person_id` INT (11) NOT NULL,
127 `Question_id` INT (11) NOT NULL,
128 `ClickCount` INT (11) NOT NULL,
129 `WrongClicks` INT (11) NOT NULL,
130 `LastEndValue` INT (11) NOT NULL,
131 `TargetValue` INT (11) NOT NULL,
132 `EndValueCorrect` BOOLEAN NOT NULL,
133 `LastEndTime` BIGINT (20) NOT NULL,
134 `TotalInteractionTime` INT (20),
135 `IndexOfDifficultyFitts` INT (11) NOT NULL,
136 `IndexOfDifficultySteering` INT (11) NOT NULL,
137 `IndexOfDifficultyCombined` INT (11) NOT NULL
138);
139
140
141
142-- Create a mapping for the Indexes of difficulty
143DROP TABLE IF EXISTS tmp_IndexesOfDifficulty_1526807959;
144CREATE TABLE tmp_IndexesOfDifficulty_1526807959 (
145 `IndexOfDifficultyFitts` INT (11) NOT NULL,
146 `IndexOfDifficultySteering` INT (11) NOT NULL,
147 `IndexOfDifficultyCombined` INT (11) NOT NULL
148);
149
150INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (0, 0, 0);
151INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (0, 1, 1);
152INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (0, 2, 2);
153INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (0, 3, 3);
154INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (1, 0, 4);
155INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (1, 1, 5);
156INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (1, 2, 6);
157INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (1, 3, 7);
158INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (2, 0, 8);
159INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (2, 1, 9);
160INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (2, 2, 10);
161INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (2, 3, 11);
162INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (3, 0, 12);
163INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (3, 1, 13);
164INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (3, 2, 14);
165INSERT INTO `tmp_IndexesOfDifficulty_1526807959` VALUES (3, 3, 15);
166
167
168-- Create a table with the LastEndValues and LastEndTimes
169-- This way we can use this information to calculate TotalInteractionTime
170SET @rank=0;
171DROP TABLE IF EXISTS tmp_LastClickValues_1526807668;
172CREATE TABLE tmp_LastClickValues_1526807668 (
173 SELECT
174 @rank:=@rank+1 AS rank,
175 data.Person_id,
176 data.Question_id,
177 data.LastEndValue,
178 data.LastEndTime
179 FROM (
180 SELECT
181 MAXID,
182 Person_id,
183 Question_id,
184 EndValue as LastEndValue,
185 LET as LastEndTime
186 FROM SliderClicks as lsc
187 LEFT JOIN (
188 SELECT
189 MAX(Id) as MAXID,
190 MAX(EndT) as LET
191 FROM SliderClicks
192 GROUP BY Person_id, Question_id
193 ) as LETtable ON LETtable.MAXID = lsc.Id
194 WHERE
195 LET IS NOT NULL AND
196 ClickType != "Other"
197 ORDER BY Person_id, CONVERT(SUBSTR(Question_id, 2), INTEGER)
198 ) as data
199 ORDER BY
200 data.MAXID ASC
201);
202
203
204INSERT INTO NewSliderClicks
205(
206 SELECT
207 nsc.Person_id,
208 CONVERT(SUBSTR(nsc.Question_id, 2), INTEGER) - 2,
209 COUNT(CONCAT(nsc.Person_id, nsc.Question_id)) as Clicks,
210 wc.WrongClicks,
211 LAST.LastEndValue,
212 nsc.Target,
213 IF (LAST.LastEndValue = nsc.Target, 1, 0),
214 LAST.LastEndTime,
215 LAST.TimePassed,
216 nsc.IndexOfDifficulty_1,
217 nsc.IndexOfDifficulty_2,
218 IDS.IndexOfDifficultyCombined
219 FROM SliderClicks as nsc
220 LEFT JOIN (
221 SELECT
222 wsc.Person_id,
223 wsc.Question_id,
224 COUNT(wct.Id) as WrongClicks
225 FROM SliderClicks as wsc
226 LEFT JOIN (
227 SELECT
228 Id,
229 Question_id,
230 Person_id
231 FROM SliderClicks
232 WHERE
233 ClickType IN ("Missclick", "Missdrag") OR
234 EndValue != Target OR
235 CorrectResult = 0 AND
236 ClickType != "Other"
237 ) as wct ON wsc.Id = wct.Id
238 WHERE wsc.ClickType != "Other"
239 GROUP BY wsc.Person_id, wsc.Question_id
240 ORDER BY Person_id ASC, Question_id ASC
241 ) as wc
242 ON (nsc.Person_id = wc.Person_id AND nsc.Question_id = wc.Question_id)
243
244
245 LEFT JOIN (
246 SELECT
247 LCV1.*,
248 COUNT(LCV2.rank) as BOGUSFIELD, -- We need this row to include rows that don't exist in our join
249 -- MySQL for one or another reason removes NULL rows on a left join
250 -- if and only if it concerns a self join (there's a bug report pending)
251 -- This field however will then account as "0" instead of NULL,
252 -- Resulting in the missing rows being added
253 IF (LCV2.LastEndTime IS NOT NULL, LCV1.LastEndTime - LCV2.LastEndTime, NULL) as TimePassed
254 FROM tmp_LastClickValues_1526807668 AS LCV1
255 LEFT JOIN tmp_LastClickValues_1526807668 AS LCV2
256 ON LCV1.rank = LCV2.rank + 1 AND
257 LCV1.Person_id = LCV2.Person_id
258 GROUP BY LCV1.Rank
259 ) as LAST
260 ON (nsc.Person_id = LAST.Person_id AND nsc.Question_id = LAST.Question_id)
261
262
263 LEFT JOIN
264 `tmp_IndexesOfDifficulty_1526807959` AS IDS
265 ON (nsc.IndexOfDifficulty_1 = IDS.IndexOfDifficultyFitts AND nsc.IndexOfDifficulty_2 = IDS.IndexOfDifficultySteering)
266
267
268 WHERE
269 nsc.ClickType != "Other" AND
270 nsc.Question_id NOT LIKE "%BOGUS%"
271 GROUP BY nsc.Person_id, nsc.Question_id
272 ORDER BY nsc.Person_id ASC, CONVERT(SUBSTR(nsc.Question_id, 2), INTEGER) ASC
273);
274
275
276DROP TABLE tmp_LastClickValues_1526807668;
277DROP TABLE tmp_IndexesOfDifficulty_1526807959;