MySQL で分位値をクエリする方法

背景

分位値の概念

統計やデータ分析では、データ分布の統計的特性を説明するために分位数 (または四分位数) がよく使用されます。一般に、分位値は 4 つの等しい部分、つまり、第 1 分位数 (Q1)、第 2 分位数 (Q2) (つまり中央値)、第 3 分位数 (Q3)、および極値差分 (IQR) に分割されます。このうち、データの 1/4 は第 1 分位より小さく、データの 1/4 は第 3 分位より大きく、データの中間の 50% は第 1 分位と第 3 分位の間にあります。統計では、最初の分位数は、データのセットをサイズの順に並べた後のシーケンス全体の上位 25% の数値を指し、第 2 分位数は、サイズの順に並べたデータのセットを指します。第 3 分位数は、データのセットをサイズ順に並べた後のシーケンス全体の下位 25% の数値を指します。中央値は第 2 四分位です。データ分析では、分位値はデータの分布を理解し、データが一側に偏っているかどうか、またはどの程度分散しているかを判断するのに役立ちます。データの分布が不均一な場合、分位値はデータの差をより正確に表すことができます。

ビジネスの背景

販売者が発行するクーポンの額面配布範囲は [1, 20] で、各クーポンには対応する額面がマークされます。クーポンのコストを正確に管理するには、クーポンの発行状況をリアルタイムで把握し、より正確な評価を行う必要があります。クーポン発行量、平均クーポン発行量、および発行量の分位値をリアルタイムに監視することにより（さまざまな間隔での平均クーポン発行量を把握）、クーポンの発行状況をより明確に把握できます。クーポン。

現在、企業は次の指標を整理し、必要な学生からデータを必要としています。すべての指標は統計的な粒度として分に基づいています:

発行量: 発行されたクーポンの総量

クーポン発行量 平均: 発行総額 / 発行総額

クーポン発行金額 0.1 パーセンタイル平均: 1 分あたりのクーポン発行量を額面ごとにソートし、額面の大きい順に並べます。 1 分あたりのクーポン発行量を計算します。クーポンの上位 10% の平均値 (たとえば、クーポン金額の順序は 10、9、8、8、6、5、4、4 です) , 2, 2 の場合、0.1 分位の平均値は 10]

発行されたクーポン金額の 0.2 パーセンタイル平均: 1 分あたりに発行されたクーポンの量は、額面ごとに並べ替えられ、大きい額面が前に、小さい額面が前に表示されます。 1 分あたりのクーポン発行額の上位 20% を計算します。クーポンの平均値 (たとえば、発行されたクーポンの額面順は 10、9、8、8、6、5、4、4、2、 2 の場合、0.2 パーセンタイルの平均値は (10 9)/2=9.5 です。]

クーポンの発行量や平均枚数などの指標は、MySQL を使用して実装できます。分位値?

思考

MySQLはsortingを実装しています

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

metric_valueはクーポンの発行量を表しており、上記の関数によりクーポンの発行量に応じてソートすることができ、 1 分あたりのクーポン発行データは金額ソートに基づいています

MySQL は topN

SELECT * FROM sales ORDER BY amount DESC LIMIT 10;

を実装しています明らかに、この topN メソッドでは分ごとのソートを実現できず、上位 N% が取得されます。 N% の量を知るには、まず合計量を決定する必要があるため、最初に 1 分あたりの合計量を計算する必要があります。次に、それに N% を掛けて、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

次に、統計結果に 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

を掛けます。このようにして、cn (分位値の計算に必要なデータの量) と orderNum (データのサイズ) を比較できます。額面に基づく現在のクーポン (ソート順のサイズ) を使用してデータの最初の N% を取得し、データのこの部分に対して平均処理を実行して分位値データを取得します。

計算ロジックを調整して融合し、次のようにパーセンタイル値の SQL を取得します。

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

このデータは、cn > orderNum の場合、パーセンタイル値の統計を計算できる範囲内にあります。 0.1 パーセンタイル値を計算するには、1 分あたりのクーポン発行データの最初の 10% を収集する必要があります。額面ごとに並べ替え、分ごとにグループ化した後、各レコードにはレコードのランクがマークされます。 1分あたりのクーポン発行量の合計に10%を乗じてcntを求めます。この値は、この1分間の0.1分の平均を計算するのに必要なデータ量です。cnt

• 説明 MySQL を使用して分位値を計算する前に、分位値は常に Java プログラムを通じて毎分クーポン発行データに対してクエリされ、ソートされて計算されていました。平均、達成する。プログラムの実装に関する最大の問題は、クーポンの発行量が比較的多い場合、一定期間の分位値指標を照会する必要があり、プログラムに大きな負担がかかることです。実際、私たちの実際のビジネスでもこの問題は発生しています。 2 時間の分位値データをクエリするたびに、100 万を超えるデータが Java プログラムに読み込まれることになります。これはデータ クエリ サービスにとって非常に恐ろしいことです。この問題を解決するには、MySQL を介して分位値のクエリを実装する必要があります。

効果

プログラムは詳細データをクエリして分位値を計算します --> MySQL は分位値の直接クエリを実装します

パフォーマンス>1分から開始 --> 15秒以内; パフォーマンスが大幅に向上

以上がMySQL で分位値をクエリする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。