给网站搭一个 GitHub 风格的活动热力图：从 SQL 到打分算法

GitHub 贡献图那种热力图，看一眼就知道自己哪天活跃哪天摸鱼。我在 Hermes Agent 的管理后台也搭了一个，基于 state.db 的 messages 表，按天聚合，用一套加权算法打出 0–7 的综合分，最后在前端渲染成 53 列的色块网格。

这篇从头讲到尾：数据怎么查、分怎么打、前端怎么画，以及上线后踩的一个坑。

1. 数据层：从 messages 表出发

热力图的原始数据是 Hermes 的 state.db。里面有两张关键表：

sessions：每次会话一条记录，存 token 用量、API 调用数、模型名
messages：每条消息一条记录，存时间戳、角色、内容

1.1 为什么用 messages 做日切

热力图用 messages 表做日切，因为 messages.timestamp 精确反映"哪天产生了消息"，而不是 session 创建日。跨天会话的 token 按消息数比例分配到每一天。

核心 CTE：

WITH day_sessions AS (
    SELECT date(datetime(m.timestamp, 'unixepoch', 'localtime')) as day,
           m.session_id, COUNT(*) as msgs
    FROM messages m
    WHERE m.timestamp IS NOT NULL
    GROUP BY day, m.session_id
),
sess_totals AS (
    SELECT id,
           COALESCE(input_tokens,0) as it,
           COALESCE(output_tokens,0) as ot,
           COALESCE(cache_read_tokens,0) as ct,
           COALESCE(api_call_count,0) as api,
           MAX(message_count,1) as msg_total
    FROM sessions
    WHERE id IN (SELECT DISTINCT session_id FROM day_sessions)
)
SELECT ds.day as date,
       CAST(ROUND(SUM(st.it * ds.msgs / st.msg_total)) AS INTEGER) as tokens,
       CAST(ROUND(SUM(st.api * ds.msgs / st.msg_total)) AS INTEGER) as api_calls,
       COUNT(DISTINCT ds.session_id) as sessions,
       SUM(ds.msgs) as messages
FROM day_sessions ds
LEFT JOIN sess_totals st ON st.id = ds.session_id
GROUP BY ds.day ORDER BY ds.day

1.2 数据设计决策

两个选择值得说一下。

用 LEFT JOIN 而不是 JOIN。实际使用中有些 session 记录会被定时清理掉但消息还在——如果用了 JOIN，这些天的格子会直接消失。LEFT JOIN 让它们仍然出现在热力图上，token 列为 0，显示为灰色而非空白。第 4 节会展开讲这个坑。

另外，sessions.source 字段记录了每次会话是人工打开的（cli、weixin）还是 cron 自动跑的。给 human_sessions 和 cron_sessions 分别计数，后面打分算法会区分两者权重。

2. 打分：综合分怎么算

热力图每个格子有一个 0–7 的等级，但原始数据是 4 个维度（token、人工会话、消息数、工具调用量），量纲和分布完全不同——token 可能一天几十万，工具调用可能一天几十次。

不能直接加和。需要两步：先归一化，再加权合成。

2.1 归一化

四个维度各自做 log1p（防止 0 值炸 logarithm），然后做 rank percentile 归一化：在所有正样本中，这个值排在百分之几的位置，就给 0–1 之间的一个分。

def norm(values, val):
    """rank percentile: 0→0, max→1"""
    positives = [v for v in values if v > 0]
    if not positives or val <= 0:
        return 0
    return sum(1 for v in positives if v < val) / len(positives)

2.2 加权合成

score = 0.45 * norm(all_tokens, day_tokens) \
      + 0.30 * norm(all_human, day_human) \
      + 0.15 * norm(all_msgs, day_msgs) \
      + 0.10 * norm(all_tools, day_tools)

权重不是拍脑袋定的。Token 占 45% 因为它是资源消耗的最直接指标。人工会话 30% 因为"我亲自用了 Agent"本身就重要，不靠 cron 代劳的日子值得深色。消息数 15% 和工具调用 10% 是辅助维度，防止只靠 token 和人工导致"开了个长对话但没干什么事"的天也标深。换一种使用场景权重可能需要调，关键是公开公式本身。

2.3 分等级

合成出 0–1 的 score 后，按百分位切成 0–7 等级。阈值取 p12.5 / p25 / p37.5 / p50 / p62.5 / p75 / p87.5，最活跃的 12.5% 天永远是 level 7，中间 50% 集中在 level 3–4。用百分位而不是固定绝对值的好处是：无论数据怎么涨，色阶的视觉分布保持稳定。如果用 >100K token = level 7 这种硬阈值，三个月后可能所有天都是最大级。

