Shulou(Shulou.com)05/31 Report--

This article is about how to use the LAG function in oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

This content comes from the official translation, because today I saw a query statement using this function, but I don't quite understand the usage. Let's take a brief look at it here.

LAG is an analysis function that is mainly used to access multiple rows of data at the same time, and this data is not obtained through self-connection. When multiple rows of values and a position cursor are obtained from the query, LAG accesses the data in which the physical offset takes precedence in the cursor location

The offset (offset) parameter is an optional integer value greater than 0. If you do not specify an offset, the default value is 1.

When offset is out of the display range, the value of the option default is displayed. If you do not specify a default value, it is empty by default.

The {RESPECT | IGNORE} NULLS option determines whether null values or values of VALUE_EXPR expressions are included or eliminated. The default is RESPECT NULLS.

For the expression VALUE_EXPR, you cannot use any other parsing functions nested in LAG, but you can use built-in functions.

The columns given in the document are representative:

SELECT hire_date, last_name, salary, LAG (salary, 1,0) OVER (ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = 'PU_CLERK' ORDER BY hire_date HIRE_DATE LAST_NAME SALARY PREV_SAL -2003-05-18 00:00:00 Khoo 3100 02005-07-24 00:00:00 Tobias 2800 31002005-12-24 00:00:00 Baida 2900 28002006-11-15 00:00:00 Himuro 2600 29002007-08-10 00:00:00 Colmenares 2500 2600

How to understand how LAG works, because the result is multiple rows of data:

SQL > select hire_date,last_name,salary from employees where job_id = 'PU_CLERK' order by hire_date HIRE_DATE LAST_NAME SALARY -2003-05-18 00:00:00 Khoo 31002005-07-24 00:00:00 Tobias 28002005-12-24 00:00:00 Baida 29002006-11-15 00:00:00 Himuro 26002007-08-10 00:00:00 Colmenares 2500

From the two query results, it is not difficult to see that LAG offsets the query result with an offset of 1, and obtains the previous query result. When the previous query of the first row record (salary=3100) does not exist, offset exceeds the display range, so it is replaced by the specified default value of 0.

Thank you for reading! This is the end of the article on "how to use the LAG function in oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.