oracle

insert into t_user_login 
SELECT round( dbms_random.value(1,10)), to_date(TRUNC(DBMS_RANDOM.VALUE(

       to_number(to_char(to_date('20201201','yyyymmdd'),'J')),

       to_number(to_char(to_date('20201231','yyyymmdd')+1,'J')))),'J')+

       DBMS_RANDOM.VALUE(1,3600)/3600

       prize_time

FROM dual;

commit;

 

 

 

--查询

with
tmp_day as  --用户登陆日期
( 
  select distinct 
         user_id
         , to_date( to_char(login_time,'YYYY-MM-DD'),'YYYY-MM-DD')  day
  from t_user_login 
  order by day,user_id
),
tmp_min as   --用户首次登陆日期
( 
  select   
         user_id
         ,min( to_date( to_char(login_time,'YYYY-MM-DD'),'YYYY-MM-DD')  )  min_day
  from t_user_login 
  group  by user_id
)
select distinct
     a.USER_ID, a.MIN_DAY,   b1.DAY, b3.DAY,  b7.DAY
from tmp_min a
left join tmp_day b1 
      on TO_NUMBER(b1.day - min_day) <= 1
      and b1.day <> min_day
      and a.user_id = b1.user_id
left join tmp_day b3
      on TO_NUMBER(b3.day - min_day) <= 3
      and b3.day <> min_day
      and a.user_id = b3.user_id

left join tmp_day b7
      on TO_NUMBER(b7.day - min_day) <= 7
      and b7.day <> min_day
      and a.user_id = b7.user_id
     
where a.user_id = 6      
order by a.min_day,a.user_id 
;
 select distinct 
         user_id
         , to_date( to_char(login_time,'YYYY-MM-DD'),'YYYY-MM-DD')  day
  from t_user_login 
  where user_id = 6
  order by day,user_id