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

SQL

USE arwiki_p;

SELECT ori.*, r1.rev_timestamp AS latest_revision, r1.rev_len AS latest_size,
DATEDIFF(STR_TO_DATE(r1.rev_timestamp, '%Y%m%d'), STR_TO_DATE(created_when, '%Y%m%d')) AS growth_period,
ROUND(100 * (r1.rev_len / original_size - 1)) AS 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')) AS growth_rate 
FROM
(
  SELECT page_title AS Page Name, a.actor_name AS Creator, rev_page, rev_timestamp AS created_when, rev_len AS original_size
  FROM revision
  JOIN page
  ON rev_page = page_id
  JOIN actor a
  ON revision.rev_actor = a.actor_id
  WHERE rev_parent_id = 0 
  AND page_namespace = 0 
  AND page_is_redirect = 0
  -- AND rev_timestamp BETWEEN 20160101000000 AND 20161230000000
  AND page_random > RAND()
  AND a.actor_name NOT IN 
  (SELECT actor_name FROM user_groups 
   INNER JOIN actor ON user_groups.ug_user = actor.actor_user 
   WHERE ug_group = 'bot')
  AND page_title IN
  (SELECT page_title
  FROM page
  INNER JOIN categorylinks
  WHERE cl_from = page_id
  AND cl_to = 'مقالات_مختارة'
  AND page_namespace = 0)
  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...
