Comment interroger la valeur quantile dans MySQL

Contexte

Le concept de valeur quantile

Dans les statistiques et l'analyse des données, les quantiles (ou quartiles) sont souvent utilisés pour décrire les caractéristiques statistiques de la distribution des données. Généralement, la valeur du quantile est divisée en quatre parties égales, à savoir le premier quantile (Q1), le deuxième quantile (Q2) (c'est-à-dire la médiane), le troisième quantile (Q3) et la différence extrême (IQR). Parmi eux, 1/4 des données sont inférieurs au premier quantile, 1/4 des données sont supérieurs au troisième quantile et les 50 % intermédiaires des données se situent entre le premier quantile et le troisième quantile. En statistiques, le premier quantile fait référence au nombre se trouvant dans les 25 % supérieurs de la séquence entière après qu'un ensemble de données a été classé par ordre de taille ; le deuxième quantile fait référence à un ensemble de données classé par ordre de taille en dernier, le nombre. en position médiane ; et le troisième quantile fait référence au nombre dans les 25 % inférieurs de la séquence entière après qu'un ensemble de données a été classé par ordre de taille. La médiane est le deuxième quartile. Dans l'analyse des données, les valeurs quantiles peuvent nous aider à comprendre la distribution des données et à déterminer si les données sont biaisées d'un côté ou dans quelle mesure elles sont dispersées. Lorsque la distribution des données est inégale, les valeurs quantiles peuvent représenter plus précisément les différences entre les données.

Contexte commercial

La plage de distribution des coupures émises par les commerçants est de [1, 20], et chaque coupon sera marqué de sa dénomination correspondante. Pour contrôler avec précision le coût des coupons, il est nécessaire de comprendre l'émission des coupons en temps réel afin de procéder à une évaluation plus précise. Grâce à la surveillance en temps réel du montant des coupons émis, du montant moyen des coupons émis et de la valeur quantile du montant émis (compréhension du montant moyen des coupons émis à différents intervalles), vous pouvez avoir une compréhension plus claire de l'émission de bons de réduction.

Actuellement, l'entreprise a trié les indicateurs suivants et les étudiants qui ont besoin de données les fourniront. Tous les indicateurs sont basés sur une granularité statistique minutieuse :

Montant émis : montant total des coupons émis

Montant moyen des coupons émis : montant total. émis/montant total émis

