关于Cosmos DB删除重复UUID及按日期对唯一UUID分组查询的技术咨询

Hey there, let's work through your Cosmos DB challenges one by one!

1. Removing Duplicate UUID Entries #

Cosmos DB doesn't have a direct "delete duplicates" command, but we can break this into two actionable steps to clean up your data:

Step 1: Identify Duplicate UUIDs

First, run this query to list all UUIDs that appear more than once, along with how many times they're duplicated:

SELECT c.uuid, COUNT(1) AS duplicateCount
FROM c
GROUP BY c.uuid
HAVING COUNT(1) > 1

Step 2: Delete Duplicates (Keep One Entry Per UUID)

We'll retain one entry per duplicate UUID (you can choose to keep the oldest entry via timestamp or the first one by document ID) and delete the rest. Use this query to get the IDs of documents marked for deletion:

SELECT c.id
FROM c
WHERE c.uuid IN (
    SELECT VALUE dups.uuid
    FROM (
        SELECT c.uuid, COUNT(1) AS cnt
        FROM c
        GROUP BY c.uuid
        HAVING COUNT(1) > 1
    ) dups
)
AND c.id NOT IN (
    SELECT VALUE MIN(doc.id) -- Replace MIN(doc.id) with MIN(doc.date) to keep the oldest entry
    FROM c doc
    WHERE doc.uuid = c.uuid
    GROUP BY doc.uuid
)

Once you have the list of IDs, you can delete these documents using:

• Azure Portal's Data Explorer (select entries and use bulk delete)
• A small script built with the Cosmos DB SDK (e.g., .NET, Python)
• Azure CLI/PowerShell commands for batch operations

2. Correctly Count Unique UUIDs by Minute #

Your second query failed because Cosmos DB SQL API doesn't support COUNT(DISTINCT) natively. Instead, we'll use a two-step subquery approach to get the result you need:

Here's the corrected query:

SELECT COUNT(1) AS total, grouped.time
FROM (
    -- First, get unique UUID-minute pairs
    SELECT DISTINCT c.uuid, LEFT(c.date, 16) AS time
    FROM c
    WHERE c.mediaID = '{ID}' 
      AND c.date BETWEEN '2021-08-02T14:48:00.000Z' AND '2021-09-03T14:48:00.000Z'
) grouped
GROUP BY grouped.time
ORDER BY grouped.time

This works by:

1. The inner subquery filters your dataset and returns only one entry per unique UUID per minute (using DISTINCT to eliminate duplicates within the same minute)
2. The outer query counts how many unique UUIDs exist for each minute, giving you the exact metric you're after.

To clarify why your original Query 1 gave incorrect results: you grouped by both uuid and time, then counted the number of entries per UUID-minute pair. That counts how many times a single UUID appeared in a minute, not how many unique UUIDs were present in that window.

内容的提问来源于stack exchange，提问作者robenson-dev