查表就能用！三大数据库时间分组统计函数 + SQL 对照大全

通用说明统一测试表biz_data时间字段dt日期时间类型 统计指标行数cnt、金额求和sum_amt分组维度分钟、小时、天、周、月、年附带排序 周统计附带说明周起始规则差异。一、MySQL5.7 / 8.01. 按分钟yyyy-MM-dd HH:mmsqlSELECT DATE_FORMAT(dt, %Y-%m-%d %H:%i) AS stat_min, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_min ORDER BY stat_min;2. 按小时yyyy-MM-dd HHsqlSELECT DATE_FORMAT(dt, %Y-%m-%d %H) AS stat_hour, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_hour ORDER BY stat_hour;3. 按天sqlSELECT DATE(dt) AS stat_day, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_day ORDER BY stat_day;4. 按周YEARWEEK(dt,1)周一为一周第一天常用sqlSELECT YEARWEEK(dt, 1) AS stat_week, DATE_FORMAT(MIN(dt), %Y-W%u) AS week_show, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_week ORDER BY stat_week;5. 按月sqlSELECT DATE_FORMAT(dt, %Y-%m) AS stat_month, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_month ORDER BY stat_month;6. 按年sqlSELECT YEAR(dt) AS stat_year, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_year ORDER BY stat_year;二、Oracle11g/12c/19c/21c时间字段支持DATE / TIMESTAMPTRUNC截断TO_CHAR格式化HH2424 小时制。1. 按分钟sqlSELECT TO_CHAR(dt, YYYY-MM-DD HH24:MI) AS stat_min, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY TO_CHAR(dt, YYYY-MM-DD HH24:MI) ORDER BY stat_min;2. 按小时sqlSELECT TO_CHAR(dt, YYYY-MM-DD HH24) AS stat_hour, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY TO_CHAR(dt, YYYY-MM-DD HH24) ORDER BY stat_hour;3. 按天sqlSELECT TRUNC(dt) AS stat_day, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY TRUNC(dt) ORDER BY stat_day;4. 按周sql-- TRUNC(dt, IW) ISO标准周周一起始跨年周规则标准 SELECT TRUNC(dt, IW) AS stat_week_start, TO_CHAR(dt, IYYY-WIW) AS stat_week, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY TRUNC(dt, IW), TO_CHAR(dt, IYYY-WIW) ORDER BY stat_week_start;5. 按月sqlSELECT TRUNC(dt, MONTH) AS stat_month_date, TO_CHAR(dt, YYYY-MM) AS stat_month, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY TRUNC(dt, MONTH), TO_CHAR(dt, YYYY-MM) ORDER BY stat_month_date;6. 按年sqlSELECT TRUNC(dt, YEAR) AS stat_year_date, EXTRACT(YEAR FROM dt) AS stat_year, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY TRUNC(dt, YEAR), EXTRACT(YEAR FROM dt) ORDER BY stat_year_date;三、ClickHouse最常用DateTime/DateTime64推荐toStartOfXXX系列函数做时间截断性能远高于字符串格式化。1. 按分钟sqlSELECT toStartOfMinute(dt) AS stat_min, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_min ORDER BY stat_min;2. 按小时sqlSELECT toStartOfHour(dt) AS stat_hour, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_hour ORDER BY stat_hour;3. 按天sqlSELECT toStartOfDay(dt) AS stat_day, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_day ORDER BY stat_day;4. 按周ISO 周周一为起始sqlSELECT toStartOfISOWeek(dt) AS stat_week_start, toISOWeek(dt) AS week_num, toYear(dt) AS stat_year, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_week_start, week_num, stat_year ORDER BY stat_week_start;5. 按月sqlSELECT toStartOfMonth(dt) AS stat_month, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_month ORDER BY stat_month;6. 按年sqlSELECT toStartOfYear(dt) AS stat_year, COUNT(*) AS cnt, SUM(amt) AS sum_amt FROM biz_data GROUP BY stat_year ORDER BY stat_year;四、核心函数对照表汇总表格粒度MySQL 格式化函数Oracle 截断 / 格式化ClickHouse 截断函数分钟DATE_FORMAT(dt,%Y-%m-%d %H:%i)TO_CHAR(dt,YYYY-MM-DD HH24:MI)toStartOfMinute(dt)小时DATE_FORMAT(dt,%Y-%m-%d %H)TO_CHAR(dt,YYYY-MM-DD HH24)toStartOfHour(dt)天DATE(dt)TRUNC(dt)toStartOfDay(dt)周YEARWEEK(dt,1)TRUNC (dt,IW) ISO 周toStartOfISOWeek(dt)月DATE_FORMAT(dt,%Y-%m)TRUNC(dt,MONTH)toStartOfMonth(dt)年YEAR(dt)TRUNC(dt,YEAR)toStartOfYear(dt)补充重要注意点周起始差异MySQL 默认周日起始YEARWEEK(dt,1)强制周一OracleIW、ClickHouse ISO 周统一周一为一周第一天报表通用。性能建议MySQL/Oracle 尽量用时间截断字段分组不要只依赖字符串TO_CHARClickHouse 必须优先toStartOf*系列自带时间索引优化。时区ClickHouse、MySQL、Oracle 聚合结果受会话 / 服务时区影响跨时区统计要手动指定时区参数。Webfunny全链路监控埋点平台 是一站式前端监控 用户行为埋点 大数据分析平台天然适配点位细查、用户行为回溯、批量导出等场景一体化架构监控 埋点同一套 SDK数据互通无壁垒私有化部署数据完全本地化满足企业合规要求高吞吐支撑基于 ClickHouse 构建亿级日志秒级查询全端覆盖H5 / 小程序 / APP / 鸿蒙全覆盖统一导出口径可定制强支持接口扩展、分布式锁、限流降级等企业级能力