我有以下文件:
{
timestamp: 2022-11-04T08:58:03.303+00:00
name: Brian
username: B2022@mail.com
type: Type A
}
{
timestamp: 2022-11-04T09:20:13.564+00:00
name: Brian
username: B2022@mail.com
type: Type A
}
{
timestamp: 2022-11-04T13:12:25.024+00:00
name: Anna
username: Anna@something.com
type: Type A
}
{
timestamp: 2022-11-04T05:32:58.834+00:00
name: Max
username: Max@somethingelse.com
type: Type B
}
{
timestamp: 2022-11-04T03:34:23.011+00:00
name: Jan
username: Jan@somethingelse.com
type: Type c
}
我将在时间轴图表中使用这些数据,所以我使用$deinsify
和$fill
,以周为单位创建桶。
现在我试着把这些数据分组成一周的单位,我这样做:
{
$group: {
_id: {
bins: {
$dateTrunc: {
date: '$timestamp',
unit: 'week',
binSize: 1,
timezone: 'Europe/Paris',
startOfWeek: 'monday'
}
}
}
}
}
这很好,但是在这个组中,我还想计算类型A和B的不同用户名的数量,并且我还想计算类型为"type A"的文档的数量。
目标是得到像这样的东西:
{
timestamp: 2022-10-30T23:00:00.000+00:00
usernameCount: 3
typeAOccurencies: 3
}
这是我尝试过的:
我找不到一种方法可以直接在该组中进行不同的计数,所以我认为这样做的一种方法是通过向数组添加值,然后在项目聚合中使用$size
,像这样:
{
$match: {
'logType': {
$in: [ 'Type A', 'Type B' ]
}
}
},
{
$group: {
_id: {
bins: {
$dateTrunc: {
date: '$timestamp',
unit: 'week',
binSize: 1,
timezone: 'Europe/Paris',
startOfWeek: 'monday'
}
}
},
usernameCount: {
$addToSet: '$username'
},
typeAOccurencies: {
$push: '$type'
},
}
},
{
$project: {
_id: 0,
timestamp: '$_id.bins'
usernameCount: {
$size: '$usernameCount'
},
typeAOccurencies: {
$size: '$typeAOccurencies'
}
}
}
我现在唯一的问题是,我不知道如何只推类型字段与"类型A"值到typeAOccurencies。现在类型B也被推了,这是不应该的。
已更新:与$cond
一起使用$sum
db.collection.aggregate([
{
$group: {
_id: {
bins: {
$dateTrunc: {
date: "$timestamp",
unit: "week",
binSize: 1,
timezone: "Europe/Paris",
startOfWeek: "monday"
}
}
},
usernameCount: {
$addToSet: "$username"
},
typeAOccurencies: {
$sum: {
$cond: {
if: {
$eq: [
"$type",
"Type A"
]
},
then: 1,
else: 0
}
}
},
typeBOccurencies: {
$sum: {
$cond: {
if: {
$eq: [
"$type",
"Type B"
]
},
then: 1,
else: 0
}
}
}
}
},
{
$project: {
_id: 0,
timestamp: "$_id.bins",
usernameCount: {
$size: "$usernameCount"
},
typeAOccurencies: 1,
typeBOccurencies: 1
}
}
])
Demo @ Mongo Playground