sql分段统计

1、取整常用到的函数

CEIL #向上取整

FLOOR #向下取整

ROUND #四舍五入

SELECT CEIL(1.2); #2

SELECT floor(1.2); #1

SELECT ROUND(1.2); #1.0

SELECT CEIL(1.5); #2

SELECT floor(1.5); #2

SELECT ROUND(1.5); #2.0

SELECT CEIL(2); #2

SELECT floor(2); #2

SELECT ROUND(2); #2

2、sql分段处理

例如统计分钟维度下的占比

select vdo_chnl_name,(ceil(CAST(ts as double)/60)-1) as min_ts,
       ceil(CAST(ts as double)/60) as max_ts,
       count(*) as cnt,
       AVG(vdo_len) as avg_vdo_len,
       AVG(watch_rate) as avg_watch_rate,
       AVG(ts) as avg_ts,
       AVG(CAST(weight as DOUBLE)) as avg_weight,
       AVG(new_weight) as avg_new_weight
       from cms_ts_analysis_${ds} group by ceil(CAST(ts as double)/60),vdo_chnl_name
       order by min_ts;

参考文献

Mysql对字段进行分段统计-阿里云开发者社区