南大通用GBase 8a窗口函数LEAD/LAG的使用样例

本文介绍GBase 8a的窗口函数LEAD、LAG的使用方法和样例。

参考

GBase 8a窗口函数使用样例索引

语法

LEAD/LAG(expr [,offset [,DEFAULT]] ) OVER([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [ASC/DESC], col_name2 [ASC/DESC],…)

说明

LEAD 超前,也就是后面的新数据,LAG 滞后,也就是以前的旧数据。

  1. expr:此参数是求偏移量的表达式。
  2. offset:此参数是偏移量,可以省略,默认值是 1。
  3. default:此参数是缺省值,可以省略,默认值是 NULL。
  4. 这两个函数 OVER 里面的规则同 RANK 类 OLAP 函数一样。
  5. 该函数可以返回任何支持的数据类型。

参数2和参数3必须是常量或常量表达式。第一个参数与第三个参数的数据类型不同时,第三个参数会根据第一个参数的数据类型做隐式转换。

样例

lead样例

获取的是后面新的数据,默认是后1行。通过第2个参数,指定后面第几行,例子中是后面第2行。 如果后面没有了,默认返回null, 通过第3个参数,替换这个null值。

select t2.*,lead(val) over(order by val) v from t2;select t2.*,lead(val,2) over(order by val) v from t2;select t2.*,lead(val,2,-1) over(order by val) v from t2;
+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 122 |
| 2 | 1 | 122 | 133 |
| 3 | 1 | 133 | 222 |
| 4 | 2 | 222 | 233 |
| 5 | 2 | 233 | 244 |
| 6 | 2 | 244 | 333 |
| 7 | 3 | 333 | NULL |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.05)
+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 133 |
| 2 | 1 | 122 | 222 |
| 3 | 1 | 133 | 233 |
| 4 | 2 | 222 | 244 |
| 5 | 2 | 233 | 333 |
| 6 | 2 | 244 | NULL |
| 7 | 3 | 333 | NULL |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)
+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 133 |
| 2 | 1 | 122 | 222 |
| 3 | 1 | 133 | 233 |
| 4 | 2 | 222 | 244 |
| 5 | 2 | 233 | 333 |
| 6 | 2 | 244 | -1 |
| 7 | 3 | 333 | -1 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)

LAG样例

和LEAD类似,只是找的是前面出现过的旧数据。参数也一样。

select t2.*,lag(val) over(order by val) v from t2;select t2.*,lag(val,2) over(order by val) v from t2;select t2.*,lag(val,2,-1) over(order by val) v from t2;
+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | NULL |
| 2 | 1 | 122 | 111 |
| 3 | 1 | 133 | 122 |
| 4 | 2 | 222 | 133 |
| 5 | 2 | 233 | 222 |
| 6 | 2 | 244 | 233 |
| 7 | 3 | 333 | 244 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)
+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | NULL |
| 2 | 1 | 122 | NULL |
| 3 | 1 | 133 | 111 |
| 4 | 2 | 222 | 122 |
| 5 | 2 | 233 | 133 |
| 6 | 2 | 244 | 222 |
| 7 | 3 | 333 | 233 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)
+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | -1 |
| 2 | 1 | 122 | -1 |
| 3 | 1 | 133 | 111 |
| 4 | 2 | 222 | 122 |
| 5 | 2 | 233 | 133 |
| 6 | 2 | 244 | 222 |
| 7 | 3 | 333 | 233 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)

带PARTITION的LEAD和LAG样例

每个partition各自计算。

gbase> select t2.*,lead(val) over(partition by type order by val) v from t2;
+------+------+------+------+
| id   | type | val  | v    |
+------+------+------+------+
|    4 |    2 |  222 |  233 |
|    5 |    2 |  233 |  244 |
|    6 |    2 |  244 | NULL |
|    1 |    1 |  111 |  122 |
|    2 |    1 |  122 |  133 |
|    3 |    1 |  133 | NULL |
|    7 |    3 |  333 | NULL |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.06)

gbase> select t2.*,lag(val) over(partition by type order by val) v from t2;
+------+------+------+------+
| id   | type | val  | v    |
+------+------+------+------+
|    4 |    2 |  222 | NULL |
|    5 |    2 |  233 |  222 |
|    6 |    2 |  244 |  233 |
|    1 |    1 |  111 | NULL |
|    2 |    1 |  122 |  111 |
|    3 |    1 |  133 |  122 |
|    7 |    3 |  333 | NULL |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.05)