通用说明
统一测试表：biz_data 时间字段：dt（日期时间类型） 统计指标：行数 cnt、金额求和 sum_amt 分组维度：分钟、小时、天、周、月、年，附带排序； 周统计附带说明周起始规则差异。
一、MySQL（5.7 / 8.0）
1. 按分钟（yyyy-MM-dd HH:mm）
sql
SELECT
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 HH）
sql
SELECT
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. 按天
sql
SELECT
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)：周一为一周第一天（常用）
sql
SELECT
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. 按月
sql
SELECT
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. 按年
sql
SELECT
YEAR(dt) AS stat_year,
COUNT(*) AS cnt,
SUM(amt) AS sum_amt
FROM biz_data
GROUP BY stat_year
ORDER BY stat_year;
二、Oracle（11g/12c/19c/21c）
时间字段支持 DATE / TIMESTAMP；TRUNC 截断，TO_CHAR 格式化；HH24 24 小时制。
1. 按分钟
sql
SELECT
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. 按小时
sql
SELECT
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. 按天
sql
SELECT
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-"W"IW') AS stat_week,
COUNT(*) AS cnt,
SUM(amt) AS sum_amt
FROM biz_data
GROUP BY TRUNC(dt, 'IW'), TO_CHAR(dt, 'IYYY-"W"IW')
ORDER BY stat_week_start;
5. 按月
sql
SELECT
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. 按年
sql
SELECT
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. 按分钟
sql
SELECT
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. 按小时
sql
SELECT
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. 按天
sql
SELECT
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 周（周一为起始）：
sql
SELECT
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. 按月
sql
SELECT
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. 按年
sql
SELECT
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)强制周一； - Oracle
IW、ClickHouse ISO 周统一周一为一周第一天，报表通用。
- MySQL 默认周日起始，
- 性能建议
- MySQL/Oracle 尽量用时间截断字段分组，不要只依赖字符串
TO_CHAR； - ClickHouse 必须优先
toStartOf*系列，自带时间索引优化。
- MySQL/Oracle 尽量用时间截断字段分组，不要只依赖字符串
- 时区 ClickHouse、MySQL、Oracle 聚合结果受会话 / 服务时区影响，跨时区统计要手动指定时区参数。
全链路监控埋点平台 是一站式前端监控 + 用户行为埋点 + 大数据分析平台，天然适配点位细查、用户行为回溯、批量导出等场景：
一体化架构：监控 + 埋点同一套 SDK，数据互通无壁垒
私有化部署：数据完全本地化，满足企业合规要求
高吞吐支撑：基于 ClickHouse 构建，亿级日志秒级查询
全端覆盖：H5 / 小程序 / APP / 鸿蒙全覆盖，统一导出口径
可定制强：支持接口扩展、分布式锁、限流降级等企业级能力
