| The example below shows the use of Oracle analytic functions LEAD and LAG to leading (next) value or trailing/lagging (previous) value to query an oracle database table. This setup shows LEAD and LAG value Over (Order BY level) syntax resulting in next/previous value respectively of the current value of level that represents the month number. The LEAD and LAG value can be specified with offset and default values as LEAD(value_expression, offset, default) and LAG(value_expression, offset, default). The offset and default values are optional. When the offset value is not specified, it defaults to 1, which corresponds to the first position. |
| In the example below LEAD(level) and LAG(level), the offset and default values are not specified and 1 and null are used as default values in the LEAD and LAG functions. For the first value of the month there is no previous month value (LAG value) as the range of values are between 1 and 12 thus computes to a null value (no default value specified). In the same way there is no next month value for the 12th month and hence computes to a null value. In other data analytics, suitable default value can be used. |
| SELECT level month_no, |
| LAG(level) OVER(ORDER BY level) prev_month, |
| LEAD(level) OVER(ORDER BY level) next_month, |
| NTILE(4) OVER (ORDER BY level) As Quarter, |
| COUNT(*) OVER() months, |
| TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1),'mm/dd/yyyy') start_date, |
| TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)),'mm/dd/yyyy') end_date, |
| LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1))- |
| ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)+1 month_days, |
| SUM(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)) - ADD_MONTHS(TRUNC(SYSDATE,'year'),level-1)+1) |
| OVER (ORDER BY level) ytd_days |
| FROM DUAL CONNECT BY level <= 12; |
74482