ويكيبيديا:إحصاءات الشهر

SQL

# https://ar.wikipedia.org/w/index.php?title=%D9%88%D9%8A%D9%83%D9%8A%D8%A8%D9%8A%D8%AF%D9%8A%D8%A7:%D8%A5%D8%AD%D8%B5%D8%A7%D8%A1%D8%A7%D8%AA_%D8%A7%D9%84%D8%B4%D9%87%D8%B1&oldid=59981404

SET @start_date = '20221101000000';
SET @end_date = '20221130235959';


# عدد عمليات المنع يشمل فعل المنع فقط لا يشمل التغير مده المنع او الغاء المنع
# https://ar.wikipedia.org/wiki/%D8%AE%D8%A7%D8%B5:%D8%B3%D8%AC%D9%84?type=block&subtype=block
select count(*) as "block count" from logging where log_type= "block" and log_action = "block" and log_timestamp between @start_date and @end_date;

# عدد عمليات الحذف يشمل فعل الحذف فقط لا يشمل باقي العمليات مثل الاستراجاع او الاخفاء .....
# https://ar.wikipedia.org/wiki/%D8%AE%D8%A7%D8%B5:%D8%B3%D8%AC%D9%84?type=delete&subtype=delete
select count(*) as "delete_count" from logging where log_type= "delete" and log_action = "delete"  and log_timestamp between @start_date and @end_date;

# عدد المستخدمين الجدد يشمل المستخدمين الذي تم انشاءها بشكل الي او المستخدمين الذي انشاء وفعل الحساب عبر الايميل 
# نوع الحساب autocreate byemail create create2
# https://ar.wikipedia.org/wiki/%D8%AE%D8%A7%D8%B5:%D8%B3%D8%AC%D9%84?type=newusers
select count(*) as "newusers_count" from logging where log_type= "newusers" and log_timestamp between @start_date and @end_date;

/*
* عدد مرات الرفع يشمل فعل الرفع نفسه وفعل اعاده الرفع لكن لا يشمل فعلا الاسترجاع
* لمزيد من التفاصيل راجع 
* https://ar.wikipedia.org/wiki/%D8%AE%D8%A7%D8%B5:%D8%B3%D8%AC%D9%84?type=upload
*/
select count(*) as "upload_count" from logging where log_type= "upload" and log_action in ("upload","overwrite") and log_timestamp between @start_date and @end_date;


# delete count

select count(*) as "delete_count",log_namespace as "namespace" from logging
where log_type= "delete" and log_action = "delete"  and log_timestamp between @start_date and @end_date
group by log_namespace;

SELECT  count(*) as "new_pages", page_namespace as "namespace"
FROM (
    SELECT page_id, page_namespace, MIN(rev_timestamp) as first_revision
    FROM page
    JOIN revision ON page_id = rev_page
    WHERE page_is_redirect = 0
    GROUP BY page_id
) as temp
WHERE first_revision >= @start_date
AND first_revision <= @end_date
GROUP BY page_namespace;


/*
select count(*),log_action from logging where log_type= "newusers"  and log_timestamp between @start_date and @end_date
group by log_action
*/
SQL

Checking query status...
