WINDOW FUNCTION LEAD

description

LEAD() 方法用来计算当前行向后数若干行的值。

  1. LEAD(expr, offset, default]) OVER (partition_by_clause order_by_clause)

example

计算第二天的收盘价对比当天收盘价的走势，即第二天收盘价比当天高还是低。

  1. select stock_symbol, closing_date, closing_price,    
  2. case   
  3. (lead(closing_price,1, 0)   
  4. over (partition by stock_symbol order by closing_date)-closing_price) > 0   
  5. when true then "higher"   
  6. when false then "flat or lower"    
  7. end as "trending"   
  8. from stock_ticker    
  9. order by closing_date;
  11. | stock_symbol | closing_date        | closing_price | trending      |
  12. |--------------|---------------------|---------------|---------------|
  13. | JDR          | 2014-09-13 00:00:00 | 12.86         | higher        |
  14. | JDR          | 2014-09-14 00:00:00 | 12.89         | higher        |
  15. | JDR          | 2014-09-15 00:00:00 | 12.94         | flat or lower |
  16. | JDR          | 2014-09-16 00:00:00 | 12.55         | higher        |
  17. | JDR          | 2014-09-17 00:00:00 | 14.03         | higher        |
  18. | JDR          | 2014-09-18 00:00:00 | 14.75         | flat or lower |
  19. | JDR          | 2014-09-19 00:00:00 | 13.98         | flat or lower |

keywords

  1. WINDOW,FUNCTION,LEAD