选择Group by object Postgres中的第一个元素

Question

我有下表，我想获得每个贷款_ID的特定金额，该金额对应于每月大于或等于10 dpd的最早观测值。

Loan_ID date    dpd Amount
1   1/1/2017    1   55
1   1/2/2017    2   100
1   1/3/2017    3   5000
1   1/4/2017    5   6000
1   1/5/2017    10  50000
1   1/6/2017    15  50001
1   1/9/2017    31  50004
1   1/10/2017   55  50005
1   1/11/2017   59  50006
1   1/12/2017   65  50007
1   1/13/2017   70  80000
1   1/20/2017   85  900000
1   1/29/2017   92  100000
1   1/30/2017   93  10000
2   1/1/2017    0   522
2   1/2/2017    8   5444
2   1/3/2017    12  8784
2   1/6/2017    15  6221
2   1/12/2017   18  2220
2   1/13/2017   20  177
2   1/29/2017   35  5151
2   1/30/2017   60  40000
2   1/31/2017   61  5500

预期输出：

Loan_ID Month Amount
1       1      50000
2       1      8784

Answer 1

SELECT DISTINCT ON ("Loan_ID", date_trunc('month', "date"))  
"Loan_ID", 
date_trunc('month', "date")::date as month, 
"Amount"
FROM 
loans
WHERE
dpd >= 10
ORDER BY
"Loan_ID", 
date_trunc('month', "date"),
"date"
;

退货：

金额

贷款ID		月份
1	2017-01-01	50000
2	2017-01-01	8784

嗯。如果您想要每月的金额和匹配条件的第一个日期，那么您需要条件聚合：

select loan_id, date_trunc('month', date) as mon,
sum(dpd),
min(case when dpd >= 10 then dpd end) as first_dpd_10
from t
group by load_id, mon;

编辑：根据您的评论，您可以使用distinct on:

select distinct on (loan_id, date_trunc('month', date)) t.*
min(case when dpd >= 10 then dpd end) as first_dpd_10
from t
where dpd >= 10
order by load_id, date_trunc('month', date), date