]> Raphaƫl G. Git Repositories - airbundle/blob - Repository/SessionRepository.php.mini.sql
Add snippet
[airbundle] / Repository / SessionRepository.php.mini.sql
1 SELECT e.id, e.l_score AS score
2 FROM (
3 SELECT
4 d.id,
5 d.user_id,
6 d.l_count,
7 d.l_score,
8 d.l_tr_ratio,
9 d.l_pn_ratio,
10 d.l_previous,
11 d.g_score,
12 d.o_tr_ratio,
13 MAX(gu.group_id) AS group_id,
14 d.remaining,
15 d.premium,
16 d.hotspot,
17 d.created
18 FROM (
19 SELECT
20 c.id,
21 c.user_id,
22 c.l_count,
23 c.l_score,
24 c.l_tr_ratio,
25 c.l_pn_ratio,
26 c.l_previous,
27 c.g_score,
28 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 o_tr_ratio,
29 c.remaining,
30 c.premium,
31 c.hotspot,
32 c.created
33 FROM (
34 SELECT
35 b.id,
36 b.user_id,
37 b.session_id,
38 b.date,
39 b.location_id,
40 b.l_count,
41 b.l_score,
42 b.l_tr_ratio,
43 b.l_pn_ratio,
44 b.l_previous,
45 SUM(IF(a3.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(b.date, INTERVAL IF(b.slot_id = 4, 1, 0) DAY), ADDDATE(s3.date, INTERVAL IF(s3.slot_id = 4, 1, 0) DAY))), 0)) AS g_score,
46 b.remaining,
47 b.premium,
48 b.hotspot,
49 b.created
50 FROM (
51 SELECT
52 a.id,
53 a.user_id,
54 s.id AS session_id,
55 s.date AS date,
56 s.slot_id,
57 s.location_id,
58 COUNT(a2.id) AS l_count,
59 SUM(IF(a2.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = 4, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY))), 0)) AS l_score,
60 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 l_tr_ratio,
61 (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 l_pn_ratio,
62 MIN(IF(a2.id IS NOT NULL, DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = 4, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY)), NULL)) AS l_previous,
63 TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), NOW()) AS remaining,
64 s.premium,
65 l.hotspot,
66 a.created
67 FROM sessions AS s
68 JOIN locations AS l ON (l.id = s.location_id)
69 JOIN applications AS a ON (a.session_id = s.id AND a.canceled IS NULL)
70 LEFT JOIN sessions AS s2 ON (s2.id != s.id AND s2.location_id = s.location_id AND s2.slot_id IN (2, 3) AND s2.application_id IS NOT NULL AND s2.locked IS NULL AND s2.date > s.date - INTERVAL 1 YEAR)
71 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))
72 WHERE s.id = 12
73 GROUP BY a.id
74 ORDER BY NULL
75 LIMIT 0, 1000000
76 ) AS b
77 LEFT JOIN sessions AS s3 ON (s3.id != b.session_id AND s3.application_id IS NOT NULL AND s3.locked IS NULL AND s3.date > b.date - INTERVAL 1 YEAR)
78 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))
79 GROUP BY b.id
80 ORDER BY NULL
81 LIMIT 0, 1000000
82 ) AS c
83 LEFT JOIN sessions AS s4 ON (s4.id != c.session_id AND s4.location_id = c.location_id AND s4.application_id IS NOT NULL AND s4.locked IS NULL AND s4.date > c.date - INTERVAL 1 YEAR)
84 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))
85 GROUP BY c.id
86 ORDER BY NULL
87 LIMIT 0, 1000000
88 ) AS d
89 LEFT JOIN groups_users AS gu ON (gu.user_id = d.user_id)
90 GROUP BY d.id
91 LIMIT 0, 1000000
92 ) AS e
93 WHERE
94 IF(e.group_id <= 2 AND e.l_previous <= 30, e.remaining <= SEC_TO_TIME(2*24*3600), 1) AND
95 IF(e.group_id <= 3 AND e.premium = 1 AND e.hotspot = 1, e.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
96 IF(e.group_id <= 4 AND e.l_count <= 5, e.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
97 IF(e.group_id <= 4 AND e.l_pn_ratio >= 1, e.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
98 IF(e.group_id <= 4 AND e.l_tr_ratio >= (e.o_tr_ratio + 5), e.remaining <= SEC_TO_TIME(3*24*3600), 1)
99 ORDER BY e.l_score, e.g_score, e.created, e.user_id