SQL
x
use nlwiki_p;
WITH raw_list as(
SELECT
loguser.actor_name as loguser,
edituser.actor_name as edituser,
edituser.actor_user as edituser_actor,
log_timestamp,
CONCAT(SUBSTRING(log_timestamp, 1, 8), ' ', SUBSTRING(log_timestamp, 9, 2), ':', SUBSTRING(log_timestamp, 11, 2), ':', SUBSTRING(log_timestamp, 13, 2)) as timestamp_human,
log_title, r.rev_timestamp, r.rev_id,
CASE WHEN edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%') THEN 1 ELSE 0 END as isAnoniem
FROM logging l
INNER JOIN revision r ON SUBSTRING(l.log_params, 26, 8) = r.rev_id # edit_id = rev_id
INNER JOIN actor loguser ON loguser.actor_id = log_actor
INNER JOIN actor edituser ON edituser.actor_id = rev_actor
WHERE log_type = 'patrol' #Enkel markeringen
AND log_namespace = '0' #Enkel hoofdnaamruimte
AND log_deleted = '0'
#AND (edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%')) #anonieme bewerkingen
#AND (log_timestamp LIKE '201%' OR log_timestamp LIKE '202%')
ORDER BY log_timestamp DESC #De meest recente bovenaan
)
#Stats no 1
/*SELECT raw_list.loguser, edituser, edituser_actor, isAnoniem, timestamp_human
FROM raw_list
WHERE edituser LIKE '%.%'
#GROUP BY raw_list.loguser
#ORDER BY COUNT(1) DESC
LIMIT 100*/
# Some stats
SELECT ROW_NUMBER() OVER (ORDER BY ipMarkeringen DESC) AS row_num, raw_list.loguser,
#sum(isAnoniem) as ipMarkeringen1,
sum(case when isAnoniem = 1 then 1 else 0 end) as ipMarkeringen,
sum(case when isAnoniem = 0 then 1 else 0 end) as gebruikerMarkeringen,
COUNT(*) as aantalMarkeringen
FROM raw_list
GROUP BY raw_list.loguser
ORDER BY ipMarkeringen DESC
/*Difference with the one below?
SELECT log_user_text, COUNT(1) as aantal
FROM logging
WHERE log_type = 'patrol' #Enkel markeringen
AND log_namespace = '0' #Enkel hoofdnaamruimte
AND log_deleted = '0'
GROUP BY log_user_text
ORDER BY COUNT(1) DESC*/
/*SELECT a.actor_name, COUNT(1) as aantal
FROM logging l INNER JOIN actor a ON l.log_actor = a.actor_id
WHERE l.log_type = 'patrol' #Enkel markeringen
AND l.log_namespace = '0' #Enkel hoofdnaamruimte
AND l.log_deleted = '0'
GROUP BY a.actor_name
ORDER BY COUNT(1) DESC*/
/*SELECT l.log_title, COUNT(1)
FROM logging l
WHERE l.log_timestamp LIKE '2019%'
AND l.log_type = 'patrol' #Enkel markeringen
AND l.log_namespace = '0' #Enkel hoofdnaamruimte
AND l.log_deleted = '0'
GROUP BY l.log_title
ORDER BY COUNT(1) DESC*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.