mysql - 执行从 MySql 到 Mongodb 的查询

Question

我正在尝试在 MongoDb-3.2 上运行查询，但无法获得我想要的结果。这是 MySQL 上的原始查询:

SELECT c.cdg,ten,IFNULL(efe,0) as efe,tcreated_at 
FROM 
(SELECT numB as cdg,COUNT(*) as ten,created_at as tcreated_at 
FROM dat_records cdr 
WHERE  LEFT(numB,@longitud)= @cod_prov AND type=4 
GROUP BY cdg ) c
LEFT JOIN
    (SELECT COUNT(*) as efe, numB,MAX(created_at) as ecreated_at 
FROM dat_records 
WHERE LEFT(numB,@longitud)= @cod_prov AND dat_records.time > 1 AND type=4 GROUP BY numB) d
ON c.cdg=d.numB AND tcreated_at=ecreated_at
WHERE ten > 40
GROUP BY c.cdg
HAVING (efe/ten)*100 <30
ORDER BY ten DESC
LIMIT 25

这是我在 MongoDB-3.2 上尝试的:

I have this, but I need to include all documents who has or not "efectivas". So, I know the 3rd match is affecting results.

db.archivos_cdr.aggregate([
   {$match: {$and:[{numB:{$regex:'^7' }},{ tipo_llamada:4}]}},
   {$group : {_id : "$numB",tentativas: { $sum: 1 }}},
   {$match:{'tentativas': {'$gt': 4}}},
   {$sort:{"tentativas":-1}},
   {$lookup:{"from": "archivos_cdr","localField": "_id","foreignField": "numB","as": "efecList" } },

    { "$unwind": "$efecList" },
    { "$match": { $or:[ {"efecList.tiempo_conv": {"$gt": 0}}] }},
    {
    "$group": {
        "_id": "$efecList.numB",
        "tentativas": { "$first": "$tentativas" },
        "efectivas": { "$sum": 1 }
        }
    }

])

我有这个结果:

{ "_id" : "123456789", "tentativas" : 5, "efectivas" : 4 }

但这就是我想要的:

{ "_id" : "123456789", "tentativas" : 5, "efectivas" : 4 }
{ "_id" : "325987741", "tentativas" : 13, "efectivas" : 0 }

感谢您的帮助!

Answer 1

我有我自己的答案!!!我明白了!!!!

db.dat_records.aggregate([
  {$match: {$and:[{numB:{$regex:'^7' }},{ tipo_llamada:4}]}},//WHERE  LEFT(numB,@longitud)= @cod_prov AND type=4 
  {$group : {_id : "$numB",//GROUP BY cdg
    ten: { $sum: 1 },//COUNT(*) as ten
    efec:{$sum:{$cond:[{$gt:["$tiempo_conv",0]},1,0]}},//
    efec_ner:{$sum:{$cond:[{$setIsSubset:[["$causa_fallo"],causas_efec_ner]},1,0]}}}//COUNT(*) as efe_ner 
           },
           {$match: { 'ten': {$gt: 40} }},//WHERE ten > 40
           {$sort:{"ten":-1}},//ORDER BY ten DESC
           { $project: {_id:0, 
           numeracion:"$_id",
           ten: 1, 
           efec: 1,
           efec_ner: 1, 
           asr: { $divide:[{$multiply: [ "$efec", 100 ]},"$ten"]},
           ner: { $divide:[{$multiply: [ "$efec_ner", 100 ]},"$ten"]},
           peso: { $multiply:[{$divide: [ "$ten", total_inter ]},100]}  }},
           {$match:{ $and:[ {"asr":{$lt:30}},//HAVING (efe/ten)*100 <30
{"peso":{$gt:10}}  ]}},//I had add this tent/total)>10(*100                
    ])

这就是结果(我需要的!!!):

{ "ten" : 13, "efec" : 0, "efec_ner" : 13, "numeracion" : "12364567", "asr" : 0, "ner" : 100, "peso" : 10.56 }

还是谢谢啦!!!!我希望这对某人有帮助。