Raphaël G. Git Repositories - airbundle/blob - 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