Complex calculation of averages

[Bart Van der Donck]

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 Van der Donck]

[...]

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,