esproc desktop-九游会登陆

df = pd.read_csv("../login_data.csv")
df["ts"] = pd.to_datetime(df["ts"]).dt.date
grouped = df.groupby("userid")
aligned_dates = pd.date_range(start=df["ts"].min(), end=df["ts"].max(), freq='d')
user_date_wether_con3days = []
for uid, group in grouped:
    group = group.drop_duplicates('ts')
    aligned_group = group.set_index("ts").reindex(aligned_dates)
    consecutive_logins = aligned_group.rolling(window=7)
    n = 0
    date_wether_con3days = []
    for r in consecutive_logins:
        n  = 1
        if n<7:
            continue
        else:
            ds = r['userid'].isna().cumsum()
            cont_login_times = r.groupby(ds).userid.count().max()
            wether_cont3days = 1 if cont_login_times>=3 else 0
            date_wether_con3days.append(wether_cont3days)
    user_date_wether_con3days.append(date_wether_con3days)
arr = np.array(user_date_wether_con3days)
day7_cont3num = np.sum(arr,axis=0)
result = pd.dataframe({'dt':aligned_dates[6:],'cont3_num':day7_cont3num})

with all_dates as (
	select distinct trunc(ts) as login_date
	from login_data),
user_login_counts as (
	select userid, trunc(ts) as login_date, 
		(case when count(*)>=1 then 1 else 0 end) as login_count
	from login_data
	group by userid, trunc(ts)),
whether_login as (
	select u.userid, ad.login_date, nvl(ulc.login_count, 0) as login_count
	from all_dates ad
	cross join (
		select distinct userid
		from login_data) u
	left join user_login_counts ulc
	on u.userid = ulc.userid
	and ad.login_date = ulc.login_date
	order by u.userid, ad.login_date),
whether_login_rn as (
	select userid,login_date,login_count,rownum as rn 
	from whether_login),
whether_eq as(
	select userid,login_date,login_count,rn,
		(case 
			when lag(login_count,1) over (order by rn)= login_count 
				and login_count =1 and lag(userid,1) over (order by rn)=userid 
			then 0 
			else 1 
		end) as wether_e	
	from whether_login_rn
),
numbered_sequence as (
	select userid,login_date,login_count,rn, wether_e,
		sum(wether_e) over (order by rn) as lab
	from whether_eq),
consecutive_logins_num as (
	select userid,login_date,login_count,rn, wether_e,lab,
  		(select (case when max(count(*))<3 then 0 else 1 end)
   		from numbered_sequence b
   		where b.rn between a.rn - 6 and a.rn
		and b.userid=a.userid
   		group by b. lab) as cnt
	from numbered_sequence a)
select login_date,sum(cnt) as cont3_num
from consecutive_logins_num
where login_date>=(select min(login_date) from all_dates) 6
group by login_date
order by login_date;