改革运动时间表.Oracle

Question

通过执行以下查询(我使用Oracle 11g)

SELECT EVENTARCHIVE.VEHID vehicle
      , to_char(EVENTARCHIVE.EVENTTIME, 'Dy DD-Mon-YYYY  HH24:MI:SS') occured_on
      , EVENTARCHIVE.EVENTDESCR description
FROM EVENTARCHIVE
     , (SELECT POX
               , POY
               , AREARAD 
        FROM POINTOBJECTS
        WHERE POID = 'PB') PO
WHERE POWER(X - PO.POX, 2) + POWER(Y - PO.POY, 2) < POWER(PO.AREARAD, 2) 
AND   EVENTARCHIVE.EVENTGROUP = 2

我得到一个像这样的数据集

vehicle | occred_on   | description
017     | .. 08:35:12 | stopped moving
021     | .. 09:40:00 | stopped moving
021     | .. 10:00:00 | started moving

可以看到，第21辆车的两个状态显示了它的停车时间。停赛时间长达20分钟。这是stop.mv和started.mv标志之间的最小跨度。

我想把它修改成

vehicle | stopped_moving | started_moving
021     | .. 09:40:00    | .. 10:00:00

我该怎么做?谢谢!

Answer 1

也许是这样的?

SQL> create table EVENTARCHIVE ( VEHID number, EVENTTIME date, EVENTDESCR  varchar2(20));
Table created.
SQL> REM vehice 17 has no started moving record.
SQL> insert into EVENTARCHIVE values(17, to_date('24/12/2012 08:35:12', 'dd/mm/yyyy hh24:mi:ss'), 'stopped moving');
1 row created.
SQL> REM vehice 21 has multiple stop / starts
SQL> insert into EVENTARCHIVE values(21, to_date('24/12/2012 09:40:00', 'dd/mm/yyyy hh24:mi:ss'), 'stopped moving');
1 row created.
SQL> insert into EVENTARCHIVE values(21, to_date('24/12/2012 10:00:00', 'dd/mm/yyyy hh24:mi:ss'), 'started moving');
1 row created.
SQL> insert into EVENTARCHIVE values(21, to_date('24/12/2012 10:22:00', 'dd/mm/yyyy hh24:mi:ss'), 'stopped moving');
1 row created.
SQL> insert into EVENTARCHIVE values(21, to_date('24/12/2012 12:00:00', 'dd/mm/yyyy hh24:mi:ss'), 'started moving');
1 row created.
SQL> REM vehice 25 has multiple stop / starts records start with a started moving record.
SQL> insert into EVENTARCHIVE values(25, to_date('23/12/2012 16:35:00', 'dd/mm/yyyy hh24:mi:ss'), 'started moving');
1 row created.
SQL> insert into EVENTARCHIVE values(25, to_date('23/12/2012 22:59:00', 'dd/mm/yyyy hh24:mi:ss'), 'stopped moving');
1 row created.
SQL> insert into EVENTARCHIVE values(25, to_date('24/12/2012 07:00:00', 'dd/mm/yyyy hh24:mi:ss'), 'started moving');
1 row created.
SQL> REM vehice 33 has some duplicate stopped data (possible?)
SQL> insert into EVENTARCHIVE values(33, to_date('23/12/2012 16:35:00', 'dd/mm/yyyy hh24:mi:ss'), 'started moving');
1 row created.
SQL> insert into EVENTARCHIVE values(33, to_date('23/12/2012 22:59:00', 'dd/mm/yyyy hh24:mi:ss'), 'stopped moving');
1 row created.
SQL> insert into EVENTARCHIVE values(33, to_date('23/12/2012 23:02:00', 'dd/mm/yyyy hh24:mi:ss'), 'stopped moving');
1 row created.
SQL> insert into EVENTARCHIVE values(33, to_date('24/12/2012 07:00:00', 'dd/mm/yyyy hh24:mi:ss'), 'started moving');
1 row created.
SQL> commit;
Commit complete.
SQL> select vehid, eventtime stopped_moving, next_time started_moving
  2    from (select vehid, eventtime, eventdescr,
  3         case
  4           when lead(eventdescr) over (partition by vehid order by eventtime) != eventdescr
  5           then
  6             lead(eventtime)  over (partition by vehid order by eventtime)
  7         end next_time
  8  from EVENTARCHIVE)
  9   where eventdescr = 'stopped moving'
 10     and next_time is not null;
     VEHID STOPPED_MOVING       STARTED_MOVING
---------- -------------------- --------------------
        21 24-dec-2012 09:40:00 24-dec-2012 10:00:00
        21 24-dec-2012 10:22:00 24-dec-2012 12:00:00
        25 23-dec-2012 22:59:00 24-dec-2012 07:00:00
        33 23-dec-2012 23:02:00 24-dec-2012 07:00:00

Answer 2

你可以用子查询来做，也许是这样的工作:

SELECT 
    e1.VEHID vehicle, 
    to_char(e1.EVENTTIME, 'Dy DD-Mon-YYYY  HH24:MI:SS') stopped, 
    to_char(e2.EVENTTIME, 'Dy DD-Mon-YYYY  HH24:MI:SS') started 
FROM 
    EVENTARCHIVE e1, 
    EVENTARCHIVE e2 
WHERE 
    e1.EVENTDESCR = 'stopped moving' 
    AND e2.ID IN (
        SELECT ID 
        FROM EVENTARCHIVE 
        WHERE 
            EVENTTIME<e1.EVENTTIME 
            AND EVENTDESCR = 'started moving' 
            AND VEHID = e1.VEHID 
        ORDER BY e1.EVENTTIME DESC LIMIT 1
    )

(我假设有一个列ID。)

Answer 3

你可以这样做，

select min(occred_on) stopped_moving,max(occred_on) started_moving
from table group by vehicle;

希望这对你有帮助，

SELECT EVENTARCHIVE.VEHID vehicle
      , min(to_char(EVENTARCHIVE.EVENTTIME, 'Dy DD-Mon-YYYY  HH24:MI:SS')) stopped_moving,
        max(to_char(EVENTARCHIVE.EVENTTIME, 'Dy DD-Mon-YYYY  HH24:MI:SS')) started_moving,
      , EVENTARCHIVE.EVENTDESCR description
FROM EVENTARCHIVE
     , (SELECT POX
               , POY
               , AREARAD 
        FROM POINTOBJECTS
        WHERE POID = 'PB') PO
WHERE POWER(X - PO.POX, 2) + POWER(Y - PO.POY, 2) < POWER(PO.AREARAD, 2) 
AND   EVENTARCHIVE.EVENTGROUP = 2 group by vehicle