Cosmos DB查询求助：计算负载测试中消息完成的平均耗时

我需要在Cosmos DB中编写SQL查询，计算负载测试期间消息完成的平均耗时。目前我能通过以下语句获取消息的开始和结束时间：

SELECT
(SELECT c.TimestampUTC WHERE c.Event = 'message-accepted') as StartTime,
(SELECT c.TimestampUTC WHERE c.Event = 'message-completed') as EndTime
FROM c
WHERE c.TrackingId = 'LoadTest' AND (c.Event = 'message-accepted' OR c.Event = 'message-completed')

但尝试用DateTimeDiff计算时间差时出现错误，语句如下：

SELECT
(SELECT c.TimestampUTC WHERE c.Event = 'message-accepted') as StartTime,
(SELECT c.TimestampUTC WHERE c.Event = 'message-completed') as EndTime,
DateTimeDiff("second", StartTime, EndTime) as TotalTime
FROM c
WHERE c.TrackingId = 'LoadTest' AND (c.Event = 'message-accepted' OR c.Event = 'message-completed')

错误信息：

"Failed to query item for container enginelog: One of the input values is invalid."

我需要得到时间差后用AVG函数计算平均值，现在陷入困境。以下是Cosmos DB中的数据示例：

{
  "PartitionKey": "LoadTest",
  "RowKey": "4ee9709f-c826-4a88-9d6f-240ba439eb1d",
  "TrackingId": "LoadTest",
  "Event": "message-accepted",
  "TimestampUTC": "2022-09-14T19:12:18.8358914Z"
}

我是按照官方文档的函数格式编写的，希望能得到帮助。

问题原因 #

当前查询逻辑存在两个核心问题：

1. 单条文档仅对应一个事件（要么是message-accepted要么是message-completed），因此单条查询时StartTime或EndTime必然有一个为空；
2. 子查询返回的是数组结构，并非单个DateTime值，导致DateTimeDiff接收到无效输入。

正确查询方案 #

需要将同一消息的两个事件关联起来，以下按两种常见场景提供解决方案：

场景1：RowKey为消息唯一标识

假设同一消息的message-accepted和message-completed事件共享相同RowKey：

SELECT
    AVG(DateTimeDiff("second", accepted.TimestampUTC, completed.TimestampUTC)) as AverageCompletionTime
FROM accepted
JOIN completed IN (SELECT * FROM c WHERE c.Event = 'message-completed' AND c.RowKey = accepted.RowKey)
WHERE accepted.Event = 'message-accepted' AND accepted.TrackingId = 'LoadTest'

场景2：TrackingId为消息唯一标识

如果TrackingId本身就是单个消息的唯一标识：

SELECT
    AVG(DateTimeDiff("second", startEvents.TimestampUTC, endEvents.TimestampUTC)) as AverageCompletionTime
FROM startEvents
JOIN endEvents IN (SELECT * FROM c WHERE c.Event = 'message-completed' AND c.TrackingId = startEvents.TrackingId)
WHERE startEvents.Event = 'message-accepted' AND startEvents.TrackingId = 'LoadTest'

关键说明 #

• 通过JOIN关联同一条消息的两个事件，确保DateTimeDiff的两个参数均为有效的ISO 8601格式时间字符串；
• Cosmos DB会自动解析符合标准的时间字符串，无需额外转换；
• 最终通过AVG()函数对所有时间差取平均值，得到负载测试的消息平均完成耗时。

内容的提问来源于stack exchange，提问作者Jonald Monday