تحليل رقمي لسير تطور المقالات المختارة في ويكيبيديا العربية

SQL

use arwiki_p;
select ori.*, r1.rev_timestamp latest_revision, r1.rev_len latest_size,
DATEDIFF(str_to_date(r1.rev_timestamp, '%Y%m%d'), str_to_date(created_when, '%Y%m%d')) growth_period,
ROUND(100 * (r1.rev_len / original_size - 1)) growth_percentage,
30 * ROUND(100 * (r1.rev_len / original_size - 1)) / DATEDIFF(str_to_date(r1.rev_timestamp, '%Y%m%d'), str_to_date(created_when, '%Y%m%d')) growth_rate 
from
(
  select page_title,rev_user_text, rev_page, rev_timestamp as created_when, rev_len original_size
  from revision
  join page
  on rev_page = page_id
  where rev_parent_id = 0 AND page_namespace = 0 AND page_is_redirect = 0
  AND rev_timestamp BETWEEN 20140101000000 AND 20161230000000
  AND page_random > RAND()
  AND rev_user_text NOT IN 
(SELECT user_name FROM user_groups 
INNER JOIN user ON user_id = ug_user 
WHERE ug_group = 'bot')
  
  limit 1000
) ori
join revision r1
on ori.rev_page = r1.rev_page
left join revision r2
on r1.rev_page = r2.rev_page
and r2.rev_timestamp > r1.rev_timestamp
where r2.rev_timestamp is null

SQL

Checking query status...