[clue-tech] hairy sql (postgres) - suggestions welcome

[Dave Price]

On 11/9/06, fasiuddin mohammed <fasi.abbu at gmail.com> wrote:
> Hey Dave,
>           Was just going through your query...First of all i did not
> understand why you gave the same condition twice while creating
> dap_test table. Anyways dap_test,dap_in and dap_out can be merged into
> an single table

The condition is ALMOST the same on the initial select, but we look at
both cdr.channel and cdr.dstchannel - these are the sip devices
(channels) that either initiate or terminate the call - we test for
them later for inbound (dstchannel) or outbound (channel) calls.

You are probably right about the UNION join, and it may speed up the query...

Right now, the query runs in .5 seconds for 1 month over 100,000
records returning 250 records for a single device on an amd64, but
takes almost 1.5 minutes against the live database on a 3ghz Xeon
which is receiving constant INSERTS from a farm of asterisk boxes.

I had problems doing a nested query because I am not grouping by
(call) duration - only the date-cast timestamp -- calldate::date ... I
want calls by date, regardless of the time portion of the timestamp.

An enhanced version of the query may actually do the duration
time-slices based on call direction.

We are looking at slony replication so that we can query an offline
copy, but that is not
in production yet

>
> Is there a union in postgresql.. If it is there then all the later
> tables can be merged into a single one using union (since you put an
> outer join later we can even use all) statement. Will try to send you
> a working query but it will take some time...
>
> Thanks
> fasi
>
> On 11/9/06, Dave Price <kinaole at gmail.com> wrote:
> > I am trying to analyze call records from an asterisk box and have an
> > almost working query that is pretty ugly - I wonder if anyone has any
> > suggestions...
> >
> > I am grabbing a group  of  relevent rows into a temp table for further
> > analysis that looks like this:
> >
> >    Column   |           Type           | Modifiers
> > ------------+--------------------------+-----------
> >  calldate   | timestamp with time zone |
> >  src        | character varying(80)    |
> >  dst        | character varying(80)    |
> >  channel    | character varying(80)    |
> >  dstchannel | character varying(80)    |
> >  duration   | integer                  |
> >
> > ... here is the final query (without vraible parameters) that gives
> > totals by call direction (in or out) and counts by duration for a
> > range of durations ...
> >
> > Any alternate strategies are welcome ... I am no SQL expert
> >
> > ..........................................
> >    Begin;
> >    create TEMP TABLE dap_test as
> >    SELECT calldate, src, dst,  channel, dstchannel, duration
> >
> >       from CDR WHERE
> >
> >       accountcode LIKE '%rockynet%' AND
> >       channel LIKE '%869%' AND
> >       created_datetime BETWEEN '2006-10-01' and '2006-11-01'
> >          OR
> >       accountcode LIKE '%rockynet%' AND
> >       dstchannel LIKE '%869%' AND
> >       created_datetime BETWEEN '2006-10-01' and '2006-11-01'
> >
> >       order by uniqueid;
> >
> > /* accountcode, channel(s) and date(s) should be parameters */
> >
> >       create TEMP table dap_in as
> >          select   calldate::date, count(duration) as total ,
> >                   sum(duration) as total_time
> >          from dap_test
> >
> >          where dstchannel like '%869%'  /* this should be a parameter */
> >          group by calldate::date
> >          order by calldate::date ;
> >
> >       create TEMP table dap_out as
> >          select   calldate::date, count(duration) as total ,
> >                   sum(duration) as total_time
> >          from dap_test
> >
> >          where    channel like '%869%'   /* this should be a parameter */
> >          group by calldate::date
> >          order by calldate::date ;
> >
> >       create TEMP table dap_less_than_10 as
> >          select   calldate::date,
> >                   count(calldate::date) as less_than_10
> >          from dap_test
> >
> >          where duration < 10
> >          group by calldate::date
> >          order by calldate::date;
> >
> >
> >       create TEMP table dap_ten_to_thirty as
> >          select   calldate::date,
> >                   count(calldate::date) as ten_to_thirty
> >          from dap_test
> >
> >          where duration >= 10 and duration < 30
> >          group by calldate::date
> >          order by calldate::date;
> >
> >
> >       create TEMP table dap_thirty_to_sixty as
> >          select   calldate::date,
> >                   count(calldate::date) as thirty_to_sixty
> >          from dap_test
> >
> >          where duration >= 30 and duration <60
> >          group by calldate::date
> >          order by calldate::date;
> >
> >
> >       create TEMP table dap_sixty_to_onetwenty as
> >          select   calldate::date,
> >                   count(calldate::date) as sixty_to_onetwenty
> >          from dap_test
> >
> >          where duration >=60 and duration < 120
> >          group by calldate::date
> >          order by calldate::date;
> >
> >
> >       create TEMP table dap_more_than_120 as
> >          select   calldate::date,
> >                   count(calldate::date) as more_than_120
> >          from dap_test
> >
> >          where duration >= 120
> >          group by calldate::date
> >          order by calldate::date;
> >
> > /*     create table dap_report as */
> >      select
> >          t.* ,
> >          i.total as in_calls,
> >          i.total_time as in_time,
> >
> >          o.total as out_calls,
> >          o.total_time as out_time,
> >
> >          l.less_than_10,
> >
> >          ten.ten_to_thirty,
> >
> >          thirty.thirty_to_sixty,
> >
> >          sixty.sixty_to_onetwenty,
> >
> >          bigger.more_than_120
> >
> >          from dap_all t
> >             left outer join
> >                dap_in i on t.calldate=i.calldate
> >                   left outer join
> >                      dap_out o  on  i.calldate=o.calldate
> >                         left outer join
> >                            dap_less_than_10 l on i.calldate=l.calldate
> >                               left outer join
> >                                  dap_ten_to_thirty ten on
> > i.calldate=ten.calldate
> >                                     left outer join
> >                                        dap_thirty_to_sixty thirty on
> > i.calldate=thirty.calldate
> >                                           left outer join
> >                                              dap_sixty_to_onetwenty
> > sixty on i.calldate=sixty.calldate
> >                                                 left outer join
> >                                                    dap_more_than_120
> > bigger on i.calldate=bigger.calldate
> >          ;
> > /*     copy dap_report to '/tmp/dap_report.csv' CSV; */
> >
> >      rollback;
> > ....................................
> >
> > --
> > aloha,
> > dave
> > _______________________________________________
> > clue-tech mailing list
> > clue-tech at cluedenver.org
> > http://www.cluedenver.org/mailman/listinfo/clue-tech
> >
> _______________________________________________
> clue-tech mailing list
> clue-tech at cluedenver.org
> http://www.cluedenver.org/mailman/listinfo/clue-tech
>


-- 
aloha,
dave