目录

1.toStartOfMinute 将UTC时间戳转换为所在分钟。

2.按uid去重，计算不同uid的个数

3.正则匹配

4.any用法

5.visitParamExtractInt 和 JSONExtractInt

6.有子查询用CTE

7.凭空增加一列常量

8.单引号和斜引号的区别

9.countIf 统计成功率 / 命中率 / 占比

---

官方文档：https://clickhouse.com/docs/zh/sql-reference/functions/arithmetic-functions 

1.toStartOfMinute 将UTC时间戳转换为所在分钟。

toUnixTimestamp(toStartOfMinute(itime)) * 1000 as t,

也可以直接使用默认的t，然后step设置为1min。

2.按uid去重，计算不同uid的个数

count(DISTINCT uid)
uniq(uid)

两者的区别：

• count(DISTINCT uid): 精确去重计数，速度慢。
• uniq(uid)： 近似去重计数，速度快。

3.正则匹配

match(msg,'shit|rubbish')

或

msg like '%shit%' or msg like '%rubbish%'

或，position是最快的，没有通配符，最简单

position(msg, 'shit') > 0
OR position(msg, 'rubbish') > 0

第二个参数是正则表达式，开销比like大，因为match要走正则引擎，%号是like专用的，不是正则符号。

LIKE： 简单包含判断； position： 精确子串判断； match： 复杂匹配（数字、边界、分组）。

4.any用法

any() 是一个聚合函数，用来在一个分组里“随便拿一条值”，不保证是哪一条。

• 不是第一条，不是最大 / 最小，不保证稳定，只是“随便挑一个”

如果select里有多个any函数，那么它们之间没有关系，都是随机取的，没有先后顺序。

anyLast是返回最后一条，但并不一定等于时间最新，只是“处理顺序最后”。

anyLast 依赖的“顺序”可能来自：

• 表的 物理写入顺序

• MergeTree 的 data part 合并顺序

• 分布式查询时各 shard 的返回顺序

👉 只要数据有 merge / shuffle，顺序就可能变

5.visitParamExtractInt 和 JSONExtractInt

visitParamExtractInt 是 ClickHouse 里一个专门从“参数字符串”中提取整型值的函数：

JSONExtractInt是从json字符串里提取整型值。

SELECT
    visitParamExtractInt('uid=123&roomId=456', 'uid')     AS uid,
    visitParamExtractInt('uid=123&roomId=456', 'roomId') AS roomId;

系列函数：

• visitParamExtractInt -> Int64
• visitParamExtractUInt -> UInt64
• visitParamExtractString -> String
• visitParamExtractBool -> UInt8

如果key不存在，则返回默认值为0。

6.有子查询用CTE

CTE 是 Common Table Expression 的缩写，中文一般叫 公共表表达式。
你可以把它理解成：“给一段子查询起个名字，先定义好，后面反复用”。是一个临时视图。

# 没有CTE视图
SELECT *
FROM (
    SELECT uid, count(*) AS cnt
    FROM logs
    WHERE resCode = 0
    GROUP BY uid
) t
WHERE cnt > 10;


# 有CTE视图
WITH user_cnt AS (
    SELECT uid, count(*) AS cnt
    FROM logs
    WHERE resCode = 0
    GROUP BY uid
)
SELECT *
FROM user_cnt
WHERE cnt > 10;

只能有1个with，多个用逗号分隔。

7.凭空增加一列常量

例子: 

SELECT
  '成功登录用户' AS name
....

使用单引号，常量表达式列，如果查询返回 N 行，那么 name 这一列会有 N 个完全相同的值。统计每天登录的用户数：

SELECT
  '成功登录用户' AS name, # 增加一列常量列
  toDate(itime) AS d,
  count(*) AS num
FROM table
GROUP BY d

8.单引号和斜引号的区别

'xxx'   -- 单引号  字符串常量（值）
`xxx`   -- 反引号（斜着的）   标识符（列名 / 表名 / 别名）

单引号：

select 'today' as period

结果:

列名是period，列值是today。

斜引号：

select count() as `日活` # 给列名起别名

9.countIf 统计成功率 / 命中率 / 占比

countIf(condition) = 只统计「满足条件的行数」，理解为:

countIf(xxx)
≈
count( if(xxx, 1, NULL) )

可以统计多个指标，

SELECT
  countIf(action = 5 AND resCode = 0) AS success,
  countIf(action = 5 AND resCode != 0) AS fail,
  count() AS total
FROM table

1. sumIf(x, cond)： 满足条件的值之和
2. avgIf(x, cond)： 满足条件的平均值

9.minIf和maxIf

minIf(值, 条件)

maxIf(值, 条件)

• 第一个参数：要参与聚合的列

• 第二个参数：布尔条件（为 true 的行才参与计算）

可以同时用，一次扫描查询两个。

SELECT
    id,
    minIf(itime, action = 0 AND resCode = 0) AS start_time,
    maxIf(itime, action = 6 AND resCode = 0) AS stop_time
FROM table
WHERE
    day BETWEEN ...
    AND action IN (0,6)
    AND resCode = 0
GROUP BY id

10.uniqExact

精确统计某列的去重数量（distinct count）。

uniqExact(x) = count(DISTINCT x)

• count(DISTINCT x) 底层其实就是 uniqExact(x)，推荐直接使用 uniqExact

# 1.多列组合去重
SELECT uniqExact(uid, sessionid)
FROM table

# 2. 分组使用，每个时间桶里有多少不同 session
SELECT
    t,
    uniqExact(sessionid)
FROM table
GROUP BY t

uniqExact是精确的，uniq是近似的。

11. 按天分桶

# 按 UTC 0点 对齐
(intDiv(toUInt32(itime), 86400) * 86400) * 1000 as t

# 按 UTC+8 0点 对齐
(intDiv(toUInt32(itime) + 28800, 86400) * 86400) * 1000 as t

如: 北京时间：2026-02-17 00:30，对应utc时间: 2026-02-16 16:30，如果不加28800，那么这个时间会被分到2.16号，加了之后会被分到2.17号。

优雅写法：

toStartOfDay(toDateTime(itime, 'Asia/Shanghai'))
toStartOfDay(toDateTime(itime) + INTERVAL 8 HOUR)

12.group by和聚合函数

SELECT k, sum(x), uniqExact(uid)
FROM t
WHERE ...
GROUP BY k

逻辑执行顺序：

1. 用 WHERE 过滤行

2. 按 k 把行分组

3. 对每个组计算 sum/uniqExact/...

4. 输出每组一行结果

所以你“先 GROUP BY，再算聚合”这个理解是对的 ✅（用于理解结果）。

如果最后有order by， 它是最后执行的，发生在聚合之后（输出排序），不改变聚合结果，只影响返回顺序。