找到数据集中最大的连续的日期之sas和mysql实现

找到数据集中最大的连续的日期-sas实现

目录

找到数据集中最大的连续的日期-sas实现

sas实现

mysql实现

---

亲测有效

sas实现

/*目标：找到数据集中最大的连续的日期？*/

/*制作只有日期的单列数据集*/
data a;
do i="01Jan2010"d to "20Jan2010"d by 1;
if i^="02Jan2010"d and i^="04Jan2010"d and i^="10Jan2010"d and i^="17Jan2010"d then output;
end;
format i yymmdd10.;
run;

proc sort data=a; by i;run;


/*查找过程*/
data b;
retain j grp cnt;
set a;
by i;
if _N_=1 then do;
j=i;
grp=0;
cnt=1;
end;
else do;
	if i-j=1 then do;
	cnt+1;
	end;
	else do;
	grp+1;
	cnt=1;
	end;
	j=i;
	end;
/*drop j;*/
format j yymmdd10.;
run;

/*展示*/
proc sql;
create table c as
select grp,max(cnt) as max ,min(i) format  yymmdd10. as start_date,max(i) format  yymmdd10. as end_date
from b group by grp
;quit;
/*proc sql;*/
/*create table c as*/
/*select*/
/*a.i*/
/*from b a*/
/*inner join (select grp,max(cnt) as max from b group by grp having max>=4) b*/
/*on a.grp=b.grp*/
/*;*/
/*quit;*/


/*如果是两个变量求*/
/*制作数据集*/
data a;
retain id date fg;
do date="01Jan2010"d to "20Jan2010"d by 1;
fg=0;

if date^="02Jan2010"d and date^="04Jan2010"d and date^="10Jan2010"d and date^="17Jan2010"d 
then  do
fg=1 ;
end;

if date<="05Jan2010"d then id=1;
else id=2;
output;
end;
format date yymmdd10.;
run;

/*处理数据集*/
data a;
set a;
where fg=1;
run;

proc sort data=a;
by id date;run;
quit;

data b;
retain id_new date_new grp cnt;
set a;
by id date;
if _N_=1 
	then do;
	id_new=id;
	date_new=date;
	grp=0;
	cnt=1;
	end;
else do;
	if date-date_new=1 and id=id_new
		then do;
		cnt+1;
		end;
	else if (id=id_new) and date-date_new>1 then do;
		grp+1;
		cnt=1;
		end;
	else do;
		grp=0;
		cnt=1;
		end;
		date_new=date;
		id_new=id;
	end;
/*drop date_new id_new;*/
  keep id date fg grp cnt;
run;

data b;
retain id date fg grp cnt;;
set b;
run;

/*制作数据集测试*/
data a;
do i=1 to 5 by 1;
output;
end;
run;

 

mysql实现

SELECT period_state, MIN(date) as start_date, MAX(date) as end_date
FROM (
    SELECT
        success_date AS date,
        "succeeded" AS period_state,
        IF(DATEDIFF(@pre_date, @pre_date := success_date) = -1, @id, @id := @id+1) AS id 
    FROM Succeeded, (SELECT @id := 0, @pre_date := NULL) AS temp
    UNION
    SELECT
        fail_date AS date,
        "failed" AS period_state,
        IF(DATEDIFF(@pre_date, @pre_date := fail_date) = -1, @id, @id := @id+1) AS id 
    FROM Failed, (SELECT @id := 0, @pre_date := NULL) AS temp
) T  WHERE date BETWEEN "2019-01-01" AND "2019-12-31"
GROUP BY T.id
ORDER BY start_date ASC



SELECT
        success_date AS date,
        "succeeded" AS period_state,
        IF(DATEDIFF(@pre_date, success_date) = -1, @id, @id := @id+1) AS id ,
        IF(DATEDIFF(@pre_date, success_date) = -1, @red := @red+1,@red:=1   ) AS red,
        @pre_date := success_date
        
    FROM riqi, (SELECT @id := 0, @pre_date := NULL ,@red := 1) AS temp