MySql のグループ化と各グループからランダムに 1 つのデータを取得する

アイデア: 最初にランダムに並べ替えてからグループ化します。

1. テーブルを作成します。

テーブル `xdx_test` を作成します (
 `id` int(11) NULLではない、
 `name` varchar(255) デフォルト NULL,
 `class` varchar(255) デフォルト NULL,
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

2. データを挿入する

xdx_testにVALUES(1, '张三-1','1')を挿入します。
xdx_test に値 (2, 'Li Si-1','1') を挿入します。
xdx_testにVALUES(3, '王五-1','1')を挿入します。
xdx_testにVALUES(4, '张三-2','2')を挿入します。
xdx_testに値(5, 'Li Si-2','2')を挿入します。
xdx_testにVALUES(6, '王五-2','2')を挿入します。
xdx_testにVALUES(7, '张三-3','3')を挿入します。
xdx_testに値(8, 'Li Si-3','3')を挿入します。
xdx_testにVALUES(9, '王五-3','3')を挿入します。

3. クエリステートメント

*から選択 
 (SELECT * FROM xdx_test ORDER BY RAND()) a
GROUP BY a.クラス

4. クエリ結果

3 王武-1 1
5 リチウムSi-2 2
9 王武-3 3

3 王武-1 1
4 張三-2 2
7 張三-3 3

2 リチウムSi-1 1
5 リチウムSi-2 2
8 リチウムSi-3 3

補足知識： MySQLはランダムに複数のデータを取得するメソッドを実装している（効率と離散型の比較）

SQL文の書き方、効率性、離散比較にはいくつかの方法がある

1: SELECT * FROM tablename ORDER BY RAND() 取得するデータエントリの数を制限します。

2: SELECT *FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id 取得するデータ項目の数を制限します。

3: SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id

ORDER BY t1.id ASC LIMIT 取得するデータ項目の数。

4: SELECT * FROM `table`WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id 取得するデータ項目の数を制限します。

5: SELECT * FROM `table` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) ORDER BY id 取得するデータ項目の数を制限します。

6: SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id 取得するデータ項目の数を制限します。

クエリ時間 1>>クエリ時間 2>>クエリ時間 5>クエリ時間 6>クエリ時間 4>クエリ時間 3 であり、3 が最も効率が高いことを意味します。

上記6つのタイプを効率の観点から単純に比較すると、

上記の 6 種類の乱数抽出は、次の 2 つのカテゴリに分けられます。

最初のものは離散性は高いが効率は低く、他の 5 つは効率は高いが離散性は低いです。

効率性と離散性の両方を満たす問題を解決するにはどうすればよいでしょうか?

私たちにはアイデアがあります: ストアドプロシージャを作成します。

select * FROM test t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM test)-(SELECT MIN(id) FROM test)) + (SELECT MIN(id) FROM test)) AS id) t2 where t1.id >= t2.id limit 1

一度に 1 つのレコードを取り出し、ループでそれを一時テーブルに書き込みます。最後に一時テーブルを選択して戻ると OK です。

これにより、効率要件を満たすだけでなく、離散問題も解決され、両方の利点を組み合わせることができます。

以下は特定のストアドプロシージャの擬似コードです。

`evaluate_Check_procedure` が存在する場合はプロシージャを削除します。
区切り文字 ;;
CREATE DEFINER=`root`@`%` PROCEDURE `evaluate_Check_procedure`(IN startTime datetime, IN endTime datetime,IN checkNum INT,IN evaInterface VARCHAR(36))
始める

-- ランダムに取得したデータを格納するための一時テーブルを作成する

存在しない場合は一時テーブルを作成する xdr_authen_tmp ( 
 `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'シリアル番号',
 `LENGTH` int(5) デフォルト NULL コメント 'バイト数',
 `INTERFACE` int(3) NOT NULL COMMENT 'インターフェース',
 `XDR_ID` varchar(32) NOT NULL COMMENT 'XDR ID',
 `MSISDN` varchar(32) デフォルト NULL コメント 'ユーザー番号',
 `PROCEDURE_START_TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '開始時刻',
 `PROCEDURE_END_TIME` datetime DEFAULT NULL COMMENT '終了時刻',
 `SOURCE_NE_IP` varchar(39) デフォルト NULL コメント 'ソースNE IP',
 `SOURCE_NE_PORT` int(5) DEFAULT NULL COMMENT 'ソースNEポート',
 `DESTINATION_NE_IP` varchar(39) DEFAULT NULL COMMENT '宛先ネットワーク要素IP',
 `DESTINATION_NE_PORT` int(5) DEFAULT NULL COMMENT '宛先ネットワーク要素ポート',
 `INSERT_DATE` datetime DEFAULT NULL COMMENT '時刻を挿入',
 `EXTEND1` varchar(50) デフォルト NULL コメント '拡張 1',
 `EXTEND2` varchar(50) デフォルト NULL コメント '拡張2',
 `EXTEND3` varchar(50) デフォルト NULL コメント '拡張3',
 `EXTEND4` varchar(50) デフォルト NULL コメント '拡張4',
 `EXTEND5` varchar(50) デフォルト NULL コメント '拡張5',
 主キー (`ID`,`PROCEDURE_START_TIME`)、
 キー `index_procedure_start_time` (`PROCEDURE_START_TIME`)、
 キー `index_source_dest_ip` (`SOURCE_NE_IP`,`DESTINATION_NE_IP`),
 キー `index_xdr_id` (`XDR_ID`) 
)ENGINE = InnoDB デフォルト文字セット = utf8;

