Wikipedia:Estaleiro/Estatísticas

Origem: Wikipédia, a enciclopédia livre.

[editar] Ferramentas

• Dump
• Wikisign

[editar] Outras Estatísticas

• Lixos
• Administrators of various Wikipedias (meta)

• Artigo com maior número de redireccionamentos: Fluoxetina (48)

[editar] Mapa

^* Inclusão dos Artigos mínimos

[editar] Queries usadas para obter as estatísticas

[editar] Número de Artigos

SELECT COUNT(DISTINCT a.cur_id)
  FROM cur a 
    LEFT JOIN links ON a.cur_id=l_from
    LEFT JOIN cur b ON l_to=b.cur_id
  WHERE a.cur_namespace=0
    AND NOT a.cur_is_redirect
    AND b.cur_namespace=0;

[editar] Artigos sem saída

SELECT COUNT(cur_title) 
  FROM cur LEFT JOIN links ON cur_id=l_from 
  WHERE l_from IS NULL
    AND cur_namespace=0 
    AND length(cur_text)>0;
    AND NOT cur_is_redirect;

[editar] Artigos órfãos

SELECT COUNT(cur_title)
  FROM cur LEFT JOIN links ON cur_id=l_to
  WHERE l_to IS NULL
    AND cur_namespace=0
    AND NOT cur_is_redirect;

[editar] Artigos vazios

SELECT COUNT(cur_title)
  FROM cur 
  WHERE cur_namespace=0 
    AND LENGTH(cur_text)=0;

[editar] Artigos sem categoria

SELECT COUNT(cur_title) 
  FROM cur LEFT JOIN categorylinks ON cur_id=cl_from 
  WHERE cl_from IS NULL
    AND cur_namespace=0 
    AND LENGTH(cur_text)>0
    AND NOT cur_is_redirect;

[editar] Artigos em falta

SELECT COUNT(DISTINCT bl_to)
  FROM brokenlinks LEFT JOIN cur ON bl_from=cur_id
  WHERE cur_namespace=0
    AND (bl_to NOT LIKE '%:%');

[editar] Artigos com lixo

SELECT COUNT(cur_id)
  FROM cur
  WHERE cur_namespace=0 
    AND (cur_text LIKE '%lixo}}%' 
    OR cur_text LIKE '%impróprio}}%');

[editar] Artigos em tradução

SELECT COUNT(cur_title)
  FROM cur
  WHERE cur_namespace=0
    AND (cur_text LIKE '%{{msg:emtradu%' 
      OR cur_text LIKE '%{{emtradu%');

[editar] Artigos em esboço

SELECT COUNT(cur_title)
  FROM cur 
  WHERE cur_namespace=0 
    AND (cur_text LIKE '%{{msg:stub}}%' 
      OR cur_text LIKE '%{{stub}}%' 
      OR cur_text LIKE '%{{msg:esboço%' 
      OR cur_text LIKE '%{{esboço%'
      OR cur_text LIKE '%{{mínimo%'
      OR cur_text LIKE '%{{_rtigo mínimo%');

[editar] Artigos sem links para outras wikipédias

SELECT COUNT(cur_title) 
  FROM cur 
  WHERE cur_namespace=0 
    AND NOT cur_is_redirect 
    AND LENGTH(cur_text)>0 
    AND cur_text NOT LIKE "%[[__:%"
    AND cur_text NOT LIKE "%[[___:%"
    AND cur_title NOT LIKE '%{{desambiguação}}%'
    AND cur_title NOT LIKE '%{{disambig}}%';

[editar] Outras queries

[editar] Especial:Deadendpages

SELECT concat("* [[",cur_title, "]]")
FROM cur LEFT JOIN links ON cur_id=l_from
WHERE l_from IS NULL
AND cur_namespace=0
AND LENGTH(cur_text)>0
ORDER BY cur_title;

[editar] Páginas de discussão sem artigo

SELECT ca.cur_id, ca.cur_title
FROM cur ca
WHERE ca.cur_namespace=1
AND NOT EXISTS ( 
   SELECT cd.cur_id
   FROM cur cd
   WHERE ca.cur_title = cd.cur_title
   AND cd.cur_namespace=0 )

[editar] Redireccionamentos duplos

SELECT ca.cur_title
   FROM cur ca, cur cb, links l 
   WHERE ca.cur_is_redirect=1 
   AND cb.cur_is_redirect=1 
   AND l.l_from=ca.cur_id
   AND l.l_to=cb.cur_id 
   AND ca.cur_namespace=0 

[editar] Especial:Wantedpages

SELECT bl_to, COUNT(bl_from)
   FROM brokenlinks
   GROUP BY bl_to
   ORDER BY 'COUNT(bl_from)' DESC;

[editar] Especial:Uncategorizedpages

SELECT cur_title
   FROM cur LEFT JOIN categorylinks ON cur_id=cl_from
   WHERE cl_from IS NULL
      AND cur_namespace=0
      AND LENGTH(cur_text)>0
      AND NOT cur_is_redirect 
   ORDER BY cur_title;

[editar] Lista de páginas pelo número de imagens

SELECT cur_title, COUNT(il_to) AS cont 
   FROM cur, imagelinks 
   WHERE (il_from = cur_id) 
      AND (cur_namespace=0)
   GROUP BY il_from 
   ORDER BY cont DESC 
   LIMIT 50;

ou, as in User:Duesentrieb/Pages by number of images:

select count(*) as numlinks,concat('[[',cur_title,']]') 
from imagelinks,cur 
where cur_namespace=0 and il_from=cur_id 
group by il_from 
order by numlinks desc 
limit 500;

[editar] Saltos de domínio

SELECT a.cur_title 
FROM cur a, cur b, links l 
WHERE (a.cur_id = l.l_from) 
   AND (l.l_to = b.cur_id) 
   AND (a.cur_is_redirect) 
   AND (a.cur_namespace = 0) 
   AND (b.cur_namespace <> 0) 
   AND NOT (b.cur_namespace = 1)
   AND NOT (a.cur_title like 'WP:%');

[editar] Ecos

SELECT CONCAT('* [[',cur_title,']]'), COUNT(cur_title) AS dups
FROM cur  
WHERE cur_namespace = 0
GROUP BY cur_title
 HAVING dups <> 1 
ORDER BY dups DESC

[editar] Artigos com interlinks para redirects

SELECT DISTINCT r.cur_title, d.cur_title 
FROM cur r, cur a, links l, links k, cur d
WHERE r.cur_is_redirect
   AND l.l_from = a.cur_id
   AND l.l_to = r.cur_id
   AND k.l_from = r.cur_id
   AND k.l_to = d.cur_id
   AND d.cur_namespace = 0

[editar] Desambiguações com afluentes

SELECT DISTINCT d.cur_id, d.cur_title
FROM cur d, cur a, links l
WHERE a.cur_namespace = 0
   AND NOT a.cur_is_redirect
   AND l.l_from = a.cur_id
   AND l.l_to = d.cur_id
   AND  (d.cur_text LIKE '%{{disambig%'
      OR d.cur_text LIKE '%{{desambig%')
   AND NOT (a.cur_text LIKE '%{{ver desambig%'
      OR a.cur_text LIKE '%{{desambigexplicada%'
      OR a.cur_text LIKE '%{{minidesambig%'
      OR a.cur_text LIKE '%{{desambiguação-redirect%')