MySQL实现Hive的lag()/lead()不同偏移量函数
1.lad()函数MySQL实现 - 上n个偏移
#偏移量为1, upusualName1为偏移量1
SELECT
@lagfield := @lagfield1 AS upusualName,
@lagfield1 := usualName AS usualName
FROM
table_name,
( SELECT @lagfield := NULL, @lagfield1 := NULL ) init;
#偏移量为2, upusualName2为偏移量2, upusualName1为偏移量1
SELECT
@lagfield := @lagfield1 AS upusualName2,
@lagfield1 := @lagfield2 AS upusualName1,
@lagfield2 := usualName AS usualName
FROM
table_name,
( SELECT @lagfield := NULL, @lagfield1 := NULL , @lagfield2 := NULL) init;
注:( SELECT @lagfield := NULL, @lagfield1 := NULL, @lagfield2 := NULL ) init为初始化null,也可初始化为空串
1.lead()函数MySQL实现 - 下n个偏移
#偏移量为1, upusualName1为偏移量1
SELECT * FROM(
SELECT
tab.id,
@lagfield := @lagfield1 AS upusualName1,
@lagfield1 := usualName AS usualName
FROM
( SELECT * FROM crrc_usualrule_info ORDER BY id DESC ) tab,
( SELECT @lagfield := NULL, @lagfield1 := NULL ) init
) tabl
ORDER BY id;
#偏移量为2, upusualName2为偏移量2, upusualName1为偏移量1
SELECT * FROM(
SELECT
tab.id,
@lagfield := @lagfield1 AS upusualName2,
@lagfield1 := @lagfield2 AS upusualName1,
@lagfield2 := usualName AS usualName
FROM
( SELECT * FROM crrc_usualrule_info ORDER BY id DESC ) tab,
( SELECT @lagfield := NULL, @lagfield1 := NULL, @lagfield2 := NULL ) init
) tabl where upusualName1 = '过节异常'
ORDER BY id;
注:( SELECT @lagfield := NULL, @lagfield1 := NULL, @lagfield2 := NULL ) init为初始化null,也可初始化为空串