sql - 为每个 "Group"选择最新文档

Question

我正在使用 Azure Cosmos DB SQL API 来尝试实现以下目标；

我们将设备数据存储在一个集合中，希望能够有效地检索每个设备序列号的最新事件数据，而不必分别对每个设备进行 N 次查询。

SELECT * 
FROM c
WHERE c.serial IN ('V55555555','synap-aim-g1') ORDER BY c.EventEnqueuedUtcTime DESC

我假设我需要使用分组依据 - https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-group-by

如有任何帮助，我们将不胜感激

粗略的数据示例:

[
    {
        "temperature": 25.22063251827873,
        "humidity": 71.54208429695204,
        "serial": "V55555555",
        "testid": 1,
        "location": {
            "type": "Point",
            "coordinates": [
                30.843687,
                -29.789895
            ]
        },
        "EventProcessedUtcTime": "2020-09-07T12:04:34.5861918Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2020-09-07T12:04:34.4700000Z",
        "IoTHub": {
            "MessageId": null,
            "CorrelationId": null,
            "ConnectionDeviceId": "V55555555",
            "ConnectionDeviceGenerationId": "637323979596346475",
            "EnqueuedTime": "2020-09-07T12:04:34.0000000"
        },
        "Name": "admin",
        "id": "6dac491e-1f28-450d-bf97-3a15a0efaad8",
        "_rid": "i2UhAI7ofAo3AQAAAAAAAA==",
        "_self": "dbs/i2UhAA==/colls/i2UhAI7ofAo=/docs/i2UhAI7ofAo3AQAAAAAAAA==/",
        "_etag": "\"430131c1-0000-0100-0000-5f5621d80000\"",
        "_attachments": "attachments/",
        "_ts": 1599480280
    }
]

更新:因此，执行以下操作会返回正确的数据，但遗憾的是，您只能返回分组依据或聚合函数内的数据(即不能选择 *)

SELECT c.serial, MAX(c.EventProcessedUtcTime)
FROM c
WHERE c.serial IN ('V55555555','synap-aim-g1') 
GROUP BY c.serial

[
    {
        "serial": "synap-aim-g1",
        "$1": "2020-09-09T06:29:42.6812629Z"
    },
    {
        "serial": "V55555555",
        "$1": "2020-09-07T12:04:34.5861918Z"
    }
]

Answer 1

感谢@AnuragSharma-MSFT 的帮助:

• 恐怕没有直接的方法可以使用查询来实现它宇宙数据库但是，您可以引用以下相同主题的链接。如果您正在使用任何 sdk，这将有助于实现所需的功能:https://learn.microsoft.com/en-us/answers/questions/38454/index.html

我们很高兴您以这种方式解决了它，感谢您分享更新:

因此，执行以下操作会返回正确的数据，但遗憾的是，您只能返回分组依据或聚合函数内的数据(即不能选择 *)

SELECT c.serial, MAX(c.EventProcessedUtcTime)
FROM c
WHERE c.serial IN ('V55555555','synap-aim-g1') 
GROUP BY c.serial

[
    {
        "serial": "synap-aim-g1",
        "$1": "2020-09-09T06:29:42.6812629Z"
    },
    {
        "serial": "V55555555",
        "$1": "2020-09-07T12:04:34.5861918Z"
    }
]