Revisita aos dumps da Wikipédia

Desta vez em português, decidi dar [alguma] continuidade ao que comecei há uns dias com a importação dos dumps da Wikipédia. Graças à dica do Rei-artur foi fácil extrair a lista de robôs, para excluir das estatísticas.
-
[myself@speedy ~]# wget 'http://pt.wikipedia.org/w/api.php?action=query&list=allusers&aufrom=A&augroup=bot&aulimit=500&format=txt' -q -O - > bots.tmp
-
-
[myself@speedy ~]# cat bots.tmp | grep '\[name\]' | sed 's,^.*\[name\] => ,,' > /tmp/bots.txt
Aproveitei e repesquei os user_id para simplificar as pesquisas sem fazer alterações na tabela user.
-
mysql> CREATE TABLE user_bots ( bot_name VARCHAR(25) );
-
Query OK, 0 rows affected (0.01 sec)
-
-
mysql> LOAD DATA INFILE '/tmp/bots.txt' INTO table user_bots;
-
Query OK, 136 rows affected (0.00 sec)
-
Records: 136 Deleted: 0 Skipped: 0 WARNINGS: 0
-
-
-
mysql> ALTER TABLE user_bots add COLUMN bot_user_id INT;
-
Query OK, 136 rows affected (0.01 sec)
-
Records: 136 Duplicates: 0 WARNINGS: 0
-
-
mysql> ALTER TABLE user add index idx_t ( user_name );
-
Query OK, 119134 rows affected (2.63 sec)
-
Records: 119134 Duplicates: 0 WARNINGS: 0
-
-
mysql> UPDATE user_bots ub JOIN user u on user_name = bot_name SET ub.bot_user_id = u.user_id;
-
Query OK, 134 rows affected (0.00 sec)
-
Rows matched: 134 Changed: 134 WARNINGS: 0
-
-
mysql> ALTER TABLE user_bots add PRIMARY KEY (bot_user_id);
-
Query OK, 136 rows affected, 1 warning (0.00 sec)
-
Records: 136 Duplicates: 0 WARNINGS: 1
-
-
mysql> SHOW WARNINGS;
-
+---------+------+---------------------------------------------------+
-
| Level | Code | Message |
-
+---------+------+---------------------------------------------------+
-
| Warning | 1265 | Data truncated for COLUMN 'bot_user_id' at row 71 |
-
+---------+------+---------------------------------------------------+
-
1 row in SET (0.00 sec)
-
-
mysql> UPDATE user_bots SET bot_user_id = -1 WHERE bot_user_id = 0;
-
Query OK, 1 row affected (0.00 sec)
-
Rows matched: 1 Changed: 1 WARNINGS: 0
Não tinha reparado que havia um utilizador/robô com o nome "MediaWiki default" mas, bem, depois de criar a Primary Key ficou com o bot_user_id=0 e, para evitar que coincidisse com o agregado para anonymous, dei-lhe o bot_user_id=-1.
Então agora já estamos prontos a completar a query onde ficámos no último dia (número de edições em artigos distintos em cada namespace por utilizador):
-
mysql> EXPLAIN SELECT epn.user_name,epn.page_namespace,epn.edits
-
-> FROM edits_per_namespace epn
-
-> LEFT JOIN user_bots ub ON epn.user_id = ub.bot_user_id
-
-> WHERE ub.bot_user_id IS NULL
-
-> AND epn.user_id <> 0
-
-> ORDER BY edits desc limit 20;
-
+----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
-
| 1 | SIMPLE | epn | ALL | NULL | NULL | NULL | NULL | 187624 | USING WHERE; USING filesort |
-
| 1 | SIMPLE | ub | eq_ref | PRIMARY | PRIMARY | 4 | ntavares.epn.user_id | 1 | USING WHERE; USING index; NOT EXISTS |
-
+----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
-
2 rows in SET (0.00 sec)
-
-
mysql> SELECT epn.user_name,epn.page_namespace,epn.edits
-
-> FROM edits_per_namespace epn
-
-> LEFT JOIN user_bots ub ON epn.user_id = ub.bot_user_id
-
-> WHERE ub.bot_user_id IS NULL
-
-> AND epn.user_id <> 0
-
-> ORDER BY edits desc limit 10;
-
+----------------+----------------+-------+
-
| user_name | page_namespace | edits |
-
+----------------+----------------+-------+
-
| EMP,Nice poa | 0 | 58138 |
-
| Dantadd | 0 | 44767 |
-
| João Carvalho | 3 | 44533 |
-
| OS2Warp | 0 | 43396 |
-
| Yanguas,Sonlui | 0 | 37020 |
-
| Lijealso | 0 | 34157 |
-
| Rei-artur | 0 | 33863 |
-
| Tumnus | 3 | 33213 |
-
| Nuno Tavares | 0 | 31910 |
-
| Bisbis | 0 | 29886 |
-
+----------------+----------------+-------+
-
10 rows in SET (0.76 sec)
Os resultados completos estão aqui.
Já agora, para finalizar, a tão afamada lista de wikipedistas por número de edições:
-
mysql> CREATE TABLE edits_per_user SELECT rev_user,count(1) as counter FROM revision GROUP BY rev_user;
-
Query OK, 119134 rows affected (12.61 sec)
-
Records: 119134 Duplicates: 0 WARNINGS: 0
-
-
mysql> SELECT u.user_name,epu.counter
-
-> FROM edits_per_user epu
-
-> LEFT JOIN user_bots ub on ub.bot_user_id = epu.rev_user
-
-> JOIN user u on epu.rev_user = u.user_id
-
-> WHERE ub.bot_user_id IS NULL ORDER BY counter desc limit 10;
-
+----------------+---------+
-
| user_name | counter |
-
+----------------+---------+
-
| anonymous | 3119758 |
-
| EMP,Nice poa | 176338 |
-
| OS2Warp | 163751 |
-
| Dantadd | 105657 |
-
| Lijealso | 90025 |
-
| Yanguas,Sonlui | 89152 |
-
| Rei-artur | 83662 |
-
| Mschlindwein | 75680 |
-
| Bisbis | 75361 |
-
| Nuno Tavares | 73141 |
-
+----------------+---------+
-
10 rows in SET (0.05 sec)
Os resultados completos estão aqui.
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
October 18th, 2009 at 4:45 am
[...] que o dump utilizado anteriormente era insuficiente, pelo que se descarregou a tabela pagelinks, desta vez do dump de [...]