3. 몽고 DB - aggregation (집계, 합산)

https://ozofweird.tistory.com/entry/MongoDB-Aggregation-Pipeline

[MongoDB] Aggregation Pipeline

 

* match 파이프라인 실습

[
  { "author" : "dave", "score" : 80, "views" : 100 },
  { "author" : "dave", "score" : 85, "views" : 521 },
  { "author" : "ahn", "score" : 60, "views" : 1000 },
  { "author" : "li", "score" : 55, "views" : 5000 },
  { "author" : "annT", "score" : 60, "views" : 50 },
  { "author" : "li", "score" : 94, "views" : 999 },
  { "author" : "ty", "score" : 95, "views" : 1000 }
]

match 파이프라인 실습을 위하여 mongo compass 에서 articles 컬렉션을 생성하고 위의 도큐먼트들을 복사하고, 추가하자!

articles 컬렉션

db.articles.aggregate(
    [ { $match : { author : "dave" } } ]
);

$match 파이프라인은 조건에 맞는 도큐먼트들만 추출한다. 

$match 파이프라인 결과

 

* group 파이프라인 실습

db.sales.insertMany(
    [
      { "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-03-01T08:00:00Z") },
      { "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-03-01T09:00:00Z") },
      { "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-03-15T09:00:00Z") },
      { "_id" : 4, "item" : "xyz", "price" : 5, "quantity" : 20, "date" : ISODate("2014-04-04T11:21:39.736Z") },
      { "_id" : 5, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-04-04T21:23:13.331Z") }
    ]
)

group 파이프라인 실습을 위하여 mongosh 에서 sales 컬렉션을 생성하고 위의 도큐먼트들을 추가하자! MongoDB Compass 에서는 ISODate 같은 함수가 제대로 동작하지 않으므로 mongosh 에서 추가하였다. 

db.sales.aggregate(
   [
      {
        $group : {
           _id : { month: { $month: "$date" }, day: { $dayOfMonth: "$date" }, year: { $year: "$date" } },
           totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } },
           averageQuantity: { $avg: "$quantity" },
           count: { $sum: 1 }
        }
      }
   ]
)

$group 파이프라인은 _id 에 설정된 기준(카테고리)으로 도큐먼트들을 그룹핑한다. 해당 명령어는 년/월/일을 기준으로 그룹핑한다. 

또한, 특정 필드에 대한 집계 연산이 가능하다. 각 그룹마다 도큐먼트의 $price와 $quantity를 곱한 값의 합을 totalPrice 필드로 지정한다.  이에 더해, 각 그룹마다 $quantity 필드값의 평균을 averageQuantity 필드로 지정하고, 그룹별 데이터의 갯수를 count로 지정한다. 단, 연산된 도큐먼트에 대한 정렬은 지원하지 않는다.

$group 파이프라인 결과

 

 

* project 파이프라인 실습

{
  "_id" : 1,
  "title": "abc123",
  "isbn": "0001122223334",
  "author": { "last": "zzz", "first": "aaa" },
  "copies": 5
}

mongo compass 에서 books 컬렉션을 생성하고, 위의 도큐먼트를 추가한다. 

books 컬렉션

db.books.aggregate( [ { $project : { title : 1 , author : 1 } } ] )

$project 파이프라인은 지정한 필드를 다음 파이프라인에 전달하거나, 전달하지 않는다. 필드값이 1이면 다음 파이프라인에 전달한다.

$project 파이프라인 결과

db.books.aggregate(
   [
      {
         $project: {
            title: 1,
            isbn: {
               prefix: { $substr: [ "$isbn", 0, 3 ] },
               group: { $substr: [ "$isbn", 3, 2 ] },
               publisher: { $substr: [ "$isbn", 5, 4 ] },
               title: { $substr: [ "$isbn", 9, 3 ] },
               checkDigit: { $substr: [ "$isbn", 12, 1] }
            },
            lastName: "$author.last",
            copiesSold: "$copies"
         }
      }
   ]
)

$project 파이프라인은 아래와 같이 화면에 출력될 결과를 reshape 해주는 용도로 사용하기도 한다. 

$project 파이프라인을 이용한 reshape

books 컬렉션의 도큐먼트들은 title 필드값만 원래의 값을 출력한다. isbn 필드값은 원본값을 그대로 사용하지 않고, 파싱해서 보여준다. 또한, author 와 copies 필드는 결과에 보여주지 않는다. 마지막으로 lastName, copiesSold 필드가 추가되었다. 

 

 

