Oracle Analytic Functions LEAD/LAG
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 with offset and default values. The query table (q_data) has duplicate product data that can be analyzed with LEAD and LAG function. The missing value in LEAD and LAG function are defaulted to 0. The use of OVER (PARTITION BY ... ORDER BY ...) defines the way data is partitioned and ordered for the LEAD/LAG values. The first value of LAG(prod_cd,1,0) is 0 since there is no previous value and last value of LEAD(prod_cd,1,0) 0 for the duplicated div_cd of 1001 and 1004. For 1002 and 1003 both LEAD and LAG values are zero since they are unique values in the query table (q_data).

SELECT q_data.div_cd, q_data.prod_cd, q_data.Cust_Cd,
  COUNT(*) OVER(PARTITION BY q_data.div_cd, q_data.prod_cd, q_data.cust_cd
   Order By q_data.div_cd, q_data.prod_cd, q_data.cust_cd) Counts,
  LAG(Prod_Cd,1,0) OVER(PARTITION BY q_data.div_cd
   ORDER BY q_data.div_cd, q_data.prod_cd, q_data.cust_cd) Lag_Prod,
  LEAD(Prod_Cd,1,0) OVER(PARTITION BY q_data.div_cd
   ORDER BY q_data.div_cd, q_data.prod_cd, q_data.cust_cd) lead_Prod
FROM (SELECT 1001 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION ALL
  SELECT 1001 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION ALL
  SELECT 1001 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION ALL
  SELECT 1002 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION ALL
  SELECT 1003 div_cd, 5001 prod_cd, 101 cust_cd FROM DUAL UNION ALL
  SELECT 1004 div_cd, 5002 prod_cd, 102 cust_cd FROM DUAL UNION ALL
  SELECT 1004 div_cd, 5002 prod_cd, 102 cust_cd FROM DUAL
) q_data;


Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: November 17, 2013

  73766