YashanDB LEAD函数

lead::= LEAD "(" expr [(RESPECT|IGNORE) NULLS] "," offset "," default ")" OVER "(" [query_partition_clause] order_by_clause ")"

-- area1表中包含如下字段和数据
SELECT area_no,area_name,DHQ,employee_count FROM area1;
AREA_NO AREA_NAME            DHQ             EMPLOYEE_COUNT
------- -------------------- --------------- --------------
01      华东               Shanghai                      
02      华西               Chengdu                    300
03      华南               Guangzhou                  400
04      华北               Beijing                    300
05      华中               Wuhan                           

-- 返回AREA_NO排序下一行的EMPLOYEE_COUNT
SELECT LEAD(employee_count) OVER(ORDER BY AREA_NO) res FROM area1;
         RES 
------------ 
         300
         400
         300
               


-- 返回AREA_NO排序下一个非空的EMPLOYEE_COUNT
SELECT LEAD(employee_count IGNORE NULLS) OVER(ORDER BY AREA_NO) res FROM area1;
         RES 
------------ 
         300
         400
         300
 


-- 返回AREA_NO排序下一个非空的EMPLOYEE_COUNT,若为空则返回0
SELECT LEAD(employee_count IGNORE NULLS,1,0) OVER(ORDER BY AREA_NO) res FROM area1;
         RES 
------------ 
         300
         400
         300
           0
           0

-- finance_info表记录了分年、月、机构的收入情况
SELECT year,month,branch,revenue_total FROM finance_info;
YEAR  MONTH BRANCH REVENUE_TOTAL 
----- ----- ------ ------------- 
2001  01    0201            2888
2021  01    0201           28888
2021  01    0101           38888
2021  02    0101           37778

-- 分年统计每一个机构每月和下月的收入对比情况
SELECT year,month,revenue_total curr_month,
LEAD(revenue_total,1,0) OVER (PARTITION BY year ORDER BY year,month,branch) next_month
FROM finance_info;
YEAR  MONTH  CURR_MONTH  NEXT_MONTH 
----- ----- ----------- ----------- 
2001  01           2888           0
2021  01          38888       28888
2021  01          28888       37778
2021  02          37778           0