描述:
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 |