Re: Analytic Problem

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 14 May 2008 21:45:04 +0200
Message-ID: <482B4140.5050701@gmail.com>

Urs Metzger schrieb:

> jimmyb schrieb:

>> Could someone provide insight on this request?
>>
>> create table scott
>> (
>> mydate date,
>> vehicle number,
>> route number,
>> tstamp date
>> )
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
>> APR-08 00:59:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
>> APR-08 01:10:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
>> APR-08 02:00:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,200, to_date('10-
>> APR-08 02:10:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,200, to_date('10-
>> APR-08 02:20:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,200, to_date('10-
>> APR-08 02:20:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
>> APR-08 02:40:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> insert into scott
>> values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
>> APR-08 03:00:00','DD-MON-YYHH24:MI:SS')
>> /
>>
>> commit
>> /
>>
>> -- return records and
>> --convert tstamp to number of minutes past midnight
>> select mydate,vehicle,route, TRUNC(tstamp,'MI') from scott ;
>>
>> 10-APR-08, 5182, 100, 59
>> 10-APR-08, 5182, 100, 70
>> 10-APR-08, 5182, 100, 120
>> 10-APR-08, 5182, 200, 130
>> 10-APR-08, 5182, 200, 140
>> 10-APR-08, 5182, 100, 160
>> 10-APR-08, 5182, 100, 180
>>
>> Requirements:
>> return group records by mydate,vehicle and route
>> convert tstamp to minutes past midnight and return MIN(tstamp) and
>> MAX(tstamp)
>> results are based on the order records were entered => tstamp
>>
>> So returned results would be:
>> MYDATE VEHICLE ROUTE MIN MAX
>> 10-APR-08 5182 100 59 120
>> 10-APR-08 5182 200 130 140
>> 10-APR-08 5182 100 160 180
>>

> this is what meets your requirements, but maybe not what you
> expected:
> 
> SQL> select mydate, vehicle, route,
>   2         min((tstamp - trunc(tstamp)) * 24 * 60) as "MIN",
>   3         max((tstamp - trunc(tstamp)) * 24 * 60) AS "MAX"
>   4  from scott
>   5  group by mydate, vehicle, route;
> 
> MYDATE      VEHICLE      ROUTE        MIN        MAX
> -------- ---------- ---------- ---------- ----------
> 10.04.08       5182        100         59        180
> 10.04.08       5182        200        130        140
> 
> I don't see why you get three rows in your sample result set.
> Is there a hidden row-number-like thing?
> 
> hth anyway,
> Urs Metzger

As i understand, the row-number-like thing is  >> results are based on the order records were entered => tstamp

So, based on your example something like this could work: SQL> select max(mydate) mydate,

   2         max(vehicle) vehicle,
   3         max(route) route,
   4         min((tstamp - trunc(tstamp)) * 24 * 60) as "MIN",
   5         max((tstamp - trunc(tstamp)) * 24 * 60) as "MAX"
   6    from (select mydate,
   7                 vehicle,
   8                 route,
   9                 tstamp,
  10                 sum(start_of_group)
  11                 over(partition by mydate, vehicle order by tstamp) 
group_no
  12            from (select t.*,
  13                         decode(lag(route) over(partition by mydate,
  14                                     vehicle order by tstamp),
  15                                route,
  16                                0,
  17                                1) start_of_group
  18                    from scott t))

  19 group by group_no
  20 order by group_no
  21 ;

MYDATE                 VEHICLE      ROUTE        MIN        MAX
------------------- ---------- ---------- ---------- ----------
10.04.2008 00:00:00       5182        100         59        120
10.04.2008 00:00:00       5182        200        130        140
10.04.2008 00:00:00       5182        100        160        180

Best regards

Maxim Received on Wed May 14 2008 - 14:45:04 CDT