我在9.5分贝的Postgres中有3张表,如下所示,
threshold

id    threshold_amount
----------------------
111   100
112   200
113   80

customers-每个客户都有一个threshold_idthreshold
id   customer_name threshold_id
--------------------------------
313  abc           111
314  xyz           112
315  pqr           113

charges-每个客户都有费用,因此此表有customer_id
id    customer_id  amount  post_date
------------------------------------
211   313         50       4/1/2017
212   313         50       4/30/2017
213   313         50       5/15/2017
214   314         100      3/1/2017
215   314         50       3/21/2017
216   314         50       4/21/2017
217   314         100      5/1/2017
218   315         80       5/5/2017

我想查询它并按post_date列的升序返回带有sum( amount ) == threshold_amount的特定charges.id
结果集如下所示,
customer_id   post_date
-----------------------
313           4/30/2017
314           4/21/2017
315           5/5/2017

我试过用sum( amount )调用group by customer_id并将存储过程从select子句中分离出来,然后传递amountpost_datethreshold_amount然后创建一个临时表,如果上面的条件匹配,则将post_date插入其中,然后再次访问该临时表,但它似乎是无效的,所以我想知道是否有其他解决方案,或者我可以在query中执行它?
谢谢

最佳答案

你的问题是问一个与门槛完全匹配的问题。这基本上是一个累积的总和:

select cct.*
from (select ch.customer_id, ch.amount,
             sum(ch.amount) over (partition by ch.customer_id order by post_date) as running_amount,
             t.threshold_amount
      from charges ch join
           customers c
           on ch.customer_id = c.id join
           threshholds t
           on c.threshold_id = t.id
     ) cct
where running_amount = threshold_amount;

关于sql - PostgreSQL-按筛选出特定行的分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44022715/

10-14 13:40