J'ai placé ici :

http://www.semiophore.net/autograph

ou ici :

http://62.147.174.219/autograph/


une archive de 330Mo : triture2_contributeurs.7z

Il s'agit de tables supplémentaires créées à partir de la base autograph et surtout de la table simplerevision (correspondant résumé des révisions généré par Benoît Habert)
L'archive contient également des fichiers au format GraphML :
- Graphe des catégories
- Graphe de type contrbuteur-article
- Graphe de type contributeur-catégorie

Pour ouvrir un archive 7z sous Windows comme sous Linux, utiliser 7zip, encore plus performant que bzip2 -9 : http://www.7-zip.org/fr

N'hésitez pas à m'appeler pour toute question.

Ci dessous, copié-collé du log des expériences et requêtages SQL pour construire les sorties GraphML.


Trituration n°2 : autour des contributeurs

Semiosys, Elie Naulleau

Avril 2006

 Dans les expériences qui suivent, on crée des tables intermédiaires pour stocker les résultats et pouvoir y accéder rapidement programmatiquement, sans avoir à rejouer les requêtes. Le temps d'éxécution des requêtes peuvent en effet être long (plusieurs jours sur ma machine).


1) Activité résumée des contributeurs.

 
Ici on s’intéresse au

- nombre total de contributions par contributeur, tous articles et namespaces confondus,

- à leur production cumulée (en nombre de caractères), tous articles et namespaces confondus,

- à leur production moyenne par contribution (en nombre de caractères), tous articles et namespaces confondus,

- à la date de leur première contribution, tous articles et namespaces confondus,

- à la date de leur dernière contribution, tous articles et namespaces confondus,

- au nombre de jour écoulés entre leur première et leur dernière contribution, tous articles et namespaces confondus

 

-- Création de la table triture2_contributors_count

DROP TABLE IF EXISTS `autograph`.`triture2_contributors_count`;

