mysql没有select into但有create select

create table login_10W(
SELECT USERNAME as user_id,date_format( logindate,'%Y-%m-%d') as login_date,count(1) as login_times
  FROM app_userloginlog where /*USERNAME like '%xx%' */ 1=1
  group by date_format( logindate,'%Y-%m-%d') ,USERNAME limit 0,100000
);

SELECT
	user_id,
	count( 1 ) cnt -- , date_sub( login_date, INTERVAL t.rn DAY ) AS flag_dt
FROM
	( SELECT user_id, login_date, row_number ( ) over
    ( PARTITION BY user_id ORDER BY login_date ) 
    AS rn, login_times FROM login_100W where login_times >= 10 ) t 
GROUP BY
	user_id,
	date_sub( login_date, INTERVAL t.rn DAY ) 
HAVING
	count( 1 ) >= 3;
-- 连续三天(或更多)每天登录超10次

这个sql可以查, 连续多少天怎么样
很巧妙,效率非常之高
虽然应用面有点窄

java的做法

public void aa(){

        int x = 7;  //阈值

        LinkedList a = new LinkedList<>(); //结果  (取结果里最大的元素, 即最大连续超过阈值的次数)
        int[] aa = {5,8,11,6,12,14,16,9}; //input 数据

        int temp=0;

        for(int index = aa.length; index > 0 ; index --){
            if(aa[index-1]  - x >  0){
                temp ++;
            }else{
                a.add(temp);
                temp = 0;
            }
        }
        a.add(temp);

        LOGGER.info(a);

        //输出 max(a)   即结果
    }