Mostra la font per a Estadístiques d'activitat. Procedures amb MySQL

__TOC__
=Introducció=
Vull fer estadístiques de l'activitat de la wiki a través dels mesos. La manera més fàcil, que ja ho havia fet, és mirar el tamany dels fitxers de còpies de seguretat (.dmp). L'únic problema és que em convé eliminar els fitxers a mida que passa el temps, que ja no són útils. 

Ho faré mirant la informació que hi ha a la base de dades, principalment en la taula ''revision''.
<pre>
mysql -u root -p**** wikidb
</pre>
<pre>
mysql> desc revision;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| rev_id         | int(8) unsigned     | NO   | PRI | NULL    | auto_increment | 
| rev_page       | int(8) unsigned     | NO   | PRI |         |                | 
| rev_text_id    | int(8) unsigned     | NO   |     |         |                | 
| rev_comment    | tinyblob            | NO   |     |         |                | 
| rev_user       | int(5) unsigned     | NO   | MUL | 0       |                | 
| rev_user_text  | varchar(255)        | NO   | MUL |         |                | 
| rev_timestamp  | char(14)            | NO   | MUL |         |                | 
| rev_minor_edit | tinyint(1) unsigned | NO   |     | 0       |                | 
| rev_deleted    | tinyint(1) unsigned | NO   |     | 0       |                | 
+----------------+---------------------+------+-----+---------+----------------+

select count(*) from page;
779

select page_id,page_title,page_len from page limit 10;
+---------+-----------------------------------------------------+----------+
| page_id | page_title                                          | page_len |
+---------+-----------------------------------------------------+----------+
|       1 | WikiJoan                                            |    18576 | 
|       2 | Pàgina_principal                                   |       22 | 
|       3 | Xuleta_MediaWiki                                    |     3336 | 
|       4 | Logo-Edubuntu.png                                   |        3 | 
</pre>
per saber el tamany actual aproximat del contingut dels articles, podria fer:
<pre>
mysql> select now(),sum(page_len) from page;
+---------------------+---------------+
| now()               | sum(page_len) |
+---------------------+---------------+
| 2010-06-04 12:56:10 |       3826434 | 
+---------------------+---------------+
</pre>
aquesta select la podria executar cada setmana des del cron i ficar-la en una taula. Ara bé, si vull inferir aquesta informació per als mesos anteriors, la única manera és utilitzar la taula revision i pensar que totes les revisions han tingut una mida aproximada.

Revisions que ha tingut la pàgina 3:
<pre>
select * from revision limit 10;

select rev_id, rev_page, rev_timestamp from revision where rev_page=3;
...
|     33 |        3 | 20081024095201 | 
|    214 |        3 | 20081030090450 | 
|    215 |        3 | 20081030090609 | 
|    303 |        3 | 20081103071832 | 
|    304 |        3 | 20081103073256 | 
|   1562 |        3 | 20090202200812 | 
...
</pre>
i la pàgina 3 té un tamany de 3336k. Per tant, cada revisió puc pensar que ha incrementat la pàgina en 3336/6 (vàlid a nivell estadístic), i tinc el timestamp de la revisió, per tant puc deduir un històric de lo que augmenta la informació de la base de dades.

El problema s'ha de poder resoldre, però és massa complicat per fer-ho amb una sola select o subconsultes. La solució passa per programar procedures i cursors, que no he fet mai amb MySQL. Per tant, aquesta és l'excusa per fer-ho per primer cop amb MySQL. La primera prova falla:
<pre>
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM page;
END;
//
</pre>
i ara ja podem executar el script:
<pre>
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 780  | 
+------+
1 row in set (0.00 sec)
</pre>

Finalment, després de fer bastantes proves, ja tinc un cursor que em funciona:
<pre>
delimiter //

DROP PROCEDURE curdemo //

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE _output TEXT DEFAULT '';

  DECLARE done INT DEFAULT 0;
  DECLARE a INT;
  DECLARE b CHAR(50);

  DECLARE cur1 CURSOR FOR SELECT page_id,page_title FROM page where page_len>0  limit 10;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO a, b;
      SELECT a,b;
  UNTIL done END REPEAT;


  CLOSE cur1;
END;
//


CALL curdemo() //
</pre>

aquest és el codi mínim per fer dos cursors, un a dins de l'altre (aquest codi de moment no fa res, només obre els dos cursors, un el màster i l'altre l'esclau):
<pre>
delimiter //

DROP PROCEDURE wiki_stats //

CREATE PROCEDURE wiki_stats()

