- 最后登录
- 2017-5-29
- 在线时间
- 10 小时
- 威望
- 16
- 金钱
- 122
- 注册时间
- 2012-2-6
- 阅读权限
- 10
- 帖子
- 22
- 精华
- 0
- 积分
- 16
- UID
- 203
|
1#
发表于 2013-11-27 13:49:53
|
查看: 4014 |
回复: 3
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吗? |
|