Блог

Главная - Вопросы по программированию - Справка по конвейеру агрегации MongoDB (Преобразование MySQL в Mongo)

Справка по конвейеру агрегации MongoDB (Преобразование MySQL в Mongo)

#mongodb

Вопрос:

Я знаю, что об этом много спрашивали, и я совершенно новичок в MongoDB, что означает, что я борюсь. Я использую компас и пытаюсь разобраться в этом конвейере агрегации. Короче говоря, мне нужно получить среднюю разницу во времени между максимальной и минимальной метками времени, сгруппированными по идентификатору.

Ожидаемый результат будет просто: avg_time: 234.00 или что-то в этом роде.

Эквивалентный запрос MySQL выглядит следующим образом:

 select SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(a.maxDate,a.minDate)))) FROM (select id, min(timestamp) as minDate, max(timestamp) as maxDate from counterHistory group by id) as a

Вот как выглядит набор данных:

 [{
  "_id": {
    "$oid": "617dce992743dd52bed811a6"
  },
  "dateStart": {
    "$date": "2021-10-30T23:00:41.056Z"
  },
  "dateEnd": {
    "$date": "2021-10-30T23:00:52.404Z"
  },
  "areas": {
    "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70": {
      "color": "yellow",
      "type": "rightleft_bottomtop",
      "location": {
        "point1": {
          "x": 1397,
          "y": 702
        },
        "point2": {
          "x": 1808,
          "y": 645
        },
        "refResolution": {
          "w": 1920,
          "h": 969
        }
      },
      "computed": {
        "a": 0.15457277801631616,
        "b": -332.7843438566361,
        "lineBearings": [
          81.21317228796154,
          261.2131722879615
        ],
        "point1": {
          "x": 465.6666666666667,
          "y": -260.80495356037153
        },
        "point2": {
          "x": 602.6666666666666,
          "y": -239.62848297213623
        }
      },
      "name": "Right line start"
    },
    "56885eaf-9808-4b5e-b193-06b20e10c39d": {
      "color": "turquoise",
      "type": "rightleft_bottomtop",
      "location": {
        "point1": {
          "x": 770,
          "y": 411
        },
        "point2": {
          "x": 1085,
          "y": 360
        },
        "refResolution": {
          "w": 1920,
          "h": 969
        }
      },
      "computed": {
        "a": 0.18045112781954886,
        "b": -199.0092879256966,
        "lineBearings": [
          79.77099171264048,
          259.77099171264047
        ],
        "point1": {
          "x": 256.6666666666667,
          "y": -152.6934984520124
        },
        "point2": {
          "x": 361.6666666666667,
          "y": -133.74613003095976
        }
      },
      "name": "right lane end"
    }
  },
  "videoResolution": {
    "w": 640,
    "h": 360
  },
  "filename": "demo.mp4",
  "counterSummary": {
    "56885eaf-9808-4b5e-b193-06b20e10c39d": {
      "_total": 8,
      "car": 8
    },
    "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70": {
      "_total": 8,
      "car": 7,
      "truck": 1
    }
  },
  "trackerSummary": {
    "totalItemsTracked": 64
  },
  "counterHistory": [
    {
      "frameId": 29,
      "timestamp": {
        "$date": "2021-10-30T23:00:42.694Z"
      },
      "area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
      "name": "car",
      "id": 408,
      "bearing": 291.8014094863518,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 30.588237198390278
    },
    {
      "frameId": 43,
      "timestamp": {
        "$date": "2021-10-30T23:00:43.619Z"
      },
      "area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
      "name": "truck",
      "id": 457,
      "bearing": 293.1985905136482,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 31.985418225686644
    },
    {
      "frameId": 50,
      "timestamp": {
        "$date": "2021-10-30T23:00:44.063Z"
      },
      "area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
      "name": "car",
      "id": 421,
      "bearing": 303.69006752597977,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 43.919075813339305
    },
    {
      "frameId": 63,
      "timestamp": {
        "$date": "2021-10-30T23:00:44.927Z"
      },
      "area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
      "name": "car",
      "id": 458,
      "bearing": 293.6293777306568,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 32.41620544269528
    },
    {
      "frameId": 65,
      "timestamp": {
        "$date": "2021-10-30T23:00:45.054Z"
      },
      "area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
      "name": "car",
      "id": 464,
      "bearing": 284.03624346792645,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 24.265251755286005
    },
    {
      "frameId": 78,
      "timestamp": {
        "$date": "2021-10-30T23:00:45.888Z"
      },
      "area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
      "name": "car",
      "id": 469,
      "bearing": 303.69006752597977,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 42.47689523801825
    },
    {
      "frameId": 86,
      "timestamp": {
        "$date": "2021-10-30T23:00:46.415Z"
      },
      "area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
      "name": "car",
      "id": 427,
      "bearing": 354.28940686250036,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 85.48158485014012
    },
    {
      "frameId": 122,
      "timestamp": {
        "$date": "2021-10-30T23:00:48.757Z"
      },
      "area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
      "name": "car",
      "id": 479,
      "bearing": 296.565051177078,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 35.35187888911645
    },
    {
      "frameId": 125,
      "timestamp": {
        "$date": "2021-10-30T23:00:48.946Z"
      },
      "area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
      "name": "car",
      "id": 408,
      "bearing": 323.13010235415595,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 63.359110641515514
    },
    {
      "frameId": 143,
      "timestamp": {
        "$date": "2021-10-30T23:00:50.143Z"
      },
      "area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
      "name": "car",
      "id": 473,
      "bearing": 284.03624346792645,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 24.265251755286005
    },
    {
      "frameId": 152,
      "timestamp": {
        "$date": "2021-10-30T23:00:50.716Z"
      },
      "area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
      "name": "car",
      "id": 438,
      "bearing": 296.565051177078,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 35.35187888911645
    },
    {
      "frameId": 160,
      "timestamp": {
        "$date": "2021-10-30T23:00:51.242Z"
      },
      "area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
      "name": "car",
      "id": 455,
      "bearing": 45,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 34.77099171264047
    },
    {
      "frameId": 174,
      "timestamp": {
        "$date": "2021-10-30T23:00:52.149Z"
      },
      "area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
      "name": "car",
      "id": 492,
      "bearing": 327.2647737278924,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 66.05160143993088
    },
    {
      "frameId": 175,
      "timestamp": {
        "$date": "2021-10-30T23:00:52.212Z"
      },
      "area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
      "name": "car",
      "id": 469,
      "bearing": 281.30993247402023,
      "countingDirection": "rightleft_bottomtop",
      "angleWithCountingLine": 21.538940761379738
    }
  ]
}]

