计算 PostgreSQL 中的累积日总和 [英] Calculating Cumulative daily sum in PostgreSQL
本文介绍了计算 PostgreSQL 中的累积日总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的数据是这样的
id,submission_state,outcome_state,company_id,office_id,date_created,location_city,location_state,location_country,work_type,is_foreign
op_01CB7TX0DS8AQYKWNZ0AQ80CAY,WILL_SUBMIT,UNKNOWN,co_01CB7TWTZJ73X1V7Y018FPM7PM,of_01CB7TWTZJ73X1V7Y018FPM7PN,2017-07-21T05:56:47.122Z,,,,,1
op_01CB7TX0DS8AQYKWNZ0AQ80CAZ,UNDECIDED,UNKNOWN,co_01CB7TX0DS8AQYKWNZ0AQ80CB0,of_01CB7TX0DS8AQYKWNZ0AQ80CB1,2017-08-17T02:42:45.304Z,Houston,TX,US,,1
op_01CB7TX0DS8AQYKWNZ0AQ80CB2,SUBMITTED,UNKNOWN,co_01CB7TWTYSQQR8B1Q7X6BASZE8,of_01CB7TWTYSQQR8B1Q7X6BASZE9,2017-08-18T21:02:31.897Z,Sandy Springs,GA,US,,1
我想按州计算每日累计机会?
I want to calculate cumulative daily opportunities by the state?
结果应该是这样的
| day_created | location_state | opp_count | cumulative_opps_received |
| ------------------- | -------------- | --------- | ------------------------ |
| 2013-12-13 00:00:00 | CA | 1 | 1 |
| 2014-05-16 00:00:00 | CA | 1 | 2 |
| 2014-08-02 00:00:00 | CA | 2 | 4 |
推荐答案
可以同时使用聚合和窗口函数:
You can use aggregation and window functions together:
select date_trunc('day', date_created) as day_created,
location_state,
count(*) opp_count,
sum(count(*)) over (partition by location_state order by min(date_created)) as cumulative_opps_received
from t
group by day_created
order by location_state, day_created;
如果你想要百分比,你可以除法:
You can divide if you want the percentage:
select date_trunc('day', date_created) as day_created,
location_state,
count(*) opp_count,
sum(count(*)) over (partition by location_state order by min(date_created)) as cumulative_opps_received,
(sum(count(*)) over (partition by location_state order by min(date_created)) * 1.0 /
sum(count(*)) over (partition by location_state)
) as cumulative_ratio
from t
group by day_created
order by location_state, day_created;
这篇关于计算 PostgreSQL 中的累积日总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文