每个银行和每天以KQL为单位的汇总数据 [英] Aggregate data per Bank and per day in KQL

问题描述

我正在从事项目，目标是将荷兰的多家银行连接到我们的平台中.

I'm in working on project with goal of connecting multiple banks, in Netherlands, into our platform.

就目前而言，每次用户连接到单个银行时，我们都希望发送一个指标并将其显示在Azure仪表板中.除了要汇总每天的总和之外，我们几乎在那儿了.这就是我们现在拥有的:

For now, every time a user connects to a single bank, we want to send out a metric and show it in Azure dashboard. We are almost there, except that we want to aggregate the sum per day. This is what we have right now:

例如，查看ABN AMRO，我们有:

For example, looking at ABN AMRO, we have:

1. 荷兰银行(ABN AMRO)在21年1月25日连接了 2081
2. 荷兰银行(ABN AMRO)在2002年2月24日建立了 2325 个连接
3. 荷兰银行(ABN AMRO)在23/31/2021具有 5082 个连接

1. ABN AMRO had 2081 connections on 25/01/2021
2. ABN AMRO had 2325 connections on 24/01/2021
3. ABN AMRO had 5082 connections on 23/31/2021

但是我们想要的是这样总结:

But what we want is to sum it like this:

1. 荷兰银行(ABN AMRO)在25/01/2021上具有 2081 + 2325 + 5082 = 9488
2. 荷兰银行(ABN AMRO)在2002年2月1日的 2325 + 5082 = 7407
3. ABN AMRO在23/31/2021 = 5082时具有 5082

1. ABN AMRO had 2081 + 2325 + 5082 on 25/01/2021 = 9488
2. ABN AMRO had 2325 + 5082 on 24/01/2021 = 7407
3. ABN AMRO had 5082 on 23/31/2021 = 5082

这是到目前为止使用的查询:

This is the query used so far:

customMetrics
| where name == "CustomerGrantedConsent"
| extend BankName = customDimensions.BankName
| summarize Count = count() by tostring(BankName), bin(timestamp, 1d)
| order by timestamp

如何?

推荐答案

尝试使用 row_cumsum

customMetrics
| where name == "CustomerGrantedConsent"
| extend BankName = customDimensions.BankName
| summarize Count = count() by tostring(BankName), bin(timestamp, 1d)
| order by timestamp
| serialize
| extend cumsum = row_cumsum(Count, BankName != prev(BankName))

它将根据您的要求返回输出

It will return the output as your require

1. 荷兰银行(ABN AMRO)在2021年1月25日的2081 + 2325 + 5082 = 9488
2. 荷兰银行(ABN AMRO)在2002年2月1日= 23407 + 5082 = 7407
3. ABN AMRO在23/31/2021时有5082 = 5082

您可以在此处.

这篇关于每个银行和每天以KQL为单位的汇总数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！