[RESOLU] Calcule du nombre d'occurances et affichage du pourcentage.

12 juil. 2017, 16:13

Bonjour,
Je souhaite calculer le nombre d'occurrences d'une chaine/identifiant dans ma base et afficher le pourcentage de chaque chaine/identifiant.

Voila mon cas :
Ma table "oiseaux" est la suivante :
id, numéro, année, ideleveur,pere, mére,idcanari

Pour l'identifiant du canari, il est calculer comme suite :
idcanari = "numéro"-"année"-"ideleveur"

A chaque enregistrement d'un oiseau, en indique les identifiant canari du pére et la mére.
Exemple d'un oiseau :
Id: 1
numéro : "002"
année : "2016"
ideleveur : AA01
pere : "010-2015-BB01"
mere : ""005-2015-BA02"
idcanari = "002-2016-AA01"

Id: 2
numéro : "005"
année : "2015"
ideleveur : BA02
pere : "010-2015-BB01"
mere : ""050-2013-BA02"
idcanari = "005-2015-BA02"

L'idée est d'utilisé une procédure récursive avec comme paramètre l'idcanari.
mon objectif est de calculer le % de consanguinité dans un pedigree.
Merci pour votre aide.

Voila l'exemple avec le résultat :

13 juil. 2017, 22:43

Bonsoir,

Attention la requête utilisée ne fonctionne que sur Mysql 8.0.

A partir de votre description je suis parti sur ce modèle :

Code : Tout sélectionner

create table oiseaux(
  id int AUTO_INCREMENT,
  numero varchar(100),
  annee varchar(100),
  ideleveur varchar(100),
  pere varchar(100),
  mere varchar(100),
  idcanari varchar(100),
  PRIMARY KEY(id)
);

Voici les données :

Code : Tout sélectionner

insert into oiseaux (numero,annee,ideleveur,pere,mere,idcanari) 
values 
('002', '2016','AA01','010-2015-BB01','005-2015-BA02','002-2016-AA01'), 
-- Pere 1
('010', '2015','BB01','011-2014-BB01','015-2014-BA02','010-2015-BB01'),
-- Grand Mere 1
('015', '2014','BA02',null,null,'015-2014-BA02'),
-- Mere 1
('005', '2015','BA02','011-2014-BB01','016-2014-BA02','005-2015-BA02'),
-- Grand Mere 2
('016', '2014','BA02',null,null,'016-2014-BA02'),
-- Grand Pere commun
('011', '2014','BB01',null,null,'011-2014-BB01');

Et voici la requête :

Code : Tout sélectionner

with recursive cte (id, idcanari, pere,mere) as
(
 select     id,
            idcanari,
            pere,
            mere
 from       oiseaux
 where      idcanari = '002-2016-AA01'
 union all
 select     p.id,
            p.idcanari,
            p.pere,
            p.mere
 from       oiseaux p
 inner join cte
         on p.idcanari = cte.pere || p.idcanari = cte.mere
)
select idcanari as 'Id',
count(*) as 'Nbr',
count(*)/(select count( distinct idcanari) from cte)*100 as '%'
from cte 
group by  idcanari 
order by count(*) desc;

14 juil. 2017, 00:38

Wooww, merci Wolan pour cette intervention de qualité, je ne savais même pas que c'était possible avec juste MySQL ! =D>

18 juil. 2017, 10:13

OHHHHHH

merci "Wolan" pour ce beau travail, je ne le savais pas que c'est possible via MySQL. BRAVO =D> =D>
J'étais entrain d'essayer de faire fonction en PHP, mais là je suis surpris . bravo
Merci encore, je vais tester et je te tiendrais au courant.

J'adore ce forum

18 juil. 2017, 11:04

