活跃时长计算：固定分母 vs 动态分母
一个真实产品中的 SQL + 数据分析优化案例，适合讲解「按小时聚合统计」时如何处理分母。
1. 背景：我们在算什么？
TimeMirror 是一个 macOS 屏幕时间追踪工具。它的 Focus 分析 页面展示一张柱状图：
- X 轴：一天中的 24 个小时（00:00～23:00）
- Y 轴：每个小时的「活跃时长比例」
举个例子：如果本周你有 3 天在 14:00 用过电脑，这 3 天的 14:00 总共活跃了 45 分钟，那么 14:00 的活跃比例就是：
45 / (3 × 60) = 25%
问题来了：分母中的「3 天」怎么算？
2. 方案 A（旧）：固定分母
思路
不管这个小时你有没有用过电脑，分母都用「统计周期内的总天数 × 3600 秒」。
flowchart LR
subgraph Row1["数据聚合"]
direction LR
A["查询 session"] --> B["按小时拆分"] --> C["activeMap[hour]<br/>+= 秒数"]
end
subgraph Row2["周期判断 — 所有小时共用同一分母"]
direction LR
D{"周期?"}
D -->|"今日"| E["× 1"]
D -->|"本周"| F["× 7"]
D -->|"本月"| G["× 30"]
D -->|"全部"| H["SQL查询<br/>总天数"]
end
Row1 --> D
E --> I["hoursPerSlot<br/>= N天 × 3600"]
F --> I
G --> I
H --> I
subgraph Row3["计算"]
direction LR
I --> J["所有小时共用<br/>同一个分母"]
J --> K["focusRatio<br/>= active / hoursPerSlot"]
end
核心代码
// 每个小时的可用时长：固定值
let hoursPerSlot: Double
switch period {
case .today, .yesterday: hoursPerSlot = 3600
case .week: hoursPerSlot = 7 * 3600
case .month: hoursPerSlot = 30 * 3600
case .all: hoursPerSlot = totalDays * 3600
}
// 所有小时共用同一个分母
for h in 0...23 {
let active = activeMap[h] ?? 0
let idle = max(0, hoursPerSlot - active)
// focusRatio = active / hoursPerSlot
}
问题在哪？
gantt
title 本周 14:00 小时的实际情况
dateFormat YYYY-MM-DD
axisFormat %m-%d
section 电脑使用
周一 14:00 活跃 :active, 2026-05-25, 1d
周二 14:00 活跃 :active, 2026-05-26, 1d
周三 14:00 没开机 :crit, 2026-05-27, 1d
周四 14:00 活跃 :active, 2026-05-28, 1d
周五 14:00 没开机 :crit, 2026-05-29, 1d
周六 14:00 没开机 :crit, 2026-05-30, 1d
周日 14:00 没开机 :crit, 2026-05-31, 1d
分母 = 7 × 3600 = 25200 秒，但其实只有 3 天开了机。
假设这 3 天的 14:00 你都在高效工作，每个小时活跃了 50 分钟（3000 秒）：
| 指标 | 固定分母 | 应该是 |
|------|---------|--------|
| 活跃秒数 | 3 × 3000 = 9000 | 9000 |
| 分母 | 7 × 3600 = 25200 | 3 × 3600 = 10800 |
| **活跃比例** | **35.7%** | **83.3%** |
35.7% 严重低估了你的真实活跃程度，因为把「根本没开机」的天数也算进了分母。
3. 方案 B（新）：动态分母
思路
用 Set<日期> 在遍历 session 时去重统计每个小时实际有活跃的天数，分母 = 活跃天数 × 3600。flowchart LR
subgraph Row1["遍历每条 Session"]
direction LR
A["startTs → endTs"] --> B["dayIndex<br/>= (startTs+8h)/86400"]
B --> C["按小时切分"]
end
subgraph Row2["双轨并行写入"]
direction LR
C --> D["activeMap[hour]<br/>+= 秒数"]
C --> E["activeDaysMap[hour]<br/>.insert(dayIndex)"]
end
subgraph Row3["Set 自动去重"]
direction LR
E --> F["同一天多条 Session<br/>→ Set 只存一次"]
F --> G["days = Set.count"]
end
subgraph Row4["每小动态分母"]
direction LR
D --> H["activeSec"]
G --> I["slotSec = days × 3600"]
H --> J["focusRatio<br/>= activeSec / slotSec"]
I --> J
end
核心代码
var activeMap = [Int: Double]()
var activeDaysMap = [Int: Set<Int>]() // ← 新增：小时 → 日期集合
for session in sessions {
let dayIndex = Int(startTs + 8*3600) / 86400 // 北京日期编号
var t = startTs + 8*3600
let end = endTs + 8*3600
while t < end {
let hour = (Int(t / 3600) % 24)
let segEnd = min(整点边界, end)
activeMap[hour, default: 0] += (segEnd - t)
activeDaysMap[hour, default: Set()].insert(dayIndex) // ← Set 自动去重
t = segEnd
}
}
// 构造结果：每个小时用各自的天数
for h in 0...23 {
let active = activeMap[h] ?? 0
let days = activeDaysMap[h]?.count ?? 0
// focusRatio = active / (days × 3600)
}
为什么 Set 就够了？
sequenceDiagram
participant S1 as Session 1<br/>周一 13:50-14:10
participant S2 as Session 2<br/>周一 14:30-14:45
participant Map as activeDaysMap[14]
S1->>Map: insert(周一)
Note over Map: {周一}
S2->>Map: insert(周一)
Note over Map: {周一} ← 自动去重，不会计数两次
同一天的多条 session 对应同一个小时，Set.insert 自动去重，无需额外逻辑。即使 session 跨天（如 23:50→00:10），也不会错：
| 时间段 | hour | dayIndex | 插入 |
|--------|------|----------|------|
| 23:50–00:00 | 23 | 周一 | `Map[23] = {周一}` |
| 00:00–00:10 | 0 | 周一 | `Map[0] = {周一}` |
4. 两种方案对比
flowchart LR
subgraph A["方案 A：固定分母"]
direction LR
A1["本周 14:00"] --> A2["分母<br/>7×3600<br/>=25200s"] --> A3["活跃 9000s"]
A3 --> A4["9000/25200<br/>= 35.7%"] --> A5["❌ 低估"]
end
subgraph B["方案 B：动态分母"]
direction LR
B1["本周 14:00"] --> B2["activeDays=3<br/>分母=3×3600<br/>=10800s"] --> B3["活跃 9000s"]
B3 --> B4["9000/10800<br/>= 83.3%"] --> B5["✅ 准确"]
end
| 维度 | 方案 A（固定分母） | 方案 B（动态分母） |
|------|-------------------|---------------------|
| **分母计算** | `N天 × 3600`，所有小时相同 | `activeDays × 3600`，每小不同 |
| **额外 SQL** | `.all` 需要额外查询总天数 | 无需额外查询 |
| **代码行数** | ~30 行 switch-case | ~5 行 Set 操作 |
| **准确性** | 包含「没开机」天数，**低估活跃度** | 只算有活跃的天数，**准确反映真实活跃** |
| **无活跃的小时** | 分母仍为 N×3600，idle 虚高 | 分母=0，柱高=0，不显示 |
5. 核心启示
5.1 分母定义决定结论
同一个分子（9000 秒活跃），分母不同，结论天差地别：
$$ \text{方案A: } \frac{9000}{25200} = 35.7\% \quad \text{vs} \quad \text{方案B: } \frac{9000}{10800} = 83.3\% $$
35.7% 看起来你「大部分时间在摸鱼」，83.3% 说明你「只要开机就很专注」。
5.2 Set 比 SQL COUNT DISTINCT 更灵活
flowchart LR
subgraph SQL["方案：SQL COUNT DISTINCT"]
direction LR
S1["额外SELECT<br/>COUNT DISTINCT"] --> S2["需要窗口函数<br/>按小时分组"] --> S3["与逐条拆分<br/>逻辑分离"] --> S4["两套代码<br/>维护成本高"]
end
subgraph Set["方案：Set 去重（5行代码）"]
direction LR
T1["Set.insert<br/>dayIndex"] --> T2["O(1) 内存操作"] --> T3["与遍历循环<br/>自然融合"] --> T4["代码短<br/>零额外查询"]
end
Set<Int> 在内存中完成去重，与遍历 session 的循环天然融合，不需要额外 SQL 查询。
5.3 「简单」≠「简陋」
方案 B 代码更短（少 25 行），逻辑更直观（「这个小时有多少天活跃过？」），结果更准确。好的设计往往更简单。
6. 课后思考
- 如果用户在一小时内的活跃跨越了两个日期（如 23:50→00:10），方案 B 的
dayIndex归属于 session 开始时所在的日期。你认为这样合理吗？如果改为归属到「该段时长落在那一天」会有什么不同？ activeDays统计的是「该小时有 session 记录的天数」。如果某天 14:00 你只开了 10 秒的浏览器就关掉了，它也算作一个「活跃天」。你认为应该加最短时长阈值吗？为什么？- 方案 B 的 Y 轴固定为 0-100%。你能想出什么场景下，某小时的活跃比例可能超过 100% 吗？它应该超过 100% 吗？
No comments yet