Bruker:Laaknor/SQL-spørringer
Liste over spørringer jeg har kjørt på Toolserver
Korte diskusjonssider
redigersql nowiki_p " SELECT page_title,page_namespace as namespace, page_len AS value
FROM page WHERE page_namespace = 1 AND page_len < 5
ORDER BY page_len ASC" > ../public_html/nowiki_short_diskusjonssider.txt
Status på patruljering
redigerViser antall upatruljerte endringer, gruppert etter hvilken dato endringen ble utført på.
SELECT LEFT(rc_timestamp,8) AS dato, COUNT(*) AS notPatrolled FROM recentchanges WHERE rc_patrolled = 0 GROUP BY LEFT(rc_timestamp, 8);
Stemmeberetigede ved adminavstemning
redigerCREATE TEMPORARY TABLE u_laaknor.temp (SELECT rev_user AS username,
(SELECT COUNT(*) FROM revision WHERE rev_user=username AND rev_timestamp > 20081126000000
AND rev_timestamp < 20090126000000) AS twoMonths,
(SELECT COUNT(*) FROM revision WHERE rev_user=username) AS total
FROM revision GROUP BY username);
SELECT u.user_name FROM (u_laaknor.temp t LEFT JOIN user u ON t.username=u.user_id)
LEFT JOIN user_groups g ON g.ug_user=t.username
WHERE g.ug_group != 'bot' AND g.ug_group != 'bureaucrat'
AND t.total >= 200 AND t.twoMonths >= 30 AND user_name NOT LIKE '%bot%'
AND user_name NOT LIKE '%Bot%' ORDER BY user_name ASC;
Liste over administratorer sortert på antall bidrag
redigerFor Wikipedia:Administratorer/liste/bidrag
SELECT u.user_name,u.user_editcount FROM user u JOIN user_groups g ON user_id=g.ug_user
WHERE g.ug_group = 'sysop' ORDER BY user_editcount DESC;
Liste over orphan diskusjonssider
redigerMerk: Endel sider/artikler er feilaktig lagt inn i namespace 0, som egentlig hører til i andre namespace. Gjelder spesielt eldre artikler/artikler opprettet mens det har vært en feil i MediaWiki. Denne listen kan derfor ikke tas bokstavelig og må manuelt sjekkes.
SELECT CONCAT("Diskusjon:", page_title) FROM page p WHERE page_namespace = 1 AND
(SELECT COUNT(*) FROM page WHERE page_title = p.page_title AND page_namespace = 0) = 0
AND page_is_redirect = 0;
Liste over antall artikler etter størrelse, utenom pekere
redigerSELECT COUNT(*) FROM page p WHERE page_is_redirect = 0 AND page_namespace = 0 AND
(SELECT COUNT(*) FROM templatelinks t WHERE t.tl_from=p.page_id AND tl_namespace = 10 AND tl_title
IN('Peker', 'Trebokstavsforkortelse','Tobokstavsforkortelse','Etternavn', 'Disambig', 'Pekerside')) = 0
AND page_len <= 1000 ;
Liste over brukerbidrag gruppert på navnerom
redigerSELECT COUNT(*),p.page_namespace FROM revision r JOIN page p ON r.rev_page=p.page_id
WHERE rev_user_text LIKE 'Laaknor' GROUP BY p.page_namespace;
Artikler i kategori sortert på størrelse
redigerSELECT p.page_len,p.page_title FROM page p JOIN categorylinks c ON p.page_id=c.cl_from WHERE c.cl_to = 'Viktige_artikler'
ORDER BY p.page_len ASC ;
Antall nye artikler gruppert på dager
redigerMerk at før 20080408 så er tallene merkelige av en eller annen grunn.
SELECT LEFT(rev_timestamp,8) AS month,COUNT(*) AS artikler FROM revision
WHERE rev_parent_id = 0 GROUP BY LEFT(rev_timestamp, 8);
Artikler om et språk men ikke IW til språket
redigerSELECT DISTINCT page.page_title, categorylinks.cl_to,(SELECT COUNT(ll_lang) FROM langlinks WHERE ll_from = page.page_id) AS IWs
FROM (page LEFT JOIN langlinks ON page.page_id = langlinks.ll_from)
LEFT JOIN categorylinks ON page.page_id=categorylinks.cl_from
WHERE langlinks.ll_from = page.page_id AND (SELECT COUNT(ll_lang) FROM langlinks WHERE ll_from = page.page_id AND ll_lang IN ('no', 'nb')) = 0
AND page_is_redirect = 0
AND page_namespace = 0
AND cl_to LIKE '%Norway%' OR '%Norwegian%' ;
Alle bilder på artikler i en kategori
redigerGrei å ha i forhold til stubb-bilder etc. i catimagesearch catimagesearch
SELECT p.page_title,il.il_to FROM
(imagelinks il JOIN page p ON p.page_id=il.il_from)
JOIN categorylinks cl ON p.page_id=cl.cl_from
WHERE cl_to NOT LIKE '%.svg'
AND cl.cl_to LIKE 'Bygninger_i_Bodø'
Alle rødlenker fra en side
redigerSELECT pl.pl_title FROM pagelinks pl WHERE pl_from = 696420
AND pl_namespace = 0
AND (SELECT COUNT(*) FROM page p WHERE p.page_title = pl.pl_title) = 0;
Antall slettinger i hovednavnerommet per måned
redigerSELECT LEFT(log_timestamp,6) AS Maned,COUNT(*) AS Antall FROM logging l WHERE log_type = 'delete' AND log_action = 'delete' AND log_timestamp LIKE '2010%' AND log_namespace IN (0) GROUP BY LEFT(log_timestamp,6);
Antall slettinger per måned
redigerSELECT LEFT(log_timestamp,6) AS Maned,COUNT(*) AS Antall FROM logging l
WHERE log_type = 'delete' AND log_action = 'delete'
AND log_timestamp LIKE '2010%'
GROUP BY LEFT(log_timestamp,6);
Antall tilbakestillinger per måned
redigerSELECT LEFT(rev_timestamp,6) AS dato, COUNT(*) AS antall FROM revision
WHERE rev_comment LIKE 'Tilbakestilte%' AND rev_timestamp LIKE '2010%'
AND rev_comment NOT LIKE '%flyttet%'
GROUP BY LEFT(rev_timestamp,6);
Antall nye artikler per måned uten flyttede
redigerSELECT LEFT(rev_timestamp,6) AS dato, COUNT(*) AS antall FROM revision
WHERE rev_parent_id = 0 AND rev_timestamp LIKE '2010%'
AND rev_comment NOT LIKE '%flyttet%'
GROUP BY LEFT(rev_timestamp,6);
Antall patruljeringer per måned
redigerSELECT LEFT(log_timestamp,6) AS Maned,COUNT(*) AS Antall FROM logging l
WHERE log_type = 'patrol' AND log_timestamp LIKE '2010%'
AND log_params LIKE '%0'
GROUP BY LEFT(log_timestamp,6);
Alle artikler som lenkes til fra en artikkel
redigerSELECT CONCAT('python interwiki.py \"',pl_title,'\" -autonomous')
FROM pagelinks
WHERE pl_from = 773423 AND pl_namespace = 0;
Lag tabell med alle eksterne lenker til geohack
redigerBrukes for å liste opp alle eksterne lenker til geohack, altså koordinater.
CREATE TABLE u_laaknor.geohack_nowiki
(SELECT page_title,el_from,el_to
FROM externallinks JOIN page on page_id=el_from
WHERE page_namespace = 0
AND page_title NOT LIKE 'Liste%'
AND el_to LIKE 'http://toolserver.org/~geohack%');