* sort 파이프라인 실습

[
  { "_id" : 1, "subject" : "History", "score" : 88 },
  { "_id" : 2, "subject" : "History", "score" : 92 },
  { "_id" : 3, "subject" : "History", "score" : 97 },
  { "_id" : 4, "subject" : "History", "score" : 71 },
  { "_id" : 5, "subject" : "History", "score" : 79 },
  { "_id" : 6, "subject" : "History", "score" : 83 }
]

mongo compass 에서 users 컬렉션을 생성하고, 위의 도큐먼트를 추가한다. 

users 컬렉션

 

db.users.aggregate(
   [
     { $sort : { score : -1} }
   ]
)

$sort 파이프라인은 도큐먼트들을 특정 필드 기준으로 오름차순(1), 내림차순(-1) 정렬한다. 

$sort 파이프라인 결과

 

 

* skip 파이프라인 실습

db.articles.aggregate({ $skip: 3 })

$skip 파이프라인은 입력한 숫자만큼 도큐먼트들을 건너뛰고, 남은 도큐먼트들을 다음 파이프라인으로 전달한다. 

$skip 파이프라인 결과

 

db.articles.aggregate([
 { $skip: 3 },
 { $group: {
     _id: "$author",
     count: { $sum: 1 },
     totalScore: { $sum: "$score" },
     totalViews: { $sum: "$views" },
 }}
])

$skip 파이프라인으로 건너띄고, 남은 도큐먼트들을 $author 기준으로 그룹핑하면 아래와 같은 결과가 나온다. 

$skip, $group 파이프라인을 사용한 결과

 

* sample 파이프라인 실습

db.articles.aggregate([ { $sample: { size: 3 } }] )

$sample 파이프라인은 컬렉션에서 주어진 갯수만큼 랜덤한 도큐먼트를 선택해서 추출한다. 

$sample 파이프라인 결과

 

* count 파이프라인 실습

db.users.aggregate(
  [
    {
      $match: {
        score: {
          $gt: 80
        }
      }
    },
    {
      $count: "passing_scores"
    }
  ]
)

$count 파이프라인은 현재 파이프라인 단계에서 도큐먼트의 갯수를 카운팅한다. 

$count 파이프라인 결과

 

 

* addFields 파이프라인 실습

[
  {
    "_id": 1,
    "student": "Maya",
    "homework": [ 10, 5, 10 ],
    "quiz": [ 10, 8 ],
    "extraCredit": 0
  },
  {
    "_id": 2,
    "student": "Ryan",
    "homework": [ 5, 6, 5 ],
    "quiz": [ 8, 8 ],
    "extraCredit": 8
  }
]

mongo compass 에서 scores 컬렉션을 생성하고, 위의 도큐먼트를 추가한다. 

scores 컬렉션

db.scores.aggregate( [
   {
     $addFields: {
       totalHomework: { $sum: "$homework" } ,
       totalQuiz: { $sum: "$quiz" }
     }
   },
   {
     $addFields: { totalScore:
       { $sum: [ "$totalHomework", "$totalQuiz", "$extraCredit" ] } }
   }
] )

$addFields 파이프라인은 도큐먼트에 새로운 필드를 추가한다. 단, 원본 도큐먼트가 변경되는 것이 아니라 집계할때 조회를 위한 용도로 사용한다. 

현재는 totalHomework, totalQuiz, totalScore 필드가 추가된다. 이때 totalScore 필드는 이전에 집계된 totalHomework, totalQuiz 필드가 존재해야 계산이 가능하므로 다음 파이프라인에서 수행한다. $sum 은 도큐먼트의 homework 필드(배열)의 합계를 계산한다. 또한, $sum 은 도큐먼트의 quiz 필드(배열)의 합계를 계산한다. 마지막으로 $sum 은 도큐먼트의 totalHomework, totalQuiz, extraCredit 필드값의 합계를 계산한다. 

$addFields 파이프라인 결과

 

https://www.mongodb.com/docs/manual/reference/operator/aggregation/sum/

$sum (aggregation) — MongoDB Manual

sum 문법

$sum 이 accumulator 로 사용될때는 $group 파이프라인으로 모든 도큐먼트들의 필드값을 합산하는 경우이다. 또한, $sum 이 accumulator 로 사용되지 않을때는 배열에 있는 엘리먼트들의 합계를 구해준다. 

 

* dateToString 

https://www.mongodb.com/docs/manual/reference/operator/aggregation/dateToString/

