Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Database Function

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •