【SQL】用SQL语句表示同比和环比

背景

计算同批次（BATCH）同品类（BREED）同名称（NAME）的商品价格（PRICE）的同比和环比。

SQL

SELECT
    t1.*,
    t1.THISDATE,
	t1.PRICE,
	nvl( round( ( t1.PRICE - t1.lastmonth ) * 100 / lastmonth, 2 ), '0' ) AS hb, --环比
	nvl( round( ( t1.PRICE - t1.lastyear ) * 100 / lastyear, 2 ), '0' ) AS tb --同比
FROM
	(
	SELECT
		t.*,
		SUBSTR(TIME, 1, 7) AS THISDATE, --截取TIME字段（yyyy-MM-dd形式）的年月
		lag ( PRICE, 12 ) over ( partition BY BATCH,BREED,NAME ORDER BY TIME ) AS lastyear, --同比基期值
		lag ( PRICE ) over ( partition BY BATCH,BREDD,NAME ORDER BY TIME ) AS lastmonth --环比基期值
	FROM
	GoodsInfo t 
	) t1
ORDER BY t1.REPORTDATE DESC

涉及到的SQL函数

nvl(string1, replace_with)

是Oracle的一个函数，如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值， 如果两个参数都为NULL ，则返回NULL。
与之相似作用的函数是Mysql、Oracle中的COALESCE函数。

round(number, num_digits)

number为数字，num_digits为保留几位小数。

substr(ch, pos, length)

ch为截取字段，pos为截取开始位置（1为起始位置），length为截取长度。

lag(ch, length, value) over( order by 列)

提供对当前行之前的指定物理偏移量的行的访问。
ch为偏移作用的字段，length为向下偏移多少位，value为超出行数时默认设置值，value不是必填项。
与之相对的是lead() over()函数，提供对当前行之后的偏移。

over(partition by 列)

partition by分区函数，也可以是over(partition by 列 order by 列)。在执行完select之后，在所得结果集之上进行分区划分。
可以参考这篇partition by对比group by分组函数的文章，更好地理解它的作用。