如何在KQL中按Time分组并对其余整数列求和适配Grafana时序可视化

需求背景 #

需要生成适配Grafana Cloud时序数据可视化要求的表格，现有KQL查询已完成初步数据处理，现需调整查询实现以下目标：

• 按Time列分组，对其余所有列进行求和计算
• 最终输出仅包含4个指定时间点的结果，格式为[Time]/[列1]/[列2]/[列3]

原始KQL查询 #

customEvents
| where name == "send_editionCO_service" and timestamp > datetime("2024-10-25, 8:00:00.000")
| evaluate bag_unpack(customDimensions)
| extend formsData = parse_json(Forms)
| mv-expand formsData
| extend FormCode = formsData.FormLibelle,
         FormLibelle = formsData.FormCode
| extend Time = bin(timestamp, 1h)
| summarize nbForms = count() by Time, EtatCode, EtatLibelle
| project Time, EtatCode, EtatLibelle, nbForms
| order by Time
| extend index = row_number()
| order by EtatCode, index
| extend total=row_cumsum(nbForms, EtatCode != prev(EtatCode))
| evaluate pivot(EtatLibelle, sum(total))
| project-away EtatCode, nbForms, index;

修改后的KQL查询 #

假设需要保留的4个时间点为2024-10-25 08:00:00、2024-10-25 09:00:00、2024-10-25 10:00:00、2024-10-25 11:00:00，可使用以下修改后的查询：

customEvents
| where name == "send_editionCO_service" and timestamp > datetime("2024-10-25, 8:00:00.000")
| evaluate bag_unpack(customDimensions)
| extend formsData = parse_json(Forms)
| mv-expand formsData
| extend FormCode = formsData.FormLibelle,
         FormLibelle = formsData.FormCode
| extend Time = bin(timestamp, 1h)
| summarize nbForms = count() by Time, EtatCode, EtatLibelle
| project Time, EtatCode, EtatLibelle, nbForms
| order by Time
| extend index = row_number()
| order by EtatCode, index
| extend total=row_cumsum(nbForms, EtatCode != prev(EtatCode))
| evaluate pivot(EtatLibelle, sum(total))
| project-away EtatCode, nbForms, index
// 筛选目标时间点
| where Time in (datetime("2024-10-25 08:00:00"), datetime("2024-10-25 09:00:00"), datetime("2024-10-25 10:00:00"), datetime("2024-10-25 11:00:00"))
// 按Time分组对所有列求和
| summarize across(*) by Time
// 拼接成指定格式
| extend result = strcat(Time, "/", column1, "/", column2, "/", column3)
| project result

关键调整点 #

• 时间点筛选：修改where Time in (...)内的时间值，匹配你需要保留的4个时间点
• 分组求和：summarize across(*) by Time自动对每个Time分组下的所有数值列求和，确保每个时间点仅输出一行结果
• 格式拼接：strcat()实现指定格式的字符串拼接，若知道具体列名，将column1、column2、column3替换为实际列名更精准

内容的提问来源于stack exchange，提问作者Imerzoukene Anisse