postgreGet SQL中过去12个月的聚合事务信息

Question

ID#     transaction_amount    transaction_yr_mon
12345   20                    202007
23455   200                   202008
12345   34                    202007

我想得到以下：

ID#    Number_of_transactions_in last_12_months   total_transaction_amount  
12345  2                                          54                       

我不知道如何在SQL中开始这样做，请建议可能的查询是什么！

感谢

Answer 1

select id,count(id) as Number_of_transactions_in last_12_months,sum(transaction_amount) as total_transaction_amount  
from tablename group by id

或

select id,count(id) as Number_of_transactions_in last_12_months,sum(transaction_amount) as total_transaction_amount  
from tablename group by id,transaction_yr_mon

Answer 2

select ID 
, count(*) Number_of_transactions_in_last_12_month
, sum(transaction_amount) total_transaction_amount
from table t
where transaction_yr_mon >= to_char(now() - interval '12 month','YYYYMM') 
group by ID

Answer 3

您可以使用filter过滤select:中的一个聚合

select id,
count(*) filter (where transaction_yr_mon >= to_char(now() - interval '12 month', 'YYYYMM')),
sum(transaction_amount)
from t
group by id;

当然，如果您也希望通过过去12个月来过滤总数，那么将逻辑转移到where子句更简单：

select id, count(*), sum(transaction_amount)
from t
where transaction_yr_mon >= to_char(now() - interval '12 month', 'YYYYMM')
group by id;