BEGIN

  DECLARE id_page INT;
  DECLARE size INT;
  DECLARE id_page2 INT;
  DECLARE dia_hora CHAR(50);

  DECLARE flag1 VARCHAR(5) DEFAULT 'START' ;
  DECLARE flag2 VARCHAR(5) DEFAULT 'START' ;

  DECLARE cur1 CURSOR FOR SELECT page_id,page_len FROM page where page_len>0  limit 10;
  DECLARE cur2 CURSOR FOR SELECT rev_page, rev_timestamp from revision where rev_page=id_page;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag1 = 'END';

  OPEN cur1;

  WHILE flag1<>'END' DO
    FETCH cur1 INTO id_page, size;
    IF flag1 <> 'END' THEN
      SELECT id_page;
       OPEN cur2;
       SET flag2 = flag1;
       WHILE flag1 <> 'END' DO
         FETCH cur2 INTO id_page2,dia_hora;
         IF flag1 <> 'END' THEN
           SELECT id_page2, dia_hora;
         END IF;
       END WHILE;

     CLOSE cur2;

    SET flag1 = flag2;
    END IF;
  END WHILE;

  CLOSE cur1;

END;
//

CALL wiki_stats() //
</pre>
Proves per extreure la informació del camp timestamp:
<pre>
SELECT rev_timestamp, LEFT(rev_timestamp, 4), MID(rev_timestamp, 5,2), MID(rev_timestamp, 7,2), MID(rev_timestamp, 9,2), MID(rev_timestamp, 11,2), MID(rev_timestamp, 13,2)  FROM revision where rev_page=116;
</pre>
Ara ja puc fer un procediment que serveixi per alguna cosa. Omplo la taula wiki
_stats amb la informació del tamany que tenia una pàgina en un mes concret. D'aquesta manera puc veure l'evolució en tamany d'informació d'una pàgina (amb validesa estadística, no real):
<pre>
CREATE TABLE wiki_stats(
id_page int,
year smallint,
month smallint,
size int
) TYPE=InnoDB;
</pre>
<pre>
delimiter //

DROP PROCEDURE wiki_stats //

CREATE PROCEDURE wiki_stats()

BEGIN

  DECLARE id_page INT;
  DECLARE size INT;
  DECLARE num_revisions INT;
  DECLARE id_page2 INT;
  DECLARE dia_hora CHAR(50);
  DECLARE increment DECIMAL(8,2);
  DECLARE size_acumulat DECIMAL(8,2);

  DECLARE flag1 VARCHAR(5) DEFAULT 'START' ;
  DECLARE flag2 VARCHAR(5) DEFAULT 'START' ;

  DECLARE cur1 CURSOR FOR SELECT page_id,page_len, count(*) num FROM page, revision where page_id=rev_page and page_len>0 GROUP BY page_id,page_len; -- limit 10
  DECLARE cur2 CURSOR FOR SELECT rev_page, rev_timestamp from revision where rev_page=id_page;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag1 = 'END';

  DELETE FROM wiki_stats;

  OPEN cur1;

  WHILE flag1<>'END' DO
    FETCH cur1 INTO id_page, size, num_revisions;
    SET size_acumulat = 0;
    IF flag1 <> 'END' THEN
      -- SELECT id_page;
      SET increment = size / num_revisions;
       OPEN cur2;
       SET flag2 = flag1;
       WHILE flag1 <> 'END' DO
         FETCH cur2 INTO id_page2,dia_hora;
         IF flag1 <> 'END' THEN
           -- SELECT id_page2, dia_hora;
           SET size_acumulat = size_acumulat + increment;
           INSERT INTO wiki_stats VALUES(id_page2, LEFT(dia_hora, 4)+0,MID(dia_hora, 5,2)+0,ROUND(size_acumulat));
         END IF;
       END WHILE;

     CLOSE cur2;

    SET flag1 = flag2;
    END IF;
  END WHILE;

  CLOSE cur1;

END;
//

CALL wiki_stats() //
</pre>
<pre>
SELECT page_id,page_len, count(*) num FROM page, revision where page_id=rev_page and page_len>0 GROUP BY page_id,page_len limit 10 ;
</pre>
Per cada pàgina, com ha evolucionat la seva informació amb el temps
<pre>
SELECT id_page,year,month,max(size) FROM wiki_stats GROUP BY id_page,year,month order by id_page,year,month;
</pre>

Què és una revisió? Quan es crea una nova fila a la taula? Aquesta informació encara no la tinc clara. Sempre que modifico una pàgina s'inserta una línia en la taula revisio. Suposo que no (comprovar).

Finalment, ara ja puc agrupar el total per cada mes, per tal de treure una estadística de com ha anat evolucionant el volum d'informació en la wiki:
<pre>
CREATE VIEW evolucio_tamany_pag AS SELECT id_page,year,month,max(size) as tamany FROM wiki_stats GROUP BY id_page,year,month order by id_page,year,month;

