尧图网站建设 
做过监控大屏或日志平台的同学大概率画过当日日志量趋势这种折线图ClickHouse 里GROUP BY时间桶一查图是出来了但折线七零八落——没有日志的分钟在结果集里直接缺失前端要么断线要么把前后两个点硬连起来低峰期看起来像跳变。更麻烦的是环比、同比、移动平均这类计算缺桶会让分母悄悄变小算出来的比率根本没法看。根因很简单GROUP BY只返回有数据的桶而业务需要的是完整等间距时间序列。ClickHouse 从 21.8 开始为ORDER BY提供了WITH FILL修饰符可以在数据库层把缺失的时间桶补出来数值列默认补 0也就是常说的补零。这篇文章整理我们在可观测平台生产环境跑通的一整套方案四张分级 TTL 的指标表1 分钟 / 5 分钟 / 1 小时 / 1 天 三个物化视图级联聚合 大屏侧的 WITH FILL 查询。建表语句和生产 SQL 都来自真实环境已脱敏改名可以直接参考。表分层、分区与排序键的通用取舍在《ClickHouse 可观测数据建模实战》里详细写过本文聚焦时间序列补零和多粒度指标表两个专题不再重复。一、整体设计一张明细表 三档聚合表可观测平台的指标查询有个典型特征查询的时间范围越长需要的粒度就越粗。看今天的心跳用 1 分钟粒度看一周 5 分钟粒度足够看一个月、一年就该用小时级、天级。如果所有查询都打在 1 分钟明细表上一个近一年趋势就要扫几亿行大屏刷一次等半分钟。所以标准做法是按粒度分表 物化视图实时聚合查询端按时间范围路由┌── mv ──→ obs_metric_5min TTL 30 天5 分钟粒度 写入 ──→ obs_metric_1min ──┼── mv ──→ obs_metric_1hour TTL 60 天小时粒度 TTL 20 天 └── mv ──→ obs_metric_1day TTL 90 天天粒度大屏的时间范围选择器直接映射到表当天查 1min 表、一周查 5min 表、一月查 1hour 表、一年查 1day 表。查询永远只扫够用就好的数据量这就是这套表存在的意义。二、四张表的建表语句2.1 一分钟明细表数据入口CREATETABLEobs_dw.obs_metric_1min(app_code StringCOMMENT应用系统编码,data_center StringCOMMENT数据中心,item_code StringCOMMENT指标编码三级,item_name StringCOMMENT指标名称三级,item_valueDecimal(20,4)COMMENT指标值1 分钟内的均值,item_group StringCOMMENT指标分组,timeDateTimeCOMMENT统计时间对齐到分钟,save_timeDateTimeDEFAULTnow()COMMENT入库时间)ENGINEMergeTreePARTITIONBYtoYYYYMMDD(time)ORDERBYtimeTTLtimetoIntervalDay(20)SETTINGS index_granularity8192;三个决策说明一下Decimal(20, 4)而不是 Float64指标值上游是流任务算好的分钟均值Decimal 保住小数精度前端展示和二次聚合不会累计浮点误差ORDER BY time这张表 20 天 TTL、按天分区主要服务最近 N 分钟全网扫描类的大屏查询时间放最前剪枝最狠。如果你的场景里单个应用查一个月曲线更高频就应该把app_code, item_code放到时间前面这个取舍在建模那篇里展开过TTL 20 天明细只服务当天 近几天的查询粗粒度查询走聚合表明细没必要长留。2.2 三档聚合表-- 5 分钟粒度按天分区保留 30 天CREATETABLEobs_dw.obs_metric_5min(app_code String,data_center String,item_code StringCOMMENT指标编码三级,item_name StringCOMMENT指标名称三级,item_valueDecimal(20,4)COMMENT5 分钟内的平均值,item_group String,timeDateTimeCOMMENT对齐到 5 分钟起点,save_timeDateTimeDEFAULTnow())ENGINEMergeTreePARTITIONBYtoYYYYMMDD(time)ORDERBY(time,app_code,item_code)TTLtimetoIntervalDay(30)SETTINGS index_granularity8192;-- 1 小时粒度按天分区保留 60 天CREATETABLEobs_dw.obs_metric_1hour(app_code String,data_center String,item_code String,item_name String,item_valueDecimal(20,4)COMMENT1 小时内的平均值,item_group String,timeDateTimeCOMMENT对齐到小时起点,save_timeDateTimeDEFAULTnow())ENGINEMergeTreePARTITIONBYtoYYYYMMDD(time)ORDERBY(time,app_code,item_code)TTLtimetoIntervalDay(60)SETTINGS index_granularity8192;-- 1 天粒度time 用 Date 更省空间按月分区保留 90 天CREATETABLEobs_dw.obs_metric_1day(app_code String,data_center String,item_code String,item_name String,item_valueDecimal(20,4)COMMENT1 天内的平均值,item_group String,timeDateCOMMENT对齐到天,save_timeDateTimeDEFAULTnow())ENGINEMergeTreePARTITIONBYtoYYYYMM(time)TTLtimetoIntervalDay(90)SETTINGS index_granularity8192;核心原则一句话粒度越粗保留越久20/30/60/90 天只是我们的配置按合规和查询需求定。天级表数据量小到一天可能只有几万行所以分区从按天换成按月避免分区数膨胀time也降级成Date四个字节省一半。排序键和明细表相反——聚合表的查询几乎总是一段时间内一批应用的一批指标(time, app_code, item_code)让时间范围和维度过滤都能吃上索引。三、物化视图级联一次写入三档聚合3.1 三个视图全部挂在明细表上这里有个容易做错的选择题1hour 视图挂在哪张表上挂在 5min 表上层层套娃看起来更省计算但avg(avg())是有偏估计——每个 5 分钟桶里的样本数不同对均值再求均值和对全部 1 分钟样本求均值结果不一样。我们的做法是三个视图全部从 1min 明细表计算CREATEMATERIALIZEDVIEWobs_dw.mv_obs_metric_5minTOobs_dw.obs_metric_5minASSELECTapp_code,data_center,replaceOne(item_code,1MIN_,5MIN_)ASitem_code,item_name,item_group,avg(item_value)ASitem_value,toStartOfInterval(time,INTERVAL5MINUTE)AStimeFROMobs_dw.obs_metric_1minGROUPBYapp_code,data_center,replaceOne(item_code,1MIN_,5MIN_),item_name,item_group,toStartOfInterval(time,INTERVAL5MINUTE);CREATEMATERIALIZEDVIEWobs_dw.mv_obs_metric_1hourTOobs_dw.obs_metric_1hourASSELECTapp_code,data_center,replaceOne(item_code,1MIN_,1HOUR_)ASitem_code,item_name,item_group,avg(item_value)ASitem_value,toStartOfHour(time)AStimeFROMobs_dw.obs_metric_1minGROUPBYapp_code,data_center,replaceOne(item_code,1MIN_,1HOUR_),item_name,item_group,toStartOfHour(time);CREATEMATERIALIZEDVIEWobs_dw.mv_obs_metric_1dayTOobs_dw.obs_metric_1dayASSELECTapp_code,data_center,replaceOne(item_code,1MIN_,1DAY_)ASitem_code,item_name,item_group,avg(item_value)ASitem_value,toDate(time)AStimeFROMobs_dw.obs_metric_1minGROUPBYapp_code,data_center,replaceOne(item_code,1MIN_,1DAY_),item_name,item_group,toDate(time);三个写法要点replaceOne前缀替换指标编码带粒度前缀如1MIN_LOG_ERR_CNT→5MIN_LOG_ERR_CNT/1HOUR_/1DAY_同一根曲线在不同粒度表里编码不同查询端拿到指标名 时间范围就能唯一定位到表和编码不需要额外维护映射表toStartOfInterval/toStartOfHour/toDate做时间对齐保证同一个桶的时间戳完全一致这是后面 WITH FILL 能对齐步长的前提GROUP BY 必须与 SELECT 的非聚合列完全一致replaceOne(...)在 SELECT 和 GROUP BY 里要原样出现两遍。代价也要说清楚一次写入 1min 表会触发三次聚合计算写入放大对我们单表日均千万行的量级完全无感如果明细写入量再大一个数量级可以评估只挂一个视图、其余粒度用流任务直接写AggregatingMergeTree 方案是另一条路线。四、大屏查询WITH FILL 补零4.1 不补零的查询长什么样大屏上的当日日志量 5 分钟趋势直观写法SELECTtoStartOfFiveMinute(window_start)AStime_bucket,sum(line_counts)AStotal_linesFROMobs_dw.dws_log_linecnt-- 流任务每 5 分钟写入的日志行数汇总表WHEREwindow_starttoStartOfDay(now())ANDwindow_startnow()GROUPBYtime_bucketORDERBYtime_bucket;没有日志的桶直接消失返回结果可能是这样的time_buckettotal_lines2026-10-07 08:10:0012342026-10-07 08:20:0011802026-10-07 08:25:00131008:15 这个桶没有行前端画出来的折线在 08:10~08:20 之间就是斜线直连看起来 08:15 也有量。在 WITH FILL 之前大家要么在前端按时间轴补点要么用arrayJoin(timeSlots(...))生成完整时间序列再 LEFT JOIN 数据两种写法都又长又难维护。4.2 WITH FILL 语法WITH FILL是ORDER BY表达式的修饰符完整形式ORDERBYexprWITHFILL[FROM常量][TO常量][STEP 常量]只能用在 ORDER BY 的列上按该列的值把缺口补齐FROM/TO可选用来控制补齐的起止边界首尾没有数据的时间段也能补出来STEP是步长数值列直接写数字Date类型单位是天DateTime类型单位是秒也支持STEP INTERVAL 5 MINUTE的写法老版本只支持数字步长STEP 300等价于INTERVAL 5 MINUTE。4.3 生产 SQL当日趋势完整版把 4.1 的查询补上 WITH FILL 三件套SELECTtoStartOfFiveMinute(window_start)AStime_bucket,sum(line_counts)AStotal_linesFROMobs_dw.dws_log_linecntWHEREwindow_starttoStartOfDay(now())ANDwindow_startnow()GROUPBYtime_bucketORDERBYtime_bucketWITHFILLFROMtoStartOfDay(now())TOtoStartOfFiveMinute(now())STEPINTERVAL5MINUTE;三个参数逐个解释FROM toStartOfDay(now())从当天 0 点开始补。就算第一条日志 8 点才出现0:00~7:55 的桶也会以total_lines 0出现在结果里——开局缺零是手工补点最容易漏的场景TO toStartOfFiveMinute(now())补到当前时间所在的桶图表拉到现在STEP INTERVAL 5 MINUTE步长 5 分钟必须与分桶函数toStartOfFiveMinute严格一致错位会出现半桶时间点。错误日志趋势、告警趋势换个表名就是同一个模板SELECTtoStartOfFiveMinute(window_start)AStime_bucket,sum(line_counts)AStotal_linesFROMobs_dw.dws_errlog_linecnt-- 错误日志行数汇总表WHEREwindow_starttoStartOfDay(now())ANDwindow_startnow()GROUPBYtime_bucketORDERBYtime_bucketWITHFILLFROMtoStartOfDay(now())TOtoStartOfFiveMinute(now())STEPINTERVAL5MINUTE;4.4 配合多粒度表近 7 天小时曲线时间范围一长就切到聚合表WITH FILL 同样适用。近 7 天某个应用的错误数小时曲线直接查 1hour 表SELECTtimeAShour_bucket,item_valueASerr_cntFROMobs_dw.obs_metric_1hourWHEREapp_codeorder-svcANDitem_code1HOUR_LOG_ERR_CNTANDtimenow()-INTERVAL7DAYORDERBYhour_bucketWITHFILLFROMtoStartOfHour(now()-INTERVAL7DAY)TOtoStartOfHour(now())STEPINTERVAL1HOUR;这里没有 GROUP BY聚合在写入侧已经做完查询只是按时间取点 补零单应用 7 天小时数据就一百多行毫秒级返回。五、WITH FILL 的行为细节用之前把这几条行为弄清楚能少走弯路补出来的行除了填充列其它列都是类型默认值数值 0、空字符串、1970-01-01。sum()补 0 正好是业务想要的但成功率这类比率指标补 0 会造成看起来全失败的误导要么前端把 0 渲染成断线要么查询层包一层 CASE WHEN 区分真 0和没数据。更新的版本还提供INTERPOLATE子句可以用相邻行的值插值填充其它列适合用上一个点的值顶替的场景。它认的是 ORDER BY不是 GROUP BY填充依据是排序键列的取值所以ORDER BY子句不能省写DESC倒序时填充方向跟着倒FROM要大于TO步长仍写正值。多列 ORDER BY 可以有多个 WITH FILL比如ORDER BY app_code, time WITH FILL ...各自带修饰符时会形成类似分组补零的二维填充——不过多数场景单列时间填充就够用了二维填充的行为建议先拿小数据验证。版本要求21.8 起支持WITH FILLINTERVAL步长和INTERPOLATE是后续版本引入的用老集群先确认版本不行就退回数字步长。六、踩坑清单按疼痛程度排序都是实际发生过的事物化视图的 GROUP BY 漏列源表写入直接失败。第一版视图 SELECT 里写了item_groupGROUP BY 里漏了它报Expression item_group is not in GROUP BY。要特别注意默认情况下物化视图执行失败会阻断源表的 INSERT等于把数据入口打挂了比查询报错严重得多。replaceOne前缀替换依赖编码规范。编码里必须严格带1MIN_前缀一旦某条数据不带前缀replaceOne原样返回这行会以1MIN_编码混进聚合表查询端按5MIN_/1HOUR_前缀取数时它就消失了。编码规范要在写入端强约束。avg of avg 偏差。聚合视图一律从 1min 明细算不要图省事从 5min 表往 1hour 表套。每个桶样本数不同时avg(avg(x))和avg(x)结果会稳定地差一截报表对不上数很难排查。步长与分桶函数错位。toStartOfFiveMinute配STEP INTERVAL 10 MINUTE这种写法补出来的点和真实桶会错开曲线上表现为锯齿或丢点。分桶函数和 STEP 永远成对修改。比率型指标补 0 的误导见第五节第 1 条大屏上0和无数据必须视觉区分这是和产品经理对过需求才能发现的坑。TTL 不是准点删除。四张表 20/30/60/90 天的 TTL 都靠 merge 异步清理写入高峰期 merge 积压时磁盘不会立刻降下来容量告警别按 TTL 边界卡预期。明细则算便宜则算贵。三视图挂一张明细表的写入放大要心里有数我们日均千万行无感但如果你的明细是十亿级就该减少视图、把粗粒度聚合挪到流任务侧做。写在最后时间序列补零是个小需求但把它做对是三层配合的结果存储层用多粒度表 分级 TTL 控制扫描量写入层用物化视图级联把聚合做成顺便的事查询层用 WITH FILL 一行修饰符替代前端补点和小表 JOIN 的祖传代码。三者拼起来大屏任意时间范围的曲线都是毫秒级返回且永不断线。你们项目的补零是在 SQL 层做还是前端做有没有踩过 WITH FILL 或者物化视图更疼的坑欢迎评论区聊聊。相关阅读ClickHouse 物化视图实战AggregatingMergeTree 实现监控指标多粒度聚合ClickHouse 可观测数据建模实战指标、日志、链路、告警四类数据的 MergeTree 表设计与集群化改造Elasticsearch 聚合 DSL 实战日志大屏的总数、成功率与分组统计bucket_script 比率计算 ClickHouse WITH FILL 实战：监控大屏时间序列补零，附多粒度指标表与物化视图级联设计
做过监控大屏或日志平台的同学大概率画过当日日志量趋势这种折线图ClickHouse 里GROUP BY时间桶一查图是出来了但折线七零八落——没有日志的分钟在结果集里直接缺失前端要么断线要么把前后两个点硬连起来低峰期看起来像跳变。更麻烦的是环比、同比、移动平均这类计算缺桶会让分母悄悄变小算出来的比率根本没法看。根因很简单GROUP BY只返回有数据的桶而业务需要的是完整等间距时间序列。ClickHouse 从 21.8 开始为ORDER BY提供了WITH FILL修饰符可以在数据库层把缺失的时间桶补出来数值列默认补 0也就是常说的补零。这篇文章整理我们在可观测平台生产环境跑通的一整套方案四张分级 TTL 的指标表1 分钟 / 5 分钟 / 1 小时 / 1 天 三个物化视图级联聚合 大屏侧的 WITH FILL 查询。建表语句和生产 SQL 都来自真实环境已脱敏改名可以直接参考。表分层、分区与排序键的通用取舍在《ClickHouse 可观测数据建模实战》里详细写过本文聚焦时间序列补零和多粒度指标表两个专题不再重复。一、整体设计一张明细表 三档聚合表可观测平台的指标查询有个典型特征查询的时间范围越长需要的粒度就越粗。看今天的心跳用 1 分钟粒度看一周 5 分钟粒度足够看一个月、一年就该用小时级、天级。如果所有查询都打在 1 分钟明细表上一个近一年趋势就要扫几亿行大屏刷一次等半分钟。所以标准做法是按粒度分表 物化视图实时聚合查询端按时间范围路由┌── mv ──→ obs_metric_5min TTL 30 天5 分钟粒度 写入 ──→ obs_metric_1min ──┼── mv ──→ obs_metric_1hour TTL 60 天小时粒度 TTL 20 天 └── mv ──→ obs_metric_1day TTL 90 天天粒度大屏的时间范围选择器直接映射到表当天查 1min 表、一周查 5min 表、一月查 1hour 表、一年查 1day 表。查询永远只扫够用就好的数据量这就是这套表存在的意义。二、四张表的建表语句2.1 一分钟明细表数据入口CREATETABLEobs_dw.obs_metric_1min(app_code StringCOMMENT应用系统编码,data_center StringCOMMENT数据中心,item_code StringCOMMENT指标编码三级,item_name StringCOMMENT指标名称三级,item_valueDecimal(20,4)COMMENT指标值1 分钟内的均值,item_group StringCOMMENT指标分组,timeDateTimeCOMMENT统计时间对齐到分钟,save_timeDateTimeDEFAULTnow()COMMENT入库时间)ENGINEMergeTreePARTITIONBYtoYYYYMMDD(time)ORDERBYtimeTTLtimetoIntervalDay(20)SETTINGS index_granularity8192;三个决策说明一下Decimal(20, 4)而不是 Float64指标值上游是流任务算好的分钟均值Decimal 保住小数精度前端展示和二次聚合不会累计浮点误差ORDER BY time这张表 20 天 TTL、按天分区主要服务最近 N 分钟全网扫描类的大屏查询时间放最前剪枝最狠。如果你的场景里单个应用查一个月曲线更高频就应该把app_code, item_code放到时间前面这个取舍在建模那篇里展开过TTL 20 天明细只服务当天 近几天的查询粗粒度查询走聚合表明细没必要长留。2.2 三档聚合表-- 5 分钟粒度按天分区保留 30 天CREATETABLEobs_dw.obs_metric_5min(app_code String,data_center String,item_code StringCOMMENT指标编码三级,item_name StringCOMMENT指标名称三级,item_valueDecimal(20,4)COMMENT5 分钟内的平均值,item_group String,timeDateTimeCOMMENT对齐到 5 分钟起点,save_timeDateTimeDEFAULTnow())ENGINEMergeTreePARTITIONBYtoYYYYMMDD(time)ORDERBY(time,app_code,item_code)TTLtimetoIntervalDay(30)SETTINGS index_granularity8192;-- 1 小时粒度按天分区保留 60 天CREATETABLEobs_dw.obs_metric_1hour(app_code String,data_center String,item_code String,item_name String,item_valueDecimal(20,4)COMMENT1 小时内的平均值,item_group String,timeDateTimeCOMMENT对齐到小时起点,save_timeDateTimeDEFAULTnow())ENGINEMergeTreePARTITIONBYtoYYYYMMDD(time)ORDERBY(time,app_code,item_code)TTLtimetoIntervalDay(60)SETTINGS index_granularity8192;-- 1 天粒度time 用 Date 更省空间按月分区保留 90 天CREATETABLEobs_dw.obs_metric_1day(app_code String,data_center String,item_code String,item_name String,item_valueDecimal(20,4)COMMENT1 天内的平均值,item_group String,timeDateCOMMENT对齐到天,save_timeDateTimeDEFAULTnow())ENGINEMergeTreePARTITIONBYtoYYYYMM(time)TTLtimetoIntervalDay(90)SETTINGS index_granularity8192;核心原则一句话粒度越粗保留越久20/30/60/90 天只是我们的配置按合规和查询需求定。天级表数据量小到一天可能只有几万行所以分区从按天换成按月避免分区数膨胀time也降级成Date四个字节省一半。排序键和明细表相反——聚合表的查询几乎总是一段时间内一批应用的一批指标(time, app_code, item_code)让时间范围和维度过滤都能吃上索引。三、物化视图级联一次写入三档聚合3.1 三个视图全部挂在明细表上这里有个容易做错的选择题1hour 视图挂在哪张表上挂在 5min 表上层层套娃看起来更省计算但avg(avg())是有偏估计——每个 5 分钟桶里的样本数不同对均值再求均值和对全部 1 分钟样本求均值结果不一样。我们的做法是三个视图全部从 1min 明细表计算CREATEMATERIALIZEDVIEWobs_dw.mv_obs_metric_5minTOobs_dw.obs_metric_5minASSELECTapp_code,data_center,replaceOne(item_code,1MIN_,5MIN_)ASitem_code,item_name,item_group,avg(item_value)ASitem_value,toStartOfInterval(time,INTERVAL5MINUTE)AStimeFROMobs_dw.obs_metric_1minGROUPBYapp_code,data_center,replaceOne(item_code,1MIN_,5MIN_),item_name,item_group,toStartOfInterval(time,INTERVAL5MINUTE);CREATEMATERIALIZEDVIEWobs_dw.mv_obs_metric_1hourTOobs_dw.obs_metric_1hourASSELECTapp_code,data_center,replaceOne(item_code,1MIN_,1HOUR_)ASitem_code,item_name,item_group,avg(item_value)ASitem_value,toStartOfHour(time)AStimeFROMobs_dw.obs_metric_1minGROUPBYapp_code,data_center,replaceOne(item_code,1MIN_,1HOUR_),item_name,item_group,toStartOfHour(time);CREATEMATERIALIZEDVIEWobs_dw.mv_obs_metric_1dayTOobs_dw.obs_metric_1dayASSELECTapp_code,data_center,replaceOne(item_code,1MIN_,1DAY_)ASitem_code,item_name,item_group,avg(item_value)ASitem_value,toDate(time)AStimeFROMobs_dw.obs_metric_1minGROUPBYapp_code,data_center,replaceOne(item_code,1MIN_,1DAY_),item_name,item_group,toDate(time);三个写法要点replaceOne前缀替换指标编码带粒度前缀如1MIN_LOG_ERR_CNT→5MIN_LOG_ERR_CNT/1HOUR_/1DAY_同一根曲线在不同粒度表里编码不同查询端拿到指标名 时间范围就能唯一定位到表和编码不需要额外维护映射表toStartOfInterval/toStartOfHour/toDate做时间对齐保证同一个桶的时间戳完全一致这是后面 WITH FILL 能对齐步长的前提GROUP BY 必须与 SELECT 的非聚合列完全一致replaceOne(...)在 SELECT 和 GROUP BY 里要原样出现两遍。代价也要说清楚一次写入 1min 表会触发三次聚合计算写入放大对我们单表日均千万行的量级完全无感如果明细写入量再大一个数量级可以评估只挂一个视图、其余粒度用流任务直接写AggregatingMergeTree 方案是另一条路线。四、大屏查询WITH FILL 补零4.1 不补零的查询长什么样大屏上的当日日志量 5 分钟趋势直观写法SELECTtoStartOfFiveMinute(window_start)AStime_bucket,sum(line_counts)AStotal_linesFROMobs_dw.dws_log_linecnt-- 流任务每 5 分钟写入的日志行数汇总表WHEREwindow_starttoStartOfDay(now())ANDwindow_startnow()GROUPBYtime_bucketORDERBYtime_bucket;没有日志的桶直接消失返回结果可能是这样的time_buckettotal_lines2026-10-07 08:10:0012342026-10-07 08:20:0011802026-10-07 08:25:00131008:15 这个桶没有行前端画出来的折线在 08:10~08:20 之间就是斜线直连看起来 08:15 也有量。在 WITH FILL 之前大家要么在前端按时间轴补点要么用arrayJoin(timeSlots(...))生成完整时间序列再 LEFT JOIN 数据两种写法都又长又难维护。4.2 WITH FILL 语法WITH FILL是ORDER BY表达式的修饰符完整形式ORDERBYexprWITHFILL[FROM常量][TO常量][STEP 常量]只能用在 ORDER BY 的列上按该列的值把缺口补齐FROM/TO可选用来控制补齐的起止边界首尾没有数据的时间段也能补出来STEP是步长数值列直接写数字Date类型单位是天DateTime类型单位是秒也支持STEP INTERVAL 5 MINUTE的写法老版本只支持数字步长STEP 300等价于INTERVAL 5 MINUTE。4.3 生产 SQL当日趋势完整版把 4.1 的查询补上 WITH FILL 三件套SELECTtoStartOfFiveMinute(window_start)AStime_bucket,sum(line_counts)AStotal_linesFROMobs_dw.dws_log_linecntWHEREwindow_starttoStartOfDay(now())ANDwindow_startnow()GROUPBYtime_bucketORDERBYtime_bucketWITHFILLFROMtoStartOfDay(now())TOtoStartOfFiveMinute(now())STEPINTERVAL5MINUTE;三个参数逐个解释FROM toStartOfDay(now())从当天 0 点开始补。就算第一条日志 8 点才出现0:00~7:55 的桶也会以total_lines 0出现在结果里——开局缺零是手工补点最容易漏的场景TO toStartOfFiveMinute(now())补到当前时间所在的桶图表拉到现在STEP INTERVAL 5 MINUTE步长 5 分钟必须与分桶函数toStartOfFiveMinute严格一致错位会出现半桶时间点。错误日志趋势、告警趋势换个表名就是同一个模板SELECTtoStartOfFiveMinute(window_start)AStime_bucket,sum(line_counts)AStotal_linesFROMobs_dw.dws_errlog_linecnt-- 错误日志行数汇总表WHEREwindow_starttoStartOfDay(now())ANDwindow_startnow()GROUPBYtime_bucketORDERBYtime_bucketWITHFILLFROMtoStartOfDay(now())TOtoStartOfFiveMinute(now())STEPINTERVAL5MINUTE;4.4 配合多粒度表近 7 天小时曲线时间范围一长就切到聚合表WITH FILL 同样适用。近 7 天某个应用的错误数小时曲线直接查 1hour 表SELECTtimeAShour_bucket,item_valueASerr_cntFROMobs_dw.obs_metric_1hourWHEREapp_codeorder-svcANDitem_code1HOUR_LOG_ERR_CNTANDtimenow()-INTERVAL7DAYORDERBYhour_bucketWITHFILLFROMtoStartOfHour(now()-INTERVAL7DAY)TOtoStartOfHour(now())STEPINTERVAL1HOUR;这里没有 GROUP BY聚合在写入侧已经做完查询只是按时间取点 补零单应用 7 天小时数据就一百多行毫秒级返回。五、WITH FILL 的行为细节用之前把这几条行为弄清楚能少走弯路补出来的行除了填充列其它列都是类型默认值数值 0、空字符串、1970-01-01。sum()补 0 正好是业务想要的但成功率这类比率指标补 0 会造成看起来全失败的误导要么前端把 0 渲染成断线要么查询层包一层 CASE WHEN 区分真 0和没数据。更新的版本还提供INTERPOLATE子句可以用相邻行的值插值填充其它列适合用上一个点的值顶替的场景。它认的是 ORDER BY不是 GROUP BY填充依据是排序键列的取值所以ORDER BY子句不能省写DESC倒序时填充方向跟着倒FROM要大于TO步长仍写正值。多列 ORDER BY 可以有多个 WITH FILL比如ORDER BY app_code, time WITH FILL ...各自带修饰符时会形成类似分组补零的二维填充——不过多数场景单列时间填充就够用了二维填充的行为建议先拿小数据验证。版本要求21.8 起支持WITH FILLINTERVAL步长和INTERPOLATE是后续版本引入的用老集群先确认版本不行就退回数字步长。六、踩坑清单按疼痛程度排序都是实际发生过的事物化视图的 GROUP BY 漏列源表写入直接失败。第一版视图 SELECT 里写了item_groupGROUP BY 里漏了它报Expression item_group is not in GROUP BY。要特别注意默认情况下物化视图执行失败会阻断源表的 INSERT等于把数据入口打挂了比查询报错严重得多。replaceOne前缀替换依赖编码规范。编码里必须严格带1MIN_前缀一旦某条数据不带前缀replaceOne原样返回这行会以1MIN_编码混进聚合表查询端按5MIN_/1HOUR_前缀取数时它就消失了。编码规范要在写入端强约束。avg of avg 偏差。聚合视图一律从 1min 明细算不要图省事从 5min 表往 1hour 表套。每个桶样本数不同时avg(avg(x))和avg(x)结果会稳定地差一截报表对不上数很难排查。步长与分桶函数错位。toStartOfFiveMinute配STEP INTERVAL 10 MINUTE这种写法补出来的点和真实桶会错开曲线上表现为锯齿或丢点。分桶函数和 STEP 永远成对修改。比率型指标补 0 的误导见第五节第 1 条大屏上0和无数据必须视觉区分这是和产品经理对过需求才能发现的坑。TTL 不是准点删除。四张表 20/30/60/90 天的 TTL 都靠 merge 异步清理写入高峰期 merge 积压时磁盘不会立刻降下来容量告警别按 TTL 边界卡预期。明细则算便宜则算贵。三视图挂一张明细表的写入放大要心里有数我们日均千万行无感但如果你的明细是十亿级就该减少视图、把粗粒度聚合挪到流任务侧做。写在最后时间序列补零是个小需求但把它做对是三层配合的结果存储层用多粒度表 分级 TTL 控制扫描量写入层用物化视图级联把聚合做成顺便的事查询层用 WITH FILL 一行修饰符替代前端补点和小表 JOIN 的祖传代码。三者拼起来大屏任意时间范围的曲线都是毫秒级返回且永不断线。你们项目的补零是在 SQL 层做还是前端做有没有踩过 WITH FILL 或者物化视图更疼的坑欢迎评论区聊聊。相关阅读ClickHouse 物化视图实战AggregatingMergeTree 实现监控指标多粒度聚合ClickHouse 可观测数据建模实战指标、日志、链路、告警四类数据的 MergeTree 表设计与集群化改造Elasticsearch 聚合 DSL 实战日志大屏的总数、成功率与分组统计bucket_script 比率计算