窗口函数LAG、LEAD、FIRST_VALUE、LAST_VALUE用法

这组函数相对不好理解，下面将结合用户的访问记录数据来进行说明，大家仔细体会。

---原始表数据---vistit_log表----device_idpagevistit_timedtandroid1homepage2022-03-0111:01:012022-03-01android2channelpage2022-03-0108:08:082022-03-01android1detailpage12022-03-0111:02:012022-03-01android2detailpage32022-03-0109:00:002022-03-01android1detailpage22022-03-0118:01:002022-03-01....

• LAG语法 LAG(column,n,default_value),取统计窗口内往上第n行值,column为需要操作的列，n为取第几行的数据[可选默认为1]，default_value为缺省值[可选默认为null],当取不到数据时，默认取default_value，如未设置缺省值，取不到数据时默认为null..

partition by 指定分组字段，order by 指定排序列并指定顺序还是逆序。

--按设备分组，按访问时间正序，取上一条记录中的时间做为本记录的last_time,一般可以用于计算当前页面的浏览时间。selectdevice_id,page,vistit_time,lag(vistit_time,1,1970-01-0100:00:00)over(PARTITIONBYdevice_idORDERBYvistit_timeasc)aslasttimefromvistit_logwheredt=2022-03-01;---------------------------------------------------------------------------device_idpagevistit_timelast_timeandroid1homepage2022-03-0111:01:011970-01-0100:00:00android1detailpage12022-03-0111:02:012022-03-0111:01:01android1detailpage22022-03-0118:01:002022-03-0111:02:01android2channelpage2022-03-0108:08:081970-01-0100:00:00android2detailpage32022-03-0109:00:002022-03-0108:08:08

• LEAG语法 LEAG(column,n,default_value),取统计窗口内往下第n行值,column为需要操作的列，n为取第几行的数据[可选默认为1]，default_value为缺省值[可选默认为null],当取不到数据时，默认取default_value，如未设置缺省值，取不到数据时默认为null.

partition by 指定分组字段，order by 指定排序列并指定顺序还是逆序。

--按设备分组，按访问时间正序，取下一条记录中的时间做为本记录的last_time,一般可以用于计算当前页面的浏览时间。selectdevice_id,page,vistit_time,leag(vistit_time,1,1970-01-0100:00:00)over(PARTITIONBYdevice_idORDERBYvistit_timeasc)aslasttimefromvistit_logwheredt=2022-03-01;---------------------------------------------------------------------------device_idpagevistit_timelast_timeandroid1homepage2022-03-0111:01:012022-03-0111:02:01android1detailpage12022-03-0111:02:012022-03-0118:01:00android1detailpage22022-03-0118:01:001970-01-0100:00:00android2channelpage2022-03-0108:08:082022-03-0109:00:00android2detailpage32022-03-0109:00:001970-01-0100:00:00

• FIRST_VALUE

取分组内排序后，截止到当前行，第一个值

--按设备分组，按访问时间正序，该分组中的第一行做为当前行的值selectdevice_id,page,vistit_time,first_value(vistit_time)over(PARTITIONBYdevice_idORDERBYvistit_timeasc)asfirst_timefromvistit_logwheredt=2022-03-01;---------------------------------------------------------------------------device_idpagevistit_timefirst_timeandroid1homepage2022-03-0111:01:012022-03-0111:01:01android1detailpage12022-03-0111:02:012022-03-0111:01:01android1detailpage22022-03-0118:01:002022-03-0111:01:01android2channelpage2022-03-0108:08:082022-03-0108:08:08android2detailpage32022-03-0109:00:002022-03-0108:08:08

• LAST_VALUE

取分组内排序后，截止到当前行，最后一个值

--按设备分组，按访问时间正序，该分组中的最后一行做为当前行的值selectdevice_id,page,vistit_time,first_value(vistit_time)over(PARTITIONBYdevice_idORDERBYvistit_timeasc)aslast_timefromvistit_logwheredt=2022-03-01;---------------------------------------------------------------------------device_idpagevistit_timelast_timeandroid1homepage2022-03-0111:01:012022-03-0111:01:01android1detailpage12022-03-0111:02:012022-03-0111:02:01android1detailpage22022-03-0118:01:002022-03-0118:01:00android2channelpage2022-03-0108:08:082022-03-0108:08:08android2detailpage32022-03-0109:00:002022-03-0109:00:00可以看出,由于是此函数是截止到当前记录取最后一值，如果排序还用正序的话，并没有达到想要的效果，所以应该改为：（为了观察方便，在最后做了排序输出）selectdevice_id,page,vistit_time,first_value(vistit_time)over(PARTITIONBYdevice_idORDERBYvistit_timedesc)aslast_timefromvistit_logwheredt=2022-03-01orderbydevice_id,vistit_time;---------------------------------------------------------------------------device_idpagevistit_timelast_timeandroid1homepage2022-03-0111:01:012022-03-0118:01:00android1detailpage12022-03-0111:02:012022-03-0118:01:00android1detailpage22022-03-0118:01:002022-03-0118:01:00android2channelpage2022-03-0108:08:08022-03-0109:00:00android2detailpage32022-03-0109:00:002022-03-0109:00:00