ClickHouse SQL 实用方法汇总（通用版）

一、JSON 处理函数

函数说明示例MySQL兼容
visitParamExtractString(json, 'key')高性能提取字符串值visitParamExtractString(json_col, 'name')❌ 不支持
visitParamExtractInt(json, 'key')高性能提取整数值visitParamExtractInt(json_col, 'id')❌ 不支持
visitParamExtractFloat(json, 'key')高性能提取浮点值visitParamExtractFloat(json_col, 'price')❌ 不支持
visitParamExtractBool(json, 'key')高性能提取布尔值visitParamExtractBool(json_col, 'is_active')❌ 不支持
visitParamExtractRaw(json, 'key')提取原始JSON片段visitParamExtractRaw(json_col, 'address')❌ 不支持
JSONExtractString(json, 'key')标准方式提取字符串JSONExtractString(json_col, 'name')❌ 不支持
JSONExtractInt(json, 'key')标准方式提取整数JSONExtractInt(json_col, 'age')❌ 不支持
JSON_VALUE(json, '$.key')SQL标准JSON提取（22.6+）JSON_VALUE(json_col, '$.user.id')✅ MySQL 5.7+
json ->> '$.key'运算符方式提取（22.6+）json_col ->> '$.user.name'✅ MySQL 5.7+

MySQL 替代方案

-- ClickHouse
visitParamExtractInt(json_col, 'id')

-- MySQL
CAST(JSON_EXTRACT(json_col, '$.id') AS UNSIGNED)
-- 或
json_col->>'$.id'

二、日期时间函数

函数说明示例MySQL兼容
now()当前日期时间now()✅ 支持
today()当前日期（00:00:00）today()❌ 用 CURDATE()
yesterday()昨天日期yesterday()❌ 不支持
toDate()转为日期类型toDate(timestamp_col)✅ 用 DATE()
toDateTime()转为日期时间类型toDateTime(value)❌ 不支持
toUnixTimestamp()转Unix时间戳（秒）toUnixTimestamp(dt_col)✅ 用 UNIX_TIMESTAMP()
fromUnixTimestamp()Unix转日期时间fromUnixTimestamp(1700000000)❌ 用 FROM_UNIXTIME()
formatDateTime()格式化日期formatDateTime(dt_col, '%Y-%m-%d')✅ 用 DATE_FORMAT()
dateDiff()计算日期差dateDiff('day', start, end)❌ 用 DATEDIFF()
toStartOfDay()当天开始时间toStartOfDay(dt_col)❌ 不支持
toStartOfMonth()月初时间toStartOfMonth(dt_col)❌ 不支持
toStartOfWeek()周初时间toStartOfWeek(dt_col)❌ 不支持
toStartOfQuarter()季度初时间toStartOfQuarter(dt_col)❌ 不支持
toYYYYMM()提取年月（整数）toYYYYMM(dt_col)❌ 用 DATE_FORMAT(dt_col, '%Y%m')
toYYYYMMDD()提取年月日（整数）toYYYYMMDD(dt_col)❌ 用 DATE_FORMAT(dt_col, '%Y%m%d')
toDayOfWeek()星期几（1-7）toDayOfWeek(dt_col)❌ 用 DAYOFWEEK()
toHour()提取小时toHour(dt_col)❌ 用 HOUR()
addDays()日期加N天addDays(dt_col, 7)❌ 用 DATE_ADD()
subtractDays()日期减N天subtractDays(dt_col, 7)❌ 用 DATE_SUB()
age()时间差age(dt_col)❌ 不支持

常用日期范围查询

-- 今天
WHERE dt_col >= today()

-- 最近7天
WHERE dt_col >= today() - 7

-- 本月
WHERE dt_col >= toStartOfMonth(now())

-- 指定时间范围（时间戳）
WHERE dt_col >= toUnixTimestamp('2026-01-01')
  AND dt_col < toUnixTimestamp('2026-02-01')

三、字符串函数