Coupons émis Montant 0,1 centile moyen : le nombre de coupons émis par minute est trié par dénomination, avec les dénominations les plus grandes devant et les dénominations plus petites plus tard. Calculez la valeur moyenne des 10 % des coupons émis par minute. minute [par exemple, l'ordre des dénominations des coupons est : 10, 9, 8, 8, 6, 5, 4, 4, 2, 2, alors la moyenne du quantile 0,1 est 10]

La moyenne du quantile 0,2 du montant du coupon : le nombre de coupons émis par minute est basé sur la dénomination. Triez par taille, avec les coupures les plus grandes devant et les coupures plus petites ensuite. Calculez la valeur moyenne des 20 % les plus élevés des coupons émis par minute [par exemple, l'ordre de coupure des coupons). émis est : 10,9,8,8, 6, 5, 4, 4, 2, 2, alors la moyenne du 0,2 percentile est (10+9)/2=9,5]

Indicateurs tels que le volume d'émission et la moyenne le montant du coupon peut être implémenté à l'aide de MySQL, alors comment implémenter et utiliser Qu'en est-il de l'interrogation des valeurs quantiles dans MySQL ?

Pensée

MySQL implémente le tri

row_number() over ( partition by a1.min order by metric_value desc) as orderNum

metric_value représente le nombre de coupons émis Grâce à la fonction ci-dessus, il peut être trié par le nombre de coupons émis, et les données des coupons émis par minute sont triées par le montant

MySQL. implémente topN

SELECT * FROM sales ORDER BY amount DESC LIMIT 10;

C'est évident, cette méthode topN ne peut pas réaliser un tri par minutes, et les N% supérieurs sont pris en compte. Afin de connaître le montant de N%, nous devons d’abord déterminer le montant total, nous devons donc d’abord calculer le montant total par minute. Multipliez-le ensuite par N% pour savoir de quelle quantité de données nous avons besoin pour extraire N%.

select hour,min, count(1) as cn 
from table  
where dt=20230423 and hour=11 and min>=0 and min<=30 
group by hour,min

Ensuite, nous multiplions les résultats statistiques par N%

select dt,a2.hour,a2.min as min,metric_value, round(cn*N%) as cn, orderNum 
from ( 
	select dt,hour,a1.min as min, 
	metric_value, row_number() over ( partition by a1.min order by metric_value desc) as orderNum 
	from table a1 
	where dt=20230423 and hour=11 and min>=0 and min<=30 
	) as a2 
inner join ( 
	select hour,min , count(1) as cn 
	from table c 
	where dt=20230423 and hour=11 and min>=0 and min<=30  
	group by hour,min ) a3
on a2.hour=a3.hour and a2.min=a3.min

De cette façon, nous pouvons l'obtenir en comparant la taille de cn (la quantité de données nécessaire pour calculer la valeur du quantile) et orderNum (l'ordre dans lequel le courant les coupons sont triés par dénomination) Obtenez les premiers N% des données, puis effectuez un traitement moyen sur cette partie des données pour obtenir les données de valeur quantile.

Ajustez la logique de calcul et fusionnez-la pour obtenir le SQL de la valeur centile comme suit :

select dt,hour,min, round(avg(metric_value)) as metric_value 
from ( 
	select dt,a2.hour,a2.min as min,metric_value, round(cn*?) as cn, orderNum 
from ( 
	select dt,hour,a1.min as min,
	metric_value, row_number() over ( partition by a1.min order by metric_value desc) as orderNum 
	from table a1 
	where dt=20230423 and hour=11 and min>=0 and min<=30 
	) as a2 
inner join ( 
	select hour,min, count(1) as cn 
	from table a1 
	where dt=20230423 and hour=11 and min>=0 and min<=30 
	) as a3
on a2.hour=a3.hour and a2.min=a3.min ) as q 
where cn>orderNum 
group by dt,hour,min 
order by dt,hour,min

Ces données sont dans la plage de calcul des statistiques de valeur centile si cn > orderNum.. Afin de calculer la valeur centile de 0,1, les premiers 10 % des données d'émission de coupons par minute doivent être collectés. Après tri par dénomination et regroupement par minutes, chaque enregistrement sera marqué du rang du disque. Le nombre total de coupons émis par minute est multiplié par 10 % pour obtenir cnt. Cette valeur est la quantité de données requise pour calculer la moyenne de 0,1 minute de cette minute. Lorsque cnt

• Explication Avant d'utiliser MySQL pour calculer la valeur du quantile, la valeur du quantile était toujours implémentée en interrogeant les données d'émission de coupons par minute via un programme Java, puis en triant et en calculant la moyenne. Le plus gros problème de la mise en œuvre du programme est que si le nombre de coupons émis est relativement important, il faudra alors interroger les indicateurs de valeur quantile pour une période donnée, ce qui exercera une forte pression sur le programme. En fait, nous rencontrons ce problème dans notre activité actuelle. Chaque fois que vous interrogez 2 heures de données de valeur quantile, plus d'un million de données seront chargées dans le programme Java, ce qui est extrêmement effrayant pour les services de requête de données. Afin de résoudre ce problème, nous devons implémenter la requête de valeurs quantiles via MySQL.

Effet

Les données détaillées sont interrogées par le programme pour calculer la valeur du quantile --> MySQL implémente une requête directe de la valeur du quantile

La performance est de >1 min --> dans les 15 secondes ; amélioré

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!