[clue-tech] hairy sql (postgres) - suggestions welcome
Dave Gray
yargevad at gmail.com
Mon Nov 13 21:47:22 MST 2006
Is this still an open issue? Maybe the following will help. I left the
other origin/duration groups as an exercise for the reader:
-- date_trunc might be faster than casting to date, not sure
-- disclaimer: mostly untested, obviously, since I have no access to
your environment
SELECT -- day and day overall totals
date_trunc('day', calldate), count(1) as day_total,
sum(duration) as day_total_time,
-- inbound totals
sum(case when dstchannel like '%' || $2 || '%' then 1 else 0
end) in_total,
sum(case when dstchannel like '%' || $2 || '%' then duration
else 0 end) in_total_time,
-- outbound totals
sum(case when channel like '%' || $2 || '%' then 1 else 0 end) out_total,
sum(case when channel like '%' || $2 || '%' then duration else
0 end) out_total_time,
-- inbound less than ten
sum(case when duration < 10 and dstchannel like '%' || $2 ||
'%' then 1 else 0 end) in_lt_10_total,
sum(case when duration < 10 and dstchannel like '%' || $2 ||
'%' then duration else 0 end) in_lt_10_total_time,
-- outbound less than ten
sum(case when duration < 10 and channel like '%' || $2 || '%'
then 1 else 0 end) out_lt_10_total,
sum(case when duration < 10 and channel like '%' || $2 || '%'
then duration else 0 end) out_lt_10_total_time,
-- duration groups: <10, >=10;<30, >=30;<60, >=60;<120, >120
FROM CDR c
WHERE accountcode like '%' || $1 || '%'
AND (channel like '%' || $2 || '%' OR
dstchannel like '%' || $2 || '%')
AND created_datetime between
$3::date and $4::date
GROUP BY date_trunc('day', calldate)
Hope that helps,
Dave
On 11/10/06, Dave Price <kinaole at gmail.com> wrote:
> 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
> _______________________________________________
> clue-tech mailing list
> clue-tech at cluedenver.org
> http://www.cluedenver.org/mailman/listinfo/clue-tech
>
More information about the clue-tech
mailing list