Чтобы установить сезонное влияние на потребление энергии, мне необходимо согласовать имеющуюся у меня информацию об использовании энергии из базы данных выставления счетов с месячными температурами.

Я работаю с набором данных для выставления счетов, который имеет счета разной длины, даты начала и окончания, и я хотел бы получить среднемесячное значение для каждой учетной записи в течение каждого месяца. Например, у меня есть платежная база данных, которая имеет следующие характеристики:

   acct amount      begin        end days
1  2242  11349 2009-10-06 2009-11-04   29
2  2242  12252 2009-11-04 2009-12-04   30
3  2242  21774 2009-12-04 2010-01-08   35
4  2242  18293 2010-01-08 2010-02-05   28
5  2243  27217 2009-10-06 2009-11-04   29
6  2243    117 2009-11-04 2009-12-04   30
7  2243  14543 2009-12-04 2010-01-08   35

Я хотел бы выяснить, как заставить эти несколько нерегулярные временные ряды (для каждого аккаунта) получать среднюю сумму в день в течение каждого месяца, охватываемого в каждом счете, таким образом, чтобы:

   acct amount      begin        end days avgamtpday
1  2242  11349 2009-10-01 2009-10-31   31          X
2  2242  12252 2009-11-01 2009-11-30   30          X
3  2242  21774 2009-12-01 2010-12-31   31          X
4  2242  18293 2010-01-01 2010-01-31   31          X
4  2242  18293 2010-02-01 2010-02-28   28          X
5  2243  27217 2009-10-01 2009-10-31   31          X
6  2243    117 2009-11-01 2009-11-30   30          X
7  2243  14543 2009-12-01 2009-12-31   30          X
7  2243  14543 2010-01-01 2010-01-31   31          X

Я довольно независим от того, какой инструмент может это сделать, поскольку я должен сделать это только один раз.

Дополнительным недостатком является то, что таблица имеет длину около 150 000 строк, что на самом деле не так уж велико по большинству стандартов, но достаточно велико, чтобы затруднить решение с использованием петель в R. Я исследовал использование пакетов zoo, xts и tempdisagg в R. Я начал писать действительно уродливый цикл, который бы разделял каждый счет, затем создавал одну строку для каждого месяца в существующем счете, а затем tapply () для суммирования по действиям. и месяцы, но, честно говоря, не могли понять, как это сделать эффективно.

В MySQL я пробовал это:

create or replace view v3 as select 1 n union all select 1 union all select 1;
create or replace view v as select 1 n from v3 a, v3 b union all select 1;
set @n = 0;
drop table if exists calendar; create table calendar(dt date primary key);
insert into calendar
select cast('2008-1-1' + interval @n:=@n+1 day as date) as dt from v a, v b, v c, v d, v e, v;

select acct, amount, begin, end, billAmtPerDay, sum(billAmtPerDay), MonthAmt, count() Days, sum(billAmtPerDay)/count() AverageAmtPerDay, year(dt), month(dt) FROM ( select *, amount/days billAmtPerDay from bills b inner join calendar c on dt between begin and end and begin <> dt) x group by acct, amount, begin, end, billAmtPerDay, year(dt), month(dt);

Но по причинам, которые я не понимаю, моему серверу не нравится эта таблица, и он зацикливается на внутреннем соединении, даже когда я выполняю различные вычисления. Я исследую, существуют ли какие-либо временные ограничения памяти.

Спасибо!