MySql grouping and randomly getting one piece of data from each group

Idea: Just sort randomly first and then group.

1. Create a table:

CREATE TABLE `xdx_test` (
 `id` int(11) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `class` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Insert data

INSERT INTO xdx_test VALUES (1, '张三-1','1');
INSERT INTO xdx_test VALUES (2, 'Li Si-1','1');
INSERT INTO xdx_test VALUES (3, '王五-1','1');
INSERT INTO xdx_test VALUES (4, '张三-2','2');
INSERT INTO xdx_test VALUES (5, 'Li Si-2','2');
INSERT INTO xdx_test VALUES (6, '王五-2','2');
INSERT INTO xdx_test VALUES (7, '张三-3','3');
INSERT INTO xdx_test VALUES (8, 'Li Si-3','3');
INSERT INTO xdx_test VALUES (9, '王五-3','3');

3. Query Statement

SELECT * FROM 
 (SELECT * FROM xdx_test ORDER BY RAND()) a
GROUP BY a.class

4. Query results

3 Wang Wu-1 1
5 Li Si-2 2
9 Wang Wu-3 3

3 Wang Wu-1 1
4 Zhang San-2 2
7 Zhang San-3 3

2 Li Si-1 1
5 Li Si-2 2
8 Li Si-3 3

Supplementary knowledge: MySQL implements a method to randomly obtain several pieces of data (comparison of efficiency and discrete type)

There are several ways to write SQL statements, efficiency, and discrete comparison

1: SELECT * FROM tablename ORDER BY RAND() LIMIT the number of data entries you want to obtain;

2: SELECT *FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT the number of data items you want to obtain;

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 The number of data items you want to obtain;

4: SELECT * FROM `table`WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT the number of data items you want to obtain;

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 LIMIT the number of data items you want to obtain;

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 LIMIT the number of data items you want to obtain;

The query time of 1>>the query time of 2>>the query time of 5>the query time of 6>the query time of 4>the query time of 3, which means that 3 has the highest efficiency.

The above 6 types are simply compared in terms of efficiency;

The above 6 types of random number extraction can be divided into 2 categories:

The first one has a higher discreteness but low efficiency; the other five have high efficiency but low discreteness;

How to solve the problem that both efficiency and discreteness are met?

We have an idea: write a stored procedure;

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

Take out one record at a time, and then write it into a temporary table in a loop; finally return to select the temporary table and it's OK;

This not only meets the efficiency requirement but also solves the discrete problem; it can combine the advantages of both.

The following is the pseudo code of the specific stored procedure

DROP PROCEDURE IF EXISTS `evaluate_Check_procedure`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `evaluate_Check_procedure`(IN startTime datetime, IN endTime datetime,IN checkNum INT,IN evaInterface VARCHAR(36))
BEGIN

-- Create a temporary table to store randomly retrieved data

create temporary table if not exists xdr_authen_tmp ( 
 `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Serial number',
 `LENGTH` int(5) DEFAULT NULL COMMENT 'Number of bytes',
 `INTERFACE` int(3) NOT NULL COMMENT 'Interface',
 `XDR_ID` varchar(32) NOT NULL COMMENT 'XDR ID',
 `MSISDN` varchar(32) DEFAULT NULL COMMENT 'User number',
 `PROCEDURE_START_TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Start time',
 `PROCEDURE_END_TIME` datetime DEFAULT NULL COMMENT 'End time',
 `SOURCE_NE_IP` varchar(39) DEFAULT NULL COMMENT 'Source NE IP',
 `SOURCE_NE_PORT` int(5) DEFAULT NULL COMMENT 'Source NE port',
 `DESTINATION_NE_IP` varchar(39) DEFAULT NULL COMMENT 'Destination network element IP',
 `DESTINATION_NE_PORT` int(5) DEFAULT NULL COMMENT 'Destination network element port',
 `INSERT_DATE` datetime DEFAULT NULL COMMENT 'Insert time',
 `EXTEND1` varchar(50) DEFAULT NULL COMMENT 'Extension 1',
 `EXTEND2` varchar(50) DEFAULT NULL COMMENT 'Extension 2',
 `EXTEND3` varchar(50) DEFAULT NULL COMMENT 'Extension 3',
 `EXTEND4` varchar(50) DEFAULT NULL COMMENT 'Extension 4',
 `EXTEND5` varchar(50) DEFAULT NULL COMMENT 'Extension 5',
 PRIMARY KEY (`ID`,`PROCEDURE_START_TIME`),
 KEY `index_procedure_start_time` (`PROCEDURE_START_TIME`),
 KEY `index_source_dest_ip` (`SOURCE_NE_IP`,`DESTINATION_NE_IP`),
 KEY `index_xdr_id` (`XDR_ID`) 
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

BEGIN
DECLARE j INT;
DECLARE i INT;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET i = 1;

-- checkNum here is the number of data to be randomly obtained. For example, if 10 records are to be obtained randomly, then it is 10 here. The while loop is used to obtain single random records one by one.

SET j = 0;
WHILE j < checkNum DO 
 set @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.IMEI,t1.MSISDN,t1.PROCEDURE_START_TIME,t1.PROCEDURE_END_TIME,t1.TRANSACTION_TYPE,t1.TRANSACTION_STATUS,t1.SOURCE_NE_IP,t1.SOURCE_NE_PORT,t1.DESTINATION_NE_IP,t1.DESTINATION_NE_PORT,t1.RESULT_CODE,t1.EXPERIMENTAL_RESULT_CODE,t1.ORIGIN_REALM,t1.DESTINATION_REALM,t1.ORIGIN_HOST,t1.DESTINATION_HOST,t1.INSERT_DATE',
    ' into @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 ',
    ' FROM 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,')',
       ' and t1.id >= t2.id limit 1');
 PREPARE sqlexi FROM @sqlexi;
 EXECUTE sqlexi;
 DEALLOCATE PREPARE sqlexi;

-- The records obtained here may be repeated. If it is duplicate data, we will not insert this data into the temporary table and obtain random data next time. And so on, until enough random data is obtained;

 select count(1) into @num from xdr_authen_tmp where id = @ID;
 
 if @num > 0 or i=1 then 
  SET j = j;
 ELSE
  insert into 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);
 
  SET j = j + 1;
 end if; 
 SET i=0;

END WHILE;

-- Finally, we query all the random numbers and return them to the backend in the form of a result set

select 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 from xdr_authen_tmp;

END;
truncate TABLE xdr_authen_tmp;

END
;;
DELIMITER ;

The above operation of MySql grouping and randomly obtaining one piece of data from each group is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:

Implement group by based on MySQL to get the latest data of each group
MySQL subqueries and grouped queries
MySQL grouping queries and aggregate functions
MySql Group By implements grouping of multiple fields
Detailed explanation of MySQL group sorting to find the top N
Detailed example of getting the maximum value of each group after grouping in MySQL
Mysql uses group by group sorting
Detailed explanation of MySQL data grouping

<<: Tutorial on how to quickly deploy a Nebula Graph cluster using Docker swarm

>>: JavaScript to achieve simple drag effect