在SQL中，按时间汇总数据常用DATE_TRUNC、GROUP BY date_trunc等函数。例如：SELECT date_trunc('day', timestamp_column) as day, COUNT(*) FROM table GROUP BY day; 这会按天聚合数据。MySQL用DATE(timestamp_column)，PostgreSQL用date_trunc('hour', time)按小时汇总。实用技巧：结合窗口函数如SUM() OVER (PARTITION BY date_trunc('day', time))计算每日累计。

按小时汇总销售数据

假设有orders表，包含order_time和amount字段。要按小时汇总销售额：SELECT date_trunc('hour', order_time) as hour, SUM(amount) as total_sales FROM orders GROUP BY hour ORDER BY hour; 这能快速看到高峰时段。

按月统计用户活跃

用户活跃统计常用：SELECT EXTRACT(YEAR FROM created_at) as year, EXTRACT(MONTH FROM created_at) as month, COUNT(DISTINCT user_id) FROM sessions GROUP BY year, month ORDER BY year, month; 或者用TO_CHAR(created_at, 'YYYY-MM')简化分组。

MySQL时间聚合示例

MySQL中：SELECT DATE(order_date) as date, SUM(total) FROM orders GROUP BY DATE(order_date); 按天汇总。如果要按周：SELECT YEARWEEK(order_date) as week, SUM(total) FROM orders GROUP BY YEARWEEK(order_date); 简单高效。

处理时区时间聚合

PostgreSQL处理UTC转本地：SELECT date_trunc('day', timezone('Asia/Shanghai', timestamp)) FROM table GROUP BY 1; 避免时区偏差导致数据错位。

SQL Server时间函数

SQL Server用：SELECT CAST(order_date AS DATE) as date, SUM(amount) FROM sales GROUP BY CAST(order_date AS DATE); 按天汇总，或者用DATEPART(weekday, order_date)分组星期。

高级技巧：滚动聚合

用窗口函数：SELECT date_trunc('day', time), SUM(sales) OVER (ORDER BY date_trunc('day', time) ROWS 6 PRECEDING) as rolling_7d FROM table; 计算7天滚动销售额。

FAQ
Q: 如何按季度汇总数据？
A: PostgreSQL用date_trunc('quarter', time)，MySQL用CONCAT(YEAR(time), QUARTER(time)) GROUP BY。
Q: 时间字段是字符串怎么办？
A: 先用TO_TIMESTAMP或STR_TO_DATE转换。
Q: 大表聚合慢怎么优化？
A: 加时间索引，如CREATE INDEX ON table (date_trunc('day', time));
Q: 跨月汇总怎么处理？
A: 用date_trunc('month', time)确保月初对齐。