Azure Cosmos DB中如何按deviceId去重并保留最新_ts记录？

Alright, let's work through this Cosmos DB challenge you're facing. You want one (deviceId, _ts) pair per device, grabbing the most recent _ts, but since every _ts is unique, SELECT DISTINCT just spits out every single record. And since Cosmos DB doesn't support GROUP BY for this aggregation scenario, we've got a couple solid workarounds to get the result you need.

Method 1: Use Window Functions (Recommended) #

Cosmos DB's SQL API supports window functions like ROW_NUMBER(), which is perfect for this use case. We can partition the data by deviceId, sort each partition by _ts descending, then pick only the top record from each partition.

Here's the query:

SELECT deviceId, _ts
FROM (
    SELECT 
        c.deviceId,
        c._ts,
        ROW_NUMBER() OVER (PARTITION BY c.deviceId ORDER BY c._ts DESC) AS row_num
    FROM c
) filtered
WHERE filtered.row_num = 1

How it works: #

• The inner query adds a row_num value to each record: for every deviceId, records are ordered by _ts (newest first), so the latest record gets row_num = 1.
• The outer query filters to only keep records where row_num = 1, leaving you with exactly one entry per deviceId (the most recent one).

Method 2: Use Subqueries with ARRAY_AGG #

If you're working with an older Cosmos DB environment where window functions aren't available (unlikely these days, but just in case), you can use ARRAY_AGG to collect all records per deviceId, then pick the first entry from the sorted array.

Query example:

SELECT 
    d.deviceId,
    ARRAY(SELECT TOP 1 c._ts FROM c WHERE c.deviceId = d.deviceId ORDER BY c._ts DESC)[0]._ts AS latest_ts
FROM (
    -- First get all unique deviceIds
    SELECT DISTINCT c.deviceId
    FROM c
) d

How it works: #

• The inner subquery grabs every unique deviceId from your container.
• For each unique deviceId, we use another subquery to fetch just the latest _ts (using TOP 1 and sorting by _ts DESC), wrap that in an array, then take the first element of the array as latest_ts.

Optimize Performance with Indexing #

To make these queries run efficiently (especially on large datasets), create a composite index on deviceId and _ts (sorted descending):

CREATE INDEX idx_deviceId_ts ON c(deviceId, _ts DESC)

This index lets Cosmos DB quickly locate and sort records per deviceId without scanning the entire container.

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