我很幸运能够得到另一个 SO 问题的精彩答案 Mongo / Mongoose - Aggregating by Date来自@chridam，它给出了一组文档，例如:
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ac"), "amount" : -33.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-04-26T23:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.581Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ba"), "amount" : -61.3, "name" : "Amazon", "method" : "VIS", "date" : ISODate("2017-03-23T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.592Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ce"), "amount" : -3.3, "name" : "Tesco", "method" : "VIS", "date" : ISODate("2017-03-15T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.601Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49cc"), "amount" : -26.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-16T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.600Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49f7"), "amount" : -63.3, "name" : "Sky", "method" : "VIS", "date" : ISODate("2017-03-02T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.617Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49be"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-22T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.593Z"), "category" : "Not Set", "__v" : 0 }
需要一个按供应商、年、月和周汇总支出的查询。查询在下面，它几乎可以正常工作，但是当我在我的应用程序中使用它时，我注意到一个严重的问题
db.statements.aggregate([
{ "$match": { "name": "RINGGO" } },
{
"$redact": {
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]}, // within my route this uses parseInt(req.params.year)
{ "$eq": [{ "$month": "$date" }, 3 ]}, // within my route this uses parseInt(req.params.month)
{ "$eq": [{ "$week": "$date" }, 12 ]} // within my route this uses parseInt(req.params.week)
]
},
"$$KEEP",
"$$PRUNE"
]
}
},{
"$group": {
"_id": {
"name": "$name",
"year": { "$year": "$date" },
"month": { "$month": "$date" },
"week": { "$week": "$date" }
},
"total": { "$sum": "$amount" }
}
},
{
"$group": {
"_id": {
"name": "$_id.name",
"year": "$_id.year"
},
"YearlySpends": { "$push": "$total" },
"totalYearlyAmount": { "$sum": "$total" },
"data": { "$push": "$$ROOT" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"month": "$data._id.month"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$push": "$data.total" },
"totalMonthlyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"week": "$data._id.week"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$push": "$data.total" },
"totalWeeklyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": "$data._id",
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$first": "$WeeklySpends" },
"totalWeeklyAmount": { "$first": "$totalWeeklyAmount" }
}
}
])
运行此查询返回
{ "_id" :
{ "name" : "RINGGO",
"year" : 2017,
"month" : 3,
"week" : 12 },
"YearlySpends" : [ -9.6 ],
"totalYearlyAmount" : -9.6,
"MonthlySpends" : [ -9.6 ],
"totalMonthlyAmount" : -9.6,
"WeeklySpends" : [ -9.6 ],
"totalWeeklyAmount" : -9.6
}
当我转而想查看当月的支出
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]},
{ "$eq": [{ "$month": "$date" }, 3 ]}
]
},
"$$KEEP",
"$$PRUNE"
]
我得到:
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 12 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -9.6 ], "totalWeeklyAmount" : -9.6 }
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 9 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -3.3 ], "totalWeeklyAmount" : -3.3 }
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 11 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -9.6 ], "totalWeeklyAmount" : -9.6 }
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 13 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -3.3 ], "totalWeeklyAmount" : -3.3 }
然而，当我运行一个简单的 db.statements.find({"name":"RINGGO"}) 我得到:
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ac"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-26T23:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.581Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ba"), "amount" : -6.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-23T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.592Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ce"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-15T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.601Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49cc"), "amount" : -6.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-16T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.600Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49f7"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-02T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.617Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49be"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-22T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.593Z"), "category" : "Not Set", "__v" : 0 }
因此您可以看到，与按名称查找的输出中显示的项目相比，先前输出中 MonthlySpends 中的项目数量不同。您还可以看到，一些值在不应该的情况下在 MonthlySpends 中被加在一起。
理想情况下，我希望获得以下输出:
当我有 $redact 包含:
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]},
{ "$eq": [{ "$month": "$date" }, 3 ]},
{ "$eq": [{ "$week": "$date" }, 12 ]}
]
},
"$$KEEP",
"$$PRUNE"
]
返回
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 12 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -9.6 ], "totalWeeklyAmount" : -9.6 }
当我有 $redact 包含:
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]},
{ "$eq": [{ "$month": "$date" }, 3 ]},
]
},
"$$KEEP",
"$$PRUNE"
]
返回
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997 }
当我有 $redact 包含:
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]}
]
},
"$$KEEP",
"$$PRUNE"
]
返回
{ "_id" : { "name" : "RINGGO", "year" : 2017 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997}
在这方面非常需要任何帮助。我试过修改查询，但恐怕我对它的理解还不足以正确修改它。
我的 Mongoose 版本是 ^4.9.5，我的 mongo 是 3.4.2。
最佳答案
您可以在 3.4 版本中尝试使用 $facet 和 $addFields 进行并行聚合。
这将降低整体复杂性，您可以同时使用自己的匹配输入运行分组。
下面的代码基于请求对象动态构建聚合管道。
// Sample request
var request = {
"name":"RINGGO",
"year": 2017,
"month":3,
"week":12
};
// Build initial match document on name
var match1 = {
name: request["name"]
};
// Build project & facet document for date based aggregation
var addFields = {};
var facet = {};
// Add year followed by year facet
if (request["year"]) {
addFields["year"] = { "$year": "$date" },
facet["Yearly"] =
[
{
"$match":{ "year": request["year"] }
},
{
"$group": {
"_id": {
"name": "$name",
"year": "$year"
},
"spend": { "$push":"$amount" },
"total": { "$sum": "$amount" }
}
}
];
}
// Add month followed by month facet
if (request["month"]) {
addFields["month"] = { "$month": "$date" };
facet["Monthly"] =
[
{
"$match":{ "month": request["month"] }
},
{
"$group": {
"_id": {
"name": "$name",
"month": "$month"
},
"spend": { "$push":"$amount" },
"total": { "$sum": "$amount" }
}
}
];
}
// Add week followed by week facet
if (request["week"]) {
addFields["week"] = { "$week": "$date" };
facet["Weekly"] =
[
{
"$match":{ "week": request["week"] }
},
{
"$group": {
"_id": {
"name": "$name",
"week": "$week"
},
"spend": { "$push":"$amount" },
"total": { "$sum": "$amount" }
}
}
];
}
// Use aggregate builder
statements.aggregate()
.match(match1)
.append({"$addFields": addFields}) // No addFields stage in mongoose builder
.facet(facet)
.exec(function(err, data) {});
name/year/month/week 标准的 Mongo Shell 查询。
db.statements.aggregate({
'$match': {
name: 'RINGGO'
}
}, {
'$addFields': {
year: {
'$year': '$date'
},
month: {
'$month': '$date'
},
week: {
'$week': '$date'
}
}
}, {
'$facet': {
Yearly: [{
'$match': {
year: 2017
}
},
{
'$group': {
_id: {
name: '$name',
year: '$year'
},
spend: {
'$push': '$amount'
},
total: {
'$sum': '$amount'
}
}
}
],
Monthly: [{
'$match': {
month: 3
}
},
{
'$group': {
_id: {
name: '$name',
month: '$month'
},
spend: {
'$push': '$amount'
},
total: {
'$sum': '$amount'
}
}
}
],
Weekly: [{
'$match': {
week: 12
}
},
{
'$group': {
_id: {
name: '$name',
week: '$week'
},
spend: {
'$push': '$amount'
},
total: {
'$sum': '$amount'
}
}
}
]
}
})
示例响应
{
"Yearly": [{
"_id": {
"name": "RINGGO",
"year": 2017
},
"spend": [-3.3, -6.3, -3.3, -6.3, -3.3, -3.3],
"total": -25.799999999999997
}],
"Monthly": [{
"_id": {
"name": "RINGGO",
"month": 3
},
"spend": [-3.3, -6.3, -3.3, -6.3, -3.3, -3.3],
"total": -25.799999999999997
}],
"Weekly": [{
"_id": {
"name": "RINGGO",
"week": 12
},
"spend": [-6.3, -3.3],
"total": -9.6
}]
}
您可以为 Year/Month 和 Year 输入值运行类似的聚合。
So you can see that there is a different number of items in MonthlySpends in previous output compared to that shown in the output from the find by name. Also you can see that some of the values are being summed together in MonthlySpends when they shouldn't be.
这发生在 $group 1 中，其中 $week 聚合将两个日期 [15, 16] 中的每一个汇总到第 11 周和其他两个日期 [22, 23] ] 数额进入第 12 周后显示为 MonthySpends 中的总计。
关于node.js - Mongo/Mongoose 聚合 - $redact 和 $cond 问题，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/43950745/
我想为Heroku构建一个Rails3应用程序。他们使用Postgres作为他们的数据库，所以我通过MacPorts安装了postgres9.0。现在我需要一个postgresgem并且共识是出于性能原因你想要pggem。但是我对我得到的错误感到非常困惑当我尝试在rvm下通过geminstall安装pg时。我已经非常明确地指定了所有postgres目录的位置可以找到但仍然无法完成安装:$envARCHFLAGS='-archx86_64'geminstallpg--\--with-pg-config=/opt/local/var/db/postgresql90/defaultdb/po
尝试通过RVM将RubyGems升级到版本1.8.10并出现此错误:$rvmrubygemslatestRemovingoldRubygemsfiles...Installingrubygems-1.8.10forruby-1.9.2-p180...ERROR:Errorrunning'GEM_PATH="/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/ruby-1.9.2-p180@global:/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/rub
我的最终目标是安装当前版本的RubyonRails。我在OSXMountainLion上运行。到目前为止，这是我的过程:已安装的RVM$\curl-Lhttps://get.rvm.io|bash-sstable检查已知(我假设已批准)安装$rvmlistknown我看到当前的稳定版本可用[ruby-]2.0.0[-p247]输入命令安装$rvminstall2.0.0-p247注意:我也试过这些安装命令$rvminstallruby-2.0.0-p247$rvminstallruby=2.0.0-p247我很快就无处可去了。结果:$rvminstall2.0.0-p247Search
由于fast-stemmer的问题，我很难安装我想要的任何rubygem。我把我得到的错误放在下面。Buildingnativeextensions.Thiscouldtakeawhile...ERROR:Errorinstallingfast-stemmer:ERROR:Failedtobuildgemnativeextension./System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/bin/rubyextconf.rbcreatingMakefilemake"DESTDIR="cleanmake"DESTDIR=
当我尝试安装Ruby时遇到此错误。我试过查看this和this但无济于事➜~brewinstallrubyWarning:YouareusingOSX10.12.Wedonotprovidesupportforthispre-releaseversion.Youmayencounterbuildfailuresorotherbreakages.Pleasecreatepull-requestsinsteadoffilingissues.==>Installingdependenciesforruby:readline,libyaml,makedepend==>Installingrub
我正在尝试使用boilerpipe来自JRuby。我看过guide从JRuby调用Java，并成功地将它与另一个Java包一起使用，但无法弄清楚为什么同样的东西不能用于boilerpipe。我正在尝试基本上从JRuby中执行与此Java等效的操作:URLurl=newURL("http://www.example.com/some-location/index.html");Stringtext=ArticleExtractor.INSTANCE.getText(url);在JRuby中试过这个:require'java'url=java.net.URL.new("http://www
我意识到这可能是一个非常基本的问题，但我现在已经花了几天时间回过头来解决这个问题，但出于某种原因，Google就是没有帮助我。(我认为部分问题在于我是一个初学者，我不知道该问什么......)我也看过O'Reilly的RubyCookbook和RailsAPI，但我仍然停留在这个问题上.我找到了一些关于多态关系的信息，但它似乎不是我需要的(尽管如果我错了请告诉我)。我正在尝试调整MichaelHartl'stutorial创建一个包含用户、文章和评论的博客应用程序(不使用脚手架)。我希望评论既属于用户又属于文章。我的主要问题是:我不知道如何将当前文章的ID放入评论Controller。
首先回顾一下拉格朗日定理的内容：函数f(x)是在闭区间[a,b]上连续、开区间(a,b)上可导的函数，那么至少存在一个，使得:通过这个表达式我们可以知道，f(x)是函数的主体，a和b可以看作是主体函数f(x)中所取的两个值。那么可以有， 也就意味着我们可以用来替换 这种替换可以用在求某些多项式差的极限中。方法： 外层函数f(x)是一致的，并且h(x)和g(x)是等价无穷小。此时，利用拉格朗日定理，将原式替换为 ，再进行求解，往往会省去复合函数求极限的很多麻烦。使用要注意：1.要先找到主体函数f(x)，即外层函数必须相同。2.f(x)找到后，复合部分是等价无穷小。3.要满足作差的形式。如果是加
SPI接收数据左移一位问题目录SPI接收数据左移一位问题一、问题描述二、问题分析三、探究原理四、经验总结最近在工作在学习调试SPI的过程中遇到一个问题——接收数据整体向左移了一位（1bit）。SPI数据收发是数据交换，因此接收数据时从第二个字节开始才是有效数据，也就是数据整体向右移一个字节（1byte）。请教前辈之后也没有得到解决，通过在网上查阅前人经验终于解决问题，所以写一个避坑经验总结。实际背景：MCU与一款芯片使用spi通信，MCU作为主机，芯片作为从机。这款芯片采用的是它规定的六线SPI，多了两根线：RDY和INT，这样从机就可以主动请求主机给主机发送数据了。一、问题描述根据从机芯片手
文章目录git常用命令（简介，详细参数往下看）Git提交代码步骤gitpullgitstatusgitaddgitcommitgitpushgit代码冲突合并问题方法一：放弃本地代码方法二：合并代码常用命令以及详细参数gitadd将文件添加到仓库：gitdiff比较文件异同gitlog查看历史记录gitreset代码回滚版本库相关操作远程仓库相关操作分支相关操作创建分支查看分支：gitbranch合并分支：gitmerge删除分支：gitbranch-ddev查看分支合并图：gitlog–graph–pretty=oneline–abbrev-commit撤消某次提交git用户名密码相关配置g