MongoDB $组和显式组计算列 [英] MongoDB $group and explicit group formation with computed column

问题描述


;使用TableWithComputedColumn作为
（

b $ b选择

计算列=

案例

当工资<30000，然后'低于30K'

当工资> 30000和工资<60000然后'30K-60K'

其他'60K以上'

结束
来自Tbl_UserMaster

） >
从TableWithComputedColumn中选择computedcolumn，COUNT（*）


y groupcolumn


我想在Mongo中执行此操作。我的猜测是，CTE部分将要求我首先将 {computed column} 输出到临时集合，然后执行该集合上的 $ group 。另一种方式（以及我喜欢的方式）可以是使用 $ project 来投影 {computed column} 作为第一个然后执行 $ group 。


db.UM.aggregate（
{$ project：{computed_column：{// with $ cond}}}，

{$ group：{_id：{Salary_Slab： $ computed_column}，count：{$ sum：1}}}

）;


请同样通过我之前问过的类似问题：

MongoDB中的条件$ sum

它基本上给了我一个旋转输出。在这种情况下，我需要不透明的输出。为了比较两者，对另一个问题的答案将创建三列：{30K以下，30K-60K以上，60K以上}单行计数，而我需要三行以下30K以下，30K-60K以及60K以上在一列和他们各自的计数在第二列。

解决方案

你在正确的轨道上。在这种情况下，您需要嵌套 $ cond 运算符： > db.UM.aggregate（
{$ project：{
computed_column：{$ cond：[{$ gt：['$ Salary'，30000]}，
{$ cond：[{$ gt：['$ Salary'，60000]}，'60K以上'，'30K-60K']}，
'在30K以下]}
}}，
{$ group：{_id：{Salary_Slab：'$ computed_column'}，count：{$ sum：1}}}）


结果：
$ b

  [{_id：{Salary_Slab： '高于60K'}，计数：3}，
 {_id：{Salary_Slab：'30K-60K'}，count：1}，
 {_id：{Salary_Slab：'Under 30K' ：2}] 
  

Consider this code in SQL that forms groups based on a range of salary:

;With TableWithComputedColumn as (
select
computedcolumn =
Case
when Salary<30000 then 'Under 30K'
when Salary>30000 and Salary<60000 then '30K-60K'
else 'Above 60K'
end from Tbl_UserMaster
)
select computedcolumn, COUNT(*)
from TableWithComputedColumn group by computedcolumn

I want to do this in Mongo.. My guess is that the CTE part will require me to first output {computed column} to a temporary collection and then do a $group on that collection. Another way (and the one I prefer) could be to use $project to project {computed column} as the first stream in the aggregation pipeline and then perform $group.

db.UM.aggregate( {$project: { "computed_column": { //something with $cond}} },
{$group: {_id: {Salary_Slab:"$computed_column"}, count: {$sum:1}}}
);

Please also go through a similar question I had asked before:

Conditional $sum in MongoDB

It basically gives me a pivoted output. In this situation, I need unpivoted output. To compare the two, the answer to the other question will create three columns:{Under 30K, 30K-60K, Above 60K} with a single row of counts, whereas I need three rows for Under 30K, 30K-60K, and Above 60K in one column and their respective counts in the second column.

解决方案

You're on the right track. In this case you need to nest $cond operators like this:

db.UM.aggregate(
    { $project: {
        computed_column: {$cond: [{$gt: ['$Salary', 30000]},
            {$cond: [{$gt: ['$Salary', 60000]}, 'Above 60K', '30K-60K']},
            'Under 30K']}
    }},
    { $group: {_id: {Salary_Slab: '$computed_column'}, count: {$sum: 1}}})

results:

[ { _id: { Salary_Slab: 'Above 60K' }, count: 3 },
  { _id: { Salary_Slab: '30K-60K' }, count: 1 },
  { _id: { Salary_Slab: 'Under 30K' }, count: 2 } ]

这篇关于MongoDB $组和显式组计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！