lag与lead函数 mysql 8.0版本前实现方式

sql oracle mysql 数据库 基础 知识 窗口函数 lag与lead函数 LISTAGG函数 connect by 结构化查询 备份和恢复 mysql5.7 mysql8.0 oracle_11g docker

lag与lead函数 mysql 8.0版本前实现方式

mysql 8.0版本前实现方式

MySQL5.7.25和 8.0.16 环境中实现类似Oracle的分析函数(8.0版本中已支持，直接使用即可)。

create table test2(
  cus_no varchar(10) DEFAULT NULL COMMENT '客户编号',
  tran_month varchar(6) DEFAULT NULL COMMENT '交易月份',
  tran_amt DECIMAL(20,2) DEFAULT NULL COMMENT '交易金额'
);

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

insert into test2(cus_no,tran_month,tran_amt) values('cus_101029','201910',540.00);
insert into test2(cus_no,tran_month,tran_amt) values('cus_101029','201911',495.00);
insert into test2(cus_no,tran_month,tran_amt) values('cus_101029','201912',360.00);
insert into test2(cus_no,tran_month,tran_amt) values('cus_101029','202001',990.00);
insert into test2(cus_no,tran_month,tran_amt) values('cus_101029','202011',190.00);

insert into test2(cus_no,tran_month,tran_amt) values('cus_101030','201910',990.00);
insert into test2(cus_no,tran_month,tran_amt) values('cus_101030','201911',330.00);
insert into test2(cus_no,tran_month,tran_amt) values('cus_101030','202001',560.00);

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

例：

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

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

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

SELECT
	t2.cus_no '客户编号',
	t2.tran_month '交易月份',
	t1.tran_amt '交易金额',
	-- 上一个月、上一年调整时间区间
	( SELECT sum(tran_amt) FROM test1 WHERE cus_no = t1.cus_no and date_format(date_add(concat(tran_month,'01'), interval '1' month),'%Y%m') = t1.tran_month ) AS 'lag上个月',-- lag上个月
	( SELECT sum(tran_amt) FROM test1 WHERE cus_no = t1.cus_no and date_format(date_add(concat(tran_month,'01'), interval '12' month),'%Y%m') = t1.tran_month ) AS 'lag上一年同月' -- lag上一年同月
FROM
	test2 AS t1
RIGHT JOIN (
	SELECT * FROM ( 
	-- <1> 生成一个时间区间的月份列表 
		SELECT
			date_format( date_add( '20191001', INTERVAL @s MONTH ), '%Y%m' ) AS tran_month,
			 @s := @s + 1 AS `index`
		FROM
			mysql.help_topic,
			( SELECT @s := 0 ) temp 
		WHERE
			-- 两个日期间相差的月份
			@s <= TIMESTAMPDIFF(MONTH,'20191001','20201201')
	-- <1>
	) t_1 
	INNER JOIN ( 
		SELECT DISTINCT cus_no FROM test2 
	) t_2
) AS t2 ON t2.tran_month = t1.tran_month and t2.cus_no = t1.cus_no
ORDER BY t2.cus_no, t2.tran_month; 

[oracle]first_value、last_value与nth_value函数

函数语法如下，他们都支持使用窗口子句：

first_value( col ) over([分区子句] [排序子句] [开窗子句] )

last_value( col ) over( [分区子句] [排序子句] [开窗子句] )

nth_value( col ,n ) [ FROM FIRST | FROM LAST ] [ RESPECT NOLLS | IGNORE NOLLS ] over( [分区子句] [排序子句] [开窗子句] )

注：
	first_value和last_value函数常用在计算排过序的结果集中的第一行和最后一行数据，或者说是最大值和最小值(依排序而定)
	nth_value则可以获取任意行的数据

需求：统计每个客户在所有消费月份中的最大消费金额与最小消费金额

说明：

统计每个客户在过往所有消费月份中，消费金额最大月份的消费金额与消费金额最小月份的消费金额

-- 统计每个客户在所有消费月份中的最大消费金额与最小消费金额
SELECT
	CUS_NO 客户编号,
	TRAN_MONTH 交易月份,
	TRAN_AMT 交易金额,
	first_value ( TRAN_AMT ) over ( partition BY CUS_NO ORDER BY TRAN_AMT ROWS BETWEEN unbounded preceding AND unbounded following ) AS 最小消费金额,
	last_value ( TRAN_AMT ) over ( partition BY CUS_NO ORDER BY TRAN_AMT ROWS BETWEEN unbounded preceding AND unbounded following ) AS 最大消费金额,
	nth_value ( TRAN_AMT, 2 ) over ( partition BY CUS_NO ORDER BY TRAN_AMT ROWS BETWEEN unbounded preceding AND unbounded following ) AS 第2小的消费金额
FROM
	test2;

窗口子句 rows between unbounded preceding and unbounded following 表示滑动窗口范围是整个分区，否则默认的滑动窗口将是分区中第一行到当前行 因为中间用了order by。

扩展需求：统计每个客户在当前月以及前两个月这三个月中的最大消费金额与最小消费金额

SELECT
	CUS_NO 客户编号,
	TRAN_MONTH 交易月份,
	TRAN_AMT 交易金额,
	min( TRAN_AMT ) over ( PARTITION BY CUS_NO ORDER BY month_num rows BETWEEN 2 preceding AND 0 following ) AS 当前至前两个月最小值,
	max( TRAN_AMT ) over ( PARTITION BY CUS_NO ORDER BY month_num rows BETWEEN 2 preceding AND 0 following ) AS 当前至两个月最大值 
FROM( 
	-- 对行进行排序并为每一行增加一个唯一编号
	SELECT CUS_NO, TRAN_MONTH, TRAN_AMT, row_number ( ) over ( PARTITION BY CUS_NO ORDER BY TRAN_MONTH ) AS month_num 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
	) 
);