SQL如何对比各产品销售增长趋势_窗口函数时间序列分析-SQL-PHP中文网

用lag()算同比/环比需显式partition by product_id、补全时间维度、用date_trunc('month',sale_date)对齐月份，并用round((cur-prev)/nullif(prev,0),4)防除零和精度问题。

sql如何对比各产品销售增长趋势_窗口函数时间序列分析

怎么用 `LAG()` 算同比/环比增长，而不是写自连接

直接用 LAG() 拿上期值最省事，但很多人写错偏移量或忽略分区边界。比如想按产品+年月排序，却只写 ORDER BY sale_date，结果跨产品混了数据。

必须显式 PARTITION BY product_id，否则不同产品的销售会串行计算
LAG(sales_amount, 1) 是默认取前1行，但若存在空月份（比如某产品2月没销量），LAG() 会跳过空行取到1月的值，造成时间错位——得先补全时间维度再开窗
增长值建议用 ROUND((sales_amount - LAG(sales_amount) OVER (...)) / NULLIF(LAG(sales_amount) OVER (...), 0), 4)，避免除零和小数精度混乱

为什么 `DATE_TRUNC('month', sale_date)` 比 `EXTRACT(YEAR FROM ...)` + `EXTRACT(MONTH FROM ...)` 更可靠

拼年月字符串或分别取年月再组合，容易在跨年时出错（比如把2023-12和2024-01当成相邻），而 DATE_TRUNC 直接产出标准时间点，排序和分组都稳。

PostgreSQL/BigQuery 支持 DATE_TRUNC('month', sale_date)；MySQL 要用 DATE_FORMAT(sale_date, '%Y-%m-01') 或 STR_TO_DATE(CONCAT(YEAR(sale_date), '-', MONTH(sale_date), '-01'), '%Y-%m-%d')
别用 TO_CHAR(sale_date, 'YYYYMM') 当分组键——它返回字符串，202312 和 202401 字典序相邻，但数值差989，窗口函数排序会乱
如果原始数据只有日期没有时间，DATE_TRUNC 结果仍是 DATE 类型，和原字段类型一致，隐式转换风险低

`ROW_NUMBER()` 和 `RANK()` 在趋势断点检测里怎么选

查“连续3个月增长”的产品，本质是找单调递增子序列，这时 ROW_NUMBER() 比 RANK() 实用得多——因为要的是严格顺序，不是并列排名。

用 ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_month) 给每条记录标序号，再用 ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY CASE WHEN growth_rate > 0 THEN 1 ELSE 0 END, sale_month) 构造增长段落ID
RANK() 遇到同值会跳号（比如两个0.12增长并列第1，下一个0.13就是第3），没法做连续性判断
真正要识别“首次突破历史高点”，才该用 MAX(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_month ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)，比 RANK() 更准

时间序列对齐：没销量的月份怎么补，`GENERATE_SERIES` 还是左连接日历表

窗口函数不会自动补空，缺月=缺行=计算断层。硬用 LAG() 会跳到更早的非空月，趋势线就歪了。

PostgreSQL 推荐 GENERATE_SERIES(MIN(sale_month), MAX(sale_month), INTERVAL '1 month') 配合 CROSS JOIN DISTINCT product_id，再左连原始销售表
MySQL 没 GENERATE_SERIES，得建最小粒度日历表（至少含年月字段），用 SELECT DISTINCT product_id FROM sales 和日历表 JOIN，再连回销售事实表
补完后，sales_amount 是 NULL，LAG() 默认跳过 NULL，所以得写成 LAG(sales_amount, 1) IGNORE NULLS OVER (...)（仅支持较新版本）；更兼容的做法是补0，但注意0和NULL在业务上含义不同

窗口函数本身不解决时间对齐，补月这事漏掉，后面所有增长计算都是拿不准的。实际跑的时候，先 SELECT COUNT(*) FROM sales GROUP BY product_id, DATE_TRUNC('month', sale_date) 看看有没有预期外的重复或缺失，比直接写窗口还重要。