ClickHouse常用函数示例

发布时间: 2025-04-08 10:06

↑

# ClickHouse常用函数示例

本文将详细介绍ClickHouse中常用的函数，帮助您更好地处理和分析数据。

## 数值函数

### 基本运算
1. **算术运算**
   ```sql
   -- 四则运算
   SELECT
       plus(a, b),      -- 加法
       minus(a, b),     -- 减法
       multiply(a, b),  -- 乘法
       divide(a, b)     -- 除法
   FROM table_name;
   
   -- 取整函数
   SELECT
       floor(x),     -- 向下取整
       ceil(x),      -- 向上取整
       round(x, n)   -- 四舍五入到n位小数
   FROM table_name;
   ```

2. **聚合计算**
   ```sql
   -- 统计函数
   SELECT
       sum(value),       -- 求和
       avg(value),       -- 平均值
       min(value),       -- 最小值
       max(value),       -- 最大值
       count(),          -- 计数
       uniq(value)       -- 去重计数
   FROM table_name;
   ```

## 字符串函数

### 字符串操作
1. **基本操作**
   ```sql
   -- 长度和截取
   SELECT
       length(str),           -- 字符串长度
       substring(str, 1, 3),  -- 截取子串
       left(str, n),          -- 左侧n个字符
       right(str, n)          -- 右侧n个字符
   FROM table_name;
   
   -- 大小写转换
   SELECT
       lower(str),    -- 转小写
       upper(str),    -- 转大写
       capitalize(str) -- 首字母大写
   FROM table_name;
   ```

2. **查找替换**
   ```sql
   -- 查找函数
   SELECT
       position(needle IN haystack),  -- 查找位置
       like(str, pattern),           -- 模式匹配
       notLike(str, pattern)         -- 模式不匹配
   FROM table_name;
   
   -- 替换函数
   SELECT
       replaceOne(str, from, to),     -- 替换首个匹配
       replaceAll(str, from, to),     -- 替换所有匹配
       replaceRegexpOne(str, pattern, replacement) -- 正则替换
   FROM table_name;
   ```

## 日期时间函数

### 时间操作
1. **日期提取**
   ```sql
   -- 提取日期部分
   SELECT
       toYear(date),        -- 年份
       toMonth(date),       -- 月份
       toDayOfMonth(date),  -- 日期
       toHour(datetime),    -- 小时
       toMinute(datetime),  -- 分钟
       toSecond(datetime)   -- 秒数
   FROM table_name;
   ```

2. **时间计算**
   ```sql
   -- 时间运算
   SELECT
       now(),              -- 当前时间
       today(),            -- 当前日期
       yesterday(),        -- 昨天日期
       toMonday(date),     -- 本周一
       toStartOfMonth(date) -- 月初
   FROM table_name;
   
   -- 时间间隔
   SELECT
       dateDiff('day', date1, date2),    -- 相差天数
       dateDiff('month', date1, date2),   -- 相差月数
       dateDiff('year', date1, date2)     -- 相差年数
   FROM table_name;
   ```

## 条件函数

### 逻辑判断
1. **条件函数**
   ```sql
   -- if函数
   SELECT
       if(condition, then, else),     -- 条件判断
       multiIf(cond1,res1,cond2,res2,else) -- 多重条件
   FROM table_name;
   
   -- case表达式
   SELECT
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result3
       END as case_result
   FROM table_name;
   ```

2. **空值处理**
   ```sql
   -- 空值判断
   SELECT
       isNull(x),           -- 是否为空
       isNotNull(x),        -- 是否非空
       coalesce(x, y, z),   -- 返回首个非空值
       ifNull(x, default)   -- 空值替换
   FROM table_name;
   ```

## 类型转换函数

### 数据类型转换
1. **基本转换**
   ```sql
   -- 类型转换
   SELECT
       toInt32(value),     -- 转整数
       toString(value),    -- 转字符串
       toFloat64(value),   -- 转浮点数
       toDate(value),      -- 转日期
       toDateTime(value)   -- 转日期时间
   FROM table_name;
   ```

2. **格式化转换**
   ```sql
   -- 格式化输出
   SELECT
       formatDateTime(datetime, '%Y-%m-%d'),  -- 日期格式化
       formatReadableSize(bytes),             -- 可读大小
       formatReadableTimeDelta(seconds)       -- 可读时间差
   FROM table_name;
   ```

## 窗口函数

### 分析函数
1. **排序函数**
   ```sql
   -- 排序相关
   SELECT
       row_number() OVER(PARTITION BY group ORDER BY value),  -- 行号
       rank() OVER(PARTITION BY group ORDER BY value),        -- 排名
       dense_rank() OVER(PARTITION BY group ORDER BY value)   -- 密集排名
   FROM table_name;
   ```

2. **分析计算**
   ```sql
   -- 窗口计算
   SELECT
       sum(value) OVER(PARTITION BY group),     -- 分组求和
       avg(value) OVER(ORDER BY date            -- 移动平均
           ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
   FROM table_name;
   ```

## 最佳实践

### 使用建议
1. **性能优化**
   - 使用合适的函数
   - 避免过度计算
   - 利用索引优化

2. **代码规范**
   - 保持代码清晰
   - 添加适当注释
   - 遵循命名规范

### 常见问题
1. **注意事项**
   - 数据类型匹配
   - 空值处理
   - 性能影响

2. **调试技巧**
   - 使用测试数据
   - 分步验证结果
   - 查看执行计划

## 总结

本文详细介绍了ClickHouse中常用的函数，包括数值函数、字符串函数、日期时间函数、条件函数、类型转换函数和窗口函数等。这些函数是数据处理和分析的重要工具，掌握它们可以帮助您更高效地完成数据分析任务。在实际应用中，建议根据具体场景选择合适的函数，并注意性能优化和代码规范。

元素码农