Use the lag and lead functions to count the ___ function

The lag and lead functions can fetch the first n rows of data from the same field and the values of the latter n rows in a single query. This can be done using table joins to the same table, but with lag and leads is more efficient. The following are examples of lag and leads:

Sql> Select Year,region,profit, Lag (profit,1) over

2 as last_year_exp from test;

Year REGION PROFIT Last_year_exp

---- ------- ---------- -------------

2003 West 88

2003 West 88 88

2003 101 88

2003 100 101

2003 East 102 100

West 77 102

103 77

West 89 103

Sql> Select Year,region,profit, lead (profit,1)

2 as next_year_exp from test;

Year REGION PROFIT Next_year_exp

---- ------- ---------- -------------

2003 West 88 88

2003 West 88 101

2003 101 100

2003 100 102

2003 East 102 77

West 77 103

103 89

89 West

The Lag function is lag (exp,n,defval), Defval is the value that is returned if the function has no value available. The lead function is similar in usage.

The lead and lag functions can also use groupings, and the following are examples of using region groupings:

Sql> Select Year,region,profit,

2 Lag (profit,1,0) over (PARTITION by region

3 as last_year_exp from test;

Year REGION PROFIT Last_year_exp

---- ------- ---------- -------------

2003 101 0

2003 100 101

2003 East 102 0

103 102

2003 West 88 0

2003 West 88 88

West 77 88

West 89 77

Practical Case:

--by Vehicle statistic alarm Amount Select U.UNITID,NVL (countinfo.cut,0) from T_unit u, (select U.unitid, Count (Al.unitid) as cut--, v_state, V_star Date from T_alarm al,t_unit u where al.unitid (+) = U.unitid and InStr (', ' | | al.state | | ',', ',' || 10 | | ', ' > 0 and Al.gpstime between Sysdate-10 and Sysdate Group by U.unitid) Countinfo where U.unitid=co Untinfo.unitid (+)--Statistics history escalation status SELECT v.history_id record ID, v.unit_id terminal number, V.stamp this article Record time, V.recordtimeon record time, v.nextrecordtime between two times _ seconds from (SELECT t.history_id, T.uni t_id, T.stamp, LAG (T.stamp, 1) over (order by t.unit_id) Recordtimeon, (T.stam
                  P-lag (T.stamp, 1) over (order by t.unit_id)) * * * as Nextrecordtime from (SELECT *
                 From T_history T WHERE t.unit_id = ' 0100000005 ' and T.stamp > SYSDATE-10 Order by T.stamp) T) V WHERE v.nextrecordtime > 40--Statistical status data escalation case SELECT v.con_id record ID, v.unit_id terminal Number, V.stamp This article record time, V.recordtimeon record time, v.nextrecordtime between two times _ seconds from (SELECT T.con _id, t.unit_id, T.stamp, LAG (T.stamp, 1) over (order by t.unit_id) Recordtimeo N, (T.stamp-lag (T.stamp, 1) over (order by t.unit_id)) * * * as nextrecordtime from (SE Lect * from t_conditions T WHERE t.unit_id = ' 0100000005 ' and T.stam  P > SYSDATE-10 ORDER by T.stamp) T V WHERE v.nextrecordtime > 40--Statistics of the maximum stop escalation time interval of a car one day CREATE OR
                                                      REPLACE FUNCTION get_interval_by_unitid_day (v_unit_id in VARCHAR2,
V_day in VARCHAR2) return number as v_count number;
       BEGIN Select MAX (nextrecordtime) into V_count from (select v.con_id,          v.unit_id, V.stamp, V.recordtimeon, V.nextrecordtime
                         From (SELECT t.con_id, t.unit_id, T.stamp, LAG (T.stamp, 1) over (order by t.unit_id) Recordtimeon, (T.stamp-lag (T.stamp, 1) over (order by T . UNIT_ID)) * * * as Nextrecordtime from (SELECT * from t_condition
                           S T WHERE t.unit_id = v_unit_id and TRUNC (t.stamp) = V_day

  ORDER by T.stamp) T) V WHERE v.nextrecordtime > 0);
return v_count;

End;
                                                      --record number of statistics escalation record interval greater than set time CREATE OR REPLACE FUNCTION get_exceeded_by_unitid_day (v_unit_id in VARCHAR2,
V_day in VARCHAR2) return number as v_count number; BEGIN SELECT COUNT (nextrecordtime) into V_count from (SELect v.con_id, v.unit_id, V.stamp, V.recordtimeon, V.N
                         Extrecordtime from (SELECT t.con_id, t.unit_id, T.stamp, LAG (T.stamp, 1) over (order by t.unit_id) Recordtimeon, T.stamp-lag (T.sta
                            MP, 1) over (order by t.unit_id)) * * Nextrecordtime as a from (SELECT * From t_conditions t WHERE t.unit_id = v_unit_id and TRUNC (t .

  STAMP = v_day ORDER by T.stamp) T) V WHERE v.nextrecordtime > 40);
return v_count;

End; --Working Condition Data analysis View CREATE OR REPLACE view v_condition_data_analysis as SELECT u.unit_id terminal number, Day Data statistic date, NVL (Counti NFO. Cut, 0) The total number of reported records, ROUND (Nvl countinfo.
  Cut, 0)/24 The average number of records reported per hour, Get_interval_by_unitid_day (u.unit_id, day) maximum time interval,     Get_exceeded_by_unitid_day (u.unit_id, day) does not conform to the escalation interval record from T_unit U, (SELECT h.unit_id, TRUNC (H.stamp) Day, C  Ount (h.unit_id) as cut from T_conditions H--where h.stamp BETWEEN-to_date (' 2013-03-09
         00:00:00 ', ' yyyy-mm-dd HH24:MI:SS ') and--to_date (' 2013-07-10 00:00:00 ', ' yyyy-mm-dd HH24:MI:SS ') GROUP by h.unit_id, TRUNC (h.stamp)) countinfo WHERE u.unit_id = countinfo.
 
UNIT_ID (+) Order by u.unit_id, day; --Vehicle Reporting GPS data analysis CREATE OR REPLACE VIEW v_history_data_analysis as SELECT u.unit_id terminal number, Day Data statistic date, NVL (COUNT INFO. Cut, 0) The total number of reported records, ROUND (Nvl countinfo. Cut, 0)/24 The average number of records reported per hour, Get_hisinterval_by_unitid_day (u.unit_id, day) maximum time interval, Get_hisexceeded_by_unitid_da
          Y (u.unit_id, day) does not conform to the escalation interval record from T_unit U, (SELECT h.unit_id, TRUNC (H.stamp) Day, COUNT (h.unit_id) as cut From T_history H--where h.stamp BETWEEN-to_date (' 2013-03-09 00:00:00 ', ' YYYY-mm-dd HH24:MI:SS ') and--to_date (' 2013-07-10 00:00:00 ', ' yyyy-mm-dd HH24:MI:SS ') GROUP by H.unit _id, TRUNC (h.stamp)) countinfo WHERE u.unit_id = countinfo.
 
 
UNIT_ID (+) Order by u.unit_id, day;

 --View status Report SELECT * FROM V_condition_data_analysis T--vehicle report GPS data Analysis Select * FROM V_history_data_analysis t