SQL workload calculations

00.sql

SELECT log_page, log_type, log_action, log_timestamp, log_user,user_id,user_name,log_timestamp,rev_timestamp,rev_page
  FROM logging
  JOIN user ON log_user = user_id
  JOIN revision_userindex ON  rev_page = log_page
 WHERE log_type = "patrol"
   AND log_action = "patrol"
   AND user_name NOT LIKE '%bot'            -- to filter robots
 GROUP BY log_page
 ORDER BY log_timestamp DESC                -- to get the last actions
 LIMIT 50;                                  -- to reduce load

005 patrol calculations.sql

SELECT log_type, log_action, log_timestamp, log_user,user_id,user_name,log_timestamp
  FROM logging 
  JOIN user ON log_user= user_id
 WHERE log_type="patrol"
   AND log_action="patrol"  
 ORDER BY log_timestamp DESC
 LIMIT 50;

010_bot_patrol.sql

--this get all user action
SELECT log_type, log_action, log_timestamp, log_user,user_id,user_name,log_timestamp
  FROM logging 
  JOIN user ON log_user= user_id
 WHERE log_type="patrol"
   AND log_action="patrol"
   AND user_name LIKE '%bot'            -- to filter humans
 ORDER BY log_timestamp DESC
 LIMIT 50;

020_human_patrol_load.sql

SELECT log_type, log_action, log_timestamp, log_user,user_id,user_name,log_timestamp
  FROM logging 
  JOIN user ON log_user= user_id
 WHERE log_type="patrol"
   AND log_action="patrol"
   AND user_name NOT LIKE '%bot'            -- to filter humans
 ORDER BY log_timestamp DESC
 LIMIT 50;

100 enwiki.revision_userindex ddl.sql

SHOW CREATE TABLE  enwik_pi.revision_userindex;

100 user edit sessions.sql

--new session calculation
--for grouping/avaraging  edits by session start
--slow ~ 3 minutes to run
SELECT ThisTimeStamp, NewSession  
FROM (      
        SELECT a.rev_timestamp AS ThisTimeStamp,     
                MIN(b.rev_timestamp ) AS NextTimeStamp,                
            IF (MIN(b.rev_timestamp ) - a.rev_timestamp > 3 600,TRUE,FALSE) AS NewSession        
          FROM revision_userindex a  
    INNER JOIN revision_userindex b ON a.rev_timest amp < b.rev_timestamp        
          WHERE a.rev_user='1526134'          
            AND b.rev_user='1526134'
       GROUP BY a.rev_ timestamp 
) Sub1;

110 user edit sessions.sql

--user edit piars for new session calculation
--for grouping/avaraging  edits by session start
--slow ~ 3 minutes to run
SELECT ThisTimeStamp, NewSession  
FROM 
(
        SELECT a.rev_timestamp AS ThisTimeStamp,             
                MIN(b.rev_timestamp ) AS NextTimeStamp,
                IF (MIN(b.rev_timestamp ) - a.rev_timestamp > 3600,TREU,FALSE) AS NewSession
        FROM revision_userindex a
  INNER JOIN revision_userindex b 
          ON a.rev_timestamp < b.rev_timestamp        
    WHERE a.rev_user='1526134'          
      AND b.rev_user='1526134'       
 GROUP BY a.rev_timestamp ) Sub1;

120 sessions

--user edit piars for new session calculation
--for grouping/avaraging  edits by session start
--adding deltas
--adding moving avarage
--slow ~ 3 minutes to run
SELECT ThisTimeStamp, NewSession  
FROM 
(
        SELECT a.rev_timestamp AS ThisTimeStamp,             
                MIN(b.rev_timestamp ) AS NextTimeStamp,
                IF (MIN(b.rev_timestamp ) - a.rev_timestamp > 3600,TRUE,FALSE) AS NewSession
                IF (MIN(b.rev_timestamp ) - a.rev_timestamp < 3600,MIN(b.rev_timestamp ) - a.rev_timestamp,FALSE) AS Delta
        FROM revision_userindex a
  INNER JOIN revision_userindex b 
          ON a.rev_timestamp < b.rev_timestamp        
    WHERE a.rev_user='1526134'          
      AND b.rev_user='1526134'       
 GROUP BY a.rev_timestamp ) Sub1;