CREATE TABLE `autograph`.`triture2_contributors_count` (

 `contributeur` varchar(128) default NULL,

 `contribid` int(11) NOT NULL default '0',

 `totaldiff` bigint(20) NOT NULL default '0',

 `avgdiff` float default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 `startdate` datetime default NULL,

 `enddate` datetime default NULL,

 `diffdate` int(10) unsigned default NULL,

 KEY `contributeur` (`contributeur`,`contribid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- Peuplement de la table triture2_contributors_count

INSERT INTO triture2_contributors_count

SELECT contributeur, contribid,

sum(prevrevdiffcharcount),

avg(prevrevdiffcharcount),count(*),min(revisiondate), max(revisiondate),datediff(max(revisiondate), min(revisiondate))

FROM simplerevision 

GROUP BY contributeur, contribid;

 

 
Cette table contient 262 150 lignes

Nom du dump de cette table : table_triture2_contributors_count.bz2


2) Activité résumée des contributeurs pour chaque namespace

Ici on s’intéresse au

- nombre total de contributions par contributeur dans chaque namespace

- à leur production cumulée (en nombre de caractères) dans chaque namespace

- à leur production moyenne par contribution (en nombre de caractères) dans chaque namespace

- à la date de leur première contribution dans chaque namespace

- à la date de leur dernière contribution dans chaque namespace

- au nombre de jour écoulés entre leur première et leur dernière contribution dans chaque namespace

 

 

-- Création de la table triture2_contributors_namespace_count

CREATE TABLE triture2_contributors_namespace_count (

namespace VARCHAR(128) NOT NULL,

-- <contributeur sur 2 colonnes>

contributeur VARCHAR(128),

contribid int NOT NULL,

totaldiff bigint not null,

avgdiff float,

compte int unsigned not null,

startdate DATETIME,

enddate DATETIME,

diffdate int unsigned,

 KEY(namespace) , KEY(contributeur,contribid)

);

 

-- Peuplement de la table triture2_contributors_namespace_count

INSERT INTO triture2_contributors_namespace_count

SELECT namespace, contributeur, contribid, SUM(prevrevdiffcharcount), AVG(prevrevdiffcharcount), count(*), min(revisiondate), max(revisiondate), datediff(max(revisiondate), min(revisiondate))

FROM simplerevision 

GROUP BY namespace, contributeur, contribid;

 

-- Application1: nombre de contributions par auteur pour le namespace "Portail"

SELECT * FROM triture2_contributors_namespace_count where namespace="Portail" order by compte desc;

 

-- Application2: Nombre de contributions dans chaque namespace

SELECT namespace, sum(compte) as cnt

FROM triture2_contributors_namespace_count group by namespace

ORDER BY cnt desc;

 


Cette table contient 318 658 lignes.
Nom du dump de cette table dans l'archive: table_triture2_contributors_namespace_count.bz2


3) Nombre de contributions par article et par contributeur

 
Ici on s’intéresse au

- nombre total de contributions par article par contributeur sans prendre en compte les namespaces.  On obtient ainsi une information pour chaque article (pour chaque article, on a la liste des contributeurs qui y ont contribué) et pour chaque contributeur

- à la production cumulée (en nombre de caractères) pour chaque article et pour chaque contributeur

- à la production moyenne par contribution (en nombre de caractères) dans chaque article et pour chaque contributeur

- à la date de leur première contribution pour chaque article et pour chaque contributeur

- à la date de leur dernière contribution pour chaque article et pour chaque contributeur

- au nombre de jour écoulés entre la première et la dernière contribution pour chaque article et pour chaque contributeur


DROP TABLE IF EXISTS `autograph`.`triture2_articles_per_contributor_count`;

CREATE TABLE `autograph`.`triture2_articles_per_contributor_count` (

 `page_id` int(8) unsigned NOT NULL default '0',

 `titre` varchar(255) default NULL,

 `contributeur` varchar(128) default NULL,

 `contribid` int(11) NOT NULL default '0',

 `totaldiff` bigint(20) NOT NULL default '0',

 `avgdiff` float default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 `startdate` datetime default NULL,

 `enddate` datetime default NULL,

 `diffdate` int(10) unsigned default NULL,

 KEY `contributeur` (`contributeur`,`contribid`),

 KEY `page_id` (`page_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

INSERT INTO triture2_articles_per_contributor_count

SELECT page_id, titre, contributeur, contribid, sum(prevrevdiffcharcount), avg(prevrevdiffcharcount), count(*), min(revisiondate), max(revisiondate), datediff(max(revisiondate), min(revisiondate))

FROM simplerevision

GROUP BY page_id, contributeur, contribid


Cette table contient 2 590 968 lignes.

 
Nom du dump de cette table : table_articles_per_contributor_count.bz2

 

 4) Comptage des types de contributions par contributeur (mineure/majeure)

 
Ici on s’intéresse au tout  namespaces confondus

- nombre total de contributions par contributeur pour chaque type de contribution (mineur/majeure)

- à leur production cumulée (en nombre de caractères) pour chaque type de contribution (mineur/majeure)

- à leur production moyenne par contribution (en nombre de caractères) pour chaque type de contribution (mineur/majeure)

- à la date de leur première contribution pour chaque type de contribution (mineur/majeure)

- à la date de leur dernière contribution pour chaque type de contribution (mineur/majeure)

- au nombre de jour écoulés entre leur première et leur dernière contribution pour chaque type de contribution (mineur/majeure)


DROP TABLE IF EXISTS `autograph`.`triture2_contribtypes_by_contributors`;

CREATE TABLE `autograph`.`triture2_contribtypes_by_contributors` (

 `contributeur` varchar(128) default NULL,

 `contribid` int(11) NOT NULL default '0',

 `contribtype` tinyint(4) default NULL,

 `totaldiff` bigint(20) NOT NULL default '0',

 `avgdiff` float default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 `startdate` datetime default NULL,

 `enddate` datetime default NULL,

 `diffdate` int(10) unsigned default NULL,

 KEY `contributeur` (`contributeur`,`contribid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- peuplement

INSERT INTO triture2_contribtypes_by_contributors

SELECT contributeur, contribid, contribtype, sum(prevrevdiffcharcount), avg(prevrevdiffcharcount), count(*) as cnt, min(revisiondate), max(revisiondate), datediff(max(revisiondate), min(revisiondate)) 

FROM simplerevision

GROUP BY contributeur, contribid, contribtype

ORDER BY contributeur, contribtype;

 

 
Cette table contient 272 182 lignes

Nom du dump de cette table : table_ triture2_contribtypes_by_contributors.bz2


5) Comptage des types de modifications par namespace (modifications mineures/majeures)

 
Ici on s’intéresse au pour chaque namespace

- nombre total de contributions par contributeur pour chaque type de contribution (mineur/majeure)

- à la production cumulée (en nombre de caractères) pour chaque type de contribution (mineur/majeure)

- à la production moyenne par contribution (en nombre de caractères) pour chaque type de contribution (mineur/majeure)

- à la date de la première contribution pour chaque type de contribution (mineur/majeure)

- à la date de la dernière contribution pour chaque type de contribution (mineur/majeure)

- au nombre de jour écoulés entre la première et la dernière contribution pour chaque type de contribution (mineur/majeure)


DROP TABLE IF EXISTS `autograph`.`triture2_contribtype_by_namespace`;

CREATE TABLE `autograph`.`triture2_contribtype_by_namespace` (

 `namespace` varchar(128) NOT NULL default '',

 `contribtype` tinyint(4) default NULL,

 `totaldiff` bigint(20) NOT NULL default '0',

 `avgdiff` float default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 `startdate` datetime default NULL,

 `enddate` datetime default NULL,

 `diffdate` int(10) unsigned default NULL,

 KEY `namespace` (`namespace`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- peuplement

INSERT INTO triture2_contribtype_by_namespace

SELECT namespace, contribtype, sum(prevrevdiffcharcount), avg(prevrevdiffcharcount), count(*) as cnt, min(revisiondate), max(revisiondate), datediff(max(revisiondate), min(revisiondate)) 

FROM simplerevision

GROUP BY namespace,contribtype 

ORDER BY cnt;


Cette table contient 38 lignes

 Nom du dump de cette table : table_ triture2_contribtype_by_namespace.bz2


6) Contributeurs et articles : réseau de liens contributeur–article

 
On s’intéresse à la relation <contributeur> a contribué à <article>.

 Et sur chaque lien <contibuteur> – <article>, on calcule :

- Le nombre total de contributions de ce contributeur pour cet article

- la production cumulée (en nombre de caractères) de ce contributeur pour cet article

- la production moyenne par contribution (en nombre de caractères) de ce contributeur pour cet article

- la date de la première contribution de ce contributeur pour cet article

- la date de la dernière contribution de ce contributeur pour cet article

- le nombre de jour écoulés entre la première et la dernière contribution de ce contributeur pour cet article


 

CREATE TABLE `autograph`.`triture2_liens_contributeurs_articles` (

 `contributeur` varchar(128) default NULL,

 `contribid` int(11) NOT NULL default '0',

 `page_id` int(8) unsigned NOT NULL default '0',

 `titre` varchar(255) default NULL,

 `totaldiff` bigint(20) NOT NULL default '0',

 `avgdiff` float default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 `startdate` datetime default NULL,

 `enddate` datetime default NULL,

 `diffdate` int(10) unsigned default NULL,

 KEY `contributeur` (`contributeur`,`contribid`),

 KEY `page_id` (`page_id`),

 KEY `titre` (`titre`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- Peuplement

 

INSERT INTO triture2_liens_contributeurs_articles

SELECT contributeur, contribid, page_id, titre, sum(prevrevdiffcharcount), avg(prevrevdiffcharcount), count(*), min(revisiondate), max(revisiondate), datediff(max(revisiondate), min(revisiondate))

FROM simplerevision

GROUP BY contributeur, contribid, page_id

ORDER BY contributeur

 

Cette table contient 2 590 968 lignes.

 Nom du dump de cette table : table_articles_liens_contributeurs_articles.bz2

 Nous avons finalement réussi à obtenir la même chose en introduisant cette fois les namespace

 

CREATE TABLE `autograph`.`triture2_liens_contributeurs_articles_ns` (

 `namespace` varchar(128) NOT NULL default '',

 `contributeur` varchar(128) default NULL,

 `contribid` int(11) NOT NULL default '0',

 `page_id` int(8) unsigned NOT NULL default '0',

 `titre` varchar(255) default NULL,

 `totaldiff` bigint(20) NOT NULL default '0',

 `avgdiff` float default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 `startdate` datetime default NULL,

 `enddate` datetime default NULL,

 `diffdate` int(10) unsigned default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

INSERT INTO triture2_liens_contributeurs_articles_ns

SELECT namespace, contributeur, contribid, page_id, titre, sum(prevrevdiffcharcount), avg(prevrevdiffcharcount), count(*), min(revisiondate), max(revisiondate), datediff(max(revisiondate), min(revisiondate))

FROM simplerevision

GROUP BY namespace, contributeur, contribid, page_id

 

 

Cette table contient 2 590 968 lignes.

 C’est cette table qui sera utilisée pour la génération d’un fichier au format GraphML.

7) Contributeurs et articles : réseau de liens contributeur–catégorie

 
On s’intéresse à la relation <contributeur> a contribué à <catégorie>.

 Et sur chaque lien <contibuteur> – <catégorie>, on calcule :

- Le nombre total de contributions de ce contributeur pour cet article

- la production cumulée (en nombre de caractères) de ce contributeur pour cette catégorie

- la production moyenne par contribution (en nombre de caractères) de ce contributeur pour cette catégorie

- la date de la première contribution de ce contributeur pour cette catégorie

- la date de la dernière contribution de ce contributeur pour cette catégorie

- le nombre de jour écoulés entre la première et la dernière contribution de ce contributeur pour cette catégorie

 

CREATE TABLE `autograph`.`triture2_liens_contributeurs_categories` (

 `contributeur` varchar(128) default NULL,

 `contribid` int(11) NOT NULL default '0',

 `categorie` varchar(255) default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 KEY `contributeur` (`contributeur`,`contribid`),

 KEY `categorie` (`categorie`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- Peuplement – très long, plus de 24 heures de calcul

INSERT INTO triture2_liens_contributeurs_categories

SELECT contributeur, contribid, categorylinks.cl_to, count(*)

FROM simplerevision, categorylinks

WHERE categorylinks.cl_from = page_id

GROUP BY contributeur, contribid, categorylinks.cl_to

ORDER BY contributeur;

 
Cette table contient 2 574 107 lignes.

 Nom du dump de cette table : table_articles_liens_contributeurs_categories.bz2

 
Nous avons finalement réussi à obtenir la même chose en introduisant cette fois les namespace (au prix de quelques heures supplémentaires d’attente).

 

CREATE TABLE `autograph`.`triture2_liens_contributeurs_categories_ns` (

 `namespace` varchar(128) NOT NULL default '',

 `contributeur` varchar(128) default NULL,

 `contribid` int(11) NOT NULL default '0',

 `categorie` varchar(255) default NULL,

 `totaldiff` bigint(20) NOT NULL default '0',

 `avgdiff` float default NULL,

 `compte` int(10) unsigned NOT NULL default '0',

 `startdate` datetime default NULL,

 `enddate` datetime default NULL,

 `diffdate` int(10) unsigned default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

INSERT INTO triture2_liens_contributeurs_categories_ns

SELECT namespace, contributeur, contribid, categorylinks.cl_to, sum(prevrevdiffcharcount), avg(prevrevdiffcharcount), count(*), min(revisiondate), max(revisiondate), datediff(max(revisiondate), min(revisiondate))

FROM simplerevision, categorylinks

WHERE categorylinks.cl_from = page_id

GROUP BY namespace, contributeur, contribid, categorylinks.cl_to


Cette table contient 2 624 801 lignes.

 C’est cette table qui sera utilisée pour la génération d’un fichier au format GraphML.


8) –Réseaux de contributeurs ayant contribués aux mêmes articles [abandonné]

On s’intéresse à la relation <contributeur1> a contribué au même article que <contributeur2>, qui correspondrait en fait au même graphe bipartite contributeur–article, en supprimant le nœud transitif article, on obtiendrait alors un graphe contributeur–contributeur sur lequel on inscrirait sur les liens, la liste des articles contribués communs aux deux contributeurs.

 

REATE TABLE triture2_liens_contributeurs_contributeurs_samepage (

contributeur1 VARCHAR(128),

contribid1 int NOT NULL,

contributeur2 VARCHAR(128),

contribid2 int NOT NULL,

page_id int(8) unsigned not null,

titre VARCHAR(255),

compte int unsigned not null,

KEY(contributeur,contribid), KEY(page_id)

)

 

INSERT INTO triture2_liens_contributeurs_contributeurs_samepage

SELECT T1.contributeur , T1.contribid, T2.contributeur , T2.contribid, T1.page_id, T1.titre, count(*)

FROM triture2_liens_contributeurs_articles as T1, triture2_liens_contributeurs_articles as T2

WHERE (T1.page_id = T2.page_id) AND (T1.contributeur != T2.contributeur)

GROUP BY T1.contributeur, T1.contribid, T2.contributeur , T2.contribid, T1.page_id

 Après plusieurs tentatives, cette requête est trop gourmande en ressources, le PC utilisé ne permet pas d’arriver jusqu’au bout, même après 32 heures de calcul (pas assez de mémoire RAM, et de mémoire virtuelle), ce qui est un peu normal vu que la table triture2_liens_contributeurs_articles contient plus de 2 millions de lignes.

 Il faudrait procéder autrement pour réduire la combinatoire de cette jointure carrée, en l’occurrence développer un script qui exécute cette requête pour chaque page page_id.

 La requête suivante compte le nombre de contributeur intervenus sur chaque article ou page : 

 select page_id , count(*) as cnt from triture2_liens_contributeurs_articles group by page_id order by cnt desc

 

age_id

cnt

148973

2069

325026

1450

3222

1306

171991

950

3229

943

1346

826

1347

699

41295

691

8104

674

19432

652

3246

630

53970

615

3219

594

21647

569

…

…

 (517 553 lignes)

 
En imposant un minimum de 3 contributeurs par page :

 select page_id , count(*) as cnt from triture2_liens_contributeurs_articles group by page_id having cnt>2 order by cnt desc

 
On obtient alors moins de la moitié des lignes: 219 986 lignes.

 
Finalement, on abandonne cette idée car on peut considérer que cela aurait produit 219 986 cliques, une clique par article/page_id qui auraient été connectées entre elles du fait qu’un contributeur peut contribuer à plusieurs articles/page_id ; ce qui aurait finalement eu peu d’intérêt à représenter graphiquement.


9) Réseaux de contributeurs ayant contribués aux mêmes catégories [abandonné]

On s’intéresse à la relation <contributeur1> a contribué à la même catégorie que <contributeur2>. On laisse tomber cette approche pour les mêmes raisons que dans le paragraphe précédent : manque de ressources et intérêt limité.

 

REATE TABLE triture2_liens_contributeurs_contributeurs_samecat (

contributeur1 VARCHAR(128),

contribid1 int NOT NULL,

contributeur2 VARCHAR(128),

contribid2 int NOT NULL,

categorie VARCHAR(255),

compte int unsigned not null,

KEY(contributeur,contribid), KEY(page_id)

)

INSERT INTO triture2_liens_contributeurs_contributeurs_samecat

SELECT T1.contributeur , T1.contribid, T2.contributeur , T2.contribid, T1.categorie

FROM triture2_liens_contributeurs_categories as T1, triture2_liens_contributeurs_categories as T2

WHERE T1.categorie= T2.categorie AND T1.contributeur <> T2.contributeur

GROUP BY T1.contributeur , T1.contribid, T2.contributeur , T2.contribid, T1.categorie

 


10) Génération de réseaux au format GraphML

 La conversion SQL vers GraphML s’est faite en Java (répertoire code/, archive wikiexplore.zip).

On a déclaré dans le GraphML tous les attributs disponibles dans la table

Les fichiers se trouven dans le répertoire graphml.

Des fichiers échantillons sample_*.xml donne une idée de la structure du document (ils peuvent être déposés dans Firefox). Les autres fichiers sont des archives bz2 qui sont un dump total. Les tailles non compressées de 3 fichiers dépassent les 700Mo.

 
Réseau des catégories : category_network.xml.bz2

Réseaux contributeur–article : contributor_articles_network.xml.bz2

Réseaux contributeur–categorie : contributor_categories_network.xml.bz2

Répondre à cet article

Par semiosys | | 06/05/2006 15:03 | Présentation | un commentaire | Lu 6940 fois | Version imprimable