Comment ajouter une colonne de pourcentage Changement (non Points de pourcentage ) dans MySQL? Il y a une table avec une colonne de modification des percents: p> Comment écrire une requête qui calcule une variation totale de valeur d'une valeur pour chaque ligne afin que la ligne calculée exprime donc la rangée calculée. son pourcentage de changement et toutes les lignes précédentes de changement de pourcentage?. p> Résultat attendu: p> où le alors à la deuxième ligne C'est exactement ce que je suis après un Voici la table de test à reproduire: P> nominal_value code> est un arbitraire valeur qui a été modifiée par pour cent code> pour la première ligne si la valeur nominale était de 1,0 (100%) mais a été modifiée par -0.50 code> ( -50% code>) Il a entraîné une valeur nominale 0.5 code>. p> pourcentage code> modification était +0.50 code> ( +50% code>) de sorte que la valeur nominale a été augmentée de la moitié de celle-ci 0.5 => 0,75 code> mais on peut également dire qu'il était juste abaissé par -0,25 code > ( -25% code>) de son ou Valeur Iginal depuis depuis 1.0 code> à 0,75 code> est un -0,25 code> ( -25% code>) de 1.0 . p> total_percent code> modifier, le nominal_value code> était juste pour le but explicatif et n'est pas nécessaire. P> J'utilise MySQL 8 afin que la requête puisse utiliser les fonctions / gammes de fenêtres, etc. p> CREATE TABLE IF NOT EXISTS test
(
percent DECIMAL(5,2) NOT NULL
)
ENGINE = InnoDB
;
INSERT INTO test (percent) VALUES
(-0.50)
,(0.50)
,(1.00)
,(-0.20)
,(0.50)
,(-1.0)
,(-2.0)
,(0.75)
,(1.0)
,(0.50)
;
4 Réponses :
DROP TABLE IF EXISTS test;
CREATE TABLE test
( id SERIAL PRIMARY KEY
, percent DECIMAL(5,2) NOT NULL
);
INSERT INTO test (percent) VALUES
(-0.5)
,(0.5)
,(1)
,(-0.2)
,(0.5)
,(-1)
;
SELECT ROUND(@i:=(@i+(@i*percent)),2)n
FROM test
, (SELECT @i:=1) vars
ORDER
BY id;
+------+
| n |
+------+
| 0.50 |
| 0.75 |
| 1.50 |
| 1.20 |
| 1.80 |
| 0.00 |
+------+
6 rows in set (0.00 sec)
mysql>
Cette requête vous donnera les résultats souhaités. Il utilise deux CTES, le premier qui ajoute simplement un numéro de ligne aux données et la seconde TPC récursive qui génère les valeurs pour faire ce travail (et n'importe quelle autre requête) De manière fiable, il doit y avoir une clé primaire que le premier CTE peut avoir ses résultats commandés par. Dans la démo, j'ai ajouté un sortie: nominal_value à partir du texte actuel et de la précédente < Code> nominal_value (où le précédent est défini par numéro de ligne). Enfin, total_percent est calculé à partir du nominal_value . auto_incrènement int Colonne ID à cette fin.
Cela a bien fonctionné sur les données fournies avec la question, toutefois, lorsque vous testez à Bigger Data Set gagnez cette erreur Erreur 3636 (HY000): requête récursive abandonnée après 1001 itérations. Essayez d'augmenter @@ cte_max_recursion_depth à une valeur plus grande. est inévitable pour utiliser la récursion dans la requête. Peut-être que c'est faute d'index?
@JimMix Oui, vous devez utiliser une requête récursive en raison du processus itératif de création des valeurs nominal_value . Mais il n'y a pas de mal à régler cette variable assez grande pour couvrir toutes les lignes de votre table.
J'aimerais éviter de définir cette variable assez haut parce que si je comprends une raison derrière celle-ci, c'est une protection contre une course proche de la course infinie de requêtes mal écrites. J'ai essayé de réécrire votre requête, mais j'ai échoué. L'utilisation de la combinaison de la gamme () / Tableau temporaire / variables peut-elle être une solution?
@JimMix Vous pouvez toujours définir une limite de temps d'exécution. Voir dev.mysql.com /doc/refman/8.0/fr/... . Le problème avec l'utilisation de variables est qu'ils sont obsolètes dans MySQL 8.0 et seront supprimés dans une version ultérieure.
Si la table TEST avait une colonne supplémentaire avec des dates uniques qui sont une clé primaire, il serait-il possible d'écrire une requête qui donne le même résultat mais n'utilise pas de variables ni de récurrence?
@Strawberry Intéressant - Pourquoi la présence de l'index change-t-elle tout? Pour être juste cependant, votre requête ne fonctionne que parce que vous avez ajouté une clé primaire Auto_incrimentation, et cette requête fonctionnerait bien avec ça aussi ...
C'est exactement mon point !! Sans une clé primaire, cette solution (toute solution) dépend entièrement du caprice du serveur.
@Strowberry a mis à jour ma réponse pour faire ce point et ajouter une clé primaire à la démo. Souffrait d'un manque de café plus tôt dans la journée, c'est pourquoi il a fallu si longtemps pour avoir votre point de vue. Merci.
@Nick a changé - -CTE-max-récursion -Depth à la valeur maximale (lignes de 4300 m), devrait suffire pendant un moment :) Je me demandais simplement s'il y a un commutateur pour désactiver cette restriction à tout causer que je n'ai trouvé aucun.
@Jimmix Je ne crois pas qu'il y ait un moyen de l'éteindre. Le réglage à 0 exclut toute récursion du tout et les valeurs négatives ne sont pas autorisées. Néanmoins, un llimit de 4300 m devrait vous garder ...
@Jimmix Juste pour garder vos options ouvertes, j'ai posté une solution alternative à l'aide d'une procédure stockée.
Il s'agit d'une faible variation de la réponse acceptée en raison de la publication des informations modifiées et d'ajouter des lignes supplémentaires de données et des résultats souhaités après acceptés ANS. a été posté et accepté:
Vous avez besoin d'une clé primaire
Je ne vois pas comment vous pouvez obtenir d'une valeur nominale de 0 à une valeur nominale de -2. Une fois que quelque chose est 0, un changement de pourcentage de cette valeur donnera 0.
Dans les premières et dernières lignes de vos données,
nominal_value est de 0,5. Toutefois, dans le premier cas,total_percent est -0.5 et dans le dernier cas, il est +0.5. Comment se peut-il?@Nick, par exemple, vous avez un solde de 100 $ sur votre compte bancaire et que vous dépensez 100% de cela, le résultat était la balance = 0 $, mais parce que votre compte dispose de la capacité de prêt, vous dépensez un autre $ 100 $ pour faire du shopping le même jour afin que vous vous retrouviez avec - 100 $ L'équilibre qui est reflété de -200% du montant de l'équilibre initial et de -100% à partir de 0 $. Un autre exemple peut être un changement de valeur de la fonction sinus qui passe de -1 via 0 à +1 et de dos à -1 croisement 0. Si vous vous êtes arrêté à 0, vous ne seriez pas en mesure d'exprimer la montée ou la chute de celle-ci dans les percents temps.
@JimMix OK, je reçois la partie de valeur négative, mais comment expliquez-vous les différentes valeurs du total_percent pour la même valeur de Nominal_Value? (à la fois 0 et 0,5 - les deux dernières lignes) ont un pourcentage total différent de celui de leurs premières occurrences
@Nick vous avez raison, merci d'avoir souligné cela. J'ai corrigé le résultat attendu avec d'autres erreurs de calcul.
@Jimmix OK, qui convient avec ce que j'attendais que les résultats soient.
@Strowberry pouvez-vous évaluer pourquoi? J'ai testé votre réponse avec la table Test sans la colonne ID et même supprimé
commander par ID et a toujours le même résultat. Avez-vous entendu des performances ou un autre problème n'est-il pas en cas d'utilisation de la clé primaire ?@jimmix Si vous n'avez pas un moyen d'identifier de manière unique des lignes, alors vous n'avez pas vraiment de table. Sans un, vous pouvez accidentellement déranger un résultat en ligne avec vos attentes, mais c'est juste aveugle de chance.