mongoDB-使用总结（$group 、$unwind、$cond等）

1.按年分库表联合查询

db.collection_2023_3.aggregate(
  {$set:{_id:'2023_3'}},
  {$unionWith:{coll:'collection_2023_4',pipeline:[ { $set: { _id: "2023_4" } } ]}}
)

2.多字段分组

{
    $group: {
        _id: {
            data_time: '$data_time',
            isoWeek: '$isoWeek'
        },
        sum_pnum: {
            $sum: '$pnum'
        }
    }
}

多字段分组时要给字段起别名，当第二次分组时再用到第一次分组的字段时，用’$_id.字段别名’

如：

db.collection_2023.aggregate(
    {$set:{_id:'2023'}},
    {$unionWith:{coll:'collection_2022',pipeline:[ { $set: { _id: "2022" } } ]}},
    {$match:{sid:'S00002',p_type:{$in:[1,2,3]},data_time:{$gte:'2023-01-01',$lte:'2023-10-13'}}},
    {$project:{data_time:'$data_time',year:{$substr:['$data_time',0,4]},pnum:1,tNum:1,stayTime:1}},      
    {$group:{_id:{data_time:'$data_time',year:'$year'},sum_pnum:{$sum:'$pnum'},sum_tNum:{$sum:'$tNum'},avg_stayTime:{$avg:'$stayTime'}}},
    {$group:{_id:'$_id.year',sum_pnum:{$sum:'$sum_pnum'},sum_tNum:{$sum:'$sum_tNum'},avg_stayTime:{$avg:'$avg_stayTime'}}},      
    {$project:{data_time:1,sum_pnum:1,avg_stayTime:{$round:[{$divide:['$avg_stay_time',60]},2]},
    jdlv:{$round:[{$divide:['$sum_pnum',{$add:['$sum_pnum','$sum_tNum']}]},4]}
    }},
  {$sort:{_id:1}}
)

3.多次分组，即一个查询中写多个$group

不同身份（p_type）的pnum要按日求和，按周时要每日平均

db.collection_2023.aggregate(
    {$set:{_id:'2023'}},
    {$unionWith:{coll:'collection_2022',pipeline:[ { $set: { _id: "2022" } } ]}},
    {$match:{sid:'S001',p_type:{$in:[2,3,4]},data_time:{$gte:'2023-01-01',$lte:'2023-10-13'}}},
    {$project:{data_time:'$data_time',isoWeek:{$isoWeek:{date:{"$dateFromString":{"dateString":"$data_time"}},"timezone":"Asia/Shanghai"}},pnum:1,cnum:1}},
    {$group:{_id:{data_time:'$data_time',isoWeek:'$isoWeek'},sum_pnum:{$sum:'$pnum'}}},
    {$group:{_id:'$_id.isoWeek',sum_pnum:{$sum:'$sum_pnum'},avg_pnum:{$avg:'$sum_pnum'}}},
    {$project:{_id:1,sum_pnum:1,avg_pnum:{$round:['$avg_pnum',0]}}},
    {$sort:{_id:1}}
)

注，一个查询中可以出现多次match和project，match和project可出现在group之前，也可以出现在group之后

4.数组展开  $unwind

使用数组的值，$数组名.字段名

db.collection_2023.aggregate(
    {$set:{_id:'2023'}},
    {$unionWith:{coll:'collection_2022',pipeline:[ { $set: { _id: "2022" } } ]}},
    {$match:{pid:'S001',data_time:{$gte:'2022-12-02',$lte:'2023-10-08'}}},
    {$unwind:'$stay_time_datas'},
    {$group:{_id:'$stay_time_datas.stay_time_label',sum_cnum:{$sum:'$stay_time_datas.cnum'}}},
    {$project:{_id:1,sum_cnum:'$sum_cnum'}},
    {$sort:{_id:1}}
)

 5.使用对象的值

使用对象的属性，$对象名.属性名

db.collection_2023.aggregate(
    {$match:{pid:'S001',data_time:{$gte:'2023-01-01',$lte:'2023-03-13'}}},
    {$group:{_id:'$data_time',one:{$sum:'$peer.one'},two:{$sum:'$peer.two'},three:{$sum:'$peer.three'},four_or_more:{$sum:'$peer.four_or_more'}}},
    {$sort:{_id:1}}
    )

