]> Raphaël G. Git Repositories - airbundle/blob - Repository/SessionRepository.php.complete.sql
Reduce connected period from 4 weeks to 3 weeks.
[airbundle] / Repository / SessionRepository.php.complete.sql
1 SELECT f.*
2 FROM (
3 # sql request
4 SELECT e.id, e.ls_score, e.l_score, e.g_score, e.ls_previous, e.l_previous, e.g_previous, e.created, e.user_id, e.premium, e.hotspot,
5 MAX(gu.group_id) AS group_id
6 FROM (
7 # Select all applications with other user scores from global excluding canceled applications (in time) and locked sessions
8 SELECT
9 d.id,
10 d.session_id,
11 d.session_date,
12 d.user_id,
13 d.ls_count,
14 d.ls_score,
15 d.ls_tr_ratio,
16 d.ls_temp,
17 d.ls_pn_ratio,
18 d.ls_previous,
19 d.l_count,
20 d.l_score,
21 d.l_tr_ratio,
22 d.l_temp,
23 d.l_pn_ratio,
24 d.l_previous,
25 d.g_count,
26 d.g_score,
27 d.g_tr_ratio,
28 d.g_temp,
29 d.g_pn_ratio,
30 d.g_previous,
31 # Compute count, score, tr_ratio, temp and pn_ratio for global
32 COUNT(a5.id) AS o_count,
33 SUM(IF(a5.id IS NOT NULL, 1/ABS(DATEDIFF(d.session_date, ADDDATE(s5.date, INTERVAL IF(s5.slot_id = 4, 1, 0) DAY))), 0)) AS o_score,
34 AVG(IF(a5.id IS NOT NULL AND s5.temperature IS NOT NULL AND s5.rainfall IS NOT NULL, s5.temperature/(1+s5.rainfall), NULL)) AS o_tr_ratio,
35 AVG(IF(a5.id IS NOT NULL AND s5.temperature IS NOT NULL, s5.temperature, NULL)) AS o_temp,
36 (SUM(IF(a5.id IS NOT NULL AND s5.premium = 1, 1, 0))+1)/(SUM(IF(a5.id IS NOT NULL AND s5.premium = 0, 1, 0))+1) AS o_pn_ratio,
37 MAX(IF(a5.id IS NOT NULL, s5.date, NULL)) AS o_previous,
38 d.remaining,
39 d.premium,
40 d.hotspot,
41 d.created
42 FROM (
43 # Select all applications with scores from global excluding canceled applications (in time) and locked sessions
44 SELECT
45 c.id,
46 c.session_id,
47 c.session_date,
48 c.user_id,
49 c.ls_count,
50 c.ls_score,
51 c.ls_tr_ratio,
52 c.ls_temp,
53 c.ls_pn_ratio,
54 c.ls_previous,
55 c.l_count,
56 c.l_score,
57 c.l_tr_ratio,
58 c.l_temp,
59 c.l_pn_ratio,
60 c.l_previous,
61 # Compute count, score, tr_ratio, temp and pn_ratio for global
62 COUNT(a4.id) AS g_count,
63 SUM(IF(a4.id IS NOT NULL, 1/ABS(DATEDIFF(c.session_date, ADDDATE(s4.date, INTERVAL IF(s4.slot_id = 4, 1, 0) DAY))), 0)) AS g_score,
64 AVG(IF(a4.id IS NOT NULL AND s4.temperature IS NOT NULL AND s4.rainfall IS NOT NULL, s4.temperature/(1+s4.rainfall), NULL)) AS g_tr_ratio,
65 AVG(IF(a4.id IS NOT NULL AND s4.temperature IS NOT NULL, s4.temperature, NULL)) AS g_temp,
66 (SUM(IF(a4.id IS NOT NULL AND s4.premium = 1, 1, 0))+1)/(SUM(IF(a4.id IS NOT NULL AND s4.premium = 0, 1, 0))+1) AS g_pn_ratio,
67 #MIN(IF(a4.id IS NOT NULL, DATEDIFF(c.session_date, ADDDATE(s4.date, INTERVAL IF(s4.slot_id = 4, 1, 0) DAY)), NULL)) AS g_previous,
68 MIN(IF(a4.id IS NOT NULL, DATEDIFF(c.session_date, s4.date), NULL)) AS g_previous,
69 c.remaining,
70 c.premium,
71 c.hotspot,
72 c.created
73 FROM (
74 # Select all applications with scores from same location excluding canceled applications (in time) and locked sessions
75 SELECT
76 b.id,
77 b.session_id,
78 b.session_date,
79 /*remonter location_id ici, on veut prendre la température pour la même location + slot_id IN (2, 3)*/
80 b.user_id,
81 b.ls_count,
82 b.ls_score,
83 b.ls_tr_ratio,
84 b.ls_temp,
85 b.ls_pn_ratio,
86 b.ls_previous,
87 # Compute count, score, tr_ratio, temp and pn_ratio for same location
88 COUNT(a3.id) AS l_count,
89 SUM(IF(a3.id IS NOT NULL, 1/ABS(DATEDIFF(b.session_date, ADDDATE(s3.date, INTERVAL IF(s3.slot_id = 4, 1, 0) DAY))), 0)) AS l_score,
90 AVG(IF(a3.id IS NOT NULL AND s3.temperature IS NOT NULL AND s3.rainfall IS NOT NULL, s3.temperature/(1+s3.rainfall), NULL)) AS l_tr_ratio,
91 AVG(IF(a3.id IS NOT NULL AND s3.temperature IS NOT NULL, s3.temperature, NULL)) AS l_temp,
92 (SUM(IF(a3.id IS NOT NULL AND s3.premium = 1, 1, 0))+1)/(SUM(IF(a3.id IS NOT NULL AND s3.premium = 0, 1, 0))+1) AS l_pn_ratio,
93 #MIN(IF(a3.id IS NOT NULL, DATEDIFF(b.session_date, ADDDATE(s3.date, INTERVAL IF(s3.slot_id = 4, 1, 0) DAY)), NULL)) AS l_previous,
94 MIN(IF(a3.id IS NOT NULL, DATEDIFF(b.session_date, s3.date), NULL)) AS l_previous,
95 /*#TODO: calculer les délais guest|regular ici ou plus haut et les remonter (au denier cran c'est mieux pour limiter la taille de la temp table)*/
96 b.remaining,
97 b.premium,
98 b.hotspot,
99 b.created
100 FROM (
101 # Select all applications with scores from same location+slot excluding canceled applications (in time) and locked sessions
102 SELECT
103 a.id,
104 s.id AS session_id,
105 ADDDATE(s.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY) AS session_date,
106 s.location_id,
107 a.user_id,
108 # Compute count, score, tr_ratio, temp and pn_ratio for same location and slot
109 COUNT(a2.id) AS ls_count,
110 SUM(IF(a2.id IS NOT NULL, 1/ABS(DATEDIFF(s.date, ADDDATE(s2.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY))), 0)) AS ls_score,
111 AVG(IF(a2.id IS NOT NULL AND s2.temperature IS NOT NULL AND s2.rainfall IS NOT NULL, s2.temperature/(1+s2.rainfall), NULL)) AS ls_tr_ratio,
112 AVG(IF(a2.id IS NOT NULL AND s2.temperature IS NOT NULL, s2.temperature, NULL)) AS ls_temp,
113 (SUM(IF(a2.id IS NOT NULL AND s2.premium = 1, 1, 0))+1)/(SUM(IF(a2.id IS NOT NULL AND s2.premium = 0, 1, 0))+1) AS ls_pn_ratio,
114 #MIN(IF(a2.id IS NOT NULL, DATEDIFF(s.date, ADDDATE(s2.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY)), NULL)) AS ls_previous,
115 MIN(IF(a2.id IS NOT NULL, DATEDIFF(s.date, s2.date), NULL)) AS ls_previous,
116 /*#TODO: calculer les délais guest|regular ici ou plus haut et les remonter (au denier cran c'est mieux pour limiter la taille de la temp table)*/
117 TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s2.slot_id = 4, 1, 0) DAY), NOW()) AS remaining,
118 s.premium,
119 l.hotspot,
120 a.created
121 FROM sessions AS s
122 JOIN locations AS l ON (l.id = s.location_id)
123 JOIN applications AS a ON (a.session_id = s.id AND a.canceled IS NULL)
124 LEFT JOIN sessions AS s2 ON (s2.id != s.id AND s2.location_id = s.location_id AND s2.slot_id = s.slot_id AND s2.application_id IS NOT NULL AND s2.locked IS NULL)
125 LEFT JOIN applications AS a2 ON (a2.id = s2.application_id AND a2.user_id = a.user_id AND (a2.canceled IS NULL OR TIMESTAMPDIFF(DAY, a2.canceled, ADDDATE(ADDTIME(s2.date, s2.begin), INTERVAL IF(s2.slot_id = 4, 1, 0) DAY)) < 1))
126 WHERE s.id = 12
127 GROUP BY a.id
128 ORDER BY NULL
129 LIMIT 0, 1000000
130 ) AS b
131 LEFT JOIN sessions AS s3 ON (s3.id != b.session_id AND s3.location_id = b.location_id AND s3.application_id IS NOT NULL AND s3.locked IS NULL)
132 LEFT JOIN applications AS a3 ON (a3.id = s3.application_id AND a3.user_id = b.user_id AND (a3.canceled IS NULL OR TIMESTAMPDIFF(DAY, a3.canceled, ADDDATE(ADDTIME(s3.date, s3.begin), INTERVAL IF(s3.slot_id = 4, 1, 0) DAY)) < 1))
133 GROUP BY b.id
134 ORDER BY NULL
135 LIMIT 0, 1000000
136 ) AS c
137 LEFT JOIN sessions AS s4 ON (s4.id != c.session_id AND s4.application_id IS NOT NULL AND s4.locked IS NULL)
138 LEFT JOIN applications AS a4 ON (a4.id = s4.application_id AND a4.user_id = c.user_id AND (a4.canceled IS NULL OR TIMESTAMPDIFF(DAY, a4.canceled, ADDDATE(ADDTIME(s4.date, s4.begin), INTERVAL IF(s4.slot_id = 4, 1, 0) DAY)) < 1))
139 GROUP BY c.id
140 ORDER BY NULL
141 LIMIT 0, 1000000
142 ) AS d
143 LEFT JOIN sessions AS s5 ON (s5.id != d.session_id AND s5.application_id IS NOT NULL AND s5.locked IS NULL)
144 LEFT JOIN applications AS a5 ON (a5.id = s5.application_id AND a5.user_id != d.user_id AND (a5.canceled IS NULL OR TIMESTAMPDIFF(DAY, a5.canceled, ADDDATE(ADDTIME(s5.date, s5.begin), INTERVAL IF(s5.slot_id = 4, 1, 0) DAY)) < 1))
145 GROUP BY d.id
146 ORDER BY NULL
147 LIMIT 0, 1000000
148 ) AS e
149 LEFT JOIN groups_users AS gu ON (gu.user_id = e.user_id)
150 GROUP BY e.id
151 ) AS f
152 /*TODO: vérifier comment se comporte o_tr_ratio quand on a zéro réservation ^_^ à l'initialisation au hasard */
153 WHERE
154 IF(f.group_id <= 2 AND f.l_previous <= 30, f.remaining <= SEC_TO_TIME(2*24*3600), 1) AND
155 IF(f.group_id <= 3 AND f.premium = 1 AND f.hotspot = 1, f.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
156 IF(f.group_id <= 4 AND f.l_count <= 5, f.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
157 IF(f.group_id <= 4 AND f.l_pn_ratio >= 1, f.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
158 IF(f.group_id <= 4 AND f.l_tr_ratio >= (f.o_tr_ratio + 5), f.remaining <= SEC_TO_TIME(3*24*3600), 1)
159 ORDER BY f.l_score ASC, f.g_score ASC, f.created ASC, f.user_id ASC