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

[fasiuddin mohammed]

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.

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
>