相当于MongoDB中的OVER PARTITION

Question

为了简洁起见，我简化了这个场景。

初始数据：

| EngineerId | FirstName | LastName | BirthdateOn | CupsOfCoffee | HoursOfSleep |
| ---------- | --------- | -------- | ----------- | ------------ | ------------ |
| 1          | John      | Doe      | 1990-01-01  | 5            | 8            |
| 2          | James     | Bond     | 1990-01-01  | 1            | 6            |
| 3          | Leeroy    | Jenkins  | 2000-06-20  | 16           | 10           |
| 4          | Jane      | Doe      | 2000-06-20  | 8            | 2            |
| 5          | Lorem     | Ipsum    | 2010-12-25  | 4            | 5            |

db.engineers.insertMany([
{ FirstName: 'John', LastName: 'Doe', BirthdateOn: ISODate('1990-01-01'), CupsOfCoffee: 5, HoursOfSleep: 8 },
{ FirstName: 'James', LastName: 'Bond', BirthdateOn: ISODate('1990-01-01'), CupsOfCoffee: 1, HoursOfSleep: 6 },
{ FirstName: 'Leeroy', LastName: 'Jenkins', BirthdateOn: ISODate('2000-06-20'), CupsOfCoffee: 16, HoursOfSleep: 10 },
{ FirstName: 'Jane', LastName: 'Doe', BirthdateOn: ISODate('2000-06-20'), CupsOfCoffee: 8, HoursOfSleep: 2 },
{ FirstName: 'Lorem', LastName: 'Ipsum', BirthdateOn: ISODate('2010-12-25'), CupsOfCoffee: 4, HoursOfSleep: 5 }
])

我们希望看到：

• 工程师喝的咖啡
• 按咖啡杯数降序排列的行号
• 出生日期相同的工程师人数
• 工程师用一个共同的生日消耗的咖啡总量
• 有共同出生日期的工程师的平均睡眠时间

SQL查询为：

SELECT
FirstName,
LastName,
BirthdateOn,
CupsOfCoffee,
ROW_NUMBER() OVER (PARTITION BY BirthdateOn ORDER BY CupsOfCoffee DESC) AS 'Row Number',
COUNT(EngineerId) OVER (PARTITION BY BirthdateOn) AS TotalEngineers,
SUM(CupsOfCoffee) OVER (PARTITION BY BirthdateOn) AS TotalCupsOfCoffee,
AVG(HoursOfSleep) OVER (PARTITION BY BirthdateOn) AS AvgHoursOfSleep
FROM Engineers

结果如下：

| FirstName | LastName | BirthdateOn | Row Number | CupsOfCoffee | TotalEngineers | TotalCupsOfCoffee | AvgHoursOfSleep |
| --------- | -------- | ----------- | ---------- | ------------ | -------------- | ----------------- | --------------- |
| John      | Doe      | 1990-01-01  | 1          | 5            | 2              | 6                 | 7               |
| James     | Bond     | 1990-01-01  | 2          | 1            | 2              | 6                 | 7               |
| Leeroy    | Jenkins  | 2000-06-20  | 1          | 16           | 2              | 24                | 6               |
| Jane      | Doe      | 2000-06-20  | 2          | 8            | 2              | 24                | 6               |
| Lorem     | Ipsum    | 2010-12-25  | 1          | 4            | 1              | 4                 | 5               |

我已经阅读了很多关于MongoDB聚合管道的内容，但还没有找到一个好的解决方案。我知道这是而不是SQL，解决方案可能不会以这种确切的格式产生结果(尽管这会很神奇(。我考虑过的一件事是将聚合和集合的结果结合起来，但这要么是不可能的，要么是我搜索了错误的术语。$merge看起来很有希望，但AFAIU会修改原始集合，这不好。

我已经得到了以下内容，但结果不包括"；行编号"；，特定工程师消耗的杯子，或工程师的ID和姓名。

db.engineers.aggregate([
{
$group: {
_id: '$BirthdateOn',
TotalEngineers: {
$count: {  }
},
TotalCupsOfCoffee: {
$sum: '$CupsOfCoffee'
},
AvgHoursOfSleep: {
$avg: '$HoursOfSleep'
}
}
}
])

我对组合的想法是find所有的工程师，然后运行聚合并"；加入"；通过CCD_ 3发送给工程师。

谢谢你的帮助！非常感谢。

Answer 1

你有一个良好的开端。要获得输入数据，可以使用$push运算符。

会是这样的吗：

db.engineers.aggregate([
{
$group: {
_id: "$BirthdateOn",
TotalEngineers: { $count: {} },
TotalCupsOfCoffee: { $sum: "$CupsOfCoffee" },
AvgHoursOfSleep: { $avg: "$HoursOfSleep" },
data: { $push: "$$ROOT" }
}
}
])

关于正确的输出尝试：

db.engineers.aggregate([
{
$group: {
_id: "$BirthdateOn",
TotalEngineers: { $count: {} },
TotalCupsOfCoffee: { $sum: "$CupsOfCoffee" },
AvgHoursOfSleep: { $avg: "$HoursOfSleep" },
data: { $push: "$$ROOT" }
}
},
{ $unwind: "$data" },
{ $replaceWith: { $mergeObjects: ["$$ROOT", "$data"] } }
])

通常，运行$group和随后的$unwind是毫无意义的，它们基本上恢复了以前的操作。

MongoDB版本5.0引入了$setWindowFields阶段，它与SQL窗口化功能非常相似：

我想应该是这个：

db.engineers.aggregate([
{
$setWindowFields: {
partitionBy: "$BirthdateOn",
sortBy: { CupsOfCoffee: 1 },
output: {
TotalEngineers: { $count: {} },
TotalCupsOfCoffee: { $sum: "$CupsOfCoffee" },
AvgHoursOfSleep: { $avg: "$HoursOfSleep" },
"Row Number": { $documentNumber: {} }
}
}
}
])