多字段排序，如{$sort:{pid:1,month:1}}

6.group分组之后可取$max  $min  $sum  $avg  $first第一个 $last最后一个

$max 最大值

$min 最小值

$sum 求和

$avg 求均值

$first第一个

$last最后一个

如：取最大值    
{$group:{_id:'',MaxModifytime:{$max:'$modify_time'}}}

如：$first第一个
db.collection.aggregate(  
{$match:{cid:'320100',ptime:{$gte:'2023-05-01 00:00:00',$lte:'2023-07-13 23:59:59'}}},
{$sort:{ptime:-1}},
{$group:{_id:{date1:{$substr:['$ptime',0,10]}},value:{$first:"$value"}}},
{$project:{_id:'$_id.date1',pubtime:1,value:1}},
{$sort:{_id:1}}
)

注，sort也可以出现多次，可出现在任何位置

7.逻辑运算符 $not  $and  $or  $nor

$not非  

$and与  

$or或  

$nor 多个条件中一个也不符合

--$not
db.user.find({age:{$ne:18}})
db.user.find({age:{$not:{$eq:18}}})

--$or
'$or':[ 
     {sre:{$in:['2']}},
     {tgt:{$in:['2']}} 
]

--'$nor
'$nor':[
     {sre:{$in:['18','19','20','21']}},
     {tgt:{$in:['18','19','20','21']}}
    ]
    
    
{$match:{dt:{$gte:'2023-08-05',$lte:'2023-09-28'},sid:'S00001',
    '$or':[ 
            {sre:{$in:['2']}},
            {tgt:{$in:['2']}} 
    ],
    '$nor':[
            {sre:{$in:['18','19','20','21']}},
            {tgt:{$in:['18','19','20','21']}}
    ],
　　sex:{$in:[0,1]}, 
}}

 $and  同理

8.$cond聚合分析的条件操作

有两种语法

{ $cond: { if: <boolean-expression>, then: <true-case>, else: <false-case> } }

或：

{ $cond: { if: <boolean-expression>, then: <true-case>, else: <false-case> } }

如果<boolean-expression>条件为true，则执行<true-case>，否则执行<false-case>

{ "_id" : 1, "item" : "abc1", qty: 300 }
{ "_id" : 2, "item" : "abc2", qty: 200 }
{ "_id" : 3, "item" : "xyz1", qty: 250 }
db.inventory.aggregate(
   [
      {
         $project:
           {
             item: 1,
             discount:
               {
                 $cond: { if: { $gte: [ "$qty", 250 ] }, then: 30, else: 20 }
               }
           }
      }
   ]
)
>>结果：
{ "_id" : 1, "item" : "abc1", "discount" : 30 }
{ "_id" : 2, "item" : "abc2", "discount" : 20 }
{ "_id" : 3, "item" : "xyz1", "discount" : 30 }

9.数学运算符 加减乘除、取余 $add  $subtract  $multiply  $divide  $mod

$add  加      { $add: [ <expression1>, <expression2>, ... ] }

$subtract 减    { $subtract: [ <expression1>, <expression2> ] }

$multiply乘     { $multiply: [ <expression1>, <expression2>, ... ] }

$divide  除     { $divide: [ <expression1>, <expression2> ] }

$mod 取余     { $mod: [ <expression1>, <expression2> ] }

jdlv:{$round:[{$divide:['$pnum',{$add:['$pnum','$tNum']}]},4]}
jkl:{$round:[{$divide:['$sum_pnum',{$multiply:[56,5]}]},2]}

10.$strLenCP

$strLenCP  是aggregate的一个管道符返回UTF-8编码下的字符数，每个字符均计数为1

{ $strLenCP: <string expression> } 

{ $strLenCP: "Hello World!" }     12
{ $strLenCP: "" }                 0
{ $strLenCP: "寿司" }             2

11.$size 计算数组长度

db.collection.find( { field: { $size: 2 } } );