sql LEAD分析函数 的bug

cshiqin

with t0 as (select a.car_id,a.plate_no,a.plate_color
              from vmc_car_info a,vmc_car_manager b
             where a.car_id = b.car_id  and a.CAR_STATUS<>'99'
               and a.enterprise_id=100000285
               and b.user_id=200000319),
t1 as
(SELECT a.car_id,LOG_TIME,ONLINE_STATUS,
         LEAD(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_time,
         LEAD(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_status,
         lag(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_time,
         lag(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_status
    FROM vmc_car_online_log a,t0
   where a.car_id = t0.car_id
     and log_time between
         to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss') and
         to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')),
t2 as
(select car_id,
         LOG_TIME online_time,
         nvl(next_time,
             to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')) offline_time
    from t1
   where t1.ONLINE_STATUS = 1),
t3 as
(select car_id,
         nvl(pre_time,
             to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss')) online_time,
         LOG_TIME offline_time
    from t1
   where t1.ONLINE_STATUS = 0),
t4 as (select * from t2  union  select * from t3),
t5 as (select CAR_ID,
              round(SUM(OFFLINE_TIME - ONLINE_TIME) /
                     (to_date('2013-11-27 12:29:09',
                              'yyyy-MM-dd hh24:mi:ss') -
                      to_date('2013-11-26 12:36:03',
                              'yyyy-MM-dd hh24:mi:ss')) * 100,   6) online_ratio,
               SUM(OFFLINE_TIME - ONLINE_TIME)*864  online_time,count(*) online_cnt   
          from t4  GROUP BY CAR_ID)
          select t5.* from t5 ,t0 where t5.car_id=t0.car_id order by t5.car_id;
结果是:
    CAR_ID ONLINE_RATIO ONLINE_TIME ONLINE_CNT
---------- ------------ ----------- ----------
600217878     7.761729       66.74          1
600217937     2.449236       21.06          1
600217938     2.770218       23.82          2
600217939     3.487777       29.99          6

最后的  select t5.* from t5 ,t0 where t5.car_id=t0.car_id order by t5.car_id;改成不与t0关联
写成select t5.* from t5 order by  t5.car_id;时,最后一笔资料完全不同.

with t0 as (select a.car_id,a.plate_no,a.plate_color
              from vmc_car_info a,vmc_car_manager b
             where a.car_id = b.car_id  and a.CAR_STATUS<>'99'
               and a.enterprise_id=100000285
               and b.user_id=200000319),
t1 as
(SELECT a.car_id,LOG_TIME,ONLINE_STATUS,
         LEAD(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_time,
         LEAD(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_status,
         lag(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_time,
         lag(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_status
    FROM vmc_car_online_log a,t0
   where a.car_id = t0.car_id
     and log_time between
         to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss') and
         to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')),
t2 as
(select car_id,
         LOG_TIME online_time,
         nvl(next_time,
             to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')) offline_time
    from t1
   where t1.ONLINE_STATUS = 1),
t3 as
(select car_id,
         nvl(pre_time,
             to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss')) online_time,
         LOG_TIME offline_time
    from t1
   where t1.ONLINE_STATUS = 0),
t4 as (select * from t2  union  select * from t3),
t5 as (select CAR_ID,
              round(SUM(OFFLINE_TIME - ONLINE_TIME) /
                     (to_date('2013-11-27 12:29:09',
                              'yyyy-MM-dd hh24:mi:ss') -
                      to_date('2013-11-26 12:36:03',
                              'yyyy-MM-dd hh24:mi:ss')) * 100,   6) online_ratio,
               SUM(OFFLINE_TIME - ONLINE_TIME)*864  online_time,count(*) online_cnt   
          from t4  GROUP BY CAR_ID)
          select t5.* from t5  order by t5.car_id;

结果变成了:

    CAR_ID ONLINE_RATIO ONLINE_TIME ONLINE_CNT
---------- ------------ ----------- ----------
600217878     7.761729       66.74          1
600217937     2.449236       21.06          1
600217938     2.770218       23.82          2
600217939      9.26081       79.63          5

请问这真的是个bug吗?

cshiqin

补充一下,环境如下
$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 27 13:58:26 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

26856649

没有用例数据，难怪没人回复

anbob

你可以用dbms_sqldiag.export_sql_testcase  生成你的测试数据打包上传，别人才好测。