Вот мой ужасный конвейер, который, по крайней мере, дает мне результат, который я ищу. Итак, я думаю, как вы это оптимизируете?

 [{$match: {
  _id:ObjectId('617dce992743dd52bed811a6')
}}, {$unwind: {
  path: "$counterHistory",
}}, {$group: {
  _id: "$counterHistory.id",
  maxDate:{$max:"$counterHistory.timestamp"},
  minDate:{$min:"$counterHistory.timestamp"}
}}, {$project: {
  _id:1,
  minDate:1,
  maxDate:1,
  noMatchingDates:{$ne:["$maxDate","$minDate"]}
}}, {$match: {
  noMatchingDates:true
}}, {$group: {
  _id: null,
  "avg_time": {
      "$avg": {
        "$subtract": [
          { "$ifNull": [ "$maxDate", 0 ] },
          { "$ifNull": [ "$minDate", 0 ] }
        ]
      }
  }
}}, {$project: {
  avg_time:1,
  hours: { $divide: [ "$avg_time", 3600000 ] },
  minutes: { $divide: [ "$avg_time", 60000 ] },
  seconds: { $divide: [ "$avg_time", 1000 ] }
}}]

Комментарии:

1. Дайте некоторые тестовые данные, и ожидаемый результат будет лучше

2. @YuTing Я только что добавил конвейер, который я собрал вместе. Это ужасно плохо, лол.

3. Будет ли только один совпадающий объект или на первом этапе может быть много документов

4. Там будет только один.

5. Подумав некоторое время, я могу только сказать, что ваша агрегация выглядит хорошо, все ясно и эффективно.

Ответ №1:

Итак, как я написал выше и подтвердил Ютингом, этот конвейер работает:

 [{$match: {
  _id:ObjectId('617dce992743dd52bed811a6')
}}, {$unwind: {
  path: "$counterHistory",
}}, {$group: {
  _id: "$counterHistory.id",
  maxDate:{$max:"$counterHistory.timestamp"},
  minDate:{$min:"$counterHistory.timestamp"}
}}, {$project: {
  _id:1,
  minDate:1,
  maxDate:1,
  noMatchingDates:{$ne:["$maxDate","$minDate"]}
}}, {$match: {
  noMatchingDates:true
}}, {$group: {
  _id: null,
  "avg_time": {
      "$avg": {
        "$subtract": [
          { "$ifNull": [ "$maxDate", 0 ] },
          { "$ifNull": [ "$minDate", 0 ] }
        ]
      }
  }
}}, {$project: {
  avg_time:1,
  hours: { $divide: [ "$avg_time", 3600000 ] },
  minutes: { $divide: [ "$avg_time", 60000 ] },
  seconds: { $divide: [ "$avg_time", 1000 ] }
}}]
Метки: