Расчет среднего значения различных инвестиционных раундов для разных компаний в PostgreSQL

У меня есть таблица, содержащая идентификатор компании, название инвестиционного раунда (например, раунд A, B, C или IPO ......) и дату (например, 2001-05-07) каждого инвестиционного раунда для каждой компании. Я хочу рассчитать средние гэпы для разных инвестиционных раундов для всех компаний. Например, сколько времени в среднем требуется всем компаниям от A до B？ Сколько времени в среднем требуется всем компаниям от B до C？ Сколько времени в среднем требуется всем компаниям от C до D？ Таблица выглядит следующим образом:

|company_id| |invest_rounds_type_name| |invest_date|
---------------------------------------------------
1             A                         2001-01-01
---------------------------------------------------
1             B                         2001-12-05
---------------------------------------------------
1             C                         2003-11-12
---------------------------------------------------
2             A                         1963-03-01
---------------------------------------------------
2             B                         1967-10-10
---------------------------------------------------
2             C                         1970-10-12
---------------------------------------------------
2             D                         1971-01-05
---------------------------------------------------
3             B                         2017-11-20
---------------------------------------------------
3             A                         2017-11-16
---------------------------------------------------
3             C                         2018-03-19
---------------------------------------------------

Спасибо за любую помощь!


postgresql sql group-by average date-difference
person Xiao Ma    schedule 13.01.2021    source источник
comment
Отметьте базу данных, которую вы используете.   -  person Popeye    schedule 13.01.2021

Ответы (2)


arrow_upward
1
arrow_downward

пошаговая демонстрация:db‹›fiddle

SELECT
    invest_round as invest_round_start,
    invest_round_end,
    AVG(days_required)
FROM (
    SELECT
        *,
        lead(invest_round) OVER w as invest_round_end,          
        lead(invest_date) OVER w - invest_date as days_required
    FROM mytable
    WINDOW w AS (PARTITION BY company_id ORDER BY invest_round)
) s
WHERE invest_round_end IS NOT NULL
GROUP BY invest_round, invest_round_end
ORDER BY invest_round

Используя lead() оконную функцию, вы можете скопировать следующее значение конкретный столбец на ваш текущий. Таким образом, вы можете получить следующие invest_round к текущим записям, а также следующие invest_date.

Со следующей датой и текущей датой вы можете рассчитать продолжительность между обоими invest_rounds.

Теперь вам просто нужно сгруппировать по the invest_rounds и вычислить совокупность AVG.

person S-Man    schedule 13.01.2021
comment
Удивительно. Моя попытка так наивна! - person mhawke; 13.01.2021
comment
@mhawke . . . Я бы с осторожностью относился к использованию order by invest_round для этого. Я думаю, вы хотите order by invest_date. - person Gordon Linoff; 13.01.2021
comment
@GordonLinoff Нет, не знаю. Инвест_дата не имеет отношения к вопросу ТО. Это необходимо только для расчета длительности. После этого я хочу заказать invest_rounds, так как это то, о чем просит ТО. Кажется правильным упорядочить invest_round A перед B и C. - person S-Man; 13.01.2021
comment
@S-Man: Я думаю, что @GordonLinoff обеспокоен тем, что имена инвестиционных раундов могут не всегда сортироваться так, как вы предполагаете. OP указывает, что IPO является допустимым именем раунда. Это может повлиять на ваш запрос, если случится раунд с именем J, который будет сортироваться после IPO, если такой раунд существует. Что, если есть посевной раунд под названием SEED? Упорядочивание по дате позволит избежать этой проблемы, так как справедливо предположить, что раунды происходят в хронологическом порядке, а не в порядке имен. - person mhawke; 14.01.2021

arrow_upward
0
arrow_downward

Пример между этапами A и B:

-- table is named 'x'
select avg(diff) from (
    select xb.invest_date - xa.invest_date as diff
    from x xa join x xb on (xa.company_id = xb.company_id)
    where xa.invest_rounds_type_name = 'A' and
          xb.invest_rounds_type_name = 'B'
) as gaps;

При работе с вашими данными это приводит к (дням):

в среднем 675,3333333333334

sqlfiddle: http://sqlfiddle.com/#!17/3559c/23

person mhawke    schedule 13.01.2021