Complex calculation of averages

Discussion in 'Perl Misc' started by Bart Van der Donck, Feb 19, 2007.

  1. Hello perl gurus,

    I have the following problem.

    $x{'63'} = "2006-05-17|2006-11-25|2006-12-04";
    $x{'67'} = "2005-04-30|2005-09-21|2006-07-17|2007-02-10";
    $x{'71'} = "2006-04-23|2006-10-05|2006-12-27|2007-01-21";
    etc.

    How can I calculate the average number of days between each date for
    %x for the last 1,2,3,4,5,6,12 months ?

    use Date::Calc(Delta_Days);
    print Delta_Days(2006,12,27,2007,1,21); # says '25'

    The dates in each value are sorted and in YYYY-MM-DD format.

    %x might be thought of as an array, because the hash values are unique
    and non-floating numbers.


    ------------------------
    Example 1
    ------------------------

    Today is 2007-02-19. 67 and 71 can be taken to calculate the average
    of the last month (=2007-01-19 to 2007-02-19) because 63 holds no data
    for it.

    Average of 208 (nr of days from 2006-07-17 to 2007-02-10 in $x{'67'})
    and 328 (nr of days from 2006-02-27 to 2007-01-21 in $x{'71'}) becomes
    268.

    Result of example 1 is 268.


    ------------------------
    Example 2
    ------------------------

    Today is 2007-02-19. All three entries can be taken to calculate the
    average of last 5 months (=2006-09-19 to 2007-02-19).

    For $x{'63'} goes:
    192 (2006-05-17 to 2006-11-25) and 9 (2006-11-25 to 2006-04-12), thus
    average 100.5.

    For $x{'67'} goes:
    208 (2006-07-17 to 2007-02-10), thus average 208.

    For $x{'71'} goes:
    165 (2006-04-23 to 2006-10-05) and 83 (2006-10-05 to 2006-12-27) and
    25 (2006-12-27 to 2007-01-21), thus average 91.

    The total average is (100.5 + 208 + 91 ) / 3.

    Result of example 2 is 103.67.

    Thanks a lot,

    --
    Bart
    Bart Van der Donck, Feb 19, 2007
    #1
    1. Advertising

  2. wrote:

    > [...]
    >
    > INSERT INTO "elapsed" VALUES (1, 63, '2006-05-17', '2006-11-25');
    > INSERT INTO "elapsed" VALUES (2, 63, '2006-11-25', '2006-12-04');
    > INSERT INTO "elapsed" VALUES (3, 67, '2005-04-30', '2005-09-21');
    > INSERT INTO "elapsed" VALUES (4, 67, '2005-09-21', '2006-07-17');
    > INSERT INTO "elapsed" VALUES (5, 67, '2006-07-17', '2007-02-10');
    > INSERT INTO "elapsed" VALUES (6, 71, '2006-04-23', '2006-10-05');
    > INSERT INTO "elapsed" VALUES (7, 71, '2006-10-05', '2006-12-27');
    > INSERT INTO "elapsed" VALUES (8, 71, '2006-12-27', '2007-01-21');
    >
    > END TRANSACTION;
    >
    > SELECT category, AVG( JULIANDAY(end_date) - JULIANDAY(start_date))
    > FROM elapsed
    > WHERE end_date > DATE('now', '-1 month')
    > GROUP BY category;
    >
    > SELECT category, AVG( JULIANDAY(end_date) - JULIANDAY(start_date))
    > FROM elapsed
    > WHERE end_date > DATE('now', '-5 month')
    > GROUP BY category;


    Brilliant, it works excellent! Ported to MySQL's dialect:

    SELECT
    category,
    AVG( TO_DAYS(end_date) - TO_DAYS(start_date) )
    FROM elapsed
    WHERE end_date > DATE_ADD(CURDATE(), INTERVAL -5 MONTH)
    GROUP BY category

    (P.S. Yes the last interval of $x{'17'} was 2006-12-17 to 2007-01-21,
    it was a typing mistake)

    Thanks again,

    --
    Bart
    Bart Van der Donck, Feb 21, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    324
    Gianni Mariani
    Feb 11, 2006
  2. Replies:
    3
    Views:
    261
  3. Bill Cunningham

    moving averages

    Bill Cunningham, Dec 28, 2008, in forum: C Programming
    Replies:
    0
    Views:
    269
    Bill Cunningham
    Dec 28, 2008
  4. Bill Cunningham

    averages

    Bill Cunningham, Jun 27, 2009, in forum: C Programming
    Replies:
    48
    Views:
    1,476
    Richard Bos
    Jul 14, 2009
  5. Bill Cunningham

    averages 2

    Bill Cunningham, Jun 29, 2009, in forum: C Programming
    Replies:
    24
    Views:
    721
    luserXtrog
    Jun 30, 2009
Loading...

Share This Page