使用LAG和LEAD函数统计

最新推荐文章于 2022-06-15 15:58:29 发布

冷月宫主

最新推荐文章于 2022-06-15 15:58:29 发布

阅读量813

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

538 篇文章

订阅专栏

本文介绍了LAG和LEAD函数的基本用法及高级应用案例，包括如何获取同一字段的前N行和后N行的值，并通过实际案例展示了如何利用这些函数进行数据统计和分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。以下是LAG和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

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

Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

Lead和Lag函数也可以使用分组，以下是使用region分组的例子：

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

实用案例：

--按车统计报警量
select u.unitid,nvl(countinfo.cut,0) from t_unit u,
(select u.unitid, count(al.unitid) as cut--, v_state, v_stardate
         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=countinfo.unitid(+)

--统计历史记录上报情况
SELECT V.HISTORY_ID         记录ID,
       V.UNIT_ID        终端编号,
       V.STAMP          本条记录时间,
       V.RECORDTIMEON   上条记录时间,
       V.NEXTRECORDTIME 两条之间的时间差_秒
  FROM (SELECT T.HISTORY_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)) * 24 * 60 * 60 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
 
--统计工况数据上报情况
SELECT V.CON_ID         记录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)) * 24 * 60 * 60 AS NEXTRECORDTIME
          FROM (SELECT *
                  FROM T_CONDITIONS T
                 WHERE T.UNIT_ID = '0100000005'
                   AND T.STAMP > SYSDATE - 10
                 ORDER BY T.STAMP) T) V
 WHERE V.NEXTRECORDTIME > 40
 
--统计某车某天最大停止上报时间间隔
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)) * 24 * 60 * 60 AS NEXTRECORDTIME
                    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 > 0);

  RETURN V_COUNT;
END;

--统计上报记录间隔大于设定时间的记录数
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.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)) * 24 * 60 * 60 AS NEXTRECORDTIME
                    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;

--工况数据分析视图
CREATE OR REPLACE VIEW V_CONDITION_DATA_ANALYSIS AS
SELECT U.UNIT_ID 终端编号,
       DAY 数据统计日期,
       NVL(COUNTINFO.CUT, 0) 上报记录总数,
       ROUND(NVL(COUNTINFO.CUT, 0) / 24) 平均每小时上报记录数,
       GET_INTERVAL_BY_UNITID_DAY(U.UNIT_ID, DAY) 最大时间间隔,
       GET_EXCEEDED_BY_UNITID_DAY(U.UNIT_ID, DAY) 不符合上报间隔记录
  FROM T_UNIT U,
       (SELECT H.UNIT_ID, TRUNC(H.STAMP) DAY, COUNT(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;
 
--车辆上报GPS数据分析
CREATE OR REPLACE VIEW V_HISTORY_DATA_ANALYSIS AS
SELECT U.UNIT_ID 终端编号,
       DAY 数据统计日期,
       NVL(COUNTINFO.CUT, 0) 上报记录总数,
       ROUND(NVL(COUNTINFO.CUT, 0) / 24) 平均每小时上报记录数,
       GET_HISINTERVAL_BY_UNITID_DAY(U.UNIT_ID, DAY) 最大时间间隔,
       GET_HISEXCEEDED_BY_UNITID_DAY(U.UNIT_ID, DAY) 不符合上报间隔记录
  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;
 
 
--查看工况上报的情况
select * from V_CONDITION_DATA_ANALYSIS t 
--车辆上报GPS数据分析
select * from V_HISTORY_DATA_ANALYSIS t