SQL时间序列统计是数据分析场景中非常常见的需求，核心是按照时间维度对业务指标进行分组计算，从而得到不同时间粒度的指标变化趋势。要完成这类统计，需要遵循清晰的处理逻辑，避免遗漏关键步骤导致结果不准确。
一、时间序列统计的核心逻辑拆解
完整的时间序列统计流程可以分为四个核心步骤，每个步骤对应不同的SQL操作：
- 时间字段预处理：将原始时间字段转换为需要的统计粒度，比如从时间戳转换为按天、按月、按周的格式。
- 基础指标计算：按照处理后的时间维度分组，计算对应的业务指标，比如计数、求和、平均值等。
- 时间维度补全：如果某个时间粒度没有对应数据，需要补全该时间维度，避免结果出现断层。
- 趋势指标计算：基于基础统计结果，计算环比、同比等趋势类指标，满足更深层的分析需求。
二、关键函数与语法说明
1. 日期处理函数
不同数据库的时间处理函数略有差异，但核心逻辑都是提取时间中的指定部分，以下是常见数据库的日期处理示例：
-- MySQL 按天统计，提取日期部分 SELECT DATE(create_time) AS stat_date FROM order_table; -- PostgreSQL 按月统计，格式化时间为年月 SELECT TO_CHAR(create_time, 'YYYY-MM') AS stat_month FROM order_table; -- SQL Server 按周统计，获取周起始日期 SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, create_time), create_time) AS stat_week FROM order_table;
2. 分组聚合函数
分组聚合是计算基础指标的核心，常用的聚合函数包括COUNT()、SUM()、AVG()等，结合GROUP BY按照时间维度分组即可：
-- 按天统计订单量和总销售额
SELECT
DATE(create_time) AS stat_date,
COUNT(order_id) AS order_count,
SUM(amount) AS total_amount
FROM order_table
GROUP BY DATE(create_time)
ORDER BY stat_date;
3. 窗口函数
如果需要计算环比、同比等趋势指标，可以使用窗口函数，避免多次关联表查询：
-- 计算日订单量的环比增长率
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 growth_rate
FROM (
SELECT
DATE(create_time) AS stat_date,
COUNT(order_id) AS order_count
FROM order_table
GROUP BY DATE(create_time)
) t
ORDER BY stat_date;
三、时间维度补全实现
当某个时间粒度没有对应业务数据时，直接分组聚合会缺失该时间维度，需要生成连续的时间序列再左关联统计结果。以下是MySQL生成连续日期的示例：
-- 生成2024年1月的连续日期序列
WITH RECURSIVE date_series AS (
SELECT '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,
IFNULL(t.order_count, 0) AS order_count,
IFNULL(t.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(amount) AS total_amount
FROM order_table
WHERE create_time >= '2024-01-01' AND create_time <= '2024-01-31'
GROUP BY DATE(create_time)
) t ON ds.stat_date = t.stat_date
ORDER BY ds.stat_date;
四、实操注意事项
- 时间字段如果存在时区问题，需要先统一转换为业务所需的时区，再进行粒度提取，避免统计结果偏差。
- 补全时间维度时，需要明确统计的时间范围，避免生成不必要的时间序列增加查询开销。
- 计算趋势指标时，注意处理分母为0的情况，避免SQL执行报错，可以通过
CASE WHEN做异常判断。
掌握以上完整逻辑后，不管是按小时、按天还是按年做时间序列统计，都可以按照步骤快速实现，应对各类业务场景下的时间维度数据统计需求。