Untitled query #12230

SQL

use fiwiki_p;

SELECT 
  all_title, 
  all_ec, 
  all_revert_c, 
  ( all_revert_c / all_ec) as all_ratio, 
  2016_ec, 
  2016_revert_c, 
  (2016_revert_c / 2016_ec) as 2016_ratio,   
  all_revert_d, 2016_revert_d,
  (2016_revert_c /13122 ) as 2016_revert_c_r
FROM
(
 
  SELECT 
     SUM(1) AS all_revert_d,
     SUM(rev_c) AS all_revert_c,
     all_ec,
     page_title as all_title
   FROM
   (
      SELECT 
         SUM(1) AS rev_c, 
         floor(rev_timestamp/1000000) as rev_d, 
         page_title,
         rev_page 
      FROM 
         revision,
         page 
      WHERE 
         rev_page=page_id 
         AND page_namespace=0
         AND rev_comment REGEXP "(ontributions|uokkaukset)/[0-9A-F]{1,4}[.:][0-9A-F]{1,4}[.:]" 
         AND NOT (rev_user=0 AND rev_comment LIKE concat("%", rev_user_text, "%"))  
      GROUP BY rev_page, rev_d
   )
   AS tmp
   LEFT JOIN
   (
      SELECT 
         sum(1) as all_ec,
         rev_page
      FROM
         revision_userindex
      WHERE
         rev_user=0
      GROUP BY rev_page
    )
    AS ec
    ON tmp.rev_page=ec.rev_page
    GROUP BY tmp.rev_page
)
AS rev_all 
LEFT JOIN
(
  SELECT 
     SUM(1) AS 2016_revert_d,
     SUM(rev_c) AS 2016_revert_c,
     2016_ec,
     page_title as 2016_title     
   FROM
   (
      SELECT 
         SUM(1) AS rev_c, 
         floor(rev_timestamp/1000000) as rev_d, 
         page_title,
         rev_page 
      FROM 
         revision,
         page 
      WHERE 
         rev_page=page_id 
         AND page_namespace=0
         AND rev_comment REGEXP "(ontributions|uokkaukset)/[0-9A-F]{1,4}[.:][0-9A-F]{1,4}[.:]" 
         AND NOT (rev_user=0 AND rev_comment LIKE concat("%", rev_user_text, "%"))  
         AND rev_timestamp > 20160000000000 
      GROUP BY rev_page, rev_d
   )
   AS tmp
   LEFT JOIN
   (
      SELECT 
         sum(1) as 2016_ec,
         rev_page
      FROM
         revision_userindex
      WHERE
         rev_user=0
         AND rev_timestamp > 20160000000000 
      GROUP BY rev_page
    )
    AS ec
    ON tmp.rev_page=ec.rev_page
    GROUP BY tmp.rev_page
)
AS rev_2016
ON rev_all.all_title=rev_2016.2016_title
ORDER BY all_revert_d DESC
LIMIT 100
SQL

Checking query status...
