Hi All,

I have a very simple function. This function calculates the minute wise aggregates based upon the number of people visited a particular website every minute.

I am looking to optimize this code. Currently based upon the start_date and end_date, the function inserts one row for every minute passed into the aggregate table.

Now start_date can be the start of a day for e.g. 2009-04-15 00:00:00 and end_date can be 2009-04-16 00:00:00 and if it is so the insert statement within the function will run 24 * 60 = 1440 times.

I am just wondering if this logic can be written in such a way so that it should fire just one single insert statement may by using any Windows/OLAP function or anything else.

Please let me know if this is possible.

Here is the function code:-

----------------------------------------
declare
start_date timestamp;
end_date timestamp;
current_start_date timestamp;
current_end_date timestamp;
rec1 RECORD;
begin

SELECT min(access_date), max(exit_date) INTO rec1 FROM web_content;

start_date := date_trunc('MINUTE', rec1.min);
end_date := date_trunc('MINUTE', rec1.max);

current_start_date := start_date;
current_end_date := start_date + interval '1 minute';

while ( current_start_date <= end_date ) loop
INSERT INTO aggr_minute (visited_date, web_site, web_content, web_content_type, total)
SELECT current_start_date, web_site, web_content, web_content_type, count(*) FROM web_content WHERE
( access_date < current_end_date AND exit_date > current_start_date )
GROUP BY web_site, web_content, web_content_type;

current_start_date = current_start_date + interval '1 minute';
current_end_date = current_end_date + interval '1 minute';

end loop;

end;

-------------------------------------------------------------

Any input on this is highly appreciated.

Regards,
Nitin

I would suggest that you table you are using look like this:

create table web_visit (
visitor_number bigint not null generate by default as identity,
start_time timestamp not null,
end_time timestamp)

Make visitor_number a PK. Then when a visitor connects to your web site you insert a new row and retrieve the new PK value. Then when they leave, you update the end_time. So each visit only generates one insert and one update.

Andy