在业务数据分析中，按时间维度做统计是最常见的需求之一，例如统计每天的下单量、每小时的接口调用次数或者每月的销售额。使用SQL处理时间序列统计，核心在于把时间字段规整到目标粒度，再做分组聚合，并结合窗口函数计算衍生指标。
一、明确统计周期与原始数据
假设我们有一张订单表 orders，核心字段如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| id | bigint | 订单ID |
| amount | decimal | 订单金额 |
| created_at | datetime | 下单时间 |
二、按天统计基础指标
先把 created_at 格式化为日期，再按日期分组求和。不同数据库格式化函数略有差异，以 MySQL 为例：
-- 按天统计订单数与总金额 SELECT DATE(created_at) AS stat_date, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY DATE(created_at) ORDER BY stat_date;
三、补齐缺失的时间点
实际统计时，某些日期可能没有数据，导致结果不连续。可以借助日历表或递归生成日期序列，再用 LEFT JOIN 补零：
-- 生成最近7天日期并补零 WITH date_series AS ( SELECT CURDATE() - INTERVAL 6 DAY AS d UNION ALL SELECT d + INTERVAL 1 DAY FROM date_series WHERE d < CURDATE() ) SELECT ds.d AS stat_date, COALESCE(COUNT(o.id), 0) AS order_cnt, COALESCE(SUM(o.amount), 0) AS total_amount FROM date_series ds LEFT JOIN orders o ON DATE(o.created_at) = ds.d GROUP BY ds.d ORDER BY ds.d;
四、使用窗口函数计算累计与环比
在已按天聚合的结果上，可以用窗口函数计算累计值和日环比。以下示例基于上一步的日统计子查询：
-- 计算累计金额与昨日对比
SELECT
stat_date,
order_cnt,
total_amount,
SUM(total_amount) OVER (ORDER BY stat_date) AS cum_amount,
total_amount - LAG(total_amount, 1) OVER (ORDER BY stat_date) AS day_diff
FROM (
SELECT
DATE(created_at) AS stat_date,
COUNT(*) AS order_cnt,
SUM(amount) AS total_amount
FROM orders
GROUP BY DATE(created_at)
) t
ORDER BY stat_date;
五、完整应用场景示例
将补零与窗口函数结合，可得到一张可直接接入看板的日报表：
WITH date_series AS (
SELECT CURDATE() - INTERVAL 29 DAY AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM date_series WHERE d < CURDATE()
),
daily AS (
SELECT
ds.d AS stat_date,
COALESCE(COUNT(o.id), 0) AS order_cnt,
COALESCE(SUM(o.amount), 0) AS total_amount
FROM date_series ds
LEFT JOIN orders o ON DATE(o.created_at) = ds.d
GROUP BY ds.d
)
SELECT
stat_date,
order_cnt,
total_amount,
SUM(total_amount) OVER (ORDER BY stat_date) AS cum_amount,
ROUND(
(total_amount - LAG(total_amount, 1) OVER (ORDER BY stat_date))
/ NULLIF(LAG(total_amount, 1) OVER (ORDER BY stat_date), 0) * 100, 2
) AS mom_rate
FROM daily
ORDER BY stat_date;
六、注意事项
- 时间字段建议统一时区，避免跨时区统计错位。
- 大数据量下，对 created_at 做函数包装会导致索引失效，可考虑冗余日期列并建索引。
- 使用 LAG 等窗口函数时，注意首行空值处理，可用 COALESCE 或 NULLIF 规避除零错误。
通过上述步骤，即可用 SQL 完整实现时间序列的统计、补零与衍生指标计算，直接服务于业务报表与监控看板。