Doris-窗口函数-LEAD()

描述:

lead()方法用于描述当前行与向后数一行或多行的关系

参数:

lead()方法共有三个参数 LEAD(expr, offset, default) OVER (partition by clause order by  clause)

1. expr：与后续行进行比较，一般为表中字段，也可以定义无效值，代表整行数据

2. offset：从当前行向后多少行

3. default：当前行为最后一行时的默认值

 例子：

记录第二天的收盘价与当天价格的走势，即第二天收盘价比当天高还是低，并按照时间升序排列

select stock_symbol, closing_date, closing_price,    
case   
#按照closing_price字段的值进行比较，比较后1行，当当前行为最后一行时，设默认值为0，当前行与0进行比较
(lead(closing_price,1, 0)   
#使用下一行的price-当前行的price
over (partition by stock_symbol order by closing_date)-closing_price) > 0   
when true then "higher"   
when false then "flat or lower"    
end as "trending"   
from stock_ticker    
order by closing_date;

| stock_symbol | closing_date        | closing_price | trending      |
|--------------|---------------------|---------------|---------------|
| JDR          | 2014-09-13 00:00:00 | 12.86         | higher        |
| JDR          | 2014-09-14 00:00:00 | 12.89         | higher        |
| JDR          | 2014-09-15 00:00:00 | 12.94         | flat or lower |
| JDR          | 2014-09-16 00:00:00 | 12.55         | higher        |
| JDR          | 2014-09-17 00:00:00 | 14.03         | higher        |
| JDR          | 2014-09-18 00:00:00 | 14.75         | flat or lower |
| JDR          | 2014-09-19 00:00:00 | 13.98         | flat or lower |