$dateToString (aggregation) — MongoDB Manual

date 객체를 문자열 포맷으로 변경할때 사용한다. 

 

* limit 파이프라인 실습

db.articles.aggregate([
 { $skip: 3 },
 { $group: {
     _id: "$author",
     count: { $sum: 1 },
     totalScore: { $sum: "$score" },
     totalViews: { $sum: "$views" },
 }},
 { $limit: 2 }
])

$limit 파이프라인은 출력된 결과에서 도큐먼트 갯수를 제한한다. 앞선 예제에서 $skip, $group 으로 연산된 결과는 아래와 같다. 

$skip, $group 파이프라인을 사용한 결과

여기서 출력되는 도큐먼트 갯수를 2개로 제한하면 아래와 같다. 

$skip, $group, $limit 파이프라인 결과

 

 

* unwind 파이프라인 실습

scores 컬렉션

db.scores.aggregate( [ { $unwind : "$quiz" } ] )

$unwind 파이프라인은 도큐먼트의 배열필드 (quiz) 를 기반으로 도큐먼트를 분리한다.

$unwind 파이프라인 결과

 

 

 

aggregate 실습문제

아래 주어진 샘플 데이터를 활용하여 오늘 배운 쿼리 연산자와 Aggregation 을 연습해보세요!
그룹핑 및 통계 기준 : Item Type (상품명), Sales Channel (오프라인/온라인), Order Date (주문날짜), Ship Date (배송일)

대용량의 데이터가 필요하신 분은 아래 링크에서 다운로드 받으시면 됩니다.
https://excelbianalytics.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/

Downloads 18 - Sample CSV Files / Data Sets for Testing (till 5 Million Records) - Sales

 

 

* aggregation 실습에 대한 보충학습하기 

aggregate 그룹핑시 문자열 날짜를 날짜 포맷으로 변경하기

[
	{ $group: {
         _id: { year: { $year: {$toDate: "$Order Date"} }, month: { $month: {$toDate: "$Order Date" }}, day: { $dayOfMonth: {$toDate: "$Order Date" }} },
         averageTotalCost: { $avg: "$Total Cost" },
         count: { $sum: 1 } 
     } }
]

위 코드는 "Order Date" 필드의 날짜가 문자열이므로 $toDate accumulator 를 사용하여 Date 포맷으로 한번 변경해주고 $year, $month, $dayOfMonth accumulator 를 사용하여 년, 월, 일을 추출한다. 

 

1000개의 도큐먼트로 그룹핑한 결과

sales 컬렉션에서 1000개의 도큐먼트로 그룹핑한 결과는 위와 같다. 

그룹핑 결과 검사하기

제대로 그룹핑되었는지 검사하기 위하여 2012년 8월 14일의 결과만 추출해보면 2개가 쿼리된다. 또한, 계산기로 해당 그룹의 Total Cost 필드값의 평균을 계산해보면 아래와 같이 aggregation 결과와 일치한다. 

Total Cose 평균값 결과

 

 

aggregate 그룹핑시 필드의 값만 가져와서 새로운 필드에 설정하고 싶은 경우

db.sales.aggregate([ 
     { $group: 
        { _id: 
              { year: { $year: { $toDate: "$Order Date" } }, 
                month: { $month: { $toDate: "$Order Date" } }, 
                day: { $dayOfMonth: { $toDate: "$Order Date" } } 
              }, 
          count : { $sum: 1 }, 
          unitsSold : { $sum : "$Units Sold" }, 
          type: { $addToSet: "$Item Type"}  
        } 
     } 
])

그룹핑된 새로운 도큐먼트의 type 필드에 기존 도큐먼트들의 "Item Type" 필드값을 그대로 복사하고 싶으면 위와 같이 $addToSet accumulator 를 사용하면 된다. 

1000개의 도큐먼트로 그룹핑한 결과

sales 컬렉션에서 1000개의 도큐먼트로 그룹핑한 결과는 위와 같다. 

그룹핑 결과 검사하기

제대로 그룹핑되었는지 검사하기 위하여 2012년 8월 14일의 결과만 추출해보면 2개가 쿼리된다. 또한, 계산기로 해당 그룹의 Units Sold 필드값의 합계를 계산해보면 아래와 같이 aggregation 결과와 일치한다. 

Units Sold 필드의 합계

또한, type 필드를 확인하면 해당 그룹의 Item Type 필드값을 배열에 중복없이 추가하였다. 중복되는 값은 모두 제거하고 다른 경우에만 추가한다.