用 Python 和 SQLite 实现一套可复算的 DAU 排查工程

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站。
https://www.captainbed.cn/roman

用 Python 和 SQLite 实现一套可复算的 DAU 排查工程

日活从 100 降到 92，报表只能告诉我们差了 8 个账号。要判断业务发生了什么，需要知道这两个数是从哪些播放记录算出来的，哪些记录漏了，以及回补后仍然少了哪批人。这个工程用一组可重复生成的模拟事件，把这些问题变成可以执行的 SQL。

获取代码并运行

GitHub 示例仓库 包含模拟数据生成脚本、建模与查询 SQL、验收测试。准备 Python 3.10 或更新版本后，克隆仓库并运行。

git clone https://github.com/david-gao1/dau-analysis-lab.git
cd dau-analysis-lab
python3 run.py ./demo-output
python3 -m unittest discover

打开 demo-output/report.md，先核对日活是否为「100 → 92 → 97 → 97」，再到同目录的 analysis.sqlite 查证据。指定的输出目录需要尚未存在。省略目录参数时，脚本会创建临时目录并打印路径。项目使用 Python 3.10 和其标准库中的 SQLite，验收覆盖报告和数据库中的查询结果。

run.py 负责生成事件、对账回补和输出报告。 model.sql 保存表结构和分析视图。报告里的数字来自查询，可以直接用 SQLite 打开产出的数据库，查看结果背后的账号和事件。

先区分播放事件和日活账号

client_play 模拟客户端上报，可能重发或漏报。confirmed_play 模拟独立的成功播放记录。两份数据用同一个播放编号对账。发生时间决定事件归属哪一天，接收时间用于排查迟到。模拟数据已经转换成北京时间，因此 SQL 可以直接取发生时间的日期部分。

playback 每个播放编号一行。重传同一编号只写入一次。 user_day 是在此基础上按日期和账号去重的视图，同一账号用电视和手机各看一次，当天仍只算一个。这里用视图让报表和分析复用口径，也能让回补结果直接反映到下游。

CREATE VIEW user_day AS
SELECT DISTINCT day, account FROM playback;

样例里，一个账号 11 月 5 日 23:58 播放，11 月 6 日 00:03 上报。电视端重传了一次。这条播放最后只写入一条明细，仍归 11 月 5 日。

先对账，再解释剩下的差额

以独立成功记录为左表，按播放编号关联 playback。右侧为空的记录就是本次要检查的缺失事件。再用业务日和账号关联初报时的 user_day，找出这些缺失事件中哪些账号也没进日活名单。新版共有 6 条缺失事件，其中一条属于当天已经活跃的账号，所以少计的是 5 个账号。

回补时按播放编号执行 ON CONFLICT(play_id) DO NOTHING，重复回放同一批成功记录不会重复插入。实跑结果如下。

昨天	今天初报	今天回补后	重复回补后
100	92	97	97

日活还少 3 个。 activity_segments 先取每个账号历史首次播放日，再关联前一天的活跃名单。当天首次播放属于首次活跃，前一天也活跃属于持续活跃，其余属于回流。用同一规则分类两天，就能把净差额拆开。

人群	昨天	今天	变化
首次活跃	8	2	−6
持续活跃	88	90	+2
回流	4	5	+1

首次活跃下降最多，所以下一步看新人进入 App 后经历了什么。

用事件顺序找首播掉点，再查业务记录

只有播放明细就只能看到成功的人。漏斗从首次打开账号出发，用 EXISTS 判断每个账号当天是否先到达详情页、随后成功播放。同一步发生多次仍只记一次。样例里首次活跃都来自当天首次打开的人群，遇到隔天首播的业务数据，应另外定义观察窗口。

日期	首次打开	到达详情页	首播	详情页到达率
11 月 5 日	40	20	8	50%
11 月 6 日	40	5	2	12.5%

到达详情页后的首播率两天均为 40%。查询把下降定位到打开 App 和到达详情页之间。再从 funnel_steps 取当天未到达的推广账号，关联失败记录和影片授权，按地区与影片分组。样例查到南区 film-a 有 15 个账号收到不可播放错误。失败发生时已有授权到期，也没有有效授权覆盖那个时刻。这个关联给出了停投过期素材并复查到达率的依据。

留存、交易和实验分别从什么数据算

D7 留存用首播日圈定人群，再按账号和「首播日 + 7 天」关联活跃表。左连接保留没回来的账号，分母始终是首播人群。独立模拟批次有 20 人首播、4 人第 7 天回来，结果为 20%。续费按到期日另外分组，样例里有 2 人具备续费资格，1 人在窗口内续费。

订单核对先按订单分别汇总支付和退款，再做关联。订单 o1 支付 10,000 分，两次退款合计 3,000 分，净收 7,000 分。如果先把支付明细和两条退款明细直接关联，再求支付总额，这笔支付就会被算两次。报告也带上会员开通状态，方便继续追查。

A/B 实验先按固定随机种子分组，模拟旧入口和新入口。按全部入组账号计算首播率，同一账号首播成功多次也只算一次。样例的 A 组是 4/20，B 组是 6/20。这两组成功记录由模拟数据指定，只用于验证统计口径。实际评估需要独立采集实验结果并衡量不确定性。

交叉检查时，可以先查 reconciliation，再沿 playback、user_day 和 funnel_steps 找到形成结果的记录。自动验收从命令行运行同一条链路，并直接查询产出的数据库，覆盖日活重算、跨午夜重传、漏斗、授权、留存、退款和实验分组。

从报告追到一笔漏报

报告中「版本 2.0 / 6 条 / 5 个账号」可以从数据库独立复算。下面用客户端原始上报和独立成功记录还原初报时的缺口。回补后的播放明细已包含缺失事件，因此回看初报要用保留下来的原始输入。

SELECT c.play_id, c.account,
       NOT EXISTS (
           SELECT 1 FROM client_play p
           WHERE p.account = c.account
             AND substr(p.occurred, 1, 10) = '2026-11-06'
       ) AS account_missing_from_initial_dau
FROM confirmed_play c
WHERE substr(c.occurred, 1, 10) = '2026-11-06'
  AND NOT EXISTS (
      SELECT 1 FROM client_play p WHERE p.play_id = c.play_id
  )
ORDER BY c.account, c.play_id;

用 SQLite 客户端打开 demo-output/analysis.sqlite，再执行上面的查询。应返回 6 行，其中 5 行标记为 1。账号 u1 那行标记为 0，因为它当天还有一条已上报的播放。接下来查 activity_delta 和 funnel，验证剩余差额如何引出首播排查。样例的处置依据来自授权和失败记录，后续是否恢复要用处理后的同口径数据判断。

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/hiliang521/article/details/170198666