MongoDB多表关联分组查询指定行数数据实践遇坑记及解析

MongoDB多表关联分组查询指定行数数据实践遇坑记及解析，基于聚合中的$lookup、$lookup嵌套pipeline、$group与$push、$slice提取子数组、$unwind组合文档等实现，并分析大数据集处理及解决办法。

肖永威

3531人浏览 · 2021-11-30 17:21:38

肖永威 · 2021-11-30 17:21:38 发布

1. 需求分析

数据处理需求是从2千万的客户特征工程数据集（customerfeature）中，抽取部分数据建立训练集进行客户流失模型监督学习，输出目标训练集（traindatas）预计5百万，处理过程如下。

（1）提取流失数据集（churndatas），也就是最后一次统计标记为流失的记录；
（2）客户特征工程数据集（customerfeature）与流失数据集（churndatas）通过“carduser_id”关联，提取流失前8个月的统计特征，构建流失训练集；
（3）提取当前（本月统计有交易）活跃用户两个月前的统计交易特征数据进流失训练集；
（4）计算同一客户交易特征时间间隔，以及计算相关交易次数等数据；
（5）标注数据。

本文，重点分享MongoDB多表关联查询，完成第（2）段工作任务，涉及多表关联、按（carduser_id）分组获取从行1开始的8组数据。

2. 多表关联遇坑记

2.1. 关于多表关联

多表关联使用"$lookup"聚合管道操作，是对同一数据库中的未分片集合执行左外部联接，以从“联接”集合中筛选文档进行处理。$lookup阶段向每个输入文档添加一个新的数组字段，其元素是来自“joined”集合的匹配文档。并且$lookup阶段将这些重新成形的文档传递到下一阶段。

$lookup阶段具有以下语法：

具有单个联接条件的相等匹配：

{
   $lookup:
     {
       from: <外部连接数据集>,
       localField: <本文档的关联关键字段>,
       foreignField: <外部关联文档关键连接字段 "from" 集合>,
       as: <输出数组字段>
     }
}

2.2. 遇坑

为了便于交流，简化churndatas文档内容：

/* 1 */
{
    "_id" : ObjectId("61a5b31c3bc7a47e1e98dedd"),
    "carduser_id" : 1313943,
    "yearmonth" : "202105"
}

/* 2 */
{
    "_id" : ObjectId("61a5b3403bc7a47e1e98dede"),
    "carduser_id" : 1492855,
    "yearmonth" : "202106"
}

第一次写的关联查询，缩略版（为了简化说明问题）：

db.getCollection('customerfeature').aggregate([
        {'$lookup':{
                 'from': 'churndatas',
                 'localField': 'carduser_id',
                 'foreignField': 'carduser_id',
                 'as': 'newdata'       
            }}
        ],
        {'allowDiskUse':true})

返回数据集如下所示：
在这里插入图片描述
很惨的结果，将返回客户特征工程数据集（customerfeature）全部记录，并关联上所对应的流失客户集。

而实际目标是只返回流失客户所对应的历史数据，这样差个数量级的结果是大量消耗了时间，长时间才能返回结果。

怎么办呢？

以客户流失数据集（churndatas）为主文档，外部关联客户特征工程数据集（customerfeature）进行多表关联查询。

db.getCollection('churndatas').aggregate([
        {'$lookup':{
                 'from': 'customerfeature',
                 'localField': 'carduser_id',
                 'foreignField': 'carduser_id',
                 'as': 'newdata'       
            }}
        ],
        {'allowDiskUse':true})

很快，返回结果如下：
在这里插入图片描述
按预期只返回流失历史数据。

但是，历史数据是以数组方式存在，如何转化成文档，取出规定行的数据呢？

3. 数组转文档及分组提取多行数据记录关键技术

3.1. 数组转文档

$unwind 扩展数组，为每个数组入口生成一个输出文档。

案例中，表关联返回“as”输出有一次数组，需要转换为文档，分组过程中，添加文档（或具体字段）到数组中，也需要转换一次文档。

3.2. 分组提取多行数据记录

在分组聚合过程中，使用$push 聚合操作符，先把分组后的文档记录添加到数组中，用于传递到下一段；再使用$slice返回数组中的子串：

$push 添加值到数组中
$slice 返回数据中的子串，与$push和$each一起使用来缩小更新后数组的大小。

3.3 组合后的案例

方案一：直接表关联，获取流失历史数据集后，再分组筛选指定多行数据。

db.getCollection('churndatas').aggregate([
      {'$lookup':{
                 'from': 'customerfeature',
                 'localField': 'carduser_id',
                 'foreignField': 'carduser_id',
                 'as': 'newdata'       
            }},
      {'$unwind': '$newdata'}, 
      {'$replaceRoot':{'newRoot': '$newdata'}},
      {'$sort':{'carduser_id':1,'occurtime':1}},
      {'$group':{'_id': '$carduser_id','data':{'$push': '$$ROOT'}}},
      {'$project':{'data':{'$slice' :['$data',1 ,3]}}},
      {'$unwind': '$data'}, 
      {'$replaceRoot':{'newRoot': '$data'}}
      ],
      {'allowDiskUse':true})

方法二：在表关联过程中，先对关联到的历史数据分组筛选指定多行数据，然后再进行文档转换。
在这里插入图片描述
其中，“$lookup”中的“pipeline”是针对外联表“customerfeature”聚合，两个表的关联是通过“let”定义关联及其表达式，其中“user_id”是表“churndatas”中“carduser_id”变量定义，而且“let”定义变量只能在“pipeline:[{$match:{$expr:”中使用，而pipeline其他地方则是外联表“customerfeature”的操作内容。

db.getCollection('churndatas').aggregate([
      {'$lookup':{
                 'from': 'customerfeature',
                 'let':{'user_id':'$carduser_id','ym':'$yearmonth'},
                 'pipeline':
                     [{'$match':
                         {'$expr': 
                            {'$and': [ 
                                { '$eq': [ '$$user_id', '$carduser_id' ] },
                                { '$gt': [ '$$ym', '$yearmonth' ] }                   
                                ]}
                         }},
                      {'$sort':{'carduser_id':1,'yearmonth':-1}},
                      {'$group':{'_id':'$carduser_id','data':{'$push':'$$ROOT'}}},
                      {'$project':{'data':{'$slice' :['$data',1 ,3]}}},
                      {'$unwind': '$data'}, 
                      {'$replaceRoot':{'newRoot': '$data'}}          
                     ],
                 'as': 'newdata'       
            }},
      {'$unwind': '$newdata'}, 
      {'$replaceRoot':{'newRoot': '$newdata'}},
      {'$project':{'_id':0}},
      {'$sort':{'carduser_id':1,'yearmonth':1}}
      ],
      {'allowDiskUse':true})

输出结果如下：
在这里插入图片描述