tools-and-features-hogql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

HogQL queries for PostHog

用于PostHog分析的HogQL查询

This skill helps you write HogQL queries for PostHog analytics. HogQL is PostHog's SQL dialect, a wrapper around ClickHouse SQL with simplified property access and PostHog-specific functions.

本技能可帮助你编写用于PostHog分析的HogQL查询。HogQL是PostHog的SQL方言，是ClickHouse SQL的包装器，具有简化的属性访问和PostHog专属函数。

Reference files

参考文件

```
references/expressions.md
```
- Sql expressions - docs
```
references/aggregations.md
```
- Supported aggregations - docs
```
references/clickhouse-functions.md
```
- Supported clickhouse functions - docs
```
references/data-access.md
```
- Accessing data using sql - docs
```
references/variables.md
```
- Sql variables - docs
```
references/useful-functions.md
```
- Useful sql functions - docs
```
references/posthog.md
```
- PostHog table schemas (events, persons, groups, sessions)
```
references/sessions.md
```
- Sessions - docs

Consult the documentation for SQL syntax, available functions, and query patterns.

```
references/expressions.md
```
- SQL表达式 - 文档
```
references/aggregations.md
```
- 支持的聚合操作 - 文档
```
references/clickhouse-functions.md
```
- 支持的ClickHouse函数 - 文档
```
references/data-access.md
```
- 使用SQL访问数据 - 文档
```
references/variables.md
```
- SQL变量 - 文档
```
references/useful-functions.md
```
- 实用SQL函数 - 文档
```
references/posthog.md
```
- PostHog表结构（events、persons、groups、sessions）
```
references/sessions.md
```
- 会话 - 文档

请查阅文档了解SQL语法、可用函数和查询模式。

Key principles

核心原则

Property access: Use
```
properties.$property_name
```
for event properties and
```
person.properties.$property_name
```
for person properties
Null handling: HogQL has simplified null handling compared to raw ClickHouse SQL
Filters placeholder: Use
```
{filters}
```
in queries to allow UI-based filtering in PostHog dashboards
Aggregations: Prefer ClickHouse aggregation functions like
```
count()
```
,
```
uniq()
```
,
```
avg()
```
,
```
sum()
```

属性访问：使用
```
properties.$property_name
```
访问事件属性，使用
```
person.properties.$property_name
```
访问用户属性
空值处理：与原生ClickHouse SQL相比，HogQL的空值处理更简化
过滤器占位符：在查询中使用
```
{filters}
```
，以便在PostHog仪表盘中实现基于UI的过滤
聚合操作：优先使用ClickHouse聚合函数，如
```
count()
```
、
```
uniq()
```
、
```
avg()
```
、
```
sum()
```

Common patterns

常见模式

Event queries

事件查询

sql

SELECT event, count()
FROM events
WHERE {filters}
GROUP BY event
ORDER BY count() DESC

sql

SELECT event, count()
FROM events
WHERE {filters}
GROUP BY event
ORDER BY count() DESC

Property breakdowns

属性细分

sql

SELECT properties.$browser AS browser, count()
FROM events
WHERE event = '$pageview' AND {filters}
GROUP BY browser

sql

SELECT properties.$browser AS browser, count()
FROM events
WHERE event = '$pageview' AND {filters}
GROUP BY browser

Person properties

用户属性

sql

SELECT person.properties.email, count()
FROM events
WHERE {filters}
GROUP BY person.properties.email

sql

SELECT person.properties.email, count()
FROM events
WHERE {filters}
GROUP BY person.properties.email

Framework guidelines

框架指南

Use properties.$name syntax for event properties, person.properties.$name for person properties
Use bracket notation for special characters like properties['$feature/cool-flag']
For cohorts, filter with person_id IN COHORT 'cohort-name'
For actions, use matchesAction('action-name') in WHERE clauses
Include {filters} placeholder in WHERE clauses to enable UI-based filtering in dashboards
Use {variables.name} for reusable SQL variables across dashboards
Access dashboard date range with {filters.dateRange.from} and {filters.dateRange.to}
ALWAYS include a time range filter - shorter is faster (e.g., timestamp >= now() - INTERVAL 7 DAY)
Prefer uniq() over count(distinct) for counting unique values - it's more efficient
Don't scan the same table multiple times - use materialized views for reusable subsets
Use timestamp-based pagination instead of OFFSET for large datasets
Name queries descriptively for easier debugging in query_log
Use dateTrunc() for time-based grouping (e.g., dateTrunc('day', timestamp))
For funnel queries, use windowFunnel() or sequenceMatch() functions
Test queries in the PostHog SQL editor before using them in insights or the API

对事件属性使用
```
properties.$name
```
语法，对用户属性使用
```
person.properties.$name
```
语法
对特殊字符使用括号表示法，如
```
properties['$feature/cool-flag']
```
针对用户分群，使用
```
person_id IN COHORT 'cohort-name'
```
进行过滤
针对操作，在WHERE子句中使用
```
matchesAction('action-name')
```
在WHERE子句中包含
```
{filters}
```
占位符，以在仪表盘中启用基于UI的过滤
使用
```
{variables.name}
```
实现跨仪表板的可复用SQL变量

通过

{filters.dateRange.from}

和

{filters.dateRange.to}

访问仪表板日期范围

务必包含时间范围过滤器——范围越短查询速度越快（例如：
```
timestamp >= now() - INTERVAL 7 DAY
```
）
统计唯一值时优先使用
```
uniq()
```
而非
```
count(distinct)
```
——前者效率更高
不要多次扫描同一张表——对可复用子集使用物化视图
针对大型数据集，使用基于时间戳的分页而非OFFSET
为查询命名时要清晰描述，以便在query_log中调试
使用
```
dateTrunc()
```
进行基于时间的分组（例如：
```
dateTrunc('day', timestamp)
```
）
针对漏斗查询，使用
```
windowFunnel()
```
或
```
sequenceMatch()
```
函数
在将查询用于洞察或API之前，先在PostHog SQL编辑器中测试