30-päivän kumoustilasto (fiwiki)

Sarakkeet
- reverts_ip = ip-osoite jota on kumottu
- revert_days = kuinka monena päivänä kumouksia on tapahtunut
- revert_count = kuinka monta muokkausta on kumottu
- edit_days = kuinka monena päivänä on muokattu
- edit_count = kuinka monta muokkausta on tehty
- revert_edit_count_ratio = kumousten ja muokkausten suhde
- revert_edit_days_ratio = kumous- ja muokkauspäivien suhde
-  af_count = kuinka monta väärinkäyttösuodattimen osumaa on tapahtunut vuonna 2016 (aika on kovakoodattu kyselyy)
- latest_revert = viimeisin kumous
- ip_block_expiry = koska mahdollinen esto loppuu. Tyhjä = ei ole estettynä.

SQL

use fiwiki_p;
SELECT
  reverts_ip, 
  reverts_d as revert_days,
  reverts_cc as revert_count,
  edits_c as edit_count,
  edits_d as edit_days,
  reverts_cc/edits_c as revert_edit_count_ratio,
  reverts_d/edits_d as revert_edit_days_ratio,
  abuse_filter_c as abuse_filter_count,
  max_revert_d as latest_revert,
  ipb_expiry as ip_block_expiry
  
FROM
(
SELECT 
  sum(1) as reverts_d, 
  sum(reverts_c) as reverts_cc, 
  edits_c,
  edits_d, 
  abuse_filter_c, 
  reverts_ip, 
  max(reverts_d) as max_revert_d,
  ipb_expiry
FROM 
(
  SELECT 
    SUM(1) AS reverts_c, 
    floor(rc_timestamp/1000000) as reverts_d, 
    SUBSTR(REGEXP_SUBSTR(lower(rc_comment),'/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})'),2) AS reverts_ip    
  FROM recentchanges    
  WHERE
    rc_comment REGEXP "(ontributions|uokkaukset)/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})"       
    AND rc_comment NOT LIKE concat("%", rc_user_text, "%")
    AND rc_source="mw.edit" 
    GROUP BY reverts_ip, reverts_d
) AS tmp 
LEFT JOIN 
(
   SELECT 
      SUM(edits_c) as edits_c,
      SUM(1) as edits_d,
      rc_user_text
   FROM
   (
      SELECT 
         SUM(1) as edits_c,
         floor(rc_timestamp/1000000) as edits_d, 
         rc_user_text 
      FROM recentchanges 
      WHERE  
         rc_user=0 
         AND rc_source="mw.edit" 
      GROUP BY rc_user_text,edits_d
   ) AS ec_tmp GROUP BY rc_user_text
) AS ec
ON rc_user_text=reverts_ip 
LEFT JOIN 
( 
   SELECT 
      SUM(1) as abuse_filter_c, 
      afl_user_text 
   FROM abuse_filter_log 
   WHERE 
      afl_user=0 
      AND afl_timestamp > 20160000000000 
   GROUP BY afl_user_text
) AS afl 
ON afl_user_text=rc_user_text  
LEFT JOIN
ipblocks_ipindex
ON ipb_address=rc_user_text
GROUP BY reverts_ip 
)
AS tmp
ORDER by revert_days DESC
LIMIT 25;

SQL

Checking query status...