So fragen Sie den Quantilwert in MySQL ab

Hintergrund

Das Konzept des Quantilwerts

In der Statistik und Datenanalyse werden Quantile (oder Quartile) häufig zur Beschreibung statistischer Datenverteilungsmerkmale verwendet. Im Allgemeinen wird der Quantilwert in vier gleiche Teile unterteilt, nämlich das erste Quantil (Q1), das zweite Quantil (Q2) (d. h. den Median), das dritte Quantil (Q3) und die extreme Differenz (IQR). Darunter ist 1/4 der Daten kleiner als das erste Quantil, 1/4 der Daten ist größer als das dritte Quantil und die mittleren 50 % der Daten liegen zwischen dem ersten Quantil und dem dritten Quantil. In der Statistik bezieht sich das erste Quantil auf die Zahl in den obersten 25 % der gesamten Folge, nachdem ein Datensatz in der Reihenfolge seiner Größe geordnet wurde; in der mittleren Position; und das dritte Quantil bezieht sich auf die Zahl in den unteren 25 % der gesamten Sequenz, nachdem ein Datensatz der Größe nach geordnet wurde. Der Median ist das zweite Quartil. Bei der Datenanalyse können Quantilwerte uns helfen, die Verteilung von Daten zu verstehen und festzustellen, ob die Daten einseitig verzerrt sind oder wie verteilt sie sind. Wenn die Datenverteilung ungleichmäßig ist, können Quantilwerte die Unterschiede in den Daten genauer darstellen.

Geschäftshintergrund

Der Nennwertverteilungsbereich der von Händlern ausgegebenen Coupons beträgt [1, 20], und jeder Coupon wird mit seinem entsprechenden Nennwert gekennzeichnet. Um die Kosten von Coupons genau kontrollieren zu können, ist es notwendig, die Ausstellung von Coupons in Echtzeit zu verstehen, um eine genauere Einschätzung vornehmen zu können. Durch die Echtzeitüberwachung der Anzahl der ausgegebenen Coupons, der durchschnittlichen Anzahl der ausgegebenen Coupons und des Quantilwerts der ausgegebenen Menge (Verstehen der durchschnittlichen Anzahl der in verschiedenen Intervallen ausgegebenen Coupons) können Sie ein klareres Verständnis der Ausgabe erhalten Gutscheine.

Derzeit werden Studenten, die Daten zum Aussortieren der folgenden Indikatoren benötigen, vom Unternehmen bereitgestellt. Alle Indikatoren basieren auf einer detaillierten statistischen Granularität:

Ausgabevolumen: Gesamtbetrag der Coupons ausgegeben

# 🎜🎜#Durchschnittliche Anzahl ausgegebener Coupons: ausgegebener Gesamtbetrag/ausgegebener Gesamtbetrag

Durchschnitt von 0,1 Prozent des ausgegebenen Couponbetrags: Die Anzahl der pro Minute ausgegebenen Coupons ist nach Nennwert sortiert , mit größeren Stückelungen zuerst, kleinere Stückelungen zuletzt. Berechnen Sie abschließend den Durchschnittswert der obersten 10 % der pro Minute ausgegebenen Coupons [die Reihenfolge der Coupon-Stückelungen lautet beispielsweise: 10,9,8,8,6,5,4]. ,4,2,2, dann beträgt der Mittelwert des 0,1-Perzentils 10]

Der Mittelwert der Coupon-Ausgabemenge von 0,2-Perzentil: Die Anzahl der pro Minute ausgegebenen Coupons ist nach Nennwert sortiert, wobei größere Nennwerte angegeben sind Vordere und kleinere Stückelung später. Berechnen Sie den Durchschnittswert der obersten 20 % der Coupons [zum Beispiel ist die Stückelungsreihenfolge der ausgegebenen Coupons: 10, 9, 8, 8, 6, 5, 4, 4, 2, 2, dann beträgt der Durchschnittswert des 0,2-Perzentils (10 +9)/2=9,5]

Indikatoren wie das Ausgabevolumen und die durchschnittliche Anzahl an Coupons können mit MySQL implementiert werden . Wie kann man den Quantilwert mit MySQL abfragen?

Thinking

MySQL implementiert die Sortierung

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

metric_value stellt die Menge der ausgegebenen Coupons dar, und die obige Funktion kann zum Sortieren nach der Menge verwendet werden Ausgestellte Coupons und jede Minute werden die Coupon-Ausstellungsdaten nach Betrag sortiert genommen. Um den Betrag von N % zu ermitteln, müssen wir zunächst den Gesamtbetrag ermitteln, also zunächst den Gesamtbetrag pro Minute berechnen. Dann multiplizieren Sie es mit N %, um zu erfahren, wie viele Daten wir zum Extrahieren von N % benötigen.

SELECT * FROM sales ORDER BY amount DESC LIMIT 10;

Dann multiplizieren wir die statistischen Ergebnisse mit 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

Auf diese Weise können wir cn (die zur Berechnung des Quantilwerts erforderliche Datenmenge) und orderNum vergleichen (Aktuell (Die Gutscheine werden nach Nennwertgröße sortiert), um die ersten N % der Daten zu erhalten, und dann eine Durchschnittsverarbeitung für diesen Teil der Daten durchführen, um die Quantilwertdaten zu erhalten.

Passen Sie die Berechnungslogik an und führen Sie sie zusammen, um die SQL des Perzentilwerts wie folgt zu erhalten:

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

Diese Daten liegen im Bereich der Berechnung der Perzentilwertstatistik, wenn cn > Bestellnummer. Um den 0,1-Perzentilwert zu berechnen, müssen die ersten 10 % der Coupon-Ausgabedaten pro Minute erfasst werden. Nach der Sortierung nach Nennwert und der Gruppierung nach Minuten wird jeder Datensatz mit dem Rang des Datensatzes gekennzeichnet. Die Gesamtmenge der pro Minute ausgegebenen Coupons wird mit 10 % multipliziert, um cnt zu erhalten. Dieser Wert ist die Datenmenge, die zur Berechnung des 0,1-Minuten-Durchschnitts dieser Minute erforderlich ist. Wenn cnt

Erläuterung

Vor der Verwendung von MySQL zur Berechnung des Quantilwerts wurde der Quantilwert für pro Minute ausgegebene Coupons immer über das Java-Programm abgefragt. Die Daten werden dann sortiert, um die mittlere Implementierung zu berechnen. Das größte Problem bei der Programmimplementierung besteht darin, dass, wenn die Menge der ausgegebenen Coupons relativ groß ist, die Quantilwertindikatoren für einen bestimmten Zeitraum abgefragt werden müssen, was einen großen Druck auf das Programm ausübt. Tatsächlich haben wir dieses Problem in unserem eigentlichen Geschäft. Jedes Mal, wenn Sie 2 Stunden Quantilwertdaten abfragen, werden über eine Million Daten in das Java-Programm geladen, was für Datenabfragedienste äußerst beängstigend ist. Um dieses Problem zu lösen, müssen wir die Abfrage von Quantilwerten über MySQL implementieren.

• Effekt

  Das Programm fragt die detaillierten Daten ab, um den Quantilwert zu berechnen --> MySQL implementiert eine direkte Abfrage des Quantilwerts#🎜 🎜## 🎜🎜#Leistung von >1 Minute --> innerhalb von 15 Sekunden; Leistung wurde erheblich verbessert

  Das obige ist der detaillierte Inhalt vonSo fragen Sie den Quantilwert in MySQL ab. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!