Postgresql11 间隔时间计算sql之优化

• Server Type    : PostgreSQL
• Server Version : 11
• 情景：
  • 计算线路车辆早7：00时开始车辆运行开始平均时间间隔。
• 思路：

  • a.rn	b.rn
    	line1
    line1	line2
    line2	line3
    line3	line4
    line4	line5
    line5
    a.rn = b.rn -1

• 解决办法：

  • with a as (
    SELECT ds,line,row_number() over (partition by ds,line order by start_time) as lable
    from bus_run_record
    where ds ='2020-04-17' and date_part('hour',start_time) = 7 
    )
    ,c as (SELECT a.ds,b.line,
    date_part('epoch',(b.start_time-a.start_time)/60):: numeric(6,2) as start_div
    from a left join a b
    on a.ds=b.ds and a.line=b.line and a.lable=b.lable-1
    where (b.ds-a.ds) is  not NULL
    )
    
    select ds,line, avg(start_div):: numeric(6,2) as start_div
    from c
    group by ds,line
    order by ds,line

     

• 优化措施：

  • with a as(
    select ds,line,start_time,
    date_part('epoch',(lead(start_time) over (partition by ds,line order by start_time) - start_time)/60) as start_div
    from bus_run_record
    where ds ='2020-04-17' and date_part('hour',start_time) = 7 
    )
    
    select ds,line, avg(start_div):: numeric(6,2) as start_div
    from a
    where start_div is not null 
    group by ds,line
    order by ds,line

     

  •  Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。
    这种操作可以代替表的join自联接，并且LAG和LEAD有更高的效率.

  • 经过实际优化测试，比一开始自联结写法快0.5倍