· 8 years ago · Nov 30, 2017, 10:46 AM
1
2#Creating unique index for ON DUPLICATE UPDATE
3#ALTER TABLE analytics_cumulative_by_week
4# ADD UNIQUE salon_date_index (salon_id, period_start, period_end);
5
6
7
8#procedure START
9DELIMITER $$
10
11DROP PROCEDURE IF EXISTS fill_cumulative_analytics_table$$
12
13#calculate previous week data if ran on Mondays
14CREATE PROCEDURE `fill_cumulative_analytics_table`()
15 BEGIN
16
17 # SET period dates (week)
18 SET @dateStart = CURDATE() - 7;
19 SET @dateEnd = CURDATE() - 1;
20
21
22 #INIT WITH SALONS -----------------------------------
23 INSERT INTO analytics_cumulative_by_week(
24 salon_id,
25 period_start,
26 period_end,
27 viewed_in_ios
28 )
29 SELECT
30 `id` AS salon_id,
31 @dateStart AS period_start,
32 @dateEnd AS period_end,
33
34 #ROUND((RAND() * (max-min))+min)
35 ROUND((RAND() * (200)) + 100) AS viewed_in_ios
36 FROM `salons`
37 WHERE `deleted_at` IS NULL
38 ON DUPLICATE KEY UPDATE salon_id = salon_id
39 ;
40
41
42 #calculate average bill --------------------------------
43 UPDATE analytics_cumulative_by_week a
44 JOIN (
45 SELECT ROUND(AVG(price)) as avg_price, salon_id
46 FROM orders
47 WHERE start_time >= @dateStart
48 AND start_time <= @dateEnd
49 AND cancel_reason IS NULL
50 AND price > 0
51 GROUP BY salon_id
52 ) o ON o.salon_id = a.salon_id
53 SET a.avg_bill = o.avg_price
54 WHERE o.salon_id = a.salon_id
55 AND period_start = @dateStart
56 AND period_end = @dateEnd
57 ;
58
59
60 #calculate master load START --------------------------------------
61 UPDATE analytics_cumulative_by_week anal
62
63 #calculate total working time by master (so need to sum() later
64 #join aggregate by salon_id
65 JOIN (
66 SELECT masters_salons.salon_id, SUM(
67 #end_time and start_time reverted to get + values
68 TIME_TO_SEC( TIMEDIFF(masters_full_time.end_time, masters_full_time.start_time) )
69 ) AS total_working_time
70 FROM masters_full_time
71 JOIN masters_salons ON masters_full_time.master_id = masters_salons.master_id
72 WHERE masters_full_time.date >= @dateStart
73 AND masters_full_time.date <= @dateEnd
74 GROUP BY masters_salons.salon_id
75 ) masters_time ON anal.salon_id = masters_time.salon_id
76
77 #get orders time by masters (time per order)
78 JOIN (
79 SELECT orders.salon_id, SUM( TIME_TO_SEC( TIMEDIFF(orders.end_time, orders.start_time) ) )
80 AS total_orders_time
81 FROM orders
82 WHERE orders.start_time >= @dateStart
83 AND orders.end_time <= @dateEnd
84 GROUP BY orders.salon_id
85 ) orders_time ON anal.salon_id = orders_time.salon_id
86
87 #calculate percentage
88 SET anal.masters_load = (SELECT ABS(FLOOR((SUM(orders_time.total_orders_time) / SUM(masters_time.total_working_time)) * 100)) )
89
90 WHERE masters_time.salon_id = anal.salon_id AND orders_time.salon_id = anal.salon_id
91 AND anal.period_start = @dateStart
92 AND anal.period_end = @dateEnd
93 ;
94 #calculate master load END
95
96
97 #calculate LOST clients by PERIOD ---------------------------
98 UPDATE analytics_cumulative_by_week anal
99 JOIN (
100 SELECT salon_id, COUNT(*)
101 AS lost
102 FROM users_salons
103 WHERE users_salons.updated_at >= @dateStart
104 AND users_salons.updated_at <= @dateEnd
105 AND users_salons.filter_by_time = 'lost'
106
107 GROUP BY users_salons.salon_id
108 ) lost_users ON anal.salon_id = lost_users.salon_id
109
110 SET anal.lost_clients_per_week = lost_users.lost
111
112 WHERE lost_users.salon_id = anal.salon_id
113 AND anal.period_start = @dateStart
114 AND anal.period_end = @dateEnd
115 ;
116 #calculate LOST clients by PERIOD
117
118
119 #calculate TOTAL LOST clients ---------------------------------
120 UPDATE analytics_cumulative_by_week anal
121 JOIN (
122 SELECT salon_id, COUNT(*)
123 AS total_lost
124 FROM users_salons
125 WHERE users_salons.filter_by_time = 'lost'
126
127 GROUP BY users_salons.salon_id
128 ) lost_users ON anal.salon_id = lost_users.salon_id
129
130 SET anal.lost_clients_total = lost_users.total_lost
131
132 WHERE lost_users.salon_id = anal.salon_id
133 AND anal.period_start = @dateStart
134 AND anal.period_end = @dateEnd
135 ;
136 #calculate TOTAL LOST clients END
137
138
139 #calculate NEW clients by PERIOD -------------------------------
140 UPDATE analytics_cumulative_by_week anal
141 JOIN (
142 SELECT salon_id, COUNT(*)
143 AS new_clients_count
144 FROM users_salons
145 WHERE users_salons.created_at >= @dateStart
146 AND users_salons.created_at <= @dateEnd
147 AND users_salons.filter_by_time = 'new'
148
149 GROUP BY users_salons.salon_id
150 ) new_users ON anal.salon_id = new_users.salon_id
151
152 SET anal.new_clients_per_week = new_users.new_clients_count
153
154 WHERE new_users.salon_id = anal.salon_id
155 AND anal.period_start = @dateStart
156 AND anal.period_end = @dateEnd
157 ;
158 #calculate NEW clients by PERIOD
159
160
161 #calculate TOTAL NEW clients ------------------------------------
162 UPDATE analytics_cumulative_by_week anal
163 JOIN (
164 SELECT salon_id, COUNT(*)
165 AS new_clients_count
166 FROM users_salons
167 WHERE users_salons.filter_by_time = 'new'
168
169 GROUP BY users_salons.salon_id
170 ) new_users ON anal.salon_id = new_users.salon_id
171
172 SET anal.new_clients_total = new_users.new_clients_count
173
174 WHERE new_users.salon_id = anal.salon_id
175 AND anal.period_start = @dateStart
176 AND anal.period_end = @dateEnd
177 ;
178 #calculate TOTAL NEW clients END
179
180
181 #calculate TOTAL orders per week ------------------------------------
182 UPDATE analytics_cumulative_by_week anal
183 JOIN (
184 SELECT salon_id, COUNT(*)
185 AS total_orders_per_week
186 FROM orders
187 WHERE orders.cancel_reason IS NULL
188 AND orders.start_time >= @dateStart
189 AND orders.start_time <= @dateEnd
190 AND orders.state NOT IN (2,3,5)
191
192 GROUP BY orders.salon_id
193 ) orders_counter ON anal.salon_id = orders_counter.salon_id
194
195 SET anal.orders_total = orders_counter.total_orders_per_week
196
197 WHERE orders_counter.salon_id = anal.salon_id
198 AND anal.period_start = @dateStart
199 AND anal.period_end = @dateEnd
200 ;
201 #calculate TOTAL orders per week END
202
203
204 #calculate salon's rating START ------------------------
205 #calculated ranking: salon's rating * count of feedback = place in ranking
206 UPDATE analytics_cumulative_by_week anal
207
208 JOIN (
209 SELECT @rownum := @rownum + 1 AS position, computed_ranking.salon_id
210 FROM (
211 #TODO: optimize SELECT
212 SELECT anal.salon_id,
213 salons_rating.rating * feedback.feedback_count AS rank
214 FROM analytics_cumulative_by_week anal
215
216 #calculate total salon's feedback
217 JOIN (
218 SELECT salon_id, COUNT(*) AS feedback_count
219 FROM salons_media_comments
220 WHERE deleted_at IS NULL
221 GROUP BY salon_id
222 ) feedback ON anal.salon_id = feedback.salon_id
223
224 #get rating
225 JOIN (
226 SELECT id, rating
227 FROM salons
228 WHERE deleted_at IS NULL
229 ) salons_rating ON salons_rating.id = anal.salon_id
230
231 #init variable rownum
232 JOIN (
233 SELECT @rownum := 0
234 ) r
235
236 WHERE salons_rating.id = anal.salon_id
237 AND anal.period_start = @dateStart
238 AND anal.period_end = @dateEnd
239
240 ORDER BY rank DESC
241 ) AS computed_ranking
242
243 ) computed ON computed.salon_id = anal.salon_id
244
245 SET anal.salons_rating = computed.position
246
247 WHERE anal.salon_id = computed.salon_id
248 AND anal.period_start = @dateStart
249 AND anal.period_end = @dateEnd
250 ;
251 #calculate salon's rating END
252
253
254 #calculate preorders per period START -----------------------
255 UPDATE analytics_cumulative_by_week anal
256
257 JOIN (
258 SELECT salon_id, COUNT(*) AS total_preorders_per_week
259 FROM preorders
260 WHERE preorders.salon_id
261 AND created_at >= @dateStart
262 AND created_at <= @dateEnd
263
264 GROUP BY salon_id
265 ) preorders_compute ON preorders_compute.salon_id = anal.salon_id
266
267 SET anal.preorders_mobile = preorders_compute.total_preorders_per_week
268
269 WHERE anal.salon_id = preorders_compute.salon_id
270 AND anal.period_start = @dateStart
271 AND anal.period_end = @dateEnd
272 ;
273 #calculate preorders per period END
274
275
276 #calculate preorders that became orders per period START -----------------------
277 UPDATE analytics_cumulative_by_week anal
278 JOIN (
279 SELECT orders.salon_id, COUNT(*)
280 AS mobile_orders_per_week
281 FROM orders
282 JOIN users_salons ON users_salons.user_id = orders.user_id
283 WHERE orders.cancel_reason IS NULL
284 AND users_salons.is_from_mobile = 1
285 AND orders.start_time >= @dateStart
286 AND orders.start_time <= @dateEnd
287 AND orders.state NOT IN (2,3,5,6)
288
289 GROUP BY orders.salon_id
290 ) orders_counter ON anal.salon_id = orders_counter.salon_id
291
292 SET anal.orders_mobile = orders_counter.mobile_orders_per_week
293
294 WHERE orders_counter.salon_id = anal.salon_id
295 AND anal.period_start = @dateStart
296 AND anal.period_end = @dateEnd
297 ;
298 #calculate preorders that became orders per period END
299
300 END$$
301
302
303DELIMITER ;
304
305CALL fill_cumulative_analytics_table;