Prev: how to browse all sys_context(...) values?
Next: Can OLAP cubes be used, when Application always queries for single
From: Sashi on 7 Apr 2010 14:58
Hi all, I have a table with details of phone calls.
The call date is given as DD-MMM-YYYY HH:MI:SS. I have a couple of
other fields that give the duration as DUR_MM and DUR_SS.
So an example record would be
SRC_NUM, DEST_NUM, CALL_DATE, DUR_MM, DUR_SS
444-1212, 555-2323, 04-APR-2010, 05, 38.

So this would be a call placed from 444-1212 to 555-2323 on Apr 04th,
that lasted 5 min and 38 sec. (Other fields not mentioned for brevity
and clarity).

On a given day, I want to be able to find out the minute at which
there are a maximum number of concurrent calls.

I've quickly come to the conclusion that I can't do this via simple
SQL. I'm new to writing procedures (though I have good experience with
programming languages in general).

Any tips/pseudo-code that give me an idea of how to proceed are
appreciated.

Thanks,
Sashi
From: joel garry on 7 Apr 2010 16:45
On Apr 7, 11:58 am, Sashi <small...(a)gmail.com> wrote:
> Hi all, I have a table with details of phone calls.
> The call date is given as DD-MMM-YYYY HH:MI:SS. I have a couple of
> other fields that give the duration as DUR_MM and DUR_SS.
> So an example record would be
> SRC_NUM, DEST_NUM, CALL_DATE, DUR_MM, DUR_SS
> 444-1212, 555-2323, 04-APR-2010, 05, 38.
>
> So this would be a call placed from 444-1212 to 555-2323 on Apr 04th,
> that lasted 5 min and 38 sec. (Other fields not mentioned for brevity
> and clarity).
>
> On a given day, I want to be able to find out the minute at which
> there are a maximum number of concurrent calls.
>
> I've quickly come to the conclusion that I can't do this via simple
> SQL. I'm new to writing procedures (though I have good experience with
> programming languages in general).
>
> Any tips/pseudo-code that give me an idea of how to proceed are
> appreciated.
>
> Thanks,
> Sashi

You may be able to do this with analytics. I'm no analytics expert,
but see http://forums.oracle.com/forums/thread.jspa?threadID=1030207&tstart=105
for one similar example, and Charles Hooper explains things well with
examples: http://hoopercharles.wordpress.com/2009/12/08/sql-combining-over-lapping-date-rows/
.. In general it is better to keep things in the SQL engine when
possible, though (I speculate) there may be cases where analytics
won't be the best way. Also, I recall there is an undocumented
analytic that can do something like this, stay away from undocumented
things and code that is, shall we say, too cute.

The above were found in a few seconds with the following google term:
oracle analytics maximum overlapping records

Tom Kyte also is a big advocate of analytics, see asktom.oracle.com

In general in this group, if you want detailed help with a coding
issue, it's good to supply create table and load data statements, and
what you've tried. Also necessary is exact versions (10gR2 is not a
version, 10.2.0.4 is a version) for Oracle and your OS/platform. Hand
people a ball and they love to run with it (or whatever your local
equivalent metaphor would be).

jg
--
@home.com is bogus.
http://www.chulavistaca.gov/City_Services/Community_Services/Nature_Center/webcams/eaglemesacam.asp
From: ddf on 7 Apr 2010 23:41
On Apr 7, 2:58 pm, Sashi <small...(a)gmail.com> wrote:
> Hi all, I have a table with details of phone calls.
> The call date is given as DD-MMM-YYYY HH:MI:SS. I have a couple of
> other fields that give the duration as DUR_MM and DUR_SS.
> So an example record would be
> SRC_NUM, DEST_NUM, CALL_DATE, DUR_MM, DUR_SS
> 444-1212, 555-2323, 04-APR-2010, 05, 38.
>
> So this would be a call placed from 444-1212 to 555-2323 on Apr 04th,
> that lasted 5 min and 38 sec. (Other fields not mentioned for brevity
> and clarity).
>
> On a given day, I want to be able to find out the minute at which
> there are a maximum number of concurrent calls.
>
> I've quickly come to the conclusion that I can't do this via simple
> SQL. I'm new to writing procedures (though I have good experience with
> programming languages in general).
>
> Any tips/pseudo-code that give me an idea of how to proceed are
> appreciated.
>
> Thanks,
> Sashi

Is this what you had in mind:

SQL> create table call_test(incoming varchar2(12),
2 outgoing varchar2(12),
3 call_start date,
4 call_end date)
5 tablespace tools;

Table created.

SQL>
SQL> begin
2 for i in 1..9999 loop
3 insert into call_test
4 values('444-555-'||rpad(1,4,'0'),
5 '777-345-'||lpad(i,4,'0'),
6 sysdate+(mod(i,13)/86400),
7 sysdate+((97*i)/86400));
8
9 end loop;
10 commit;
11 end;
12 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Call volume per minute
SQL> --
SQL> select
2 to_char(call_start, 'DD-MON-RRRR HH24:MI') start_time,
3 count(*) call_vol
4 from
5 call_test
6 group by to_char(call_start, 'DD-MON-RRRR HH24:MI');

START_TIME CALL_VOL
----------------------- ----------
07-APR-2010 23:39 1113
07-APR-2010 23:40 8886

SQL>
SQL> --
SQL> -- Peak call vol
SQL> --
SQL> with max_calls as
2 (select max(call_vol) call_peak
3 from
4 (select
5 to_char(call_start, 'DD-MON-RRRR HH24:MI') start_time,
6 count(*) call_vol
7 from
8 call_test
9 group by to_char(call_start, 'DD-MON-RRRR HH24:MI'))
10 )
11 select start_time, call_vol
12 from
13 (select
14 to_char(call_start, 'DD-MON-RRRR HH24:MI') start_time,
15 count(*) call_vol
16 from
17 call_test
18 group by to_char(call_start, 'DD-MON-RRRR HH24:MI'))
19 where call_vol = (select call_peak from max_calls);

START_TIME CALL_VOL
----------------------- ----------
07-APR-2010 23:40 8886

SQL>


David Fitzjarrell
 | 
Pages: 1
Prev: how to browse all sys_context(...) values?
Next: Can OLAP cubes be used, when Application always queries for single