始める
j INT を宣言します。
i INT を宣言します。

NOT FOUND SET i = 1 の継続ハンドラーを宣言します。

-- ここでの checkNum はランダムに取得するデータの数です。たとえば、10 レコードをランダムに取得する場合は、ここでは 10 になります。while ループは、単一のランダムレコードを 1 つずつ取得するために使用されます。

j = 0 に設定します。
j < checkNum DOの間 
 @sqlexi = concat（ 'Select t1.id、t1.length、t1.local_province、t1.local_city、t1.owner_province、t1.owner_city、t1.roaming_type、t1.interface、t1.xdr_id、t1.rat、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi、t1.imsi _start_time、t1.procedure_end_time、t1.transaction_type、t1.transaction_status、t1.source_ne_pe、t1.source_ne_port、t1.destination_ne_p、t1.destination_ne_port、t1。reusult_code、t1 ESTINATION_REALM、T1.ORIGIN_HOST、T1.DESTINATION_HOST、T1.INSERT_DATE '、
    ' を @ID、@LENGTH、@LOCAL_PROVINCE、@LOCAL_CITY、@OWNER_PROVINCE、@OWNER_CITY、@ROAMING_TYPE、@INTERFACE、@XDR_ID、@RAT、@IMSI、@IMEI、@MSISDN、@PROCEDURE_START_TIME、@PROCEDURE_END_TIME、@TRANSACTION_TYPE、@TRANSACTION_STATUS、@SOURCE_NE_IP、@SOURCE_NE_PORT、@DESTINATION_NE_IP、@DESTINATION_NE_PORT、@RESULT_CODE、@EXPERIMENTAL_RESULT_CODE、@ORIGIN_REALM、@DESTINATION_REALM、@ORIGIN_HOST、@DESTINATION_HOST、@INSERT_DATE に代入 ',
    ' xdr_authen t1 から JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM xdr_authen)-(SELECT MIN(id) FROM xdr_authen)) + (SELECT MIN(id) FROM xdr_authen)) AS id) t2',
    ' WHERE t1.PROCEDURE_START_TIME >= "',startTime,'"',
       ' AND t1.PROCEDURE_START_TIME < "',endTime,'"',' AND t1.INTERFACE IN (',evaInterface,')',
       ' かつ t1.id >= t2.id 制限 1');
 @sqlexi から sqlexi を準備します。
 sqlexi を実行します。
 PREPARE sqlexi の割り当てを解除します。

-- ここで取得したレコードは重複している可能性があります。重複データの場合は、このデータを一時テーブルに挿入せず、次回ランダムデータを取得します。十分なランダムデータが得られるまでこれを繰り返します。

 id = @ID である xdr_authen_tmp から count(1) を @num に選択します。
 
 @num > 0 または i=1 の場合 
  j = j を設定します。
 それ以外
  xdr_authen_tmp(ID、LENGTH、LOCAL_PROVINCE、LOCAL_CITY、OWNER_PROVINCE、OWNER_CITY、ROAMING_TYPE、INTERFACE、XDR_ID、RAT、IMSI、IMEI、MSISDN、PROCEDURE_START_TIME、PROCEDURE_END_TIME、TRANSACTION_TYPE、TRANSACTION_STATUS、SOURCE_NE_IP、SOURCE_NE_PORT、DESTINATION_NE_IP、DESTINATION_NE_PORT、RESULT_CODE、EXPERIMENTAL_RESULT_CODE、ORIGIN_REALM、DESTINATION_REALM、ORIGIN_HOST、DESTINATION_HOST、INSERT_DATE) に挿入します。
  VALUES(@ID、@LENGTH、@LOCAL_PROVINCE、@LOCAL_CITY、@OWNER_PROVINCE、@OWNER_CITY、@ROAMING_TYPE、@INTERFACE、@XDR_ID、@RAT、@IMSI、@IMEI、@MSISDN、@PROCEDURE_START_TIME、@PROCEDURE_END_TIME、@TRANSACTION_TYPE、@TRANSACTION_STATUS、@SOURCE_NE_IP、@SOURCE_NE_PORT、@DESTINATION_NE_IP、@DESTINATION_NE_PORT、@RESULT_CODE、@EXPERIMENTAL_RESULT_CODE、@ORIGIN_REALM、@DESTINATION_REALM、@ORIGIN_HOST、@DESTINATION_HOST、@INSERT_DATE);
 
  j = j + 1 に設定します。
 終了の場合; 
 i=0 に設定します。

終了しながら;

-- 最後に、すべての乱数をクエリし、結果セットの形式でバックエンドに返します。

xdr_authen_tmp から ID、LENGTH、LOCAL_PROVINCE、LOCAL_CITY、OWNER_PROVINCE、OWNER_CITY、ROAMING_TYPE、INTERFACE、XDR_ID、RAT、IMSI、IMEI、MSISDN、PROCEDURE_START_TIME、PROCEDURE_END_TIME、TRANSACTION_TYPE、TRANSACTION_STATUS、SOURCE_NE_IP、SOURCE_NE_PORT、DESTINATION_NE_IP、DESTINATION_NE_PORT、RESULT_CODE、EXPERIMENTAL_RESULT_CODE、ORIGIN_REALM、DESTINATION_REALM、ORIGIN_HOST、DESTINATION_HOST、INSERT_DATE を選択します。

終わり;
テーブル xdr_authen_tmp を切り捨てます。

終わり
;;
区切り文字 ;

MySql のグループ化と各グループからランダムに 1 つのデータを取得する上記の操作は、エディターが皆さんと共有する内容のすべてです。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません: