时间戳

首页 > 解决方案 > 在 PostgreSQL 中计算不同公司不同投资轮次的平均值

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

问题描述

我有一个表格，其中包含 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
---------------------------------------------------

谢谢你的帮助！

标签： sqlpostgresqlgroup-byaveragedate-difference

解决方案

分步演示：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总和。

推荐阅读