在分析业务数据时，我们常常需要观察不同分组（例如地区、渠道、商品类目）随时间推移的指标变化。如果只做简单的GROUP BY聚合，只能得到每个时间点的静态汇总，无法直观反映上升或下降的趋势。通过把时间序列处理与分组聚合结合起来，可以用纯SQL计算出环比、同比、移动平均等趋势指标，从而支撑运营决策。
一、构建时间序列与分组聚合的基础模型
要使用SQL分析趋势，第一步是把无序的时间戳整理成规律的“时间桶”，例如按天、周或月聚合。大多数数据库都提供了时间截断函数，PostgreSQL使用date_trunc，MySQL可用date_format，SQL Server则用datetrunc。将时间字段规整后，再配合GROUP BY维度列与时间段，就能得到一张二维的趋势底表。
下面以PostgreSQL为例，假设有一张销售记录表sales，包含渠道channel、下单时间order_time和金额amount。我们希望看每个渠道每月的销售额。基础聚合写法如下：
select
channel,
date_trunc('month', order_time) as month,
sum(amount) as monthly_sales
from sales
where order_time >= '2023-01-01'
group by channel, date_trunc('month', order_time)
order by channel, month;
这段查询把order_time截断到月份，并按channel和month分组求和。得到的结果集是每个渠道每月一条记录，已经具备了分析趋势的基础。不过此时还只是静态汇总，看不出环比变化，需要借助窗口函数进一步加工。
二、用窗口函数计算分组内的趋势指标
窗口函数可以在保留原行的前提下，按指定分区和排序计算相邻行的值。最常用的趋势算子是lag，它能取当前行之前第N行的值。结合partition by channel，我们可以在每个渠道内部独立计算上月销售额与环比增长率，而不会跨渠道错乱。
以下代码在前面聚合子查询的基础上，增加lag与增长率计算：
with monthly as (
select
channel,
date_trunc('month', order_time) as month,
sum(amount) as monthly_sales
from sales
where order_time >= '2023-01-01'
group by channel, date_trunc('month', order_time)
)
select
channel,
month,
monthly_sales,
lag(monthly_sales) over (
partition by channel
order by month
) as prev_month_sales,
round(
(monthly_sales - lag(monthly_sales) over (
partition by channel order by month
)) / nullif(lag(monthly_sales) over (
partition by channel order by month
), 0) * 100, 2
) as mom_growth_percent
from monthly
order by channel, month;
这里使用CTE先把月度聚合结果固化，再用lag取同渠道上一月的值。nullif用于避免除零错误，round控制百分比精度。最终每一行都带有环比增幅，运营可以直接看出哪些渠道在萎缩、哪些在高速上涨。
除了环比，还可以用avg配合窗口帧计算三月移动平均，平滑短期波动：
select
channel,
month,
monthly_sales,
avg(monthly_sales) over (
partition by channel
order by month
rows between 2 preceding and current row
) as ma_3month
from monthly
order by channel, month;
移动平均把当前月及前两个月作为窗口，有效弱化季节性抖动。对于数据波动大的分组，移动平均比单月值更能反映真实趋势。
三、自连接与窗口函数的性能对比
在还不支持窗口函数的旧版本数据库里，开发者常使用自连接来取上期值。思路是将同一张聚合表与自己连接，on条件为channel相同且时间差一个月。这种方式逻辑直观，但会产生较大的中间结果集。
select
a.channel,
a.month,
a.monthly_sales,
b.monthly_sales as prev_month_sales
from monthly a
left join monthly b
on a.channel = b.channel
and a.month = b.month + interval '1 month'
order by a.channel, a.month;
上述自连接对每张表扫描一次再做连接，若渠道数多、时间长，关联开销明显。窗口函数只需一次有序扫描，数据库引擎可在分区内高效定位相邻行，通常执行计划更优。在千万级明细数据上，窗口写法常比自连接快数倍，且代码更短、更易维护。
需要注意的是，如果某些月份数据缺失，lag会直接跳过空档取到再上一个月的值，而自连接左连接则会显示null。业务上若要求“缺失月补零”，就需先生成连续时间轴再left join明细，这部分在下节展开。
四、补齐缺失时间点与多维下钻
真实业务中，某个渠道某月可能没有成交，导致GROUP BY结果不连续。直接画折线会出现断裂。可以用generate_series生成连续月份，再左接聚合数据，把空值补成0。
with months as (
select generate_series(
'2023-01-01'::date,
'2023-12-01'::date,
interval '1 month'
) as month
),
channels as (
select distinct channel from sales
),
base as (
select channel, month from channels, months
),
agg as (
select
channel,
date_trunc('month', order_time) as month,
sum(amount) as monthly_sales
from sales
group by channel, date_trunc('month', order_time)
)
select
b.channel,
b.month,
coalesce(a.monthly_sales, 0) as monthly_sales
from base b
left join agg a
on b.channel = a.channel
and b.month = a.month
order by b.channel, b.month;
generate_series先铺出所有月份，与渠道做笛卡尔积得到完整底座，再用coalesce把空缺填零。这样每个分组都有十二条连续记录，后续接窗口函数算趋势不会错位。
若还需在渠道下看子维度，例如渠道加商品类目，只需把group by与partition by同时增加一列即可。SQL的分组趋势分析具有良好的扩展性，从二维到多维只是增加维度列，核心时间桶与窗口逻辑保持不变。
五、总结与实践建议
用SQL分析分组数据趋势，核心三步是：时间截断形成规律桶、GROUP BY做分组聚合、窗口函数算环比与移动平均。相较于导出到Python再做透视，纯SQL方案更贴近数据源，能利用数据库索引与并行能力，适合沉淀为日常报表。
建议在写这类查询时优先使用CTE拆分步骤，先聚合再算指标，既清晰又方便调优。对缺失时段主动补零，可以避免图表误读。当数据量增长后，为时间字段与维度列建立复合索引，能显著加快date_trunc与分组速度，让趋势分析稳定高效。
SQLtime_series_analysisgroup_by_aggregation修改时间：2026-08-01 09:39:32