PostHog 数据查询实战：`system.cohorts` 人群与计算历史模型详解（HogQL）

PostHog 数据查询实战system.cohorts人群与计算历史模型详解HogQL【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog本文基于 PostHog 仓库querying-posthog-data技能中的数据模型参考文档Cohorts Persons模板文件 models-cohorts.md.j2展开完整讲解system.cohorts与system.cohort_calculation_history两张系统表的字段语义、人群Cohort类型体系、filters 过滤结构、计算审计追踪与常用 HogQL 查询模式。读完本文后你可以直接编写可执行的 HogQL按名称检索人群、列出人群成员含按版本查询、诊断人群计算失败的错误码并理解 filters JSON 中各类条件项的字段含义。一、文档定位querying-posthog-data 技能与 system 表在 PostHog 的 SKILL.md 中该技能被定义为编写任何 HogQL/SQL 或调用execute-sql之前必读的参考资料库覆盖系统表结构system.*、可用函数、查询示例与 schema 发现工作流。其中 Cohorts Persons 一节数据模型参考专门描述人群相关表。技能文档中有一条关键约定决定了本文所有查询的适用前提system.*表暴露的是各 Django 模型的精选字段子集。因此某个字段能通过 REST 工具如insight-get返回不代表它能被execute-sql查询——应以这些 schema 参考表为准而不是以 REST 响应形状为准文档中每个列Columns表格都由实时 HogQL 目录live HogQL catalog生成列出的就是execute-sql实际能解析的字段。原文通过{{ schema_columns(system.cohorts) }}模板指令按项目动态生成列清单即列即契约标准工作流是先用execute-sql查询 system 表发现实体通常拿到 ID再用对应类型的专用读取工具按 ID 取回完整实体。SQL 用于发现读取工具用于取数不要用 SQL 去重建实体。二、system.cohorts人群定义主表Cohorts 是用于细分segmentation与定向targeting的人群组groups of persons。表system.cohorts是人群定义的主体列清单由目录动态生成常见于查询模式中的字段包括id、name、count、is_static、deleted、last_calculation等。2.1 五种人群类型Cohort Types参考文档给出如下类型表TypeDescriptionstaticManually uploaded/managed list of persons手工上传/维护的人员列表person_propertyBased on person properties基于人员属性如 email 包含 example.combehavioralBased on events performed基于行为事件如最近 30 天查看过定价页realtimeCan be evaluated in real-time ( 20M persons)可实时求值人数上限 2000 万analyticalComplex queries with temporal/sequential logic via HogQL通过 HogQL 表达带时序/顺序逻辑的复杂查询这一分类在后端源码中有直接印证。products/cohorts/backend/models/cohort.py 中定义了CohortType枚举取值与文档完全一致class CohortType(StrEnum): STATIC static PERSON_PROPERTY person_property BEHAVIORAL behavioral REALTIME realtime ANALYTICAL analytical值得注意的是realtime的定位从源码结构看它表示可实时求值的资格eligibility与CohortType中描述过滤器形状的其他类型维度不同——cohort.py 中的CohortConditionFlags注释明确写道CohortTypeabout realtime-evaluation eligibility, not filter shape。2.2 20M 人数上限realtime 人群降级规则文档Important Notes指出realtime人群在超过 2000 万人时会被清除为NULL类型。该阈值在源码中是一个明确的常量 REALTIME_COHORT_MAX_PERSON_COUNT# Maximum person count for a cohort to be eligible for real-time evaluation # Cohorts with more than 20M persons cannot be real-time due to system limitations REALTIME_COHORT_MAX_PERSON_COUNT 20_000_000结合文档其余注意事项完整的人群运维语义为人群可以引用其他人群形成嵌套依赖nested dependencies静态人群通过 CSV 上传或 API 填充动态人群会周期性重算recalculated periodically。2.3 关键关系Key Relationships关系载体基数Personsraw_cohort_people表多对多Calculation Historysystem.cohort_calculation_history一对多raw_cohort_people是人群成员关系的事实表含cohort_id、person_id、version等字段下文 4.5 的查询会用到version而计算历史表则记录每次重算任务详见第三节。2.4 filters 结构三类条件项详解动态人群的filters字段是 PostHog 统一的属性过滤器结构顶层为{type: OR, values: [...]}每个条件项通过type区分语义。文档给出三种典型示例。行为过滤器performed event最近 30 天执行过某事件{ properties: { type: OR, values: [ { key: address page viewed, type: behavioral, value: performed_event, negation: false, event_type: events, time_value: 30, time_interval: day } ] } }字段解读key为事件名value为行为条件类型performed_event表示执行过该事件time_valuetime_interval构成时间窗口30 天negation取反event_type区分数据平面。人员属性过滤器person property{ properties: { type: OR, values: [ { key: email, type: person, value: [example.com], negation: false, operator: icontains } ] } }type: person表示按人员属性求值value为值数组可含通配符operator为属性比较算子icontains即不区分大小写的包含匹配。人群引用过滤器嵌套人群nested cohorts{ properties: { type: OR, values: [ { key: id, type: cohort, value: 8814, negation: false } ] } }type: cohortvalue指向另一人群的 ID由此实现人群 A 包含人群 B 的成员这类嵌套依赖。三、system.cohort_calculation_history人群计算审计追踪该表是人群计算任务的审计记录audit trail每个cohort_id对应多条计算历史一对多。列清单同样由{{ schema_columns(system.cohort_calculation_history) }}按实时目录生成从文档的常用查询可见至少包含id、started_at、finished_at、count、error_code等字段。3.1 错误码Error CodesCodeDescriptioncapacitySystem busy系统繁忙interruptedSocket timeout套接字超时timeoutQuery timeout ( 1200s)查询超时超过 1200 秒memory_limitMemory exceeded内存超限query_sizeQuery too large查询过大invalid_regexRegex compilation error正则编译错误incompatible_typesType mismatch类型不匹配no_propertiesNo filters defined未定义过滤器validation_errorGeneric validation error通用校验错误这张表是人群为什么没更新/人数为什么是 0这类问题的第一排查入口按error_code区分是资源类capacity/timeout/memory_limit还是定义类no_properties/invalid_regex/incompatible_types问题。3.2 queries 结构逐查询的执行明细历史记录的queries字段是一个 JSON 数组逐条记录本次计算中每个子查询的执行指标[ { query: SELECT ..., query_id: abc123, query_ms: 1234, memory_mb: 256, read_rows: 1000000, written_rows: 5000 } ]其中query_ms耗时、memory_mb内存、read_rows/written_rows读/写行数可用于评估某次人群计算的成本与规模判断timeout/memory_limit类错误是偶发还是系统性。3.3 实体关系图原文 ER 结构system.cohorts (main cohort definition) ├── - system.cohort_calculation_history.cohort_id └── persons through IN COHORT即计算历史通过cohort_id外键挂到主表人群与人的成员关系在查询侧则通过 HogQL 的IN COHORT子句表达见 4.3。四、常用查询模式Common Query Patterns以下是参考文档给出的六个可直接复用的 HogQL 查询模式覆盖找人、看数、列成员、查历史的完整链路。4.1 按名称查找人群SELECT id, name, count, is_static FROM system.cohorts WHERE name ILIKE %paying% AND NOT deleted注意NOT deleted过滤软删除人群以及ILIKE不区分大小写。4.2 获取指定人群及其成员数SELECT c.id, c.name, c.count, c.last_calculation FROM system.cohorts c WHERE c.id 123count为最近一次计算得到的成员数last_calculation用于判断人群数据新鲜度。4.3 列出人群成员经由事件表按人群 IDSELECT DISTINCT person_id, person.properties.email FROM events WHERE person_id IN COHORT 123 LIMIT 100IN COHORT id是 HogQL 特有的成员判断子句对应实体关系图中 persons throughIN COHORT。按人群名称注意名称大小写敏感select count() from persons where id IN COHORT Case-sensitive cohort name字符串形式的IN COHORT ...直接以人群名匹配文档特别标注了名称是大小写敏感的Case-sensitive。4.4 检查人群计算历史SELECT id, started_at, finished_at, count, error_code FROM system.cohort_calculation_history WHERE cohort_id 123 ORDER BY started_at DESC LIMIT 10按started_at倒序取最近 10 次是定位最近一次计算失败原因的标准动作失败时结合 3.1 节错误码表解读error_code。4.5 查询人群某个指定版本的成员人群成员是版本化的每次重算产生新version因此可以精确回看历史快照SELECT tuple(coalesce(toString(properties.email), toString(properties.name), toString(properties.username), toString(id)), toString(id)), id, created_at FROM persons WHERE in(id, (SELECT person_id FROM raw_cohort_people WHERE and(equals(cohort_id, 212606), equals(version, 2)))) ORDER BY id ASC LIMIT 101 OFFSET 0该查询的要点内层子查询在raw_cohort_people中按cohort_id 212606且version 2取成员 ID 集合外层用in(id, (...))关联persons拉取人员详情coalesce链按 email → name → username → id 的优先级生成展示标签LIMIT 101100 1是典型的分页是否还有下一页探测写法。这也印证了 2.3 节中raw_cohort_people作为人群成员事实表的角色。五、与后端实现对照模型与重算体系文档描述的system.cohorts是 Django 模型Cohort在 ClickHouse/HogQL 侧的精选投影实体定义位于 products/cohorts/backend/models/cohort.py。除前文核实的CohortType枚举与 20M 常量外该文件还定义了生命周期类行为条件集合LIFECYCLE_BEHAVIORAL_VALUESperformed_event_first_time、performed_event_regularly、stopped_performing_event、restarted_performing_event即前端Lifecycle人群过滤器区块对应的behavioral条件value取值——这与文档中 behavioral 过滤器的value字段相互呼应。此外products/cohorts/backend/models/ 目录下的calculation_history.py、dependencies.py人群间依赖、leaf_shape.py叶子条件形状哈希等文件对应文档中的计算历史与嵌套依赖语义从源码结构看人群依赖关系与条件形状在写入侧被显式建模从而支撑动态人群的周期性重算与嵌套引用的一致性校验。六、实践要点小结以 system 表为查询契约字段以system.cohorts/system.cohort_calculation_history的实际列为准由实时目录生成不要以 REST 响应形状反推可查询字段类型决定求值方式static看填充来源CSV/APIperson_property/behavioral看 filters 结构realtime关注是否超 20M 被降级为NULLanalytical关注其 HogQL 定义排障路径固定count/last_calculation判新鲜度 →cohort_calculation_history看error_code→queriesJSON 看单查询耗时与资源指标成员查询两条路查询侧用IN COHORT id大小写敏感的名称形式亦可需要历史版本或分页时落到raw_cohort_people的version上做精确子查询。以上路径与查询均来自 Cohorts Persons 参考文档由 querying-posthog-data 技能 引用及其后端模型源码可直接在当前仓库中复核。【免费下载链接】posthog:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.项目地址: https://gitcode.com/GitHub_Trending/po/posthog创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考