Shulou(Shulou.com)06/03 Report--

MongoDB often encounters the problem that the shell script is too complex when facing some complicated calculation situations when doing grouping statistics. On the other hand, the aggregator SPL language can make up for the deficiency of Mongo because of its rich function library and ease of use.

As a NoSql document database, MongoDB has been widely supported and applied all over the world. Among the more commonly used database functions, compared with ordinary additions, deletions, modifications and queries, using group aggregate grouping statistics is somewhat complex, and MongoDB also supports it. This paper will analyze the implementation methods and examples of MongoDb grouping, and make a simple summary by operating in MongoDB script and using aggregator SPL language. The specific problem scenarios include the following aspects:

1. Statistics of embedded array structure. one

two。 The sum of embedded documents. two

3. Segment grouping structure statistics. four

4. Multi-field grouping statistics. six

1. Statistics of embedded array structure

Perform statistical processing on the data in the nested array structure, such as querying the average score of the examination subjects and the total score of each student:

Test data:

_ idnamesexScroe1TomF [{"lesson": "Physics", "mark": 60}

{"lesson": "Chemical", "mark": 72}] 2JerryM [{"lesson": "Physics", "mark": 92}

{"lesson": "Math", "mark": 81}] expect statistical results:

Physics76

Tom132Chemical72

Jerry173Math81

Mongodb script:

Db.student.aggregate ([

{$unwind: "$scroe"}

{$group: {

"_ id": {"lesson": "$scroe.lesson"}

"qty": {"$avg": "$scroe.mark"}

}

}

])

Db.student.aggregate ([

{$unwind: "$scroe"}

{$group: {

"_ id": {"name": "$name"}

"qty": {"$sum": "$scroe.mark"}

}

}

])

As the scroe of each subject is based on the array structure of course items and achievement records, it is necessary to disassemble it before statistics, correspond the scores of each subject to the students, and then realize the grouping calculation. This requires familiarity with the combined application of unwind and group.

SPL script (student.dfx):

AB1=mongo_open ("mongodb://127.0.0.1:27017/raqdb")

2=mongo_shell (A1, "student.find ()"). Fetch ()

3=A2.conj (scroe) .groups (lesson:LESSON;avg (mark): AVG)

4=A2.new (name:NAME,scroe.sum (mark): TOTAL)

5 > A1.close ()

Total score by subject:

LESSONAVGChemical72.0Math81.0Physics76.0

The total score of each student:

NAMETOTALTom132Jerry173

Script description:

A1: connect to the mongodb database.

A2: get the data in the student table.

A3: merge the scroe data into a sequence table, then group by course, and calculate the average score.

A4: after counting the scores of each student, return the order table named NAME and TOTAL. The new function means to generate a new ordinal table.

A5: close the database connection.

This example of nested structure statistics is more common, I believe many people have encountered, need to disassemble and then group calculation, mainly familiar with the mongodb processing of nested data structures.

two。 Embedded document summation

Sum the data in the embedded document, such as counting the sum of the number of income,output in each record below.

Test data:

_ idincomeoutput1 {"cpu": 1000, "mem": 1000, "mouse": "100"} {"cpu": 1000, "mem": 600,120 "mouse"} 2 {"cpu": 2000, "mem": 1000

"mouse": "50", "mainboard": 500} {"cpu": 1500, "mem": 300}

Expected statistical results:

_ id

Income

Output

one

1600

1720

two

3550

1800

Mongodb script:

Var fields = ["income", "output"]

Db.computer.aggregate ([

{

$project: {

"values": {

$filter: {

Input: {

"$objectToArray": "$$ROOT"

}

Cond: {

$in: [

"$$this.k"

Fields

]

}

}

}

}

}

{

$unwind: "$values"

}

{

$project: {

Key: "$values.k"

Values: {

"$sum": {

"$let": {

"vars": {

"item": {

"$objectToArray": "$values.v"

}

}

"in": "$item.v"

}

}

}

}

}

{$sort: {"_ id":-1}}

{"$group": {

"_ id": "$_ id"

'income': {"$first": "$values"}

"output": {"$last": "$values"}

}}

])

Filter stores part of the income,output information in an array, disassembles it into records with unwind, accumulates and sums up the values, and merges the data according to _ id.

SPL script:

AB1=mongo_open ("mongodb://127.0.0.1:27017/raqdb")

2=mongo_shell (A1, "computer.find ()"). Fetch ()

3=A2.new (_ id:ID,income.array () .sum (): INCOME,output.array () .sum (): OUTPUT)

4 > A1.close ()

Statistical results

IDINCOMEOUTPUT11600.01720.023550.01800.0

Script description:

A1: connect to the database

A2: get data from the computer table

A3: convert the data in income and output fields into sequence summation respectively, and then combine with ID to generate a new order table.

A4: close the database connection.

Getting the field value of the child record and then summing it is much simpler than the mongo script. This embedded document is somewhat similar to the embedded array in organization, and it is easy to be confused accidentally, so you need to pay special attention to the different scripts written compared to the scroe array structure in the example above.

3. Segment grouping structure statistics

Count the number of records in each paragraph. For example, the volume of data in each segment is calculated by sales volume. The data are as follows:

_ idNAMESTATESALES1AshleyNew York110002RachelMontana90003EmilyNew York88004MatthewTexas80005AlexisIllinois14000

Segmentation method: 0-3000-5000-5000-7500-10000 or more.

Expected results:

Segmentnumber3342

Mongo script

Var a_count=0

Var b_count=0

Var c_count=0

Var d_count=0

Var e_count=0

Db.sales.find ({

}. ForEach (

Function (myDoc) {

If (myDoc.SALES

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.