Utilizador:Malafaya/scripts
Páginas sem saída[editar]
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 categoria[editar]
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íngua[editar]
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 categorias[editar]
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 categorias[editar]
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;