N1Easy★ BA/DS 转正高频 · 数据工程桥梁Part N · 数据特征管道与生产系统算子
队列留存矩阵与 SQL->Python 事件流归因
脱离 BI 手工透视表,用 O(E) 一次扫描从行为日志构建精确去重的 Cohort Day-N 留存矩阵。
⏱️ 时间复杂度:
O(E)💾 空间复杂度:
O(U * K)🎯 免登录实战自测闭环(先做再看答案)
按「机制回忆 → 关键行填空 → 边界反例 → 追问与错题记录」顺序自测,进度自动保存在本地。
Step 1 · 机制口述与数值稳定性自检
Step 2 · 核心代码关键行补全
import numpy as np
def compute_cohort_retention(signup_day: list, event_day: list, user_ids: list, max_offset: int = 3) -> np.ndarray:
"""
Vectorized Day-0..Day-K cohort retention matrix without nested SQL loops.
Returns matrix M of shape (num_cohorts, max_offset + 1) where M[c, k] is fraction retained.
"""
cohorts = sorted(set(signup_day))
c_to_idx = {c: idx for idx, c in enumerate(cohorts)}
cohort_users = [set() for _ in cohorts]
for uid, sd in zip(user_ids, signup_day):
cohort_users[c_to_idx[sd]].add(uid)
uid_signup = dict(zip(user_ids, signup_day))
ret = np.zeros((len(cohorts), max_offset + 1), dtype=np.float64)
seen =
for uid, ed in zip(user_ids, event_day):
off =
if 0 <= off <= max_offset:
seen[c_to_idx[uid_signup[uid]]][off].add(uid)
for ci, users in enumerate(cohort_users):
denom = float(len(users)) if users else 1.0
for k in range(max_offset + 1):
ret[ci, k] = len(seen[ci][k]) / denom
return ret
Step 3 · 反例与边界断言核对
Step 4 · 面试追问盲答与错题记录
Q1: 在百万用户 DAU 管道中,如何把 Cohort 计算进一步向量化到 NumPy / PySpark?
💡
实战一句话记忆口诀
单遍哈希求天差,集合去重保真率,负偏移与零分母必拦截
📐 核心数学公式与机理推导
### 从 SQL 自连接向 Python 状态桶迁移
BA/DA 习惯用 SQL
在 Python 特征工程与数据管道中,通过建立
- 关键防坑:同一天同一用户多次活跃只能记 1 次,直接累加事件次数会导致留存率 > 100%。
BA/DA 习惯用 SQL
LEFT JOIN ... ON a.user_id = b.user_id AND b.day = a.signup + k,当事件流膨胀到上亿行时会产生严重 Shuffle 爆炸。在 Python 特征工程与数据管道中,通过建立
uid -> signup_day 哈希表并在单次线性遍历中将 offset = event_day - signup_day 归入去重集合,时间复杂度降至 。- 关键防坑:同一天同一用户多次活跃只能记 1 次,直接累加事件次数会导致留存率 > 100%。
🔄 张量维度与形状流转说明
(user_ids, signup_day, event_day) [E] -> uid 哈希偏移差 -> seen_sets[C, K+1] -> float64 matrix [C, K+1]
🛡️ 数值稳定性自检与边界要点
- 必须按
user_id去重统计每日活跃人数,防止单个高频用户在 Day-1 刷出 >100% 留存率 - 严格约束
0 <= event_day - signup_day <= max_offset,过滤历史脏数据回溯导致的负偏移 - 空队列分母做
denom = 1.0零保护避免 NaN 污染下游特征矩阵
💻 完整参考代码实现
import numpy as np
def compute_cohort_retention(signup_day: list, event_day: list, user_ids: list, max_offset: int = 3) -> np.ndarray:
"""
Vectorized Day-0..Day-K cohort retention matrix without nested SQL loops.
Returns matrix M of shape (num_cohorts, max_offset + 1) where M[c, k] is fraction retained.
"""
cohorts = sorted(set(signup_day))
c_to_idx = {c: idx for idx, c in enumerate(cohorts)}
cohort_users = [set() for _ in cohorts]
for uid, sd in zip(user_ids, signup_day):
cohort_users[c_to_idx[sd]].add(uid)
uid_signup = dict(zip(user_ids, signup_day))
ret = np.zeros((len(cohorts), max_offset + 1), dtype=np.float64)
seen = [[set() for _ in range(max_offset + 1)] for _ in cohorts]
for uid, ed in zip(user_ids, event_day):
off = ed - uid_signup[uid]
if 0 <= off <= max_offset:
seen[c_to_idx[uid_signup[uid]]][off].add(uid)
for ci, users in enumerate(cohort_users):
denom = float(len(users)) if users else 1.0
for k in range(max_offset + 1):
ret[ci, k] = len(seen[ci][k]) / denom
return ret
🧪 本地测试与正确性断言
可直接复制到本地 Python / Jupyter 验证:
import numpy as np
# Cohort Day 0: u1, u2 (u1 active Day 0,1; u2 active Day 0 only -> D1 retention = 0.5)
# Cohort Day 1: u3 (active Day 1, 2 -> D1 retention = 1.0)
signups = [0, 0, 0, 1, 1]
events = [0, 1, 0, 1, 2]
uids = ["u1", "u1", "u2", "u3", "u3"]
mat = compute_cohort_retention(signups, events, uids, max_offset=2)
assert np.allclose(mat[0], [1.0, 0.5, 0.0]), f"Unexpected cohort 0: {mat[0]}"
assert np.allclose(mat[1], [1.0, 1.0, 0.0]), f"Unexpected cohort 1: {mat[1]}"
print("✓ Vectorized cohort retention assertion passed")🎯 核心工程追问与标准应答
Q1:在百万用户 DAU 管道中,如何把 Cohort 计算进一步向量化到 NumPy / PySpark?
先按
(cohort_day, offset, user_id) 做一次 Hash-Distinct,再通过 np.add.at 累加到二维计数矩阵并用 cohort_size[:, None] 广播除法即可,无需多层字典套集合。⚡
想检验本题的肌肉记忆?进入 3 阶闪卡主动回忆盲测
隐藏答案遮罩,自测:审题锚点 ➔ 数理公式机理 ➔ 工业标杆代码与连环追问。3 分钟攻破认知盲区!