按mongodb中的mongodate分组

Question

我的插入数据是

   {    "_id" : ObjectId("5992e6f52479d56b0b2709bf"), 
        "TIMESTAMP" : ISODate("2017-08-15T12:19:25.680+0000"), 
        "RECORD" : 20507361.0, 
        "BattV_Min" : 11.9, 
        "SEVolt" : 0.0
    }

我对获取日期的查询是

        $collection1 = $db->tablename;
        $filter1 = array('timestamp' => array('$gte' => $acst1, '$lte' => $acst2));
        $options1 = array('projection' => array('_id' => 0,'temperature' => 1,timestamp => 1));
        $oresult = $collection1->find($filter1,$options1);

我正在获得数十亿个数据，所以我想使用逐组的方法来优化我的查询，就像如果我的数据低于70000，我将按秒分组，如果我的数据范围在70000到100000之间，而不是按分钟、按时间和一天分组的话。

Answer 1

您可以使用聚合函数在mongodb中使用group，而不是按需要使用日期格式的项目，如果需要列值，也可以使用$addToSet函数和平均值使用avg函数的项目。

$collection1 = $db->tablename;
                $oresult = $collection1->aggregate([
                                [
                                    '$match' => array(TIMESTAMP => array('$gte' => $acst1, '$lte' => $acst2))
                                ],
                                [
                                    '$project' => [
                                    'year'  => ['$year' =>'TIMESTAMP'],
                                        'month' => ['$month' => '$TIMESTAMP'],
                                        'day'   => ['$dayOfMonth' => '$TIMESTAMP'],
                                        'hour'  => ['$hour' => '$TIMESTAMP'],
                                        'minute'   => ['$minute' => '$TIMESTAMP'],
                                        'second'   => ['$second' => '$TIMESTAMP'],
                                        'millisecond'   => ['$millisecond' => '$TIMESTAMP'],
                                            'value'   => '$BattV'

                                    ]
                                ],
                                [
                                    '$group' => [
                                        '_id'    => [
                                            'year'  => '$year',
                                            'month' => '$month',
                                            'day'   => '$day',
                                            'hour'  => '$hour',
                                            'minute'   => '$minute',
                                            'second' => '$second',
                                            'millisecond' => '$millisecond'

                                        ],
                                        'count'  => ['$sum' => 1],
                                        'values' => ['$addToSet' => '$value']
                                    ]
                                ],
                                 [
                                    '$sort' => ['_id' => 1]
                                ],
                                [
                                    '$project' => [
                                        'a' => '$count',
                                        'new' =>  ['$avg' => '$values'],
                                        'y' => [
                                            '$concat' => [
                                                ['$substr' => ['$_id.year', 0, 4]],
                                                '-',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.month', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.month', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.month', 0, 2]]
                                                    ]
                                                ],
                                                '-',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.day', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.day', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.day', 0, 2]]
                                                    ]
                                                ],
                                                ' ',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.hour', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.hour', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.hour', 0, 2]]
                                                    ]
                                                ],
                                                ':',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.minute', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.minute', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.minute', 0, 2]]
                                                    ]
                                                ],
                                                ':',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.second', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.second', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.second', 0, 2]]
                                                    ]
                                                ],
                                                '.',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.millisecond', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.millisecond', 0, 1]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.millisecond', 0, 1]]
                                                    ]
                                                ]

                                            ]
                                        ],
                                    ]
                                ]
                            ]
                        );