[PHP 7] Crosstab sur un array

Crosstab sur un array

Bonjour,

J'ai un array de 3 colonnes (ref, date, valeur) comme ça :

Ref Date Valeur

REF01 Janvier 1000

REF01 Fevrier 2000

REF01 Mars

REF02 Janvier 500

REF02 Fevrier 600

REF02 Mars 400

REF02 Avril 450

REF03 Mars 10

REF03 Avril 30

Ce que je cherche à faire, c'est un pivot "Date" et un groupby "Ref" pour avoir le résultat ci-dessous.
Les données sont normalement triées par Ref puis Date, mais je ne peux pas garantir que c'est toujours le cas.

Ref Janvier Fevrier Mars Avril

REF01 1000 2000

REF02 500 600 400 450

REF03 10 30

A travers des boucles, je sais faire. Mon problème c'est d'optimiser au mieux sur des centaines de milliers de lignes.
Si vous avez un bout de code (php, ou javascript sans passer par un truc tout fait type lib), je suis preneur

Merci de votre aide.

PS : J'ai modifié le titre pour plus de précision après avoir trouvé le terme Crosstab, mais je ne trouve que des tools et rien en pure PHP ou Javascript.

Et d'où proviennent ces centaines de milliers de "lignes"?

Citation:

Envoyé par CosmoKnacki

Et d'où proviennent ces centaines de milliers de "lignes"?

D'une requête qui fait appel à une fonction SQL que je ne peux pas modifier (droit d'accès). Même avec le sgbd (postgresql), ça prend environ 30 minutes de traitement pour une extraction de 25 millions de lignes de données dans de rares cas.
J'ai simplifié le tableau car en fait, c'est des données sur plusieurs années (parfois mensuelle, journalières, horaires). Donc le pivot "Date" n'est pas toujours Janvier, Février,... et peut contenir environ 24x365=8760 colonnes
La moulinette peut prendre du temps, mais c'est pas grave, le résultat sera déposé dans un fichiers sur un FTP + un email pour prévenir. Mais ça c'est du détail.

Je pose la question pour savoir comment procéder sans tools externe et soit en PHP ou en javascript.

Bon, j'ai une petite idée de comment je vais m'y prendre en php : (pour JS, je pense faire la même chose)

1) je vais créer un array contenant l'ensemble des "Ref" trié unique avec array_unique.
1) je vais créer un array contenant l'ensemble des "Date" trié unique avec array_unique.
2) je vais créer un arrayDest au dimension de 1) et 2)
3) je vais lister chaque ligne du arraySource chercher l'index de Ref et Date, ça me donnera les index (Y et Y)
4) La valeur de la ligne en cours sera placé dans le arrayDest au coordonnées X et Y

Je pense que c’est une des solutions les plus simple et me semble la plus rapide même si le array source n'est pas trié

Bonjour,

Tu pourras nous montrer ton code quand tu auras terminé ?
Quand tu auras fini, dis-nous aussi le temps que ça met en PHP avec ta solution. Ca m'intéresse.
Mais vraiment SQL est FAIT pour traiter ça.

Citation:

Donc le pivot "Date" n'est pas toujours Janvier, Février,... et peut contenir environ 24x365=8760 colonnes

Colonnes ???? Genre une colonne par jour ? Tu es sûr ? Alors c'est qu'il y a un problème de modélisation.
Je suppose que tu veux dire valeurs possibles de date, et donc le type de la colonne date n'est pas un datetime.
Donne-nous des exemples de la variété de tes formats de date.
Tu peux nous donner un petit jeu de données, genre 100, qu'on se rende compte ?

J'ai bien peur que si "leur" procédure SQL met une demi-heure et qu'elle est bien conçue, avec des tables bien indexées etc... tu ne feras pas mieux avec PHP.

Par contre, tu pourrais faire un cron chaque nuit qui te lance le résultat une fois par jour.
Pour des données aussi énormes, je ne vois pas comment tu pourrais faire un calcul en temps réel à chaque clic client. Donc je ne vois pas pourquoi tu penses à Javascript.

