hive中lead将下一行移动当前和lag函数将上一行移动到当前的使用

hive中lead和lag函数的使用

导入数据

select * from bus ;
+-------+-------------+
| name  |  orderdate  |
+-------+-------------+
| jack  | 2017-01-01  |
| jack  | 2017-02-03  |
| jack  | 2017-04-06  |
| jack  | 2017-01-05  |
| jack  | 2017-01-08  |
| mart  | 2017-04-08  |
| mart  | 2017-04-09  |
| mart  | 2017-04-11  |
| mart  | 2017-04-13  |
| neil  | 2017-05-10  |
| neil  | 2017-06-12  |
| tony  | 2017-01-02  |
| tony  | 2017-01-04  |
| tony  | 2017-01-07  |
+-------+-------------+

1.lead()over()将下一行提到当前行来另起字段
参数lesd(需要调整的字段名,下几行,空余部分用什么补充)over()

select name,orderdate,
lead(orderdate,1,0)over(partition by name order by orderdate)as lead_or 
from bus;
+-------+-------------+-------------+
| name  |  orderdate  |   lead_or   |
+-------+-------------+-------------+
| jack  | 2017-01-01  | 2017-02-03  |
| jack  | 2017-02-03  | 2017-04-06  |
| jack  | 2017-04-06  | 2017-01-05  |
| jack  | 2017-01-05  | 2017-01-08  |
| jack  | 2017-01-08  | 0           |
| mart  | 2017-04-08  | 2017-04-09  |
| mart  | 2017-04-09  | 2017-04-11  |
| mart  | 2017-04-11  | 2017-04-13  |
| mart  | 2017-04-13  | 0           |
| neil  | 2017-05-10  | 2017-06-12  |
| neil  | 2017-06-12  | 0           |
| tony  | 2017-01-02  | 2017-01-04  |
| tony  | 2017-01-04  | 2017-01-07  |
| tony  | 2017-01-07  | 0           |
+-------+-------------+-------------+

2.lag()over()将上一行提到当前行来另起字段
参数lag(需要调整的字段名,上几行,空余部分用什么补充)over()

select name,orderdate,
lag(orderdate,1,0)over(partition by name order by orderdate)as lag_or 
from bus;

```sql
+-------+-------------+-------------+
| name  |  orderdate  |   lag_or   |
+-------+-------------+-------------+
| jack  | 2017-01-01  | 0           |
| jack  | 2017-02-03  | 2017-01-01  |
| jack  | 2017-04-06  | 2017-02-03  |
| jack  | 2017-01-05  | 2017-04-06  |
| jack  | 2017-01-08  | 2017-01-05  |
| mart  | 2017-04-08  | 0           |
| mart  | 2017-04-09  | 2017-04-08  |
| mart  | 2017-04-11  | 2017-04-09  |
| mart  | 2017-04-13  | 2017-04-11  |
| neil  | 2017-05-10  | 0           |
| neil  | 2017-06-12  | 2017-05-10  |
| tony  | 2017-01-02  | 0           |
| tony  | 2017-01-04  | 2017-01-02  |
| tony  | 2017-01-07  | 2017-01-04  |
+-------+-------------+-------------+

3.substr获取字符转的某些字符
参数substr(需要截取的字段名,从第几位开始,从第几位结束)

select name,orderdate from sub
where substr(orderdate,1,7)="2017-04";
+----------+---------------+
| sub.name | sub.orderdate |
+----------+---------------+
| jack     | 2017-04-06    |
| mart     | 2017-04-08    |
| mart     | 2017-04-09    |
| mart     | 2017-04-11    |
| mart     | 2017-04-13    |
+----------+---------------+