根据条件进行计数，然后除以记录数以获取时间间隔
我已经准备好mongoplayground示例来解决这个问题.
I have mongoplayground example ready for this question.
https://mongoplayground.net/p/m_G-yBuX6uk
当前，它在某些字段上显示基于15分钟时间间隔的数据聚合. 有一个名为"presenceStatus"的字段，其值为0或1.
Currently it shows data aggregation based on 15min time interval on some fields. There is a field called "presenceStatus" which has value either 0 or 1.
当前结果
{
"accountId": ObjectId("5e1fe45cd05bfb0cc549297d"),
"applicationNumber": 30,
"area": "area2",
"avgZoneCountNumber": 0,
"avgZoneCountNumberInstant": 0,
"buildingId": ObjectId("5e1fe5e3d05bfb0cc5494146"),
"createdAt": ISODate("1970-01-01T00:00:00Z"),
"dateHour": "2020-03-19T18:15:00.000Z",
"deviceId": ObjectId("5e1fe7a9d05bfb0cc5495cdf"),
"gatewayId": ObjectId("5e1fe651d05bfb0cc54947f0"),
"serialNumber": "129300000012",
"spaceType": null
},
预期: 我需要找到每个时间间隔的occupancyRate.
Expected: I need to find occupancyRate for each time interval.
对于15分钟的时间间隔，公式为count presentStatus = 1并除以该时间范围内的总记录. 期望值应采用％格式，如下所示 occupancyRate 字段.
For 15min interval, formula is count presenceStatus = 1 and divide by total records for that time frame. Expected value should be in % format as shown below for occupancyRate field.
预期结果:
{
"accountId": ObjectId("5e1fe45cd05bfb0cc549297d"),
"applicationNumber": 30,
"area": "area2",
"avgZoneCountNumber": 0,
"avgZoneCountNumberInstant": 0,
"buildingId": ObjectId("5e1fe5e3d05bfb0cc5494146"),
"createdAt": ISODate("1970-01-01T00:00:00Z"),
"dateHour": "2020-03-19T18:15:00.000Z",
"deviceId": ObjectId("5e1fe7a9d05bfb0cc5495cdf"),
"gatewayId": ObjectId("5e1fe651d05bfb0cc54947f0"),
"serialNumber": "129300000012",
"spaceType": null,
"occupancyRate": 80%
},
在Mongo游乐场示例(totalPresent字段)中，我试图显示presentStatus = 1的计数，但显示为0.
In Mongo playground example(totalPresent field), I have tried to display count for presenceStatus= 1 but it shows as 0.
感谢您的帮助.
如果我理解您的问题，以下是使用$group的解决方案:
If I understand your problem, here is a solution using $group :
db.sensingresults.aggregate([
{
"$lookup": {
"from": "accounts",
"localField": "accountId",
"foreignField": "_id",
"as": "accountInfo"
}
},
{
"$unwind": "$accountInfo"
},
{
$addFields: {
"dateHour": {
"$dateToString": {
"format": "%Y-%m-%dT%H",
"date": "$updatedAt"
}
},
"minuteBucket": {
"$trunc": {
"$divide": [
{
"$minute": "$updatedAt"
},
15.0
]
}
},
"device": {
"$let": {
"vars": {
"building": {
"$arrayElemAt": [
{
"$filter": {
"input": "$accountInfo.buildings",
"cond": {
"$eq": [
"$$this._id",
"$buildingId"
]
}
}
},
0
]
}
},
"in": {
"$let": {
"vars": {
"gateway": {
"$arrayElemAt": [
{
"$filter": {
"input": "$$building.gateways",
"cond": {
"$eq": [
"$$this._id",
"$gatewayId"
]
}
}
},
0
]
}
},
"in": {
"$arrayElemAt": [
{
"$filter": {
"input": "$$gateway.devices",
"cond": {
"$eq": [
"$$this._id",
"$deviceId"
]
}
}
},
0
]
}
}
}
}
}
}
},
{
$group: {
_id: {
area: "$device.area",
applicationNumber: "$device.applicationNumber",
accountId: "$accountId",
dateHour: "$dateHour",
minuteBucket: "$minuteBucket",
buildingId: "$buildingId"
},
avgZoneCountNumberInstant: {
$avg: "$zoneCountNumberInstant"
},
avgZoneCountNumber: {
$avg: "$zoneCountNumber"
},
total: {
$sum: 1
},
"spaceType": {
"$first": "$device.spaceType"
},
presences: {
$sum: "$presenceStatus"
}
}
},
{
$addFields: {
occupancyRate: {
$concat: [
{
$toString: {
$multiply: [
100,
{
$divide: [
"$presences",
"$total"
]
}
]
}
},
"%"
]
}
}
}
])
结果将是
[
{
"_id": {
"accountId": ObjectId("5e1fe45cd05bfb0cc549297d"),
"applicationNumber": 30,
"area": "area2",
"buildingId": ObjectId("5e1fe5e3d05bfb0cc5494146"),
"dateHour": "2020-03-19T18",
"minuteBucket": 1
},
"avgZoneCountNumber": 0,
"avgZoneCountNumberInstant": 0,
"occupancyRate": "0%",
"presences": 0,
"spaceType": null,
"total": 1
},
{
"_id": {
"accountId": ObjectId("5e1fe45cd05bfb0cc549297d"),
"buildingId": ObjectId("5e1fe5e3d05bfb0cc5494146"),
"dateHour": "2020-03-19T18",
"minuteBucket": 1
},
"avgZoneCountNumber": 0,
"avgZoneCountNumberInstant": 0,
"occupancyRate": "0%",
"presences": 0,
"spaceType": null,
"total": 2
},
{
"_id": {
"accountId": ObjectId("5e1fe45cd05bfb0cc549297d"),
"buildingId": ObjectId("5e1fe5e3d05bfb0cc5494146"),
"dateHour": "2020-03-20T18",
"minuteBucket": 1
},
"avgZoneCountNumber": 0.5,
"avgZoneCountNumberInstant": 0,
"occupancyRate": "100%",
"presences": 2,
"spaceType": null,
"total": 2
}
]