mongo聚合操作符: $split/$arrayElemAt/$addFields/$strLenCP/$subtract/$substr/$toInt/$push/$arrayToObject

1.原始数据

2. 需求

3.思路

• 首先考虑需要拆分code,分成三个字段device_code(设备编码)，kpi_code(指标code)， shift(班次)
• 再根据以上三个字段分组，再每个设备，当天当班次的指标到一个字典里。

4.操作

db.datas.aggregate([{
    '$match': {
    'code': {
    '$in': ['dev_plc_LS_01_001~zb_r_cl_bc1', 'dev_plc_LS_01_001~zb_r_cl_bc2', 'dev_plc_LS_01_001~zb_r_bhgs_bc1','dev_plc_LS_01_001~zb_r_bhgs_bc2', 'dev_plc_LS_01_001~zb_r_sbjgsj_bc1', 'dev_plc_LS_01_001~zb_r_sbjgsj_bc2','dev_plc_LS_01_001~zb_r_sbsjsj_bc1', 'dev_plc_LS_01_001~zb_r_sbsjsj_bc2', 'dev_plc_LS_01_001~zb_r_sbsbsj_bc1','dev_plc_LS_01_001~zb_r_sbsbsj_bc2', 'dev_plc_LS_01_001~zb_r_sbrlsj_bc1', 'dev_plc_LS_01_001~zb_r_sbrlsj_bc2','dev_plc_LS_01_001~zb_r_sbyxsj_bc1', 'dev_plc_LS_01_001~zb_r_sbyxsj_bc2', 'dev_plc_LS_01_002~zb_r_cl_bc1','dev_plc_LS_01_002~zb_r_cl_bc2', 'dev_plc_LS_01_002~zb_r_bhgs_bc1', 'dev_plc_LS_01_002~zb_r_bhgs_bc2','dev_plc_LS_01_002~zb_r_sbjgsj_bc1', 'dev_plc_LS_01_002~zb_r_sbjgsj_bc2', 'dev_plc_LS_01_002~zb_r_sbsjsj_bc1','dev_plc_LS_01_002~zb_r_sbsjsj_bc2', 'dev_plc_LS_01_002~zb_r_sbsbsj_bc1', 'dev_plc_LS_01_002~zb_r_sbsbsj_bc2','dev_plc_LS_01_002~zb_r_sbrlsj_bc1', 'dev_plc_LS_01_002~zb_r_sbrlsj_bc2', 'dev_plc_LS_01_002~zb_r_sbyxsj_bc1','dev_plc_LS_01_002~zb_r_sbyxsj_bc2', 'dev_plc_LS_01_003~zb_r_cl_bc1', 'dev_plc_LS_01_003~zb_r_cl_bc2','dev_plc_LS_01_003~zb_r_bhgs_bc1', 'dev_plc_LS_01_003~zb_r_bhgs_bc2', 'dev_plc_LS_01_003~zb_r_sbjgsj_bc1','dev_plc_LS_01_003~zb_r_sbjgsj_bc2', 'dev_plc_LS_01_003~zb_r_sbsjsj_bc1', 'dev_plc_LS_01_003~zb_r_sbsjsj_bc2','dev_plc_LS_01_003~zb_r_sbsbsj_bc1', 'dev_plc_LS_01_003~zb_r_sbsbsj_bc2', 'dev_plc_LS_01_003~zb_r_sbrlsj_bc1','dev_plc_LS_01_003~zb_r_sbrlsj_bc2', 'dev_plc_LS_01_003~zb_r_sbyxsj_bc1', 'dev_plc_LS_01_003~zb_r_sbyxsj_bc2','dev_plc_LS_01_004~zb_r_cl_bc1', 'dev_plc_LS_01_004~zb_r_cl_bc2', 'dev_plc_LS_01_004~zb_r_bhgs_bc1','dev_plc_LS_01_004~zb_r_bhgs_bc2', 'dev_plc_LS_01_004~zb_r_sbjgsj_bc1', 'dev_plc_LS_01_004~zb_r_sbjgsj_bc2','dev_plc_LS_01_004~zb_r_sbsjsj_bc1', 'dev_plc_LS_01_004~zb_r_sbsjsj_bc2', 'dev_plc_LS_01_004~zb_r_sbsbsj_bc1','dev_plc_LS_01_004~zb_r_sbsbsj_bc2', 'dev_plc_LS_01_004~zb_r_sbrlsj_bc1', 'dev_plc_LS_01_004~zb_r_sbrlsj_bc2','dev_plc_LS_01_004~zb_r_sbyxsj_bc1', 'dev_plc_LS_01_004~zb_r_sbyxsj_bc2']},'timestamp': {
    '$gte': ISODate("2020-01-01T00:00:00.000+0000"), '$lte': ISODate("2020-12-31T00:00:00.000+0000")},'value': {
    '$ne': 0}}},{
    '$addFields': {
    "d_c": {
    "$split": ["$code", "~"]}}},{
    '$addFields': {
    'device_code': {
    "$arrayElemAt": ["$d_c", 0]},'kpi_code': {
    "$arrayElemAt": ["$d_c", 1]}}},{
    '$addFields': {
    "length": {
    '$strLenCP': "$kpi_code"}}},{
    '$addFields': {
    "kpi_length": {
    '$toInt': {
    '$subtract': ["$length", 4]}}}},{
    '$project': {
    '_id': 0,'device_code': '$device_code','timestamp': '$timestamp','value': '$value','kpi_code': {
    '$substr': ["$kpi_code", 0, "$kpi_length"]},'shift': {
    '$substr': ["$kpi_code", {
    '$toInt': {
    '$add': ["$kpi_length", 1]}}, 3]}}},{
    '$group': {
    '_id': {
    'device_code': '$device_code', 'timestamp': '$timestamp', 'shift': '$shift'},"kpi_dict": {
    "$push": {
    "k": "$kpi_code", "v": "$value"}}}},{
    '$project': {
    "_id": 0,'shift': '$_id.shift','device_code': '$_id.device_code','kpi_dict': {
    "$arrayToObject": "$kpi_dict"},'timestamp': '$_id.timestamp'}},{
    '$sort': {
    'device_code': 1, 'timestamp': 1, 'shift': 1}}])

5.解析

5.1 '$match'操作符先匹配出满足条件的数据,缩小数据操作范围

  {
    '$match': {
    'code': {
    '$in': ['dev_plc_LS_01_001~zb_r_cl_bc1', 'dev_plc_LS_01_001~zb_r_cl_bc2']},'timestamp': {
    '$gte': ISODate("2020-01-01T00:00:00.000+0000"), '$lte': ISODate("2020-12-31T00:00:00.000+0000")},'value': {
    '$ne': 0}}
}

5.2 '$split'拆分code字段分成两部分，设备编码和带班次的指标code. '$addFields'增加字段d_c,内容是个数组。如['dev_plc_LS_01_001', 'zb_r_cl_bc1']

{
    '$addFields': {
    "d_c": {
    "$split": ["$code", "~"]}}
}

5.3 '$arrayElemAt'按下标取数组的数据区分出设备编码和指标code,再'$addFields'增加以上两个字段，分别命名device_code，kpi_code

{
    '$addFields': {
    'device_code': {
    "$arrayElemAt": ["$d_c", 0]},'kpi_code': {
    "$arrayElemAt": ["$d_c", 1]}}
}

5.4 接下来要拆分kpi_code字段，可以确定的是班次名长度是3，所以先'$strLenCP'统计每个kpi_code总的长度'length’，再统计每个kpi_code使用'$subtract'操作符减去’_bc1’后缀以后的’指标长度"kpi_length"

 {
    '$addFields': {
    "length": {
    '$strLenCP': "$kpi_code"}}},
{
    '$addFields': {
    "kpi_length": {
    '$toInt': {
    '$subtract': ["$length", 4]}}}
},

5.5 由于上述操作引入了很多中间过渡字段，所以这里'$project'过滤出实际需要的字段。并使用'$substr'操作符取’kpi_code’下标0开始长度为kpi_length长度即为拆分后的真正指标code，班次shift取’kpi_code’下标kpi_length+1，长度为3得到shift

 {
    '$project': {
    '_id': 0,'device_code': '$device_code','timestamp': '$timestamp','value': '$value','kpi_code': {
    '$substr': ["$kpi_code", 0, "$kpi_length"]},'shift': {
    '$substr': ["$kpi_code", {
    '$toInt': {
    '$add': ["$kpi_length", 1]}}, 3]}}},

5.6 对数据进行分组，按设备编码、时间、班次分组，并使用"$push"操作符将每个设备每天每个班次的指标汇总起来。

{
    '$group': {
    '_id': {
    'device_code': '$device_code', 'timestamp': '$timestamp', 'shift': '$shift'},"kpi_dict": {
    "$push": {
    "k": "$kpi_code", "v": "$value"}}}
},

5.7 数据最终显示与排序"$arrayToObject"将数组转为字典(文档)格式

  {
    '$project': {
    "_id": 0,'shift': '$_id.shift','device_code': '$_id.device_code','kpi_dict': {
    "$arrayToObject": "$kpi_dict"},'timestamp': '$_id.timestamp'}},{
    '$sort': {
    'device_code': 1, 'timestamp': 1, 'shift': 1}}

至此，本次mongo聚合操作结束，大功告成！水平有限，上述如有优化还请不吝赐教！