Sinon en PHP, tu peux tester ceci, je l'ai déjà fait et ça marche très bien.
Via un cron, tu lances dix pages qui font le taf en parallèle cumul1.php, cumul2.php etc.
Comme ça, ça bosse en parallèle et en même temps.

Puis qui rebalancent chaque tableau vers une dernière page pour recomposer le cumul final.

Sinon, c'est quoi la boucle que tu as testée ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<?php
//tableau ou ref et date sont dans le désordre
$data = array(
0 => array('ref' => 'REF01', 'date' => 'janvier', 'valeur' => 1000),
1 => array('ref' => 'REF01', 'date' => 'janvier', 'valeur' => 245),
2 => array('ref' => 'REF01', 'date' => 'janvier', 'valeur' => 54),
3 => array('ref' => 'REF01', 'date' => 'mai', 'valeur' => 1000),
4 => array('ref' => 'REF01', 'date' => 'juillet', 'valeur' => 1000),
5 => array('ref' => 'REF01', 'date' => 'novembre', 'valeur' => 1000),
6 => array('ref' => 'REF01', 'date' => 'decembre', 'valeur' => 1000),
7 => array('ref' => 'REF02', 'date' => 'mars', 'valeur' => 1000),
8 => array('ref' => 'REF03', 'date' => 'janvier', 'valeur' => 1000),
9 => array('ref' => 'REF46', 'date' => 'novembre', 'valeur' => 1000),
10 => array('ref' => 'REF03', 'date' => 'decembre', 'valeur' => 1000),
11 => array('ref' => 'REF04', 'date' => 'janvier', 'valeur' => 1000),
12 => array('ref' => 'REF04', 'date' => 'janvier', 'valeur' => 1000),
13 => array('ref' => 'REF04', 'date' => 'novembre', 'valeur' => 1000),
14 => array('ref' => 'REF10', 'date' => 'février', 'valeur' => 1000),
15 => array('ref' => 'REF10', 'date' => 'mars', 'valeur' => 1000),
16 => array('ref' => 'REF10', 'date' => 'avril', 'valeur' => 1000),
17 => array('ref' => 'REF02', 'date' => 'janvier', 'valeur' => 700),
18 => array('ref' => 'REF24', 'date' => 'octobre', 'valeur' => 1000),
19 => array('ref' => 'REF25', 'date' => 'novembre', 'valeur' => 1000),
20 => array('ref' => 'REF30', 'date' => 'juin', 'valeur' => 1000),
21 => array('ref' => 'REF30', 'date' => 'juillet', 'valeur' => 1000),
22 => array('ref' => 'REF30', 'date' => 'août', 'valeur' => 1000),
23 => array('ref' => 'REF30', 'date' => 'septembre', 'valeur' => 1000),
24 => array('ref' => 'REF30', 'date' => 'octobre', 'valeur' => 1000),
25 => array('ref' => 'REF30', 'date' => 'novembre', 'valeur' => 1000)
);
 
$keys=array('ref','janvier','février','mars','avril','mai','juin','juillet','août','septembre','octobre','novembre','décembre');
 
$refs=array_column($data,'ref');
$refs=array_unique($refs);
sort($refs);
$cumuls=array();
$i=0;
foreach($refs as $ref){
  foreach($keys as $key){
    if($key=='ref'){
      $cumuls[$i]['ref']=$ref;
    }
    else{
      $cumuls[$i][$key]=0;
    }
    foreach($data as $row){
      if($row['ref']==$ref && $row['date']==$key){
        $cumuls[$i][$key]+=$row['valeur'];
      }
    }
  }
  $i++;
}
 
echo '<pre>';
print_r($cumuls);
echo '</pre>';

Résultat cohérent :

Code:

Ref	Date	Valeur
REF01	Janvier	1000
REF01	Fevrier	2000
REF01	Mars
REF02	Janvier	500
REF02	Fevrier	600
REF02	Mars	400
REF02	Avril	450
REF03	Mars	10
REF03	Avril	30