2.4 色板

1 灰 + 7 橙：#f4f4f5（无活动）→ #fff7ed → #fed7aa → #fdba74 → #f97316 → #ea580c → #c2410c → #9a3412。全站设计主色是 owOrange (#F97316)，热力图作为系统监控页的一部分，跟整体视觉保持一致。

3. 前端渲染：53 列自适应网格

3.1 列宽动态计算

核心结构是 CSS Grid：7 行（周一到周日）× 53 列（一年最多 53 周）。列宽不能写死，不同屏幕宽度下卡片大小不同，格子也要跟着缩。用 JS 拿卡片实际宽度，减去左侧工作日标签列和格子间距，除以周数：

var cellW = (cardWidth - wdayLabelWidth - gap * (weekCount - 1)) / weekCount;
cellW = Math.round(cellW * 100) / 100; // 子像素精度，防舍入空隙

算出的 cellW 同时设给 gridAutoColumns 和 gridTemplateRows，确保每个格子是正方形。

3.2 月份标签和交互

月份标签的算法：遍历 53 周 × 7 天的所有格子，记下当前月份。每当月份变化，在对应列的最上方插入一个月份名——但如果新月份从这一列的第 4 天以后才开始（比如 6 月 30 日是周三，7 月 1 日是周四，在同一列内），就把标签放到行内而非列顶，避免两个月的标签挤在一起。

工作日标签固定左侧一列，只标一三五，二四六七留空。

hover 时弹出 tooltip，一行日期加周几，然后活跃度等级、token 数（千分位）、会话拆分（人工 N / cron M）、消息和工具调用量、综合分。

3.3 做减法：从 4 Tab 到 1 个

初版热力图有 4 个 Tab 切换（综合 / Token / 会话 / 人工），每个维度独立算一遍分位数和色阶。上线后发现 Token 和人工两个维度在数据空白期全是灰格子——不怪算法，是数据源本身不完整。四个维度里真正经常看的就是综合分。

砍成只剩综合分，删了 40 行 JS、18 行 CSS、一个 pill 组件。有时候少一个 Tab 比多三个更好。

4. 踩坑：上线后数据对不上了

4.1 问题发现

给热力图换数据源的时候，我顺手把 /token/database 的每日明细也改成了 messages 聚合。改完部署上去，扫了一眼没报错，就以为没事了。

第二天打开，发现 /token/database 底部的合计行和上面的每日明细加起来对不上。差了将近 10%。

合计行来自 sessions 表直接 SUM：

SELECT SUM(input_tokens + output_tokens + cache_read_tokens) FROM sessions

每日明细行是 messages CTE 按比例分配。两个算法出来的总数不一样。

4.2 根因

一查，messages 表有 849 个不同的 session_id，但 sessions 表只有 560 条记录：

$ python3 -c "
SELECT COUNT(DISTINCT session_id) FROM messages  -- 849
SELECT COUNT(*) FROM sessions                    -- 560
"

那 328 个幽灵 session_id 全部集中在 2026 年 4 月 27 日到 5 月 26 日，共 15,521 条消息。谁清的？

在 ~/.hermes/config.yaml 里找到了：

session_reset:
  at_hour: 4
  idle_minutes: 1440
  mode: both

Hermes Agent 每天凌晨 4 点自动清理闲置超过 24 小时的 session。但它只删 sessions 表，不联动清理 messages 表。session 记录被清掉了，1.5 万条消息纹丝不动。messages 级聚合去 sessions 表查 token 时全是 NULL，每日明细的和比合计行少了 5600 万。

4.3 修复：分离口径

知道了根因，修法就清楚了。不需要让两套口径勉强对齐，应该让它们各管各的。

/token/ 三个子页面全部用 sessions 表直接聚合，合计行和每日明细行用同一句 SQL，GROUP BY date(started_at)：

合计行 total:    6,029,269,616
每日行 sum:      6,029,269,616
diff:            0

/system/ 的热力图保留 messages 级聚合，互不干扰。

5. 总结

改数据源之前先查数据完整性。多看一眼 messages 和 sessions 的 COUNT，就不会上线一个有 10% 误差的页面。踩一次就记住了，现在每次改 SQL 第一件事就是跑 SELECT COUNT(*) 对比两张表。

两套口径不必强行对齐。合计行用 A、明细行用 B，B 的输入数据不完整，硬要对齐就得把 A 也变不完整。分开，各自完整就够了。

一个维度做好比四个维度都半吊子强。4 Tab 切换看起来很全面，但两个维度数据不可靠、剩下两个没人切，不如把精力花在把一个维度做好。删代码和写代码一样重要。