我有以下要求。我有一些如下所示的记录（作为示例）
agreementid = 1, lastdispositioncode = PTP , feedbackdate = 30/11/2020
agreementid = 1, lastdispositioncode = PTP , feedbackdate = 29/11/2020
agreementid = 1, lastdispositioncode = BPTP , feedbackdate = 21/11/2020
agreementid = 2, lastdispositioncode = BPTP , feedbackdate = 29/11/2020
agreementid = 2, lastdispositioncode = BPTP , feedbackdate = 11/11/2020
agreementid = 3, lastdispositioncode = SBPTP , feedbackdate = 24/11/2020
在这里，首先我们必须
- 第一组协议id，
- 然后对feedbackdate进行排序，得到最新的记录
- 之后必须计算最后的处置代码的数量。
上面的数据将给出下面的结果
ptp = 1 (since latest record of PTP is on 30/11/2020 for AggrementID = 1)
bptp = 3 (since
for AggrementID = 1, latest record of BPTP is on 21/11/2020
for AggrementID = 2, latest record of BPTP is on 29/11/2020
for AggrementID = 3, latest record of SBPTP is on 24/11/2020)
我尝试了以下程序，但查询失败。
class Program
{
static void Main(string[] args)
{
var ptpDispositionCodes = new TermsQuery
{
IsVerbatim = true,
Field = "lastdispositioncode",
Terms = new string[] { "PTP" },
};
var bptpDispositionCodes = new TermsQuery
{
IsVerbatim = true,
Field = "lastdispositioncode",
Terms = new string[] { "BPTP","SBPTP" },
};
ISearchResponse<TestReportModel> searchResponse =
ConnectionToES.EsClient()
.Search<TestReportModel>
(s => s
.Index("feedbackdata")
.From(0)
.Size(50000)
.Query(q =>q.MatchAll())
.Aggregations(a => a
.Terms("Agreement_ID", t => t
.Field(f => f.agreementid.Suffix("keyword"))
.Aggregations(aa => aa
.TopHits("latest_feedbackdate_sort", th => th.Sort(so => so.Descending(f => f.feedbackdate))
.Size(1) //get the latest record
)
)
.Aggregations(fa => fa
.Filter("ptp_aggs", f => f.Filter(fd => ptpDispositionCodes))
.Filter("bptp_aggs", f => f.Filter(fd => bptpDispositionCodes))
)
)
)
);
var ptpDocCount = ((Nest.SingleBucketAggregate)trailSearchResponse.Aggregations["ptp_aggs"]).DocCount;
var bptpDocCount = ((Nest.SingleBucketAggregate)trailSearchResponse.Aggregations["bptp_aggs"]).DocCount;
}
}
DTO
public class TestReportModel
{
public string agreementid { get; set; }
public string trailstatus { get; set; }
public string lastdispositioncode { get; set; }
}
public class TestOutputAPIModel
{
public List<TestModel> TestModelDetail { get; set; }
}
public class TestModel
{
public string TrailStatus { get; set; }
public int NoOfAccounts { get; set; }
}
这是迄今为止我收到的回复
可以看出有两个问题
a) 仅对前 10 条记录进行“agreementID”聚合（但系统中有许多唯一的AgreementID，例如超过 5/6 lac）
b) ptp_aggs / bptp_aggs 为零，因为它们有匹配的 Lastdispositioncodes。
接下来我可以尝试什么？
Edit
这是我从 DebugInformation 获得的生成的原始查询
ApiCall.RequestBodyInBytes
{"aggs":{"Agreement_ID":{"aggs":{"ptp_aggs":{"filter":{"terms":{"lastdispositioncode":["ptp"]}}},"bptp_aggs":{"filter":{"terms":{"lastdispositioncode":["bptp"]}}},"paid_aggs":{"filter":{"terms":{"lastdispositioncode":["clmpd","dib","exceemi","odp","partpaid","pr"]}}}},"terms":{"field":"agreementid.keyword"}}},"from":0,"query":{"match_all":{}},"size":50000}
Api Call.Response 正文（以字节为单位）
"aggregations" : {
"sterms#Agreement_ID" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 658,
"buckets" : [
{
"key" : "2233585285",
"doc_count" : 513,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 0
},
"filter#ptp_aggs" : {
"doc_count" : 513
}
},
{
"key" : "ABCD123456",
"doc_count" : 95,
"filter#bptp_aggs" : {
"doc_count" : 8
},
"filter#paid_aggs" : {
"doc_count" : 0
},
"filter#ptp_aggs" : {
"doc_count" : 87
}
},
{
"key" : "8728441356",
"doc_count" : 53,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 53
},
"filter#ptp_aggs" : {
"doc_count" : 0
}
},
{
"key" : "5385541121",
"doc_count" : 39,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 0
},
"filter#ptp_aggs" : {
"doc_count" : 39
}
},
{
"key" : "VEH001001",
"doc_count" : 30,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 0
},
"filter#ptp_aggs" : {
"doc_count" : 0
}
},
{
"key" : "2671278479",
"doc_count" : 29,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 29
},
"filter#ptp_aggs" : {
"doc_count" : 0
}
},
{
"key" : "4937845646",
"doc_count" : 27,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 0
},
"filter#ptp_aggs" : {
"doc_count" : 0
}
},
{
"key" : "4472873928",
"doc_count" : 25,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 0
},
"filter#ptp_aggs" : {
"doc_count" : 0
}
},
{
"key" : "LD1736377086",
"doc_count" : 24,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 0
},
"filter#ptp_aggs" : {
"doc_count" : 0
}
},
{
"key" : "5548762365",
"doc_count" : 23,
"filter#bptp_aggs" : {
"doc_count" : 0
},
"filter#paid_aggs" : {
"doc_count" : 23
},
"filter#ptp_aggs" : {
"doc_count" : 0
}
}
]
