Regroupements à taille fixe avec MySQL

Savez-vous découper une table MySQL en groupes de taille identique?

Il existe bien la clause GROUP BY, qui permet de regrouper les lignes en fonction d'un critère commun : par exemple,

SELECT left(nom, 1), count(*) FROM annuaire GROUP BY left(nom, 1);

Cette requête permet de compter les personnes enregistrées dans l'annuaire dont le nom commence par une lettre particulière. Mais cela ne va pas donner des groupes de taille identique, à moins que vous ayez la chance d'avoir autant de personnes dont le nom commence par K et W que par A et S : en gros, aucune.


En fait, ce que je cherche ici est un équivalent SQL de la fonction array_chunk() de PHP. Cette fonction découpe un tableau (en SQL, une table), en une série de petits tableaux, comme ceci :

<?php
 
$tableau = range(0,18);
 
$petits = array_chunk($tableau, 4);
 
print_r($petits);
 
?>
 

On obtient ceci :

Array
 
(
 
    [0] => Array
 
        (
 
            [0] => 0
 
            [1] => 1
 
            [2] => 2
 
            [3] => 3
 
        )
 
 
 
    [1] => Array
 
        (
 
            [0] => 4
 
            [1] => 5
 
            [2] => 6
 
            [3] => 7
 
        )
 
 
 
    [2] => Array
 
        (
 
            [0] => 8
 
            [1] => 9
 
            [2] => 10
 
            [3] => 11
 
        )
 
 
 
    [3] => Array
 
        (
 
            [0] => 12
 
            [1] => 13
 
            [2] => 14
 
            [3] => 15
 
        )
 
 
 
    [4] => Array
 
        (
 
            [0] => 16
 
            [1] => 17
 
            [2] => 18
 
        )
 
 
 
)
 

4 tableaux contenant 4 éléments, et le dernier tableau contenant 3 éléments, puisque nous avons commencé avec un tableau de 19 éléments.

Question : comment faire en SQL?

Evidemment, GROUP BY est notre allié dans cette mission. Il faut simplement arriver à lui donner un compteur de lignes, et non pas un critère à géométrie variable. La première idée qui me vient est d'utiliser la division entière, pour regrouper les informations par dividende.

mysql> SELECT group_concat(i) FROM table GROUP BY round(id / 4, 0);
 
+------------------+
 
| group_concat(id) |
 
+------------------+
 
| 0,1,2,3          | 
 
| 7,6,5,4          | 
 
| 8,9              | 
 
+------------------+
 
3 rows in set (0.54 sec)
 

group_concat() sert ici à montrer les différentes lignes qui ont été sélectionnées pour chaque groupe. Le résultat est bien identique à celui de array_chunk().

Toutes fois, les colonnes de type auto_increment ne contiennent pas de données ausi linéaires que présenté ci-dessus : au mieux, il y a des trous. On risque donc de se trouver avec un résultat comme celui-ci :

+------------------+
 
| group_concat(id) |
 
+------------------+
 
| 0,1,2            | 
 
| 4,6,7            | 
 
| 9,8,11           | 
 
| 13,15            | 
 
+------------------+
 
4 rows in set (0.00 sec)
 

Les groupes font maintenant au plus 4, mais ce n'est pas ce que nous cherchons.

La première solution va être de rétablir la séquence dans la colonne id. Pour ne pas perturber la colonne (on ne va pas la réindexer), on peut ajouter une table de jointure.

mysql> CREATE TABLE table_id (id tinyint unsigned unique auto_increment, eid int);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> insert into table_id select 0, i from table;
 
Query OK, 11 rows affected (0.00 sec)
 
Records: 11  Duplicates: 0  Warnings: 0
 
 
 
mysql> select * from table_id;
 
+----+------+
 
| id | eid  |
 
+----+------+
 
|  1 |    0 | 
 
|  2 |    1 | 
 
|  3 |    2 | 
 
|  4 |   13 | 
 
|  5 |    4 | 
 
|  6 |   15 | 
 
