lag与lead函数 oracle_11g

lag与lead函数 oracle_11g

lag与lead函数都可以实现跨行引用，语法如下：

lag( col [,n1] [,n2] ) over( [分区子句] 排序子句 )
lead( col [,n1] [,n2] ) over( [分区子句] 排序子句 )

语法注解：
参数：
	col,n1,n2
	
lag可以返回按排序子句排序后指定列的前n1行的值(如果不指定n1，则默认为1)；
lead可以返回按排序子句排序后指定列的后n1行的值。如果不存在可以指定值n2，否则默认为空值null。
lag与lead函数中排序子句是必要的

--建表
create table test2(
  CUS_NO varchar2(10),    --客户编号
  TRAN_MONTH varchar2(6), --交易月份
  TRAN_AMT numeric(20,2)  --交易金额
);

--插入测试数据
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101028','201910',1415.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101028','201911',39.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101028','201912',580.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101028','202010',915.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101028','202011',1200.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101028','202012',800.00);commit;

insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101029','201910',540.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101029','201911',495.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101029','201912',360.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101029','202001',990.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101029','202011',190.00);commit;

insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101030','201910',990.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101030','201911',330.00);commit;
insert into test2(CUS_NO,TRAN_MONTH,TRAN_AMT) values('cus_101030','202001',560.00);commit;

需求：统计每个客户月度消费的环比增长率，同比增长率

例：

月度消费环比增长率 =（当月消费金额－上月消费金额）／上月消费金额 ×100%

月度消费同比增长率 =（当月消费金额－去年同期消费金额）／去年同期消费金额 ×100%

为了计算环比、同比增长率，需要获取每个月对应的上个月、去年同期的消费金额

full join全连接

在执行完全外连接时，Oracle 会执行一个完整的左外连接和右外连接查询，然后将查询结果合并，并消除重复的记录行。

-- 补齐客户月度消费数据
SELECT 
	COALESCE( a.CUS_NO, b.CUS_NO ) AS 客户编号,
	COALESCE ( a.TRAN_MONTH, b.TRAN_MONTH ) AS 交易月份,
	a.TRAN_AMT 交易金额
FROM
	test2 a
	FULL JOIN (
		-- 内联接表 笛卡尔 客户编号+日期月份 排重
		SELECT DISTINCT
			b.CUS_NO,
			to_char ( a.date_list, 'YYYYMM' ) AS TRAN_MONTH 
		FROM (
				-- 给定日期开始根据日期天数生成日期
				SELECT
					to_date ( '20191001', 'YYYYMMDD' ) + rownum - 1 AS date_list 
				FROM
					DUAL 
					-- 生成数字序列结果集  日期天数
				connect BY rownum <= ( to_date ( '20201201', 'YYYYMMDD' ) - to_date ( '20191001', 'YYYYMMDD' ) ) + 1 
			) a, ( SELECT DISTINCT CUS_NO FROM test2 ) b 
	) b ON a.CUS_NO = b.CUS_NO 
	AND a.TRAN_MONTH = b.TRAN_MONTH;

-- 计算客户月度消费的环比、同比增长率
SELECT
	CUS_NO 客户编号,
	TRAN_MONTH 交易月份,
	TRAN_AMT 交易金额,
	lag ( TRAN_AMT, 1 ) over ( PARTITION BY CUS_NO ORDER BY TRAN_MONTH ) AS lag上个月, -- lag上个月
	lag ( TRAN_AMT, 12 ) over ( PARTITION BY CUS_NO ORDER BY TRAN_MONTH ) AS lag上一年同月, -- lag上一年同月
	lead ( TRAN_AMT, 1 ) over ( PARTITION BY CUS_NO ORDER BY TRAN_MONTH DESC ) AS lead上个月, -- lead上个月
	lead ( TRAN_AMT, 12 ) over ( PARTITION BY CUS_NO ORDER BY TRAN_MONTH DESC ) AS lead上一年同月 -- lead上一年同月 
FROM (
	SELECT 
		COALESCE( a.CUS_NO, b.CUS_NO ) AS CUS_NO,
		COALESCE ( a.TRAN_MONTH, b.TRAN_MONTH ) AS TRAN_MONTH,
		a.TRAN_AMT TRAN_AMT
	FROM
		test2 a
		FULL JOIN (
			-- 内联接表 笛卡尔 客户编号+日期月份 排重
			SELECT DISTINCT
				b.CUS_NO,
				to_char ( a.date_list, 'YYYYMM' ) AS TRAN_MONTH 
			FROM (
					-- 给定日期开始根据日期天数生成日期
					SELECT
						to_date ( '20191001', 'YYYYMMDD' ) + rownum - 1 AS date_list 
					FROM
						DUAL 
						-- 生成数字序列结果集  日期天数
					connect BY rownum <= ( to_date ( '20201201', 'YYYYMMDD' ) - to_date ( '20191001', 'YYYYMMDD' ) ) + 1 
				) a, ( SELECT DISTINCT CUS_NO FROM test2 ) b 
		) b ON a.CUS_NO = b.CUS_NO 
		AND a.TRAN_MONTH = b.TRAN_MONTH
	) test3;

上面代码中，

lag(TRAN_AMT,1) 表示在按客户编号分组并且按交易月份排序后，取当前行往前第1行，即当前月份的上月，如果往前第1行没有数据，则会置为空。

lag(TRAN_AMT,12) 表示取当前行往前第12行，即当前月份的去年同月。

lead的语用法和lag相反，上面代码排序子句中将TRAN_MONTH倒序排序后，lead取得的结果和lag一致。