aggregation 연산과 활용 예제

6. aggregation 연산과 활용 예제

* 산술연산
  - $sum : 각 문서에서 해당 필드의 값을 합함
  - $avg : 각 문서에서 해당 필드의 평균을 구함
* 극한연산
  - $max : 입력값 중 최대값 반환
  - $min : 입력값 중 최소값 반환
  - $first : 그룹의 첫번째 값 반환 
  - $last : 그룹의 마지막 값 반환
  - first, last 연산자의 경우에는 보통 sort 후 사용
* 배열연산
  - $addToSet : 해당 값이 없는 경우, 배열에 추가. 순서 보장 하지 않음
  - $push : 차례대로 배열에 추가
  • 1시간 단위의 날씨 데이터가 있고, 하루 동안의 평균 기온을 찾고자 한다면 'day'로 묶을 수 있다.
  • 도 / 시별 인구 데이터가 있고, 시별 총 인구의 합을 구하려면 'state'와 'city'로 묶을 수 있다.
  • 학생들 성적이 있고, 전공별로 성적을 구분하려고 한다면, major필드로 묶을 수 있다.

  • 그루핑하게 될 필드들을 선택한 뒤에는 $group 함수의 _id 필드로 전달하여 해당 필드로 그루핑 한다.

  • 이때, 단순히 그루핑만 하는 것은 의미가 없고, 여러가지 연산자를 이용하여 그룹에 대한 결과를 계산할 수 있다.

6.1. sum 예제

