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.

[code]
[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
[/code]
Aproveitei e repesquei os user_id para simplificar as pesquisas sem fazer alterações na tabela user.
[mysql]
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
[/mysql]

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]
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)
[/mysql]
Os resultados completos estão aqui.

Já agora, para finalizar, a tão afamada lista de wikipedistas por número de edições:

[mysql]
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)
[/mysql]
Os resultados completos estão aqui.