Bagaimana untuk menanyakan nilai kuantiti dalam MySQL

Latar Belakang

Konsep kuantil

Dalam statistik dan analisis data, kuantil (atau kuartil) sering digunakan untuk menerangkan ciri statistik pengedaran data. Secara umumnya, nilai kuantil dibahagikan kepada empat bahagian yang sama iaitu kuantil pertama (Q1), kuantil kedua (Q2) (iaitu median), kuantil ketiga (Q3) dan Perbezaan melampau (IQR). Antaranya, 1/4 daripada data adalah lebih kecil daripada kuantil pertama, 1/4 daripada data lebih besar daripada kuantil ketiga, dan 50% tengah data adalah antara kuantil pertama dan kuantil ketiga. Dalam statistik, kuantil pertama merujuk kepada nombor dalam 25% teratas daripada keseluruhan jujukan selepas satu set data disusun mengikut urutan saiz; di kedudukan tengah; dan kuantil ketiga merujuk kepada nombor di bahagian bawah 25% daripada keseluruhan jujukan selepas satu set data disusun mengikut susunan saiz. Median ialah kuartil kedua. Dalam analisis data, nilai kuantil boleh membantu kita memahami pengagihan data dan menentukan sama ada data itu berat sebelah kepada satu pihak atau bagaimana penyebarannya. Apabila pengedaran data tidak sekata, nilai kuantil boleh mewakili perbezaan dalam data dengan lebih tepat.

Latar Belakang Perniagaan

Julat pengedaran denominasi kupon yang dikeluarkan oleh peniaga ialah [1, 20], dan setiap kupon akan ditandakan dengan denominasi yang sepadan. Untuk mengawal kos kupon dengan tepat, adalah perlu untuk memahami pengeluaran kupon dalam masa nyata untuk membuat penilaian yang lebih tepat. Melalui pemantauan masa nyata jumlah kupon yang dikeluarkan, jumlah purata kupon yang dikeluarkan dan nilai kuantiti amaun yang dikeluarkan (memahami jumlah purata kupon yang dikeluarkan dalam selang masa yang berbeza), anda boleh mempunyai pemahaman yang lebih jelas tentang pengeluaran kupon.

Pada masa ini, perniagaan telah menyusun penunjuk berikut dan memerlukan pelajar menyediakan data Semua penunjuk adalah berdasarkan minit sebagai butiran statistik:

Jumlah yang dikeluarkan: Jumlah kupon yang dikeluarkan

Jumlah kupon yang dikeluarkan Purata: Jumlah amaun yang dikeluarkan/Jumlah amaun yang dikeluarkan

Purata 0.1 peratus daripada jumlah kupon yang dikeluarkan: Jumlah kupon yang dikeluarkan seminit diisih mengikut denominasi, dengan denominasi yang lebih besar di hadapan dan yang lebih kecil denominasi kemudiannya. , 2, 2, maka nilai purata bagi kuantil 0.1 ialah 10]

Purata 0.2 persentil jumlah kupon yang dikeluarkan: Jumlah kupon yang dikeluarkan seminit diisih mengikut denominasi, dengan denominasi yang lebih besar di hadapan dan denominasi yang lebih kecil terakhir. Kira 20% teratas daripada jumlah kupon yang dikeluarkan setiap minit [contohnya, susunan denominasi kupon ialah: 10,9,8,8,6,5,4,4,2,2. , maka purata peratusan 0.2 ialah (10+9)/2=9.5]

Petunjuk seperti volum terbitan dan jumlah purata kupon boleh dilaksanakan menggunakan MySQL Jadi bagaimana untuk menggunakan MySQL untuk membuat pertanyaan kuantil nilai?

Berfikir

MySQL melaksanakan pengisihan

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

nilai_metrik mewakili jumlah kupon yang dikeluarkan Melalui fungsi di atas, ia boleh diisih mengikut jumlah kupon yang dikeluarkan, dan data pengeluaran kupon seminit adalah berdasarkan pengisihan Jumlah

MySQL melaksanakan topN

SELECT * FROM sales ORDER BY amount DESC LIMIT 10;

Jelas sekali, kaedah topN ini tidak dapat mencapai pengisihan mengikut minit, dan N% teratas diambil. Untuk mengetahui jumlah N%, kita perlu terlebih dahulu menentukan jumlah keseluruhan, jadi kita perlu terlebih dahulu mengira jumlah setiap minit. Kemudian darabkannya dengan N% untuk mengetahui berapa banyak data yang kita perlukan untuk mengekstrak 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

Kemudian, kita darabkan keputusan statistik dengan 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

Dengan cara ini, kita boleh membandingkan cn (jumlah data yang diperlukan untuk mengira nilai kuantiti) dan orderNum (saiz kupon semasa mengikut denominasi Saiz susunan isihan) digunakan untuk mendapatkan N% pertama data, dan kemudian pemprosesan purata dilakukan pada bahagian data ini untuk mendapatkan data nilai kuantiti.

Laraskan logik pengiraan dan gabungkannya untuk mendapatkan SQL nilai persentil seperti berikut:

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

Data ini berada dalam julat pengiraan statistik nilai persentil jika cn > orderNum.. Untuk mengira nilai persentil 0.1, 10% pertama data pengeluaran kupon seminit perlu dikumpul. Selepas mengisih mengikut denominasi dan mengumpulkan mengikut minit, setiap rekod akan ditandakan dengan pangkat rekod. Jumlah kupon yang dikeluarkan setiap minit didarabkan dengan 10% untuk mendapatkan cnt Nilai ini ialah jumlah data yang diperlukan untuk mengira purata 0.1 minit minit ini Apabila cnt

• Penjelasan Sebelum menggunakan MySQL untuk mengira nilai kuantil, nilai kuantil sentiasa untuk menanyakan data pengeluaran kupon setiap minit melalui program Java, dan kemudian mengisih dan hitungkan min. Masalah terbesar dengan pelaksanaan program ialah jika jumlah kupon yang dikeluarkan agak besar, maka penunjuk nilai kuantiti untuk satu tempoh masa perlu disoal, yang akan memberi tekanan yang besar kepada program. Sebenarnya, kami mempunyai masalah ini dalam perniagaan sebenar kami. Setiap kali anda menanyakan 2 jam data nilai kuantil, lebih sejuta data akan dimuatkan ke dalam program Java, yang sangat menakutkan untuk perkhidmatan pertanyaan data. Untuk menyelesaikan masalah ini, kita mesti melaksanakan pertanyaan nilai kuantil melalui MySQL.

Kesan

Atur cara menanyakan data terperinci untuk mengira nilai kuantil --> MySQL melaksanakan pertanyaan langsung bagi nilai kuantil

Prestasi bermula dari >1min --> prestasi telah dipertingkatkan dengan ketara

Atas ialah kandungan terperinci Bagaimana untuk menanyakan nilai kuantiti dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!