SELECT year,month,SUM(tamany) from evolucio_tamany_pag GROUP BY year,month ORDER BY year,month;

+------+-------+-------------+
| year | month | SUM(tamany) |
+------+-------+-------------+
| 2008 |    10 |      135374 | 
| 2008 |    11 |      520909 | 
| 2008 |    12 |      141472 | 
| 2009 |     1 |      244809 | 
| 2009 |     2 |      461239 | 
| 2009 |     3 |      282417 | 
| 2009 |     4 |      697701 | 
| 2009 |     5 |      322949 | 
| 2009 |     6 |      147404 | 
| 2009 |     7 |      202464 | 
| 2009 |     9 |      206520 | 
| 2009 |    10 |      467842 | 
| 2009 |    11 |      858343 | 
| 2009 |    12 |      245407 | 
| 2010 |     1 |      206324 | 
| 2010 |     2 |      430448 | 
| 2010 |     3 |      356278 | 
| 2010 |     4 |      322640 | 
| 2010 |     5 |      404099 | 
| 2010 |     6 |      130314 | 
+------+-------+-------------+
20 rows in set (0.00 sec)
</pre>
És correcte aquest resultat? Primer de tot, fixem-nos que no hi ha dades de l'agost del 2009, totalment correcte perquè el servidor estava caigut i el Pere no tenia ni un mes. Què significa la següent línia?:
<pre>
| 2010 |     6 |      130314 | 
</pre>
Doncs significa que totes les pàgines que he tocat el juny del 2010 tenen un total de 130 K, però aquesta informació no em serveix per al meu objectiu, que és els bytes acumulats més a més. Cada mes hauria d'omplir una línia de la següent taula:
<pre>
CREATE TABLE wiki_stats_acum(
year smallint,
month smallint,
size int
) TYPE=InnoDB;
</pre>

Per exemple, per a l'octubre del 2008 (10-2008): pàgines que he toca fins l'octubre del 2008
<pre>
select * from wiki_stats where year <= 2008 and month <= 10;
o millor
 select * from evolucio_tamany_pag where year <= 2008 and month <= 10;
