SQL时间序列统计是业务分析中非常常见的需求，比如统计每日订单量、每月用户活跃数、每小时接口调用次数等，处理过程需要兼顾时间维度准确性、数据完整性和指标计算逻辑合理性。
一、SQL时间序列统计标准流程
1. 时间维度提取与格式化
首先需要把原始数据中的时间字段转换为统计所需的粒度，比如按天、周、月统计时，需要先把时间戳或日期时间字段格式化为对应粒度的字符串或日期类型。不同数据库的时间格式化函数略有差异，以下是常见数据库的处理示例：
-- MySQL 提取日期粒度
SELECT
DATE(create_time) AS stat_date, -- 按天统计
DATE_FORMAT(create_time, '%Y-%m') AS stat_month, -- 按月统计
DATE_FORMAT(create_time, '%Y-%u') AS stat_week -- 按周统计（周一是每周第一天）
FROM order_table;
-- PostgreSQL 提取日期粒度
SELECT
DATE(create_time) AS stat_date,
TO_CHAR(create_time, 'YYYY-MM') AS stat_month,
TO_CHAR(create_time, 'YYYY-WW') AS stat_week
FROM order_table;
-- SQL Server 提取日期粒度
SELECT
CONVERT(DATE, create_time) AS stat_date,
FORMAT(create_time, 'yyyy-MM') AS stat_month,
DATEPART(YEAR, create_time) * 100 + DATEPART(WEEK, create_time) AS stat_week
FROM order_table;
2. 基础聚合统计
完成时间维度提取后，按照格式化后的时间字段分组，计算对应的业务指标，比如计数、求和、平均值等：
-- 统计每日订单量
SELECT
DATE(create_time) AS stat_date,
COUNT(order_id) AS order_count,
SUM(order_amount) AS total_amount
FROM order_table
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'
GROUP BY DATE(create_time)
ORDER BY stat_date;
3. 缺失时段数据补全
直接分组聚合会出现没有数据的时段被跳过的问题，比如某天没有订单，统计结果里就不会出现该天的记录，需要生成连续的时间序列再左关联聚合结果补全数据：
-- MySQL 8.0+ 生成连续日期并补全数据
WITH RECURSIVE date_series AS (
SELECT DATE('2024-01-01') AS stat_date
UNION ALL
SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
FROM date_series
WHERE stat_date < '2024-01-31'
)
SELECT
ds.stat_date,
COALESCE(agg.order_count, 0) AS order_count,
COALESCE(agg.total_amount, 0) AS total_amount
FROM date_series ds
LEFT JOIN (
SELECT
DATE(create_time) AS stat_date,
COUNT(order_id) AS order_count,
SUM(order_amount) AS total_amount
FROM order_table
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'
GROUP BY DATE(create_time)
) agg ON ds.stat_date = agg.stat_date
ORDER BY ds.stat_date;
4. 衍生指标计算
如果需要计算同比、环比、累计值等衍生指标，可以结合窗口函数实现，避免使用自关联带来的性能问题：
-- 计算每日订单量环比（前一天对比）
WITH daily_stat AS (
SELECT
DATE(create_time) AS stat_date,
COUNT(order_id) AS order_count
FROM order_table
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'
GROUP BY DATE(create_time)
)
SELECT
stat_date,
order_count,
LAG(order_count, 1) OVER (ORDER BY stat_date) AS prev_day_count,
ROUND((order_count - LAG(order_count, 1) OVER (ORDER BY stat_date)) / LAG(order_count, 1) OVER (ORDER BY stat_date) * 100, 2) AS ring_growth_rate
FROM daily_stat
ORDER BY stat_date;
二、常见使用误区及规避方法
1. 时间粒度转换错误
误区：直接使用字符串截取获取时间粒度，比如用LEFT(create_time, 10)获取日期，当时间字段格式变化或者包含时区信息时，会出现截取错误。规避方法：使用数据库内置的时间格式化函数，明确指定转换规则，避免依赖字段的字符串格式。
2. 忽略缺失时段补全
误区：直接输出分组聚合的结果给前端展示，导致时间轴不连续，折线图出现断层。规避方法：根据统计的时间范围生成连续的时间序列，再左关联聚合结果，用COALESCE函数将空值转换为0或业务默认值。
3. 时间范围边界错误
误区：使用BETWEEN '2024-01-01' AND '2024-01-31'筛选时间，会包含2024-01-31 23:59:59之后的数据，或者漏掉跨天的边界数据。规避方法：使用大于等于起始时间、小于结束时间的方式筛选，比如create_time >= '2024-01-01' AND create_time < '2024-02-01'，明确时间边界。
4. 聚合逻辑不符合业务需求
误区：统计去重指标时直接使用COUNT(*)，比如统计每日活跃用户数用COUNT(*)而不是COUNT(DISTINCT user_id)，导致结果偏大。规避方法：明确业务指标的定义，去重类指标使用COUNT(DISTINCT 字段名)，普通计数使用COUNT(字段名)忽略空值，COUNT(*)统计所有行数。
5. 时区处理不当
误区：数据库存储的是UTC时间，统计时直接按字段转换本地时间，导致统计时段和业务的本地时间不符。规避方法：先统一将时间转换为业务所在时区，再进行粒度提取和聚合，比如MySQL可以用CONVERT_TZ(create_time, '+00:00', '+08:00')转换为东八区时间后再处理。
三、总结
SQL时间序列统计的核心是保证时间维度准确、数据完整、指标逻辑符合业务需求，按照时间提取、聚合、补全、衍生计算的流程处理，同时规避常见的时间函数使用、边界处理、逻辑定义等误区，就能得到准确可靠的统计结果，支撑业务分析决策。