MySQL совокупная группа продуктов по

Question

Я работал с набором данных WRDS/CRSP (база данных цен на акции, поддерживаемая UPenn для научных исследований). Я загружаю данные в Python и вставляю их в свою локальную базу данных MySQL.

Данные выглядят следующим образом и имеют первичный ключ (quote_date, security_id):

quote_date  security_id tr              accum_index
10-Jan-86   10002       null            1000
13-Jan-86   10002       -0.026595745    973.4042548
14-Jan-86   10002       0.005464481     978.7234036
15-Jan-86   10002       -0.016304348    962.7659569
16-Jan-86   10002       0               962.7659569
17-Jan-86   10002       0               962.7659569
20-Jan-86   10002       0               962.7659569
21-Jan-86   10002       0.005524862     968.0851061
22-Jan-86   10002       -0.005494506    962.765957
23-Jan-86   10002       0               962.765957
24-Jan-86   10002       -0.005524862    957.4468078
27-Jan-86   10002       0.005555556     962.7659569
28-Jan-86   10002       0               962.7659569
29-Jan-86   10002       0               962.7659569
30-Jan-86   10002       0               962.7659569
31-Jan-86   10002       0.027624309     989.3617013
3-Feb-86    10002       0.016129032     1005.319148
4-Feb-86    10002       0.042328041     1047.872338
5-Feb-86    10002       0.04568528      1095.744679

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

accum_index_t = accum_index_{t-1} * (1 + tr_t)

В таблице 80 метров строк. Я написал некоторый код для итерации каждого security_id и вычисления кумулятивного продукта, например, так:

select @sid := min(security_id)
from stock_prices;

create temporary table prices (
    quote_date datetime,
    security_id int,
    tr double null,
    accum_index double null,
    PRIMARY KEY (quote_date, security_id)
);

while @sid is not null
do

    select 'security_id', @sid;
    select @accum := null;

    insert into prices
    select quote_date, security_id, tr, accum_index
    from stock_prices
    where security_id = @sid
    order by quote_date asc;

    update prices
    set accum_index = (@accum := ifnull(@accum * (1 + tr), 1000.0));

    update stock_prices p use index(PRIMARY), prices a use index(PRIMARY)
    set p.accum_index = a.accum_index
    where p.security_id = a.security_id
    and p.quote_date = a.quote_date;

    select @sid := min(security_id)
    from stock_prices
    where security_id > @sid;

    delete from prices;

end while;

drop table prices;

Но это слишком медленно, на моем ноутбуке уходит около минуты, и для расчета этой серии уйдут годы. Есть ли способ векторизовать это?

Ура, Стив

Answer 1

Если вы используете MySQL 8, вы можете использовать оконные функции для создания накопительного продукта. К сожалению, нет PROD() Функция агрегата / окна в любой базе данных SQL, о которой я знаю, но вы можете эмулировать ее, используя EXP(SUM(LOG(factor))):

SELECT
  quote_date,
  security_id,
  tr,
  EXP(SUM(LOG(1000 * (1 + COALESCE(tr, 1)))) 
    OVER (PARTITION BY security_id ORDER BY quote_date))
    AS accum_index
FROM stock_prices

Answer 2

Если вы используете MySQL 5, вы можете эмулировать эту функцию, умножая текущее значение на последний tr построчно. После этого берем накопленное значение последней строки.

tr - это процентное значение, прямо сейчас? Итак, давайте добавим 1 к каждому tr.

Первое сохраненное значение будет нейтральным 1.

Попробуй это:

      SET @variation = 1;
SET @row_number = 0;

SELECT accumulateTr
FROM
    (SELECT
        @row_number := (@row_number + 1) AS rowNumber,
        @variation := (1 + variation) * @variation AS accumulateTr
     FROM
        prices) accumulatedTrs
ORDER BY rowNumber DESC
LIMIT 1;