[clue-tech] hairy sql (postgres) - suggestions welcome
Dave Price
kinaole at gmail.com
Mon Nov 13 21:50:05 MST 2006
Thanks Dave
I will test this in the AM
aloha,
dave
On 11/13/06, Dave Gray <yargevad at gmail.com> wrote:
> 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
> >
> _______________________________________________
> clue-tech mailing list
> clue-tech at cluedenver.org
> http://www.cluedenver.org/mailman/listinfo/clue-tech
>
--
aloha,
dave
More information about the clue-tech
mailing list