MongoDB学习笔记（3）在group的前提下，进行多列整合

最新推荐文章于 2022-01-26 21:01:35 发布

BigPomme

最新推荐文章于 2022-01-26 21:01:35 发布

阅读量255

点赞数

本文链接：https://blog.csdn.net/u012560213/article/details/80029699

版权

原数据表：

{ "_id" : ObjectId("5ad9a64337b2d74102d33f08"), "node_id" : 1, "time" : NumberLong("1524208449000"), "cpu" : "0.6", "memory" : 56.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f09"), "node_id" : 1, "time" : NumberLong("1524208749000"), "cpu" : "0.4", "memory" : 56.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f0a"), "node_id" : 1, "time" : NumberLong("1524209049000"), "cpu" : "10.6", "memory" : 56.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f0b"), "node_id" : 1, "time" : NumberLong("1524209349000"), "cpu" : "0.1", "memory" : 56.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f0c"), "node_id" : 1, "time" : NumberLong("1524209649000"), "cpu" : "20.2", "memory" : 22.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f0d"), "node_id" : 1, "time" : NumberLong("1524209949000"), "cpu" : "30.9", "memory" : 12.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f0e"), "node_id" : 1, "time" : NumberLong("1524210249000"), "cpu" : "50.6", "memory" : 5.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f0f"), "node_id" : 1, "time" : NumberLong("1524210549000"), "cpu" : "25.6", "memory" : 76.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f10"), "node_id" : 1, "time" : NumberLong("1524210849000"), "cpu" : "6.6", "memory" : 58.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f11"), "node_id" : 1, "time" : NumberLong("1524211149000"), "cpu" : "77.6", "memory" : 6.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f12"), "node_id" : 1, "time" : NumberLong("1524211449000"), "cpu" : "8.6", "memory" : 6.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f13"), "node_id" : 1, "time" : NumberLong("1524211749000"), "cpu" : "6.6", "memory" : 56.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f14"), "node_id" : 2, "time" : NumberLong("1524208449000"), "cpu" : "99.6", "memory" : 88.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f15"), "node_id" : 2, "time" : NumberLong("1524208749000"), "cpu" : "24.4", "memory" : 78.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f16"), "node_id" : 2, "time" : NumberLong("1524209049000"), "cpu" : "22.6", "memory" : 18.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f17"), "node_id" : 2, "time" : NumberLong("1524209349000"), "cpu" : "6.1", "memory" : 12.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f18"), "node_id" : 2, "time" : NumberLong("1524209649000"), "cpu" : "77.2", "memory" : 1.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f19"), "node_id" : 2, "time" : NumberLong("1524209949000"), "cpu" : "65.9", "memory" : 99.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f1a"), "node_id" : 2, "time" : NumberLong("1524210249000"), "cpu" : "53.6", "memory" : 85.13 }
{ "_id" : ObjectId("5ad9a64337b2d74102d33f1b"), "node_id" : 2, "time" : NumberLong("1524210549000"), "cpu" : "43.6", "memory" : 5.13 }

1.利用聚合，整合单个列

db.t_node_usage.aggregate({$group:{_id:"$time",nodes:{$push:"$cpu"}}})

结果：

{ "_id" : NumberLong("1524211749000"), "nodes" : [ "6.6", "0.6" ] }
{ "_id" : NumberLong("1524211449000"), "nodes" : [ "8.6", "26.6" ] }
{ "_id" : NumberLong("1524209349000"), "nodes" : [ "0.1", "6.1" ] }
{ "_id" : NumberLong("1524208749000"), "nodes" : [ "0.4", "24.4" ] }
{ "_id" : NumberLong("1524208449000"), "nodes" : [ "0.6", "99.6" ] }
{ "_id" : NumberLong("1524209649000"), "nodes" : [ "20.2", "77.2" ] }
{ "_id" : NumberLong("1524209949000"), "nodes" : [ "30.9", "65.9" ] }
{ "_id" : NumberLong("1524210249000"), "nodes" : [ "50.6", "53.6" ] }
{ "_id" : NumberLong("1524209049000"), "nodes" : [ "10.6", "22.6" ] }
{ "_id" : NumberLong("1524210549000"), "nodes" : [ "25.6", "43.6" ] }
{ "_id" : NumberLong("1524210849000"), "nodes" : [ "6.6", "2.6" ] }
{ "_id" : NumberLong("1524211149000"), "nodes" : [ "77.6", "20.6" ] }

2. 利用聚合，将多列数据整合

db.t_node_usage.aggregate(
	[
		{
			$project:{
				time:1,
				nodes:{
					$concat:[
					"{id:",
					{
						"$substr":[
							"$node_id", 0, -1
						]
					},
					",cpu:",
					{
						"$substr":[
							"$cpu", 0, -1
						]
					},
					",memory:",
					{
						"$substr":[
							"$memory", 0, -1
						]
					},
					"}",
					]
				}
			}
		},
		{
			$group:{
				_id:"$time", nodes:{$push:"$nodes"}
			}
		}
	]
)

substr是为了将数字转化成字符串，防止concat报错

结果：

{ "_id" : NumberLong("1524211749000"), "nodes" : [ "{id:1,cpu:6.6,memory:56.13}", "{id:2,cpu:0.6,memory:65.13}" ] }
{ "_id" : NumberLong("1524211449000"), "nodes" : [ "{id:1,cpu:8.6,memory:6.13}", "{id:2,cpu:26.6,memory:77.13}" ] }
{ "_id" : NumberLong("1524209349000"), "nodes" : [ "{id:1,cpu:0.1,memory:56.13}", "{id:2,cpu:6.1,memory:12.13}" ] }
{ "_id" : NumberLong("1524208749000"), "nodes" : [ "{id:1,cpu:0.4,memory:56.13}", "{id:2,cpu:24.4,memory:78.13}" ] }
{ "_id" : NumberLong("1524208449000"), "nodes" : [ "{id:1,cpu:0.6,memory:56.13}", "{id:2,cpu:99.6,memory:88.13}" ] }
{ "_id" : NumberLong("1524209649000"), "nodes" : [ "{id:1,cpu:20.2,memory:22.13}", "{id:2,cpu:77.2,memory:1.13}" ] }
{ "_id" : NumberLong("1524209949000"), "nodes" : [ "{id:1,cpu:30.9,memory:12.13}", "{id:2,cpu:65.9,memory:99.13}" ] }
{ "_id" : NumberLong("1524210249000"), "nodes" : [ "{id:1,cpu:50.6,memory:5.13}", "{id:2,cpu:53.6,memory:85.13}" ] }
{ "_id" : NumberLong("1524209049000"), "nodes" : [ "{id:1,cpu:10.6,memory:56.13}", "{id:2,cpu:22.6,memory:18.13}" ] }
{ "_id" : NumberLong("1524210549000"), "nodes" : [ "{id:1,cpu:25.6,memory:76.13}", "{id:2,cpu:43.6,memory:5.13}" ] }
{ "_id" : NumberLong("1524210849000"), "nodes" : [ "{id:1,cpu:6.6,memory:58.13}", "{id:2,cpu:2.6,memory:55.13}" ] }
{ "_id" : NumberLong("1524211149000"), "nodes" : [ "{id:1,cpu:77.6,memory:6.13}", "{id:2,cpu:20.6,memory:6.13}" ] }

缺点：nodes数组中存有的是字符串，无法成为对象