C'est le début de l'année et le moment des bilans concernant les statistiques de votre blog. Voici donc un petit billet sur quelques hacks MySQL permettant d'obtenir des statistiques à diffuser vers vos lecteurs ou à garder au chaud !

Avant tout il faut disposer d'un accès à votre base de donnée MySQL (soit via une interface de type phpMySQL, soit directement via une ligne de commande mysql). Pour avoir vos paramètres de connexion à votre BD WordPress il suffit de regarder le fichier wp-config.php à la racine de votre répertoire Web.

Voici l'architecture des tables de votre BD WordPress. Je ne suis plus trop un expert en requêtes MySQL (les cours datent de plus de 10 ans maintenant ) alors si vous voyez des améliorations dans les requêtes ci-dessous je suis preneur de commentaires.

Nombre de billets publiés en 2010

Cette première requête donne? le nombre de billets publiés (status="publish") sur l'année 2010 (c'est à dire entre le 1er janvier et le 31 décembre 2010).

mysql> SELECT COUNT(*) FROM wp_posts WHERE post_status="publish" AND post_date BETWEEN '2010-01-01' AND '2010-12-31';

+----------+

| COUNT(*) |

+----------+

| 161 |

+----------+

1 row in set (0.00 sec)

Nombre de commentaires pour les billets publiés en 2010

Une requête un peu plus complexe qui somme (SUM) le nombre de commentaires pour les billets 2010.

mysql> SELECT SUM(comment_count) FROM wp_posts WHERE post_status="publish" AND post_date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY post_status;

+--------------------+

| SUM(comment_count) |

+--------------------+

| 1314 |

+--------------------+

1 row in set (0.01 sec)

Liste des 10 billets les plus commentés publiés en 2010

On continu pas le classement (TOP 10) des nouveaux billets les plus commentés.

mysql> SELECT post_title,comment_count FROM wp_posts WHERE post_status="publish" AND post_date BETWEEN '2010-01-01' AND '2010-12-31' ORDER BY comment_count DESC LIMIT 0,10;

+-------------------------------------------------------------------+---------------+

| post_title | comment_count |

+-------------------------------------------------------------------+---------------+

| Configurer VPNTunnel sous Ubuntu | 58 |

| Installation et test de Flumotion 0.8 | 46 |

| Script d'installation automatique de Nagios | 42 |

| Spideroak, un sérieux concurrent à Dropbox | 40 |

| Utiliser Nmap pour générer vos fichiers de configuration Nagios | 38 |

| Installation d'un serveur OpenVPN sous Debian/Ubuntu | 36 |

| 12 étapes pour optimiser les performances de son blog WordPress | 35 |

| Le blog de Nicolargo a son application IOS | 35 |

| Tu fais quoi après l'installation de Firefox ? | 26 |

| MyScreenCast, comment faire du screencast avec GStreamer | 26 |

+-------------------------------------------------------------------+---------------+

10 rows in set (0.01 sec)

Top 10 des lecteurs ayant le plus posté de commentaires sur le blog en 2010

Enfin une requête un peu plus complexe qui donne le classement des plus gros "commentateurs" sur le blog.

mysql> SELECT comment_author,COUNT(comment_count) AS F01 FROM wp_comments,wp_posts WHERE comment_approved=1 AND comment_post_ID=ID AND comment_date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY comment_author ORDER BY F01 DESC LIMIT 0,10;+----------------+-----+

| comment_author | F01 |

+----------------+-----+

| NicoLargo | 315 |

| Albert | 22 |

| Pierre-Yves | 18 |

| ninja21a | 18 |

| Mr Xhark | 15 |

| Sylvain | 14 |

| Christophe | 12 |

| floppy84 | 12 |

| Nico | 11 |

| Guillaume | 10 |

+----------------+-----+

10 rows in set (0.07 sec)

Si vous avez d'autres requêtes MySQL dans votre besace et bien faites tourner !