# 이전 예제로 상기 (GROUP BY state)
result = db.zip.aggregate([
             {'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}}, 
             {'$limit' : 5}
])
for record in result:
    print(record)
# 이전 예제로 상기 (GROUP BY state, city)
# 컬럼에 도시 하나에도 여러 세부적으로 나뉘어 인구가 지정되어 있으므로, state와 city 두 기준으로 그룹핑을 해야 함
# {'state': 'MA', 'city': 'BOSTON', 'pop': 10246}
# {'state': 'MA', 'city': 'BOSTON', 'pop': 17459}
# {'state': 'MA', 'city': 'BOSTON', 'pop': 17769}
# {'state': 'MA', 'city': 'BOSTON', 'pop': 25597}
result = db.zip.aggregate([
    {'$group' : {'_id' : {'state' : '$state', 'city' : '$city'}, 'total_pop' : {'$sum' : '$pop'}}},
    {'$limit' : 5}    
])
for record in result:
    print(record)
# 이전 예제로 상기 (GROUP BY state, city HAVING city = 'POINT BAKER')
# $match 에서 기준점인 city를 _id.city 로 표기
# {'_id': {'state': 'AK', 'city': 'POINT BAKER'}, 'total_pop': 426} 이 결과를 기반으로 파이프라인
result = db.zip.aggregate([
    {'$group' : {'_id' : {'state' : '$state', 'city' : '$city'}, 'total_pop' : {'$sum' : '$pop'}}},
    {'$match' : {'_id.state' : 'AK'}}    
])

for record in result:
    print(record)
# 이전 예제로 상기 (GROUP BY state, city HAVING SUM(pop) = 426)
# $match 에서 기준점인 city를 _id.city 로 표기
# {'_id': {'state': 'AK', 'city': 'POINT BAKER'}, 'total_pop': 426} 이 결과를 기반으로 파이프라인
result = db.zip.aggregate([
    {'$group' : {'_id' : {'state' : '$state', 'city' : '$city'}, 'total_pop' : {'$sum' : '$pop'}}},
    {'$match' : {'total_pop' : 426}}    
])

for record in result:
    print(record)

6.2. 실제 예제3: 주별 도시 인구 평균 구하기 - avg 예제

result = db.zip.aggregate([
    {'$group' : {'_id' : 'null', 'avg_pop' : {'$avg' : '$pop'}}}
])
for record in result:
    print(record)
# 이전 예제로 상기 (SELECT AVG(pop) FROM zip GROUP BY state, city)
# 주별 도시 인구 평균 구하기 (컬럼에 도시 하나에도 여러 세부적으로 나뉘어 인구가 지정되어 있으므로, state와 city 두 기준으로 그룹핑을 해야 함)
# {'state': 'MA', 'city': 'BOSTON', 'pop': 10246}
# {'state': 'MA', 'city': 'BOSTON', 'pop': 17459}
# {'state': 'MA', 'city': 'BOSTON', 'pop': 17769}
# {'state': 'MA', 'city': 'BOSTON', 'pop': 25597}

result = db.zip.aggregate([
    {'$group' : {'_id' : {'state' : '$state', 'city' : '$city'}, 'avg_pop' : {'$avg' : '$pop'}}},
    {'$limit' : 5}
])
for record in result:
    print(record)
result = db.zip.aggregate([
    {'$group' : {'_id' : {'state' : '$state', 'city' : '$city'}, 'avg_pop' : {'$avg' : '$pop'}}},
    {'$match' : {'_id.state' : 'MA'}},
    {'$project' : {'_id' : 0}},
    {'$limit' : 5}    
])
for record in result:
    print(record)
이해하고 실습하기
주별 도시 인구 평균이 30000 이 넘는 곳의 주와 도시 이름만 출력하기 (3개만 출력하기)
result = db.zip.aggregate([
    {'$group' : {'_id' : {'state' : '$state', 'city' : '$city'}, 'avg_pop' : {'$avg' : '$pop'}}},
    {'$match' : {'avg_pop' : { '$gte' : 30000 } } },
    {'$project' : {'avg_pop' : 0 }},
    {'$limit' : 3}    
])
for record in result:
    print(record)

6.3. max, min 예제

# 이전 예제로 상기 (SELECT MAX(pop), MIN(pop) FROM zip GROUP BY state)

result = db.zip.aggregate([
    {'$group' : {'_id' : '$state', 'maximum' : {'$max' : '$pop'}, 'minimum' : {'$min' : '$pop'} }}
])
for record in result:
    print(record)

6.4. first, last 예제

# 이전 예제로 상기 (first: 첫번째 document 데이터, last: 마지막 document 데이터) 

result = db.zip.aggregate([
    {'$group' : {'_id' : '$state', 'first' : {'$first' : '$pop'}, 'last' : {'$last' : '$pop'} } },
])
for record in result:
    print(record)

6.5. addToSet, push 예제

# push: 전체 값을 하나의 배열로 만들기
# CA 주에 있는 도시 이름을 배열로 뽑기
result = db.zip.aggregate([
    {'$group' : {'_id' : '$state', 'cityset' : {'$push' : '$city'} } },
    {'$match' : {'_id' : 'CA'}}
])
for record in result:
    print(record)
# addToSet: 전체 값을 하나의 배열로 만들기, $push와 동일하지만, 중복된 데이터는 추가하지 않음
# CA 주에 있는 도시 이름을 배열로 뽑기
result = db.zip.aggregate([
    {'$group' : {'_id' : '$state', 'cityset' : {'$addToSet' : '$city'} } },
    {'$match' : {'_id' : 'CA'}}
])
for record in result:
    print(record)

6.6. 실제 예제4: 주(state)별 최대, 최소 인구 수(pop)를 가진 city 구하기

주(state)별로 도시 평균 인구 구하기

result = db.zip.aggregate([
    {'$group' : 
        {
            '_id' : '$state',
            'avg_city_pop' : { '$avg' : '$pop'}
        }
    },
    {'$limit' : 200} 
])
for record in result:
    print(record)

주(state)와 도시(city)를 기준으로 도시별 인구 수(pop) 구해서 정렬하기

result = db.zip.aggregate([
    {'$group' : 
        {
            '_id' : { 'state' : '$state', 'city' : '$city'},
            'pop' : { '$sum' : '$pop'}
        }
    },
    { '$sort': { 'pop': -1 } },    
    {'$limit' : 10} 
])
for record in result:
    print(record)

주(state)와 도시(city)를 기준으로 도시별 인구 수(pop) 구해서 정렬한 후에, 주(state)를 기준으로 last, fisrt를 가져오면 인구최대/최소 도시를 구할 수 있음

# 파이프라인으로 생각해봅니다.

# SQL로도 한번 생각만 해봅니다. (특별히 이 예제에 큰 도움이 되지는 않지만...)
# MAX, MIN을 구하면?
# SELECT zip.city, zip.pop 
# FROM 
#    zip,
#    (SELECT MAX(pop) AS max_pop FROM zip GROUP BY state) AS zip2,
#    (SELECT MIN(pop) AS min_pop FROM zip GROUP BY state) AS zip3
# WHERE 
#    zip.pop = zip2.max_pop OR zip.pop = zip3.min_pop
result = db.zip.aggregate([
    {'$group' : 
        {
            '_id' : { 'state' : '$state', 'city' : '$city'},
            'pop' : { '$sum' : '$pop'}
        }
    },
    { '$sort': { 'pop': 1 } },
    { '$group':
        {
            '_id' : "$_id.state",
            'biggest_city':  { '$last': "$_id.city" },
            'biggest_pop':   { '$last': "$pop" },
            'smallest_city': { '$first': "$_id.city" },
            'smallest_pop':  { '$first': "$pop" }
        }
    },    
    {'$limit' : 5} 
])
for record in result:
    print(record)

주(state)와 도시(city)를 기준으로 도시별 인구 수(pop) 구해서 정렬한 후에, 주(state)를 기준으로 last, fisrt를 가져오면 인구최대/최소 도시를 구하되, 출력은 주(state 이름), 최대 인구 가진 도시, 최소 인구 가진 도시 출력하기

result = db.zip.aggregate([
    {'$group' : 
        {
            '_id' : { 'state' : '$state', 'city' : '$city'},
            'pop' : { '$sum' : '$pop'}
        }
    },
    { '$sort': { 'pop': 1 } },
    { '$group':
        {
            '_id' : "$_id.state",
            'biggest_city':  { '$last': "$_id.city" },
            'biggest_pop':   { '$last': "$pop" },
            'smallest_city': { '$first': "$_id.city" },
            'smallest_pop':  { '$first': "$pop" }
        }
    },    
    { '$project':
        { 
            '_id' : 0,
            'state' : "$_id",
            'biggest_city' :  { 'name' : "$biggest_city",  'pop': "$biggest_pop" },
            'smallest_city' : { 'name' : "$smallest_city", 'pop': "$smallest_pop" }
        }
    },
    {'$limit' : 5} 
])
for record in result:
    print(record)