Bonjour,
J'ai l'erreur suivante, mais je n'arrive pas à voir d'où ça vient :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive cte (id,idcanari,pere,mere) as (
select id,
idcanari,
pere,
mere
' at line 1

18 juil. 2017, 14:15

vérifie ta version de mysql, probablement infèrieure à 8.0

18 juil. 2017, 14:30

@Spols : effectivement, je n'ai pas bien fait attention à la la phrase de Wolan "Attention la requête utilisée ne fonctionne que sur Mysql 8.0" car j'ai bien aimé la solution qu'il m'a proposé que je n'ai pas fait attention.
Oui je suis en version 5.5 (serveur 1&1) .
Dommage car c'était une belle solution, mais j'ai un appris un nouveau truc.

07 août 2017, 14:27

07 août 2017, 14:41

Bah tu as la solution en MySQL 8.0 donc change de serveur si tu veux la faire fonctionner, ou sinon je dirai que ce n'est pas possible en SQL pur donc il va falloir que tu développes l'équivalent en PHP.
A toi d'essayer de le faire maintenant puis de nous dire là où tu bloques le cas échéant !

08 août 2017, 16:54

Bonjour,
Je peux pas changer de serveur car je suis chez 1and1.
Développer un truc en php, je n'arrive pas. j'essaye depuis avec une fonction récursive et apprendre sur le net mais ça ne fonctionne pas pour ça que j'ai posté ce message.
Ce que j'ai fait pour le moment est une fonction qui récupére les parents. Et à chaque fois je l'appel soit avec la mère ou le père . Mais c'est nul comme idée car je sais bien qu'on peut faire un truc propre en récursive.

13 août 2017, 23:52

Bonsoir,

Voici une solution possible en PHP en utilisant la même table et les mêmes données que pour la solution en Mysql.

Tout est mélangé pour que l'exemple soit court mais il faudrait bien découper la partie affichage et la partie calcul.

Code : Tout sélectionner

<?php

class Oiseau
{
    public $id;
    public $numero;
    public $annee;
    public $ideleveur;
    public $pere;
    public $mere;
    public $idcanari;
}

function calculerConsanguinite($premierCanari, $oiseau, $occurence)
{
    if ($premierCanari == false) {
        $occurence[$oiseau->idcanari] += 1;
    } else if ($premierCanari == true) {
        $premierCanari = false;
    }
    if ($oiseau->pere != null) {
        $occurence = calculerConsanguinite($premierCanari, $oiseau->pere, $occurence);
    }
    if ($oiseau->mere != null) {
        $occurence = calculerConsanguinite($premierCanari, $oiseau->mere, $occurence);
    }

    return $occurence;
}

function afficherTauxConsanguinite($occurences)
{
    $totalParents = array_sum($occurences);
    foreach ($occurences as $id => $occurence) {
        echo "<tr><td>" . $id . "</td><td>" . $occurence . "</td><td>" . round(100 * $occurence / $totalParents, 2) . "% </td></tr>";
    }

}


$user = "login";
$pass = "mdp";
$dbh = new PDO('mysql:host=127.0.0.1;dbname=canari', $user, $pass);

$PDOStatement = $dbh->query("select * from canari.oiseaux");

$PDOStatement->execute();
$oiseaux = $PDOStatement->fetchAll(PDO::FETCH_CLASS, 'Oiseau');
$oiseauxByKey = array();

foreach ($oiseaux as $oiseau) {
    $oiseauxByKey[$oiseau->idcanari] = $oiseau;
}

foreach ($oiseaux as $oiseau) {
    if ($oiseau->pere != null) {
        $oiseau->pere = $oiseauxByKey[$oiseau->pere];
    }
    if ($oiseau->mere != null) {
        $oiseau->mere = $oiseauxByKey[$oiseau->mere];
    }
}


?>
<table>
    <thead>
    <tr>
        <th>Id</th>
        <th>Nbr</th>
        <th>%</th>
    </tr>
    </thead>
    <tbody>
    <?php
        afficherTauxConsanguinite(calculerConsanguinite(true, $oiseaux[0], array()));
    ?>
    </tbody>
</table>

Bonne soirée

[RESOLU] Calcule du nombre d'occurances et affichage du pourcentage.

Qui est en ligne