Bekleyen değişiklikler için

SQL

USE trwiki_p;
SET @M = 10;

SELECT NOW();
SELECT AVG(DATEDIFF(NOW(),fpp_pending_since)) as ort_gun, SUM(DATEDIFF(NOW(),fpp_pending_since))/291150 as genel_ort_gun from flaggedpage_pending
;


SELECT *, concat("tr.wikipedia.org/wiki?redirect=no&curid=",fpp_page_id), page_is_redirect, page_latest,page_len
from flaggedpage_pending  join page on page_id = fpp_page_id
where not exists (select * from categorylinks where cl_from = fpp_page_id);

SELECT concat("tr.wikipedia.org/wiki?redirect=no&curid=",page_id), page_is_redirect, page_latest,page_len,cl_to  
from categorylinks  join page on page_id = cl_from 
where page_is_redirect = 1 group by page_id order by page_latest;


SELECT cl_to, count(*)
from flaggedpage_pending join categorylinks on cl_from = fpp_page_id
where cl_to in (select pc.page_title from page pc join page_props on pp_page = pc.page_id 
              where pc.page_namespace = 14 and pp_propname in ("hiddencat"))
group by 1
having count(*) > @M
order by 2 desc
;


SELECT cl_to, count(*)
from flaggedpage_pending join categorylinks on cl_from = fpp_page_id
where cl_to not in (select pc.page_title from page pc join page_props on pp_page = pc.page_id 
              where pc.page_namespace = 14 and pp_propname in ("hiddencat"))
group by 1
having count(*) > @M
order by 2 desc
;

SET @N = -70; -- bekleyen değişiklikler için kaç gün öncesinden itibaren baksın.
SET @M = -5; -- bekleyen değişiklikler için kaç gün öncesine kadar baksın.
SELECT concat("[[",replace(page_title,"_"," "),"]]") as madde
-- , concat("http://tr.wikipedia.org/wiki?curid=",cl_from) as url
,concat("[[:Kategori:",cl_to,"|",replace(cl_to,"_"," "),"]]") as kat
, round(floor(fpp_pending_since/1000000),0) as tarih
, DATEDIFF(NOW(),fpp_pending_since) as kac_gun
, CONCAT("[http://tr.wikipedia.org/w/index.php?diff=cur&oldid=",fpp_rev_id," tüm]") as tumbekleyen
, CONCAT("[http://tr.wikipedia.org/wiki/Özel:Diff/",rev_id," ilk]") as ilkbekleyen
from categorylinks right join page on page_id = cl_from 
join flaggedpage_pending on fpp_page_id = page_id 
join 
(select rev_id, rev_parent_id, rev_deleted, rev_user_text,rev_user,rev_len
 from revision_userindex where rev_timestamp > DATE_ADD(NOW(), INTERVAL @N DAY) and rev_timestamp < DATE_ADD(NOW(), INTERVAL @M DAY)
) as rev on rev.rev_parent_id = fpp_rev_id
where cl_to in ("Türk_erkek_dizi_oyuncuları", "Türk_kadın_dizi_oyuncuları")
and page_namespace = 0
order by tarih
-- limit 10
;
SQL

Checking query status...
