桌面数据处理和分析的必备神器 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;