Shulou(Shulou.com)06/01 Report--

Let's learn about the simple method of Mysql continuous data query. I believe you will benefit a lot after reading it. The text is not much in essence. I hope the simple method of Mysql continuous data query is what you want.

Sequential line number-minus head difference = continuous difference block

Sequential line numbers are like rownum in oracle, but mysql doesn't have this feature yet, so it can only be achieved through local variables.

The subtractive difference is the difference between each record and the initial record (it needs to be ensured that the difference is the same as the sequential line number increment, of course, if it is self-increment, it does not need to be calculated separately)

As long as the sequential line number is the same as the subtractive difference value, the continuous difference block value is the same, and the continuous length can be calculated.

Sample table: (take a simple check-in table as an example)

Create table user_sign (id int unsigned primary key auto_increment,user_id int unsigned not null comment 'user ID',date date not null comment' check-in date', created_time int unsigned not null comment 'creation time', updated_time int unsigned not null comment 'modification time') engine=innodb default charset=utf8 comment 'user check-in'

Randomly generate data (create function to randomly generate check-in data)

Create function insert_sign_data (num int) returns intbegindeclare _ num int default 0 continue handler for SQLSTATE _ date date;declare _ tmpdate date;declare _ user_id int;declare line int default 0 * * get_last cursor for select date from user_sign where user_id=_user_id order by date desc limit 1 *

< num doset _user_id = CEIL( RAND( ) * 500 );open _get_last;fetch _get_last into _tmpdate;IF line THENset _date = FROM_UNIXTIME( unix_timestamp( ) - 86400 * round( RAND( ) * 200 ), '%Y-%m-%d' );set line = 0;ELSEset _date = FROM_UNIXTIME( unix_timestamp( _tmpdate ) + 86400 * round( RAND( ) * 2 + 1), '%Y-%m-%d' );END IF;INSERT INTO user_sign ( user_id, date, created_time, updated_time ) VALUES (_user_id, _date, unix_timestamp( ), unix_timestamp( ));set _num = _num + 1;close _get_last;end while;return _num;end 生成数据（由于生成时有判断最近打卡日期生成有会点慢） select insert_sign_data(20000); 提取出连续打卡超过6天的用户 SELECTuser_id,val - ( @rownum := @rownum + 1 ) AS type,group_concat( date ) AS date_join,count( 1 ) num FROM(SELECTus1.date,us1.user_id,( unix_timestamp( us1.date ) - min_timestamp ) / 86400 + 1 AS val FROMuser_sign AS us1LEFT JOIN ( SELECT UNIX_TIMESTAMP( min( date ) ) AS min_timestamp, user_id, min( date ) AS min_date FROM user_sign GROUP BY user_id ) AS us2 ON us1.user_id = us2.user_id ORDER BYus1.user_id ASC,us1.date ASC ) AS t1,( SELECT @rownum := 0 ) AS t2 GROUP BYuser_id,type HAVINGnum >

six

What is queried here is more than 3 clock-ins in the whole table, and the date is displayed.

The idea of the query is:

Extract the difference between each punch record of the full table user and the first punch record, but sort by user and date.

Add a local variable rownum to check with the query data above.

Using the date difference minus the self-increasing sequence line number in the result field set is worth going to the continuous difference block

Obtain continuous check-in times by grouping users and continuous difference blocks

Use having to extract users who have checked in more than 6 times

After reading this article on simple methods of Mysql continuous data query, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.