[DB] [Oracle] [statistical function] The lag and lead functions can be used to retrieve the data of the first n rows and the values of the last n rows of the same field in a query.

The lag and lead functions can be used to retrieve the data of the first n rows and the values of the last n rows of the same field in a query.

The lag and lead functions can retrieve the data of the first n rows and the value of the last n rows of the same field in a query. This operation can be achieved through table join for the same table, but lag and lead are more efficient. The following are examples of lag and lead: SQL> Select Year, region, profit, lag (profit, 1) over (order by year)   2 As last_year_exp from test;   Year region profit last_year_exp ---------------------------------- 2003 West 88.cn 2003 West 88 88 2003 Central 101 88 2003 Central 100 101 2003 East 102 100 2004 west 77 102 2004 East 103 77 2004 West 89 103   SQL> Select Year, region, profit, lead (profit, 1) over (order by year)   2 As next_year_exp from test;   Year region profit next_year_exp ---------------------------------- 2003 West 88 88 2003 West 88 101 2003 Central 101 100 2003 Central 100 102 2003 East 102 77 2004 west 77 103 2004 East 103 89 2004 West 89 The lag function is lag (exp, N, defval), and defval is the value returned when the function has no value available. The usage of the lead function is similar. The lead and lag functions can also be grouped. The following is an example of using region grouping: SQL> Select Year, region, profit, 2 lag (profit, 1, 0) over (partition by region order by year)   3 As last_year_exp from test;   Year region profit last_year_exp ---------------------------------- 2003 Central 101 0 2003 Central 100 101 2003 East 102 0 2004 East 103 102 2003 West 88 0 2003 West 88 88 2004 west 77 88 2004 West 89 77 This function can also be used to convert a vertical table to a horizontal table. Example: SQL> select * From test_value; Mons jjr cjl cjje ---------------------------------------- 200801 lzf 250 1999 200802 lzf 200 2000 200803 lzf 300 1000 200804 lzf 23 189 200805 lzf 356 456 200806 lzf 100 200 200807 lzf 600 700 200808 lzf 23 123 200809 lzf 400 500 9 rows selected SQL> SQL> select rownum No., Mons, cjl cjl_01, 2 lead (cjl, 1) over (order by Mons DESC) cjl_02, 3 lead (cjl, 2) over (order by Mons DESC) cjl_03, 4 lead (cjl, 3) over (order by Mons DESC) cjl_04, 5 lead (cjl, 4) over (order by Mons DESC) cjl_05, 6 lead (cjl, 5) over (order by Mons DESC) cjl_06, 7 lead (cjl, 6) over (order by Mons DESC) cjl_07, 8 Lead (cjl, 7) over (order by Mons DESC) cjl_08, 9 lead (cjl, 8) over (order by Mons DESC) cjl_09 10 from test_value No. Mons cjl_01 cjl_02 cjl_03 cjl_04 cjl_05 cjl_06 cjl_07 cjl_08 cjl_09 -------------------------------------------------------------------------------------------------------------- 9 200809 400 23 600 100 356 23 300 200 250 8 200808 23 600 100 356 23 300 200 250 7 200807 600 100 356 23 300 200 250 6 200806 100 356 23 300 200 250 5 200805 356 23 300 200 250 4 200804 23 300 200 250 3 200803 300 200 250 2 200802 200 250 1 200801 250