|  7 |    6 | 
 
|  8 |    7 | 
 
|  9 |    8 | 
 
| 10 |    9 | 
 
| 11 |   11 | 
 
+----+------+
 
11 rows in set (0.00 sec)
 

La colonne qui nous intéresse est la première colonne, tandis que la colonne eid contient les identifiants associés. On peut maintenant reprendre la requête précédente, avec une jointure en plus.

SELECT group_concat(i) FROM entiers2 join entiers2_id on entiers2_id.eid = entiers2.i GROUP BY round((id + 1) / 4, 0);
 
 
 
+------------------+
 
| group_concat(id) |
 
+------------------+
 
| 0,1,2,13         | 
 
| 4,15,6,7         | 
 
| 8,9,11           | 
 
+------------------+
 
3 rows in set (0.00 sec)
 

Nous retrouvons bien nos groupes de 4 lignes à chaque fois. Ne vous laissez pas impressionner par le fait que 13 fait partie du groupe 1 : nous aurions pu classer les lignes lors de la création de la table d'assistance, pour nous assurer de l'ordre, mais comme ce dernier n'est pas dans les pré-requis, on l'a ignoré.

Cette solution peut être améliorée en ajoutant un index pour la jointure : vous l'aurez remarqué. On peut aussi gagner en calculant directement le groupe avec ROUND dès la création de la table : cela évitera de le faire durant la requête finale.

mysql> CREATE TABLE table_id (id tinyint , eid int);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> set @a := 1;
 
 
 
mysql> insert into table_id select round((@a := @a + 1) / 4,0), i from table;
 
Query OK, 11 rows affected (0.00 sec)
 
Records: 11  Duplicates: 0  Warnings: 0
 
 
 
mysql> select * from table_id;
 
+------+------+
 
| id   | eid  |
 
+------+------+
 
|    1 |    0 | 
 
|    1 |    1 | 
 
|    1 |    2 | 
 
|    1 |   13 | 
 
|    2 |    4 | 
 
|    2 |   15 | 
 
|    2 |    6 | 
 
|    2 |    7 | 
 
|    3 |    8 | 
 
|    3 |    9 | 
 
|    3 |   11 | 
 
+------+------+
 
11 rows in set (0.00 sec)
 

La requête finale devient :

SELECT group_concat(i) FROM entiers2 join entiers2_id on entiers2_id.eid = entiers2.i GROUP BY id;
 

et le résultat est le même. Cette approche sera plus intéressante si vous devez utiliser plusieurs fois la requête de découpage, ou bien si vous voulez accéder à une des partitions de la table, indépendamment des autres. Il suffit maintenant d'ajouter une clause WHERE sur id (et un index aussi, bien sur).

Finalement, cette dernière approche nous donne une solution à notre problème initial en une seule requête : encore une fois, ce sont les variables MySQL qui viennent à la rescousse.

set @a := 1;
 
select @a, group_concat(i) from entiers2 group by round((@a := @a + 1) / 4,0);
 
 
 
+------+-----------------+
 
| @a   | group_concat(i) |
 
+------+-----------------+
 
|    2 | 0,1,2,13        | 
 
|    6 | 4,15,6,7        | 
 
|   12 | 11,8,9          | 
 
+------+-----------------+
 
3 rows in set (0.00 sec)
 

Cette approche nous évite la table externe au prix d'une variable supplémentaire. Sa valeur nous est complètement indifférente, tant que nous arrivons à obtenir le regroupement par bloc de 4.

En conclusion, n'oubliez jamais les variables MySQL : elles sont très souvent utiles pour produire des calculs intermédiaires.

Et n'oubliez pas non plus qu'une requête SQL est aussi un petit programme en soit, capable de faire des calculs intermédaires (@a := @a + 1), et avec un ordre d'exécution important : il n'était pas possible de mettre l'affectation précédente dans la liste des colonnes, car l'affichage se fait après le regroupement.

Quand à faire un GROUP BY en PHP, il n'existe pas d'autre outils qu'une bonne vieille boucle.