Bruker:Laaknor/SQL-spørringer

Liste over spørringer jeg har kjørt på Toolserver

Korte diskusjonssider

sql 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

Viser 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

CREATE 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

For 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

Merk: 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

SELECT 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

SELECT 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

SELECT 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

Merk 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

SELECT 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

Grei å 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

SELECT 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

SELECT 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

SELECT 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

SELECT 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

SELECT 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

SELECT 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

SELECT 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

Brukes 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%');