Utilizador:Malafaya/scripts

Páginas sem saídaEditar

SELECT CONCAT('# [[', page_title, ']]') as deadendpage FROM page LEFT JOIN pagelinks ON page_id = pl_from WHERE pl_from IS NULL AND page_namespace = 0 AND page_is_redirect = 0 ORDER BY page_title;

Páginas apontadas apenas por si mesmo (pseudo-não-órfãs)Editar

SELECT CONCAT('# [//pt.wiktionary.org/wiki/', page_title, ' ', page_title, ']') AS title, count(*) as cont, pl_from, page_id FROM page LEFT JOIN pagelinks ON page_namespace=pl_namespace AND page_title=pl_title WHERE page_namespace=0 AND page_is_redirect=0 group by page_title having cont=1 and page_id = pl_from;

Artigos com apenas uma categoriaEditar

SELECT CONCAT('# [[', page_title, ']]') AS title, count(*) as cont FROM page LEFT JOIN categorylinks ON page_id=cl_from WHERE page_namespace=0 AND page_is_redirect=0 group by page_title having cont=1;

Contagem de interwikis numa línguaEditar

SELECT page.page_namespace, count(*) FROM langlinks,page where ll_from = page.page_id and langlinks.ll_lang='lb' group by page.page_namespace

Lista com nº interwikis das categoriasEditar

SELECT page_id, page_title, Coalesce(int_cont, 0) cont from page left outer join ( SELECT ll_from, count(*) int_cont from langlinks, page where page_id = ll_from and page_namespace=14 and page_is_redirect = 0 group by ll_from ) ll on ll.ll_from = page.page_id where page_namespace=14 and page_is_redirect = 0 order by cont, page_title;

Média de interwikis nas categoriasEditar

SELECT sum(cont)/count(*) FROM ( SELECT page_id, page_title, Coalesce(int_cont, 0) cont from page left outer join ( SELECT ll_from, count(*) int_cont from langlinks, page where page_id = ll_from and page_namespace=14 and page_is_redirect = 0 group by ll_from ) ll on ll.ll_from = page.page_id where page_namespace=14 and page_is_redirect = 0 order by cont, page_title) xx;

Páginas de discussão cujo artigo não existe (extensível a outros namespaces)Editar

select * from (select page.page_id, page.page_title, page2.page_namespace from page left join page page2 on page.page_title=page2.page_title where page.page_namespace=1) as xxx where page_title not in ( (select page.page_title from page left join page page3 on page.page_title=page3.page_title where page.page_namespace=1 and page3.page_namespace=0) )

Artigos não existentes com título com mais de 30 bytes (em UTF-8)Editar

select distinct pagelinks.pl_from, pagelinks.pl_title, CHAR_LENGTH(pagelinks.pl_title) as len from pagelinks where pagelinks.pl_namespace=0 and CHAR_LENGTH(pagelinks.pl_title) > 30 and pagelinks.pl_title not in ( select page_title from page where page_namespace = 0 and CHAR_LENGTH(page_title) > 30 ) order by len desc;