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