Ici un petit travail pratique pour réaliser le tableau d’analyse 20/80 pareto et ABC avec SQL. Le travail sera d’afficher le tableau qui permet de contruire les diagramme 20/80.
Dans ce TP l’étude sera faite sur les recettes des produits et nous considérerons que les 20% des produits génerent 80% des recettes. Ce sont ces produits qu’il faudra privilegié dans les efforts de qualité de production et d’objectif de ventes.
Télécharger la base de données
Télécharger le SGBD sqlite (DB Browser for sqlite)
L’analyse ABC et 20/80 sont deux méthodes de contrôle de qualité en amont.
Notre table aura trois attributs l’id du produit, le nom du produit et les recettes.
Commençons par insérer des articles dans la base de données:
INSERT INTO produit(nom,Recette) VALUES (« CEINTURE »,15750)
PROBLEMES A RESOUDRE
Notre requette SQL doit être capable de calculer la proportion des recettes de chaque produit par rapport aux recettes totales. Notre requete doit ranger les produits par ordre décroissant des recettes. Et enfin notre requete doit être capable de calculer les cumuls progressifs.
Cela fait en tout 3 problèmes. En résolvant ces problèmes nous devrions obtenir une vue de ce genre
Nous avons bien le cumul des fréquences jusqu’à 1 (100%)
Premier problème (Les fréquences ou proportions) et rangement par ordre décroissant
Nous allons utiliser une table intermédiaire appelée PARETO avec la syntaxe WITH
WITH PARETO AS (SELECT id,Nom,Recette,Recette/(SELECT round(Sum(Recette),0) FROM PRODUIT) as Frequence FROM produit ORDER BY Recette DESC )
SELECT * FROM PARETO
Explication du code:
Recette/(SELECT round(Sum(Recette),0) FROM PRODUIT) as Frequence permet d’afficher la proportion nommée fréquence. La fonction round force la calcul en décimal
ORDER BY Recette DESC permet de ranger les produit par ordre décroissant des recette.
Dernier problème les cumuls
En SQL pour obtenir les cumuls il faut utiliser ce qu’on appelle des requêtes récursives
Pour élucider le concept essayons d’afficher les cumul des recettes de notre table
SELECT Nom,Recette,(SELECT SUM(Recette) FROM produit as P2 where P2.id <= P1.id ) AS CUMUL FROM Produit AS P1
Explication : nous avons ici la table produit qui est nommé différement P1, et P2. Et à l’intérier de la requete nous avons fait appel à P2 de la même table. P1 est la table produit en totalité. Mais P2 est pour chaque ligne de la Table produit une section qui n’inclus pas les produits suivants. C’est la raison pour laquelle il faut utiliser la contrainte » where P2.id <= P1.id «
Voici donc la requête complète qui permet d’afficher le cumul des proportions pour une analyse ABC ou 20/80
Requette complète
WITH PARETO AS (SELECT id,Nom,Recette,Recette/(SELECT round(Sum(Recette),0) FROM PRODUIT) as Frequence FROM produit ORDER BY Recette DESC )
SELECT Nom,Recette,Frequence, (SELECT SUM(P.Frequence) FROM PARETO as P where P.Recette >= G.Recette ) AS CUMUL FROM PARETO AS G
Télécharger la base de données
Télécharger le SGBD sqlite (DB Browser for sqlite)