mongo一些基本語(yǔ)法概念:
- aggregate方法中的本質(zhì)是一系列的 pipeline, 會(huì)按定義的順序一個(gè)一個(gè)串聯(lián)執(zhí)行,前一個(gè)pipeline的結(jié)果是后一個(gè)pipeline的參數(shù).
- 字段與字段的比較需要使用特殊手段,如
$redact或$expr - 時(shí)間戳格式化需要用到
$dateToString,需要注意時(shí)區(qū)問題.
前置條件：
- mongodb版本: 4.2.1
- collection數(shù)據(jù)結(jié)構(gòu): t_wechat_user
{
"_id": "ObjectId(\"5f5f3ef2b53b633689108dfs\")",
"open_id": "o69xlwMzwkhTIlYoFGEWeHzUtles",
"app_id": "wxa82301b25sdf0153",
"subscribe_time": "NumberLong(1578880338000)",
"custom_time": "NumberLong(1638328107017)",
"subject": "游戲原畫",
"create_time": "ISODate(\"2020-09-14T09:59:14.955Z\")",
"update_time": "ISODate(\"2021-12-01T05:37:35.962Z\")"
}
custom_time 和 subscribe_time 類型為時(shí)間戳毫秒數(shù)
正常情況 subscribe_time 一般在 custom_time 之前
但 subscribe_time 也有可能在 custom_time 之后
需求1：
按 subject 統(tǒng)計(jì)某個(gè)custom_time區(qū)間的 custom_time 在 subscribe_time 之后且不超過7天的間隔時(shí)間平均值,翻譯成sql語(yǔ)言類似下面這樣
SELECT subject, avg( custom_time - subscribe_time ) from t_wechat_user
WHERE
custom_time BETWEEN 1635696000000 AND 1638288000000
AND custom_time > subscribe_time
AND ( custom_time - subscribe_time ) < 604800000
GROUP BY subject
對(duì)應(yīng)mongo寫法如下
db.t_wechat_user.aggregate([{
$match: {
custom_time: {
"$gte": 1635696000000,
"$lt": 1638288000000
}
}
}, {
$redact: {
$cond: {
if : {
$and:[{"$gt":["$custom_time","$subscribe_time"]}, {"$lt": [{"$subtract": ["$custom_time", 604800000]}, "$subscribe_time"]}]
},
then: "$$KEEP",
else : "$$PRUNE"
}
}
}, {
$project:
{
custom_time: 1,
subject: 1,
"subTime": {
"$subtract": ["$custom_time", "$subscribe_time"]
}
}
}, {
$group: {
_id: "$subject",
myCount: { $sum: 1 },
subTimeAvg: {
$avg: "$subTime"
}
}
}]);
需求2：
按日統(tǒng)計(jì)某個(gè)custom_time區(qū)間的 custom_time 在 subscribe_time 之后且不超過7天的間隔時(shí)間平均值,翻譯成sql語(yǔ)言類似下面這樣
SELECT DATE_FORMAT(custom_time,"%Y-%m-%d") AS day, avg( custom_time - subscribe_time ) from t_wechat_user
WHERE
custom_time BETWEEN 1635696000000 AND 1638288000000
AND custom_time > subscribe_time
AND ( custom_time - subscribe_time ) < 604800000
GROUP BY DATE_FORMAT(custom_time,"%Y-%m-%d")
這里就涉及到如何將時(shí)間戳轉(zhuǎn)換成指定的日期格式,需要用到$dateToString函數(shù),需要注意時(shí)區(qū)問題
db.t_wechat_user.aggregate([{
$match: {
custom_time: {
"$gte": 1635696000000,
"$lt": 1638288000000
}
}
}, {
$redact: {
$cond: {
if : {
$and:[{"$gt":["$custom_time","$subscribe_time"]}, {"$lt": [{"$subtract": ["$custom_time", 604800000]}, "$subscribe_time"]}]
},
then: "$$KEEP",
else : "$$PRUNE"
}
}
}, {
$project:
{
custom_time: 1,
subject: 1,
"subTime": {
"$subtract": ["$custom_time", "$subscribe_time"]
}
}
}, {
$group: {
_id: { $dateToString: { format: "%Y-%m-%d", date:{$add:[ISODate("1970-01-01T00:00:00Z"),"$custom_time"]},timezone: "+08:00" }},
myCount: { $sum: 1 },
subTimeAvg: {
$avg: "$subTime"
}
}
}]);
需求3:
如果我只是想查詢出這些數(shù)據(jù),而不是分組統(tǒng)計(jì)呢?
SELECT open_id, subject, app_id, subscribe_time, custom_time FROM t_wechat_user
WHERE
custom_time BETWEEN 1635696000000 AND 1638288000000
AND custom_time > subscribe_time
AND ( custom_time - subscribe_time ) < 604800000
相應(yīng)的mongo寫法如下:
db.t_wechat_user.find({
custom_time: {
$gte: 1638288000000,
$lt: 1638892800000
},
$expr: {
$and: [{
"$gt": ["$custom_time", "$subscribe_time"]
}, {
"$lt": [{
"$subtract": ["$custom_time", 604800000]
}, "$subscribe_time"]
}]
}
}, {
open_id: 1,
subject: 1,
app_id: 1,
subscribe_time: 1,
custom_time: 1
});
到這里可能有人已經(jīng)發(fā)現(xiàn),前面兩個(gè)需求其實(shí)還可以換一種寫法,把$redact這一步可以并入到$match這個(gè)pipeline里面去,寫法如下:
db.t_wechat_user.aggregate([{
$match: {
custom_time: {
"$gte": 1635696000000,
"$lt": 1638288000000
},
$expr: {
$and: [{
"$gt": ["$custom_time", "$subscribe_time"]
}, {
"$lt": [{
"$subtract": ["$custom_time", "$subscribe_time"]
}, 604800000]
}]
}
}
}, {
$project: {
custom_time: 1,
subject: 1,
"subTime": {
"$subtract": ["$custom_time", "$subscribe_time"]
}
}
}, {
$group: {
_id: "$subject",
myCount: {
$sum: 1
},
subTimeAvg: {
$avg: "$subTime"
}
}
}]);
