postgresql - 按周累计 - postgresql

Question

使用 postgresql，版本 9.5.8

下面我有一个工作查询，它​​为我提供了所有帐户的准备帐户的百分比。然后将此表按周拆分，给出该周创建的帐户数量，这些帐户随后就绪。

查询如下:

SELECT 
               date_trunc('week', al.created_at) as week_created,
               count(case when ra.status='ready' then 1 end) as total_accounts,
               count(case when ra.status='ready' AND ra.tests > 0 then 1 end) as accounts_ready,
               concat(round(count(case when ra.status='ready' AND ra.tests > 0 then 1 end) :: decimal /count(case when ra.status='ready' then 1 end) :: decimal * 100.0), '%') as pct_accounts_ready

    FROM "ready_accounts" ra
    JOIN "accounts_list" al
    ON al.id=ra.id
    GROUP BY week_created
    ORDER BY week_created DESC;

结果集如下所示:

创建周 -------- 帐户总数 ---- 帐户就绪 ---- Pct 帐户就绪

Monday 14 Aug ----  50 ----------------39 ---------------- 78%
Monday 7 Aug  ----  20 ----------------10 ---------------- 20%

问题是，我得到的结果不是累积的，它们只是一周的结果，这对我想要实现的目标毫无意义。

我想要一个显示的结果集:

Monday 14 Aug ---  70 ------------------- 49 ---------------- 70%
Monday 7 Aug  ---  20 ------------------- 10 ---------------- 20%

示例输入数据:

示例数据如下所示:准备好的账户表:

ra.id   ra.status   ra.tests
123     ready       1
124     not_ready   2
125     not_ready   0
126     ready       1
127     ready       0
128     ready       0
129     ready       1

帐户列表:

al.id   al.created_at

123     Monday 14 August
124     Monday 7 August
125     Monday 14 August
126     Monday 14 August
127     Monday 7 August
128     Monday 14 August
129     Monday 31 July

我尝试了很多解决方案，但都卡住了。任何解决方案示例都会非常有帮助!

提前致谢。我对此很陌生，所以任何解释都会很有用!

Answer 1

在派生表中使用没有最后一列的查询(FROM 子句中的子查询)并使用 sum() 作为窗口函数。计算外包装查询中的百分比:

select 
    week_created,
    total_accounts,
    accounts_ready,
    concat((accounts_ready/ total_accounts* 100)::int, '%') as pct_accounts_ready
from (
    select
        week_created,
        sum(total_accounts) over w as total_accounts,
        sum(accounts_ready) over w as accounts_ready
    from (
        select 
            date_trunc('week', al.created_at) as week_created,
            count(case when ra.status='ready' then 1 end) as total_accounts,
            count(case when ra.status='ready' and ra.tests > 0 then 1 end) as accounts_ready
        from "ready_accounts" ra
        join "accounts_list" al
        on al.id=ra.id
        group by week_created
        ) s
    window w as (order by week_created)
    ) s
order by week_created desc;