函数说明示例MySQL兼容
length()字符串长度length(str)✅ 支持
upper() / lower()大小写转换upper(str)✅ 支持
substring(str, pos, len)截取子串substring(str, 1, 10)✅ 支持
concat()拼接多个字符串concat(a, b, c)✅ 支持
concatWithSeparator()带分隔符拼接concatWithSeparator(',', a, b)❌ 用 CONCAT_WS()
trim() / trimLeft() / trimRight()去除空格trim(str)✅ 支持
replaceOne()替换单个replaceOne(str, 'old', 'new')❌ 用 REPLACE()
replaceAll()替换全部replaceAll(str, 'old', 'new')❌ 用 REPLACE()
splitByChar()按字符分割为数组splitByChar(',', str)❌ 不支持
splitByString()按字符串分割`splitByString(’
match()正则匹配（返回0/1）match(str, '^[A-Z]')❌ 用 REGEXP
extractAll()提取所有正则匹配extractAll(str, '\\d+')❌ 不支持
extract()提取第一个正则匹配extract(str, '\\d+')❌ 不支持
position()查找子串位置position(str, 'abc')❌ 用 LOCATE()
positionCaseInsensitive()忽略大小写查找positionCaseInsensitive(str, 'abc')❌ 不支持
startsWith()是否以…开头startsWith(str, 'prefix')❌ 用 LIKE 'prefix%'
endsWith()是否以…结尾endsWith(str, 'suffix')❌ 用 LIKE '%suffix'
empty()是否为空字符串empty(str)❌ 用 str = ''
notEmpty()是否非空字符串notEmpty(str)❌ 用 str != ''

四、聚合函数

函数说明示例MySQL兼容
count()计数count(*)✅ 支持
countIf()条件计数countIf(score > 60)❌ 不支持
sum()求和sum(amount)✅ 支持
sumIf()条件求和sumIf(amount, status = 'paid')❌ 不支持
avg()平均值avg(score)✅ 支持
avgIf()条件平均值avgIf(score, score > 0)❌ 不支持
max()最大值max(price)✅ 支持
min()最小值min(price)✅ 支持
uniq()近似去重计数uniq(user_id)❌ 用 COUNT(DISTINCT)
uniqExact()精确去重计数uniqExact(user_id)❌ 用 COUNT(DISTINCT)
uniqIf()条件去重计数uniqIf(user_id, is_active = 1)❌ 不支持
median()中位数median(value)❌ 不支持
quantile(level)分位数quantile(0.95)(latency)❌ 不支持
quantiles()多个分位数quantiles(0.5, 0.95)(latency)❌ 不支持
topK(n)出现最多的N个值topK(10)(category)❌ 不支持
groupArray()聚合为数组groupArray(name)❌ 用 GROUP_CONCAT()
groupUniqArray()聚合为去重数组groupUniqArray(category)❌ 不支持
groupArrayIf()条件聚合数组groupArrayIf(name, status = 1)❌ 不支持
any()任意一个值any(status)❌ 不支持
anyLast()最后一个值anyLast(status)❌ 不支持
argMax()最大值对应的值argMax(name, score)❌ 不支持
argMin()最小值对应的值argMin(name, score)❌ 不支持

五、窗口函数

函数说明示例MySQL兼容
row_number()行号ROW_NUMBER() OVER (PARTITION BY cat ORDER BY dt)✅ MySQL 8.0+
rank()排名（有间隔）RANK() OVER (ORDER BY score DESC)✅ MySQL 8.0+
dense_rank()密集排名DENSE_RANK() OVER (ORDER BY score DESC)✅ MySQL 8.0+
lag(col, offset, default)前N行值LAG(amount, 1, 0) OVER (ORDER BY dt)✅ MySQL 8.0+
lead(col, offset, default)后N行值LEAD(amount, 1, 0) OVER (ORDER BY dt)✅ MySQL 8.0+
sum() OVER累计和/窗口求和SUM(amount) OVER (PARTITION BY uid ORDER BY dt)✅ MySQL 8.0+
avg() OVER窗口平均值AVG(score) OVER (PARTITION BY class)✅ MySQL 8.0+
count() OVER窗口计数COUNT(*) OVER (PARTITION BY cat)✅ MySQL 8.0+

注意：MySQL 5.7 不支持窗口函数，需 MySQL 8.0+。

六、数组函数

函数说明示例MySQL兼容
array()构建数组array(1, 2, 3)❌ 不支持
arrayJoin()展开数组为多行arrayJoin(arr)❌ 不支持
arrayLength()数组长度arrayLength(arr)❌ 用 JSON_LENGTH()
arrayMap(func, arr)对数组每个元素映射arrayMap(x -> x*2, arr)❌ 不支持
arrayFilter(func, arr)过滤数组arrayFilter(x -> x > 0, arr)❌ 不支持
arrayReduce()数组聚合arrayReduce('sum', arr)❌ 不支持
has(arr, val)是否包含值has(arr, 5)❌ 不支持
hasAll(arr1, arr2)是否包含所有hasAll(arr1, arr2)❌ 不支持
arrayDistinct()数组去重arrayDistinct(arr)❌ 不支持
arrayConcat()合并数组arrayConcat(arr1, arr2)❌ 不支持
arraySlice()切片arraySlice(arr, 1, 3)❌ 不支持

七、条件与逻辑函数

函数说明示例MySQL兼容
if(cond, then, else)条件判断if(value > 0, 'positive', 'non-positive')❌ 用 IF() 或 CASE
multiIf(cond1, val1, cond2, val2, else)多条件判断multiIf(score>90,'A', score>80,'B', 'C')❌ 不支持
CASE WHEN ... END标准条件表达式CASE WHEN score>60 THEN 'pass' ELSE 'fail' END✅ 支持
coalesce(val1, val2, ...)返回第一个非NULLcoalesce(a, b, c)✅ 支持
isNull(val)判断是否为NULLisNull(column)❌ 用 IS NULL
isNotNull(val)判断是否非NULLisNotNull(column)❌ 用 IS NOT NULL
ifNull(val, default)NULL时返回默认值ifNull(column, 0)❌ 用 IFNULL() 或 COALESCE()
nullIf(val1, val2)相等时返回NULLnullIf(a, b)✅ 支持

八、类型转换函数

函数说明示例MySQL兼容
toInt8() / toInt32() / toInt64()转为整数toInt64(value)❌ 用 CAST()
toString()转为字符串toString(value)❌ 用 CAST() 或 CONCAT('', val)
toFloat32() / toFloat64()转为浮点数toFloat64(value)❌ 用 CAST()
toDate()转为日期toDate(value)❌ 用 CAST() 或 DATE()
toDateTime()转为日期时间toDateTime(value)❌ 用 CAST()
CAST(value AS Type)标准类型转换CAST('123' AS Int64)✅ 支持
toTypeName()查看值类型toTypeName(column)❌ 不支持

九、条件聚合（ClickHouse 特色）

ClickHouse 支持 *If 系列聚合，可直接在聚合中加条件：

-- 普通写法（通用）
SELECT 
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount

-- ClickHouse 特色写法（更简洁）
SELECT 
    COUNT(*) AS total,
    SUMIf(amount, status = 'paid') AS paid_amount,
    AVGIf(score, score > 0) AS avg_positive,
    uniqIf(user_id, is_active = 1) AS active_users
FROM table
WHERE dt >= today() - 30;

十、数据定义语言（DDL）差异

操作ClickHouseMySQL兼容性
CREATE TABLE需指定引擎（如 MergeTree）默认 InnoDB⚠️ 语法差异
ALTER TABLE ADD COLUMN支持支持⚠️ 语法差异
ALTER TABLE DROP COLUMN支持支持⚠️ 语法差异
ALTER TABLE MODIFY COLUMN支持支持⚠️ 语法差异
ALTER TABLE RENAME COLUMN支持支持⚠️ 语法差异
DELETE不支持，用 ALTER TABLE DELETE支持❌ 不支持
UPDATE不支持，用 ALTER TABLE UPDATE支持❌ 不支持
INSERT支持支持✅ 基本相同
TRUNCATE支持支持✅ 支持
DROP TABLE支持支持✅ 支持
RENAME TABLE支持支持⚠️ 语法差异
事务（ACID）不支持支持❌ 不支持
外键约束不支持支持❌ 不支持
唯一约束不支持支持❌ 不支持

ClickHouse 建表示例

CREATE TABLE table_name (
    dt Date,
    id Int64,
    name String,
    json_col String,
    amount Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, id)
SETTINGS index_granularity = 8192;

MySQL 建表示例

CREATE TABLE table_name (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    json_col JSON,
    amount DECIMAL(10,2),
    dt DATE,
    INDEX idx_dt (dt)
) ENGINE = InnoDB;

十一、常用实用查询模板

1. 按时间分组统计

SELECT 
    toStartOfDay(dt) AS day,
    count() AS total_records,
    uniq(user_id) AS unique_users
FROM table_name
WHERE dt >= today() - 7
GROUP BY day
ORDER BY day DESC;

2. JSON 字段提取与过滤

SELECT 
    id,
    visitParamExtractString(json_col, 'name') AS name,
    visitParamExtractInt(json_col, 'id') AS ext_id
FROM table_name
WHERE visitParamExtractInt(json_col, 'status') = 1
  AND dt >= today() - 30;

3. 分组统计（含条件聚合）

SELECT 
    category,
    count() AS total,
    countIf(status = 'active') AS active_count,
    sum(amount) AS total_amount,
    avg(amount) AS avg_amount,
    uniq(user_id) AS unique_users
FROM table_name
WHERE dt >= toStartOfMonth(now())
GROUP BY category
ORDER BY total DESC
LIMIT 10;

4. 去重计数对比

SELECT 
    'approx' AS type,
    uniq(user_id) AS user_count  -- 近似去重，速度快
FROM table_name

UNION ALL

SELECT 
    'exact' AS type,
    uniqExact(user_id) AS user_count  -- 精确去重，速度慢
FROM table_name;

5. 分位数计算（性能分析）

SELECT 
    quantile(0.5)(latency) AS median,
    quantile(0.90)(latency) AS p90,
    quantile(0.95)(latency) AS p95,
    quantile(0.99)(latency) AS p99
FROM api_logs
WHERE dt >= today() - 7;

6. 窗口函数示例（排名）

SELECT 
    category,
    product_id,
    sales,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_in_category
FROM product_sales
WHERE dt >= today() - 30;

7. 数组展开（处理 JSON 数组）

SELECT 
    id,
    arrayJoin(visitParamExtractRaw(json_col, 'items')) AS item
FROM table_name
WHERE visitParamExtractString(json_col, 'type') = 'order';

8. 抽样查询

-- 10% 随机采样
SELECT 
    count() * 10 AS estimated_total,
    avg(score) AS estimated_avg
FROM table_name
SAMPLE 0.1;

十二、快速参考：常用 MySQL → ClickHouse 转换

MySQLClickHouse
CURDATE()today()
DATE(col)toDate(col)
NOW()now()
UNIX_TIMESTAMP(col)toUnixTimestamp(col)
FROM_UNIXTIME(val)fromUnixTimestamp(val)
DATEDIFF(end, start)dateDiff('day', start, end)
DATE_ADD(col, INTERVAL 7 DAY)addDays(col, 7)
DATE_SUB(col, INTERVAL 7 DAY)subtractDays(col, 7)
DATE_FORMAT(col, '%Y-%m-%d')formatDateTime(col, '%Y-%m-%d')
JSON_EXTRACT(json, '$.key')visitParamExtractString(json, 'key') 或 JSON_VALUE(json, '$.key')
json->>'$.key'json->>'$.key'（都支持）
COUNT(DISTINCT col)uniq(col) 或 uniqExact(col)
GROUP_CONCAT(col)groupArray(col)
REPLACE(str, a, b)replaceAll(str, a, b)
IF(cond, then, else)if(cond, then, else)
IFNULL(col, default)ifNull(col, default)
IS NULLisNull(col) 或 col IS NULL
IS NOT NULLisNotNull(col) 或 col IS NOT NULL
UPDATE table SET ...ALTER TABLE table UPDATE ...
DELETE FROM table WHERE ...ALTER TABLE table DELETE WHERE ...
SHOW TABLESSHOW TABLES ✅
DESC tableDESC table ✅
USE databaseUSE database ✅
# clickhouse # sql # 数据库
Logo
亚马逊云科技技术品牌专区

更多推荐

cover

基于大数据+Hadoop+深度学习的淘宝用户购物可视化与行为预测系统设计与开发

avatar 亚马逊云科技技术品牌专区
cover

基于大数据爬虫+Hadoop+深度学习的旅游推荐系统设计与开发

avatar 亚马逊云科技技术品牌专区

学Linux选学校：一线城市还是老家？

而二线城市很多机构的老师，自己都没进过大厂，技术停留在五年前的水平，甚至对着PPT照本宣科。而二线机构缺乏企业合作资源，项目案例永远是“搭建博客系统”“配置个LAMP环境”——这些在现在的云计算岗位面试中，连初筛都过不了。在一线城市学习，你接触的同学、老师、企业内推资源，都是二线城市没法比的。而老家机构的学员，最好的结果也就是去本地小公司做网管，月薪五六千，天花板肉眼可见。在IT培训这行干了快十年

avatar 亚马逊云科技技术品牌专区