azure - cosmos无法删除重复的uuid

Question

你好，我发这个小消息，知道是否可以删除

模型如下：

创建日志数据

{
    "publisherID": "",
    "managerID": "",
    "mediaID": "",
    "type": "",
    "ip": "",
    "userAgent": "",
    "playerRes": "",
    "title": "",
    "playerName": "",
    "videoTimeCode": 0,
    "geo": {
        "country": "",
        "region": "",
        "city": "",
        "ll": []
    },
    "date": "",
    "uuid": "",
    "id": ""
}

选择所有日志数据：SELECT * FROM a

[
    {
        "publisherID": "{ID}",
        "mediaID": "{ID}",
        "type": "Load",
        "ip": "67.69.69.41",
        "userAgent": "Mozilla/5.0 (Android 11; Mobile; rv:91.0) Gecko/91.0 Firefox/91.0",
        "parentReferrer": "link.com",
        "title": "title here",
        "playerName": "page",
        "videoTimeCode": 0,
        "geo": {
            "country": "CA",
            "region": "Ontario",
            "city": "Guelph",
            "ll": [
                43.5588,
                -80.3004
            ]
        }
    }
]

[SQL-QUERY]: 01 - 不起作用，因为总是有重复的 uuid

1. 我想要所有类型为进度的请求type: 'Progress'
2. type根据日期按分钟整理进度数据

SELECT COUNT(c.uuid) as total,
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')
GROUP BY c.uuid, left(c.date, 16)

[
    {
        "total": 9,
        "time": "2021-09-03T14:07"
    },
    {
        "total": 40,
        "time": "2021-09-02T12:51"
    },
    {
        "total": 51,
        "time": "2021-09-02T12:50"
    }
]

[SQL-QUERY]：02 - 宇宙错误！

语法错误，“DISTINCT”附近的语法不正确。

SELECT COUNT(DISTINCT c.uuid) as total, left(c.date,16) as time
FROM c
WHERE c.mediaID = 'ckpwphqbj10852aav7ib0713o8'
AND (c.date BETWEEN '2021-08-02T14:48:00.000Z' AND '2021-09-03T14:48:00.000Z')
GROUP BY c.uuid, left(c.date, 16)

我想删除重复的 uuid，然后按日期对唯一的 uuid 进行分组

标签： azureazure-cosmosdbazure-cosmosdb-sqlapi

Answer 1

你在找这个吗？

计算distinctuuid：

SELECT COUNT(distinct c.uuid) as total, left(c.date,16) as time 
FROM c 
WHERE c.postID = '155478' 
AND (c.date BETWEEN '2021-08-02T14:48:00.000Z' AND '2021-09-03T14:48:00.000Z') 
GROUP BY left(c.date, 16)