计算PostgreSQL中不同公司不同投资轮次的平均值

Question

我有一个表，其中包含每个公司的company_id、投资轮次名称(如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
---------------------------------------------------

谢谢你的帮助！

Answer 1

step-by-step demo: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() window function，您可以将特定列的下一个值复制到当前列。因此，您可以获得指向当前记录的以下invest_round以及以下invest_date。

通过以下日期和当前日期，您可以计算两个invest_round之间的持续时间。

现在，您只需按the invest_round分组并计算AVG聚合。

Answer 2

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