SQL函数之lag() over()、lead() over()函数

清疚 2023-02-23 12:29 41阅读 0赞

lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数，通过这两个函数**可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤**。这种操作可以代替表的自联接，并且 LAG 和 LEAD 有更高的效率。

over() 表示 lag() 与 lead() 操作的数据都在 over() 的范围内，他里面可以使用 partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b 表示以 a 字段进行分组，再 以 b 字段进行排序，对数据进行查询。

**例如：**lead(field, num, defaultvalue) field 需要查找的字段，num 往后查找的 num 行的数据，defaultvalue 没有符合条件的默认值

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc_size_16_color_FFFFFF_t_70][]

**1. 获取当前记录的 id，以及下一条记录的 id**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc_size_16_color_FFFFFF_t_70 1][]

**2. 获取品牌号码相同的，当前记录的 id 和下一条记录的 id**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc_size_16_color_FFFFFF_t_70 2][]

3. 查询 cphm 的总数，当 create\_date 与下一条记录的 create\_date 时间间隔不超过 10 分钟则忽略

select cphm, count(1) total from
    (
    select t.id, 
      t.create_date t1,
      lead(t.create_date,1, null) over( partition by  cphm order by create_date asc ) t2,  
      ( lead(t.create_date,1, null) over(  partition by  cphm order by create_date asc )  - t.create_date ) * 86400 as itvtime,
      t.cphm
    from tb_test t 
      order by t.cphm, t.create_date asc
    ) tt
    where tt.itvtime >= 600 or  tt.itvtime  is null
    group by tt.cphm

![20191107163103141.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191107161747813.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191107161934470.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019110716261821.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFfMTU3MDc5NTE5MDc=,size_16,color_FFFFFF,t_70
[20191107163103141.png]: https://img-blog.csdnimg.cn/20191107163103141.png