SQLServer 学习笔记4 销量环比 LAG窗口函数

语法

lag( expr [, offset [, default] ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER clause

参数

expr：一个任意类型的表达式。

offset：指定偏移量的可选整数文本。

default：其类型与 expr 相同的表达式。

SELECT T2.FYEAR, t2.FMONTH, T2.AMOUNT,
	LAG(t2.AMOUNT, 1, t2.AMOUNT) OVER (PARTITION BY T2.FYEAR ORDER BY T2.FMONTH) AS PRE_MONTH_AMOUNT,
	CAST((T2.AMOUNT - LAG(t2.AMOUNT, 1, t2.AMOUNT) OVER (PARTITION BY T2.FYEAR ORDER BY T2.FMONTH)) / LAG(t2.AMOUNT, 1, t2.AMOUNT) OVER (PARTITION BY T2.FYEAR ORDER BY T2.FMONTH) * 100 AS DECIMAL(18,2)) AS MOM_GROWTH
FROM
(
	SELECT YEAR(t0.FDATE) AS FYEAR, MONTH(t0.FDATE) AS FMONTH,
		CAST (SUM(t1.FQTY) AS DECIMAL(18,2)) AS AMOUNT
	FROM T_SAL_ORDER t0
	INNER JOIN T_SAL_ORDERENTRY t1 ON t0.FID = t1.FID
	WHERE t0.FDOCUMENTSTATUS = 'C'
	AND DATEDIFF(YEAR, t0.FDATE, GETDATE())  =0
	GROUP BY YEAR(t0.FDATE), MONTH(t0.FDATE)
) T2
ORDER BY t2.FMONTH