</pre>
Si ara acumulo el total de cada pàgina, sabré el tamany total del contingut. El total de volum d'informació de la bd l'octubre del 2008 era de:
<pre>
select sum(tamany) from evolucio_tamany_pag where year <= 2008 and month <= 10;
135 K
</pre>
ara em situo en el mes següent: novembre 2008:
<pre>
select * from wiki_stats where year <= 2008 and month <= 11;
o millor:
select * from evolucio_tamany_pag where year <= 2008 and month <= 11;
</pre>
però ara compte com faig la suma, doncs no puc sumar la mateixa pàgina: (per ex, la pàg 42 es va editar l'octubre i el novembre). He de destriar el màxim de cada pàgina:
<pre>
select id_page, max(tamany)  from evolucio_tamany_pag where year <= 2008 and month <= 11 GROUP BY id_page;

+---------+-------------+
| id_page | max(tamany) |
+---------+-------------+
|       1 |        6062 | 
|       2 |          22 | 
|       3 |        2535 | 
|       4 |           3 | 
|       5 |        9916 | 
</pre>
i ara sí que puc fer la suma.

Per tant, per acabar, la idea és fer un procediment que li passis el mes i l'any, i et fa tota aquesta suma i la fica dins la taula wiki_stats_acum, que és la taula que necessitaré per fer la gràfica.
<pre>
delimiter //

DROP PROCEDURE wiki_stats_acum //

CREATE PROCEDURE wiki_stats_acum (IN year2 INT, IN month2 INT)
BEGIN
  DECLARE size INT;
  DECLARE size_total INT UNSIGNED DEFAULT 0;
  DECLARE flag1 VARCHAR(5) DEFAULT 'START' ;
  DECLARE cur1 CURSOR FOR select max(tamany)  from evolucio_tamany_pag where (year*12 + month) <= (year2*12 + month2) GROUP BY id_page;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag1 = 'END';

  OPEN cur1;
  WHILE flag1<>'END' DO
    FETCH cur1 INTO size;
    SET size_total = size_total + size;
    -- SELECT size_total ;
  END WHILE;
  CLOSE cur1;
  INSERT INTO wiki_stats_acum VALUES (year2, month2, size_total);

END;
//
</pre>
<pre>
CALL wiki_stats_acum(2008,10) //
CALL wiki_stats_acum(2008,11) //
CALL wiki_stats_acum(2008,12) //
CALL wiki_stats_acum(2009,1) //
CALL wiki_stats_acum(2009,2) //
CALL wiki_stats_acum(2009,3) //
CALL wiki_stats_acum(2009,4) //
CALL wiki_stats_acum(2009,5) //
CALL wiki_stats_acum(2009,6) //
CALL wiki_stats_acum(2009,7) //
CALL wiki_stats_acum(2009,8) //
CALL wiki_stats_acum(2009,9) //
CALL wiki_stats_acum(2009,10) //
CALL wiki_stats_acum(2009,11) //
CALL wiki_stats_acum(2009,12) //
CALL wiki_stats_acum(2010,1) //
CALL wiki_stats_acum(2010,2) //
CALL wiki_stats_acum(2010,3) //
CALL wiki_stats_acum(2010,4) //
CALL wiki_stats_acum(2010,5) //
</pre>
<pre>
SELECT * FROM wiki_stats_acum order by year,month;

+------+-------+---------+
| year | month | size    |
+------+-------+---------+
| 2008 |    10 |  137573 | 
| 2008 |    11 |  638656 | 
| 2008 |    12 |  724944 | 
| 2009 |     1 |  860140 | 
| 2009 |     2 | 1082540 | 
| 2009 |     3 | 1257477 | 
| 2009 |     4 | 1864286 | 
| 2009 |     5 | 2052130 | 
| 2009 |     6 | 2083192 | 
| 2009 |     7 | 2182424 | 
| 2009 |     8 | 2182424 | 
| 2009 |     9 | 2288744 | 
| 2009 |    10 | 2511852 | 
| 2009 |    11 | 2953352 | 
| 2009 |    12 | 3036565 | 
| 2010 |     1 | 3192675 | 
| 2010 |     2 | 3392391 | 
| 2010 |     3 | 3521430 | 
| 2010 |     4 | 3694670 | 
| 2010 |     5 | 3801415 | 
+------+-------+---------+
</pre>
'''Nota''': recordar que és millor utilitzar l'opció -s en arrencar el client de mysq i així m'evito les engorroses línies:
<pre>
$ mysql -s -u root -p wikidb117
mysql> SELECT * FROM wiki_stats_acum order by year,month;
...
2011	4	4496752
2011	5	4591316
2011	6	4675005
2011	7	4753610
2011	8	4785711
2011	9	4952189
</pre>
(com a cosa curiosa, hi ha mesos que disminueix el volum d'informació.)

i ara ja puc fer fàcilment una gràfica de les estadístiques. Si vull automatitzar aquesta tasca, hauré de fer que s'executi aquests procediments en un cron (tasca mensual), i la publicació de les estadístiques la puc fer en la pàgina de Langtrainer amb Open Flash Chart.

En la gràfica es pot veure l'evolució del tamany del contingut de la wiki, de l'octubre de 2008 al setembre de 2011:
[[Fitxer:Wiki 2008 2011.png]]

===ToDo (coses a millorar)===
*Com és que fico page_len>0?? Què vol dir que hi ha pàgines que page_len=0 (deuen ser Kb)
*les imatges/pdf també són pages, i no les hauria de tenir en compte a l'hora de calcular el volum d'informació
*m'hauria de centrar en el namespace=0 (encara que de fet no hi ha d'haver res en el namespace=1)
=Activitat durant el COVID-19=
<pre>
mysql> select distinct page_title 'COVID-19 editons' from page, revision where page_id=rev_page and rev_timestamp >= '20200313%' and page_namespace=0;
+--------------------------------------------------------------------+
| COVID-19 editons                                                   |
+--------------------------------------------------------------------+
| Llibres_electrònics_i_l'editor_Sublime_Text2._Convertir_pdf_a_txt |
| Esglésies_romàniques_de_Catalunya                                |
| Aplicació_web_amb_motor_Python                                    |
| Pàgina_principal                                                  |
| Mapes_medievals,_mapes_ficticis,_mapes_de_fantasia                 |
| Fusionar_GeoJSON                                                   |
| Opendata-barcelona                                                 |
| Openlayers:_ol_package._Exemple_Casa_de_Papel                      |
| DAW-M06-WEC                                                        |
| React:_A_JavaScript_library_for_building_user_interfaces           |
| JQuery:_Tablesorter                                                |
| React_Native                                                       |
| Github_pràctic:_control_de_versions                               |
| Desplegar_un_projecte_web_amb_Express_i_Heroku                     |
| Aplicació_Reactjs_a_través_del_servidor_web_Express              |
| Openlayers_amb_ReactJS                                             |
| ReactJS._Curs_EdX._Introduction_to_ReactJS._Microsoft:_DEV281x     |
| Curriculum_Vitae_Joan_Quintana_Compte                              |
| Introducció_als_frameworks_de_Javascript                          |
| React_CRUD_tutorial                                                |
| Exemples_Openlayers_(ol_package)                                   |
+--------------------------------------------------------------------+
21 rows in set (0.00 sec)
</pre>

{{Autor}}, juny 2010, octubre 2011, abril 2020
[[Category: MySQL]]