· 7 years ago · Oct 22, 2018, 09:52 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() - 8;
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 #TODO: выкинуть переÑечениÑ
62 UPDATE analytics_cumulative_by_week anal
63
64 #calculate total working time by master (so need to sum() later
65 #join aggregate by salon_id
66 JOIN (
67 SELECT masters_salons.salon_id, SUM(
68 #end_time and start_time reverted to get + values
69 TIME_TO_SEC( TIMEDIFF(masters_full_time.end_time, masters_full_time.start_time) )
70 ) AS total_working_time
71 FROM masters_full_time
72 JOIN masters_salons ON masters_full_time.master_id = masters_salons.master_id
73 WHERE masters_full_time.date >= @dateStart
74 AND masters_full_time.date <= @dateEnd
75 GROUP BY masters_salons.salon_id
76 ) masters_time ON anal.salon_id = masters_time.salon_id
77
78 #get orders time by masters (time per order)
79 JOIN (
80 SELECT orders.salon_id, SUM( TIME_TO_SEC( TIMEDIFF(orders.end_time, orders.start_time) ) )
81 AS total_orders_time
82 FROM orders
83 WHERE orders.start_time >= @dateStart
84 AND orders.end_time <= @dateEnd
85 GROUP BY orders.salon_id
86 ) orders_time ON anal.salon_id = orders_time.salon_id
87
88 #calculate percentage
89 SET anal.masters_load = (SELECT ABS(FLOOR((SUM(orders_time.total_orders_time) / SUM(masters_time.total_working_time)) * 100)) )
90
91 WHERE masters_time.salon_id = anal.salon_id AND orders_time.salon_id = anal.salon_id
92 AND anal.period_start = @dateStart
93 AND anal.period_end = @dateEnd
94 ;
95 #calculate master load END
96
97
98 #calculate LOST clients by PERIOD ---------------------------
99 UPDATE analytics_cumulative_by_week anal
100 JOIN (
101 SELECT salon_id, COUNT(*)
102 AS lost
103 FROM users_salons
104 WHERE users_salons.updated_at >= @dateStart
105 AND users_salons.updated_at <= @dateEnd
106 AND users_salons.filter_by_time = 'lost'
107
108 GROUP BY users_salons.salon_id
109 ) lost_users ON anal.salon_id = lost_users.salon_id
110
111 SET anal.lost_clients_per_week = lost_users.lost
112
113 WHERE lost_users.salon_id = anal.salon_id
114 AND anal.period_start = @dateStart
115 AND anal.period_end = @dateEnd
116 ;
117 #calculate LOST clients by PERIOD
118
119
120 #calculate TOTAL LOST clients ---------------------------------
121 UPDATE analytics_cumulative_by_week anal
122 JOIN (
123 SELECT salon_id, COUNT(*)
124 AS total_lost
125 FROM users_salons
126 WHERE users_salons.filter_by_time = 'lost'
127
128 GROUP BY users_salons.salon_id
129 ) lost_users ON anal.salon_id = lost_users.salon_id
130
131 SET anal.lost_clients_total = lost_users.total_lost
132
133 WHERE lost_users.salon_id = anal.salon_id
134 AND anal.period_start = @dateStart
135 AND anal.period_end = @dateEnd
136 ;
137 #calculate TOTAL LOST clients END
138
139
140 #calculate NEW clients by PERIOD -------------------------------
141 UPDATE analytics_cumulative_by_week anal
142 JOIN (
143 SELECT salon_id, COUNT(*)
144 AS new_clients_count
145 FROM users_salons
146 WHERE users_salons.created_at >= @dateStart
147 AND users_salons.created_at <= @dateEnd
148 AND users_salons.filter_by_time = 'new'
149
150 GROUP BY users_salons.salon_id
151 ) new_users ON anal.salon_id = new_users.salon_id
152
153 SET anal.new_clients_per_week = new_users.new_clients_count
154
155 WHERE new_users.salon_id = anal.salon_id
156 AND anal.period_start = @dateStart
157 AND anal.period_end = @dateEnd
158 ;
159 #calculate NEW clients by PERIOD
160
161
162 #calculate TOTAL NEW clients ------------------------------------
163 UPDATE analytics_cumulative_by_week anal
164 JOIN (
165 SELECT salon_id, COUNT(*)
166 AS new_clients_count
167 FROM users_salons
168 WHERE users_salons.filter_by_time = 'new'
169
170 GROUP BY users_salons.salon_id
171 ) new_users ON anal.salon_id = new_users.salon_id
172
173 SET anal.new_clients_total = new_users.new_clients_count
174
175 WHERE new_users.salon_id = anal.salon_id
176 AND anal.period_start = @dateStart
177 AND anal.period_end = @dateEnd
178 ;
179 #calculate TOTAL NEW clients END
180
181
182 #calculate TOTAL orders per week ------------------------------------
183 UPDATE analytics_cumulative_by_week anal
184 JOIN (
185 SELECT salon_id, COUNT(*)
186 AS total_orders_per_week
187 FROM orders
188 WHERE (orders.cancel_reason IS NULL OR orders.cancel_reason =7)
189 AND orders.start_time >= @dateStart
190 AND orders.start_time <= @dateEnd
191 AND orders.state NOT IN (2,3,5)
192
193 GROUP BY orders.salon_id
194 ) orders_counter ON anal.salon_id = orders_counter.salon_id
195
196 SET anal.orders_total = orders_counter.total_orders_per_week
197
198 WHERE orders_counter.salon_id = anal.salon_id
199 AND anal.period_start = @dateStart
200 AND anal.period_end = @dateEnd
201 ;
202 #calculate TOTAL orders per week END
203
204
205 #calculate salon's rating START ------------------------
206 #calculated ranking: salon's rating * count of feedback = place in ranking
207 UPDATE analytics_cumulative_by_week anal
208
209 JOIN (
210 SELECT @rownum := @rownum + 1 AS position, computed_ranking.salon_id
211 FROM (
212 #TODO: optimize SELECT
213 SELECT anal.salon_id,
214 salons_rating.rating * feedback.feedback_count AS rank
215 FROM analytics_cumulative_by_week anal
216
217 #calculate total salon's feedback
218 JOIN (
219 SELECT salon_id, COUNT(*) AS feedback_count
220 FROM salons_media_comments
221 WHERE deleted_at IS NULL
222 GROUP BY salon_id
223 ) feedback ON anal.salon_id = feedback.salon_id
224
225 #get rating
226 JOIN (
227 SELECT id, rating
228 FROM salons
229 WHERE deleted_at IS NULL
230 ) salons_rating ON salons_rating.id = anal.salon_id
231
232 #init variable rownum
233 JOIN (
234 SELECT @rownum := 0
235 ) r
236
237 WHERE salons_rating.id = anal.salon_id
238 AND anal.period_start = @dateStart
239 AND anal.period_end = @dateEnd
240
241 ORDER BY rank DESC
242 ) AS computed_ranking
243
244 ) computed ON computed.salon_id = anal.salon_id
245
246 SET anal.salons_rating = computed.position
247
248 WHERE anal.salon_id = computed.salon_id
249 AND anal.period_start = @dateStart
250 AND anal.period_end = @dateEnd
251 ;
252 #calculate salon's rating END
253
254
255 #calculate preorders per period START -----------------------
256 UPDATE analytics_cumulative_by_week anal
257
258 JOIN (
259 SELECT salon_id, COUNT(*) AS total_preorders_per_week
260 FROM preorders_funnel
261 WHERE preorders_funnel.salon_id
262 AND created_at >= @dateStart
263 AND created_at <= @dateEnd
264
265 GROUP BY salon_id
266 ) preorders_compute ON preorders_compute.salon_id = anal.salon_id
267
268 SET anal.preorders_mobile = preorders_compute.total_preorders_per_week
269
270 WHERE anal.salon_id = preorders_compute.salon_id
271 AND anal.period_start = @dateStart
272 AND anal.period_end = @dateEnd
273 ;
274 #calculate preorders per period END
275
276
277 #calculate preorders that became orders per period START -----------------------
278 UPDATE analytics_cumulative_by_week anal
279 JOIN (
280 SELECT orders.salon_id, COUNT(*)
281 AS mobile_orders_per_week
282 FROM orders
283 JOIN users_salons ON users_salons.user_id = orders.user_id
284 WHERE (orders.cancel_reason IS NULL OR orders.cancel_reason =7)
285 #TODO: check if we change cancel_reason
286 AND users_salons.is_from_mobile = 1
287 AND orders.start_time >= @dateStart
288 AND orders.start_time <= @dateEnd
289 AND orders.state NOT IN (2,3,5,6)
290
291 GROUP BY orders.salon_id
292 ) orders_counter ON anal.salon_id = orders_counter.salon_id
293
294 SET anal.orders_mobile = orders_counter.mobile_orders_per_week
295
296 WHERE orders_counter.salon_id = anal.salon_id
297 AND anal.period_start = @dateStart
298 AND anal.period_end = @dateEnd
299 ;
300 #calculate preorders that became orders per period END
301
302 END$$
303
304
305DELIMITER ;
306
307CALL fill_cumulative_analytics_table;