MySQL uses User-Defined variable simulation analysis functions

MySQL uses User-Defined variable simulation analysis functions

MySQL uses a user-defined variable simulation analysis function to initialize the experiment structure and data:

Create table test (
 
Tid varchar (32) primary key,

Stat int not null,

Createtime timestamp not null

);

Insert into test (tid, stat, createtime) VALUES ('ac551ad7ba3f9067b19ac8bb20caca6d ',-1, '2017-08-20 10:01:09 ');

Insert into test (tid, stat, createtime) VALUES ('dc9a4438e577f4b08f7033a305544d47 ',-1, '2017-08-20 10:00:19 ');

Insert into test (tid, stat, createtime) VALUES ('23055228532bbba5a68d6ada11bcf33f ',-1, '2017-08-20 09:58:32 ');

Insert into test (tid, stat, createtime) VALUES ('5711ee1610d07a55e64c7948667de6e8',-1, '2017-08-20 09:58:09 ');

Insert into test (tid, stat, createtime) VALUES ('035e06d8afd681a9904bd74e9860f8cb ',-1, '2017-08-20 09:57:52 ');

Insert into test (tid, stat, createtime) VALUES ('3890efc08f37fa489a4e130cb04f71ac',-1, '2017-08-20 09:57:48 ');

Insert into test (tid, stat, createtime) VALUES ('1b6ed9db663dae470b45c722a61d08b0',-1, '2017-08-20 09:56:40 ');

Insert into test (tid, stat, createtime) VALUES ('8fb3409015e6b2cf85ba6ee90f15b58f',-1, '2017-08-20 09:54:40 ');

Insert into test (tid, stat, createtime) VALUES ('0badb1f4c2b1a89f1c473b992183add3 ',-1, '2017-08-20 09:54:33 ');

Insert into test (tid, stat, createtime) VALUES ('89b8af5eb473b2d4f50dd9e10773a9cc',-1, '2017-08-20 09:53:54 ');

Insert into test (tid, stat, createtime) VALUES ('77923a70000110224b5f94e7d0bd297de', 2, '2017-08-19 17:13:17 ');

Insert into test (tid, stat, createtime) VALUES ('0df1da77cfdbe64edcd4d645197174af', 2, '2017-08-19 12:20:21 ');

Insert into test (tid, stat, createtime) VALUES ('43daef6bfbc46dbfdbb97e71_3dab30', 2, '2017-08-19 09:54:08 ');

Insert into test (tid, stat, createtime) VALUES ('d512c510391314f48054c6c9ab9535c', 2, '2017-08-19 09:23:41 ');

Insert into test (tid, stat, createtime) VALUES ('f7c123143752498b7c9a226a9583ae49 ', 2, '2017-08-19 01:14:21 ');

Insert into test (tid, stat, createtime) VALUES ('da6a9a78897a42ae0a565cd0fabd76bb ', 2, '2017-08-18 21:59:46 ');

Insert into test (tid, stat, createtime) VALUES ('9cd3f83ab04120504a880523702491d7', 2, '2017-08-18 16:26:30 ');

Insert into test (tid, stat, createtime) VALUES ('4dfa129ba64e7062afa37e56bb9632de ', 2, '2017-08-18 14:32:41 ');

Insert into test (tid, stat, createtime) VALUES ('a9a731870e1c02278c22ce1ab36fa43c ', 2, '2017-08-18 14:31:26 ');

Insert into test (tid, stat, createtime) VALUES ('97f39d2a1e519f99e602e72cfc45fe0c ', 2, '2017-08-17 11:47:52 ');

Insert into test (tid, stat, createtime) VALUES ('31ba95265a96971221ddf9320c79eed8', 3, '2017-08-20 02:08:50 ');

Insert into test (tid, stat, createtime) VALUES ('060d92222edcb6f583cb4cd0244aadc0 ', 3, '2017-08-20 02:05:54 ');

Insert into test (tid, stat, createtime) VALUES ('7d3eb4ea201906b08e961b9fe7726fd4 ', 3, '2017-08-20 02:00:11 ');

Insert into test (tid, stat, createtime) VALUES ('c633bc16cb8c3bb4ffa7f00682701b92', 3, '2017-08-20 01:54:22 ');

Insert into test (tid, stat, createtime) VALUES ('e43bb7e7272139712b389e3feabc068f', 3, '2017-08-20 01:49:36 ');

Insert into test (tid, stat, createtime) VALUES ('bdabf3d80fb097222368cb30gj48117 ', 3, '2017-08-20 01:48:48 ');

Insert into test (tid, stat, createtime) VALUES ('170e2bdc11d517a56b7ce23d85633e42 ', 3, '2017-08-20 01:46:56 ');

Insert into test (tid, stat, createtime) VALUES ('7e79f6065ae8bb215cee43a4efbcd852 ', 3, '2017-08-20 01:44:17 ');

Insert into test (tid, stat, createtime) VALUES ('04728676e3305de05a18333ddfc76c01 ', 3, '2017-08-20 01:39:05 ');

Insert into test (tid, stat, createtime) VALUES ('d987176d350d4fefcc92b9a7ebb4f288 ', 3, '2017-08-20 01:35:52 ');

COMMIT;

Requirements:
Stat indicates the status, while Createtime indicates the creation time.
Queries the content of the last three records in each status.
 
This requirement is almost unknown.
 
But this is the MySQL database. He has no analysis function and can only simulate it with custom variables.
The stat Field of the test table is the group ID.
Gid is the group ID of the previous record,
Cgid is the group ID of the current record,
If the gid and cgid are different, the Group ID has changed and the Rank sorting is reset.
In this way, Rank is auto-incrementing according to each group, and the first N records are enough.
 

SELECT t3.tid, t3.stat, t3.createtime
 
FROM (SELECT @ gid: = @ cgid, @ cgid: = t1.stat, if (@ gid = @ cgid, @ rank: = @ rank + 1, @ rank: = 1) AS rank, t1 .*

FROM (SELECT *

FROM test

Order by stat, createtime DESC

) T1, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 1) t2
) T3

WHERE t3.rank <= 3;
Result:

MySQL allows N records to be randomly extracted from each group.
(Features in the previous link)
 
SELECT t3.tid, t3.stat, t3.createtime
 
FROM (SELECT @ gid: = @ cgid, @ cgid: = t1.stat, if (@ gid = @ cgid, @ rank: = @ rank + 1, @ rank: = 1) AS rank, t1 .*

FROM (SELECT *

FROM test

Order by stat, rand ()

) T1, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 1) t2
) T3

WHERE t3.rank <= 3;
 
---------------------------- Split line ---------------------------------
 
All of the above are simulation analysis functions using MySQL custom variables,
This function can also be implemented using traditional related subqueries, but the efficiency is low.
 
SELECT t1.tid, t1.stat, t1.createtime
 
FROM test t1

WHERE (

Select count (*)

FROM test t2

WHERE t2.stat = t1.stat

AND t1.createtime <t2.createtime

) <3

Order by stat, createtime DESC

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: