aggregation 연산과 활용 예제

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.

잔재미코딩 소식 공유

좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 강의 사이트와 유투브 채널을 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다

● 잔재미코딩 유투브 오픈 [구독해보기]

6. aggregation 연산과 활용 예제¶

* 산술연산
  - $sum : 각 문서에서 해당 필드의 값을 합함
  - $avg : 각 문서에서 해당 필드의 평균을 구함
* 극한연산
  - $max : 입력값 중 최대값 반환
  - $min : 입력값 중 최소값 반환
  - $first : 그룹의 첫번째 값 반환 
  - $last : 그룹의 마지막 값 반환
  - first, last 연산자의 경우에는 보통 sort 후 사용
* 배열연산
  - $addToSet : 해당 값이 없는 경우, 배열에 추가. 순서 보장 하지 않음
  - $push : 차례대로 배열에 추가

1시간 단위의 날씨 데이터가 있고, 하루 동안의 평균 기온을 찾고자 한다면 'day'로 묶을 수 있다.
도 / 시별 인구 데이터가 있고, 시별 총 인구의 합을 구하려면 'state'와 'city'로 묶을 수 있다.
학생들 성적이 있고, 전공별로 성적을 구분하려고 한다면, major필드로 묶을 수 있다.
그루핑하게 될 필드들을 선택한 뒤에는 $group 함수의 _id 필드로 전달하여 해당 필드로 그루핑 한다.
이때, 단순히 그루핑만 하는 것은 의미가 없고, 여러가지 연산자를 이용하여 그룹에 대한 결과를 계산할 수 있다.

6.1. sum 예제¶

In [40]:

# 이전 예제로 상기 (GROUP BY state)
result = db.zip.aggregate([
             {'$group' : {'_id' : '$state', 'total_pop' : {'$sum' : '$pop'}}}, 
             {'$limit' : 5}
])
for record in result:
    print(record)

{'_id': 'CA', 'total_pop': 29754890}
{'_id': 'MT', 'total_pop': 798948}
{'_id': 'MS', 'total_pop': 2573216}
{'_id': 'FL', 'total_pop': 12686644}
{'_id': 'AR', 'total_pop': 2350725}

In [15]:

# 이전 예제로 상기 (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)

{'_id': {'state': 'AK', 'city': 'POINT BAKER'}, 'total_pop': 426}
{'_id': {'state': 'AK', 'city': 'SKAGWAY'}, 'total_pop': 692}
{'_id': {'state': 'AK', 'city': 'SITKA'}, 'total_pop': 8638}
{'_id': {'state': 'AK', 'city': 'GUSTAVUS'}, 'total_pop': 258}
{'_id': {'state': 'AK', 'city': 'ANGOON'}, 'total_pop': 1002}

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

In [ ]:

# 이전 예제로 상기 (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)

In [19]:

# 이전 예제로 상기 (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)

{'_id': {'state': 'AK', 'city': 'POINT BAKER'}, 'total_pop': 426}
{'_id': {'state': 'NE', 'city': 'DIX'}, 'total_pop': 426}
{'_id': {'state': 'ND', 'city': 'ANETA'}, 'total_pop': 426}
{'_id': {'state': 'SD', 'city': 'DIMOCK'}, 'total_pop': 426}
{'_id': {'state': 'ME', 'city': 'PHIPPSBURG'}, 'total_pop': 426}
{'_id': {'state': 'PA', 'city': 'BLAKESLEE'}, 'total_pop': 426}
{'_id': {'state': 'KY', 'city': 'SKYLINE'}, 'total_pop': 426}
{'_id': {'state': 'NE', 'city': 'GRESHAM'}, 'total_pop': 426}

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

In [61]:

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

{'_id': 'null', 'avg_pop': 8462.794262937348}

In [20]:

# 이전 예제로 상기 (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)

{'_id': {'state': 'AK', 'city': 'POINT BAKER'}, 'avg_pop': 426.0}
{'_id': {'state': 'AK', 'city': 'SKAGWAY'}, 'avg_pop': 692.0}
{'_id': {'state': 'AK', 'city': 'SITKA'}, 'avg_pop': 8638.0}
{'_id': {'state': 'AK', 'city': 'GUSTAVUS'}, 'avg_pop': 258.0}
{'_id': {'state': 'AK', 'city': 'ANGOON'}, 'avg_pop': 1002.0}

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

In [22]:

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)

{'avg_pop': 3314.0}
{'avg_pop': 2792.0}
{'avg_pop': 23106.5}
{'avg_pop': 9557.0}
{'avg_pop': 36963.0}

이해하고 실습하기
주별 도시 인구 평균이 30000 이 넘는 곳의 주와 도시 이름만 출력하기 (3개만 출력하기)

In [25]:

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)

{'_id': {'state': 'WA', 'city': 'WALLA WALLA'}}
{'_id': {'state': 'WA', 'city': 'LONGVIEW'}}
{'_id': {'state': 'WA', 'city': 'LACEY'}}

6.3. max, min 예제¶

In [ ]:

# 이전 예제로 상기 (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)

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

6.4. first, last 예제¶

In [ ]:

# 이전 예제로 상기 (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 예제¶

In [ ]:

# push: 전체 값을 하나의 배열로 만들기
# CA 주에 있는 도시 이름을 배열로 뽑기
result = db.zip.aggregate([
    {'$group' : {'_id' : '$state', 'cityset' : {'$push' : '$city'} } },
    {'$match' : {'_id' : 'CA'}}
])
for record in result:
    print(record)

In [ ]:

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

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

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

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

In [29]:

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

{'_id': 'CA', 'avg_city_pop': 19627.236147757256}
{'_id': 'MT', 'avg_city_pop': 2544.420382165605}
{'_id': 'MS', 'avg_city_pop': 7088.749311294766}
{'_id': 'FL', 'avg_city_pop': 15779.407960199005}
{'_id': 'AR', 'avg_city_pop': 4066.998269896194}
{'_id': 'GA', 'avg_city_pop': 10201.914960629922}
{'_id': 'WA', 'avg_city_pop': 10055.148760330578}
{'_id': 'SC', 'avg_city_pop': 9962.00857142857}
{'_id': 'MN', 'avg_city_pop': 4958.02947845805}
{'_id': 'NE', 'avg_city_pop': 2749.3710801393727}
{'_id': 'MD', 'avg_city_pop': 11384.235714285714}
{'_id': 'TN', 'avg_city_pop': 8378.792096219931}
{'_id': 'DE', 'avg_city_pop': 12569.207547169812}
{'_id': 'DC', 'avg_city_pop': 25287.5}
{'_id': 'AZ', 'avg_city_pop': 13574.918518518518}
{'_id': 'ME', 'avg_city_pop': 2991.8243902439026}
{'_id': 'OR', 'avg_city_pop': 7401.877604166667}
{'_id': 'AL', 'avg_city_pop': 7126.255731922399}
{'_id': 'PA', 'avg_city_pop': 8149.275034293552}
{'_id': 'RI', 'avg_city_pop': 14539.391304347826}
{'_id': 'MA', 'avg_city_pop': 12692.879746835442}
{'_id': 'NJ', 'avg_city_pop': 14315.162962962962}
{'_id': 'NY', 'avg_city_pop': 11279.248902821317}
{'_id': 'ND', 'avg_city_pop': 1632.4092071611253}
{'_id': 'VT', 'avg_city_pop': 2315.8765432098767}
{'_id': 'NM', 'avg_city_pop': 5489.380434782609}
{'_id': 'CT', 'avg_city_pop': 12498.539923954373}
{'_id': 'KY', 'avg_city_pop': 4543.243510506799}
{'_id': 'OH', 'avg_city_pop': 10771.119165839125}
{'_id': 'WY', 'avg_city_pop': 3239.4857142857145}
{'_id': 'UT', 'avg_city_pop': 8404.146341463415}
{'_id': 'HI', 'avg_city_pop': 13852.8625}
{'_id': 'MI', 'avg_city_pop': 10611.069634703197}
{'_id': 'AK', 'avg_city_pop': 2793.3230769230768}
{'_id': 'WV', 'avg_city_pop': 2733.454268292683}
{'_id': 'IA', 'avg_city_pop': 3011.301518438178}
{'_id': 'WI', 'avg_city_pop': 6832.079608938548}
{'_id': 'VA', 'avg_city_pop': 7575.341911764706}
{'_id': 'IN', 'avg_city_pop': 8201.384615384615}
{'_id': 'SD', 'avg_city_pop': 1810.9296875}
{'_id': 'IL', 'avg_city_pop': 9238.137429264349}
{'_id': 'MO', 'avg_city_pop': 5141.496981891348}
{'_id': 'KS', 'avg_city_pop': 3461.937062937063}
{'_id': 'LA', 'avg_city_pop': 9089.644396551725}
{'_id': 'NH', 'avg_city_pop': 5088.311926605505}
{'_id': 'OK', 'avg_city_pop': 5367.892491467577}
{'_id': 'NC', 'avg_city_pop': 9402.321985815603}
{'_id': 'TX', 'avg_city_pop': 10164.333333333334}
{'_id': 'CO', 'avg_city_pop': 7955.929951690821}
{'_id': 'ID', 'avg_city_pop': 4126.020491803279}
{'_id': 'NV', 'avg_city_pop': 11556.086538461539}

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

In [30]:

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

{'_id': {'state': 'IL', 'city': 'CHICAGO'}, 'pop': 2452177}
{'_id': {'state': 'NY', 'city': 'BROOKLYN'}, 'pop': 2300504}
{'_id': {'state': 'CA', 'city': 'LOS ANGELES'}, 'pop': 2102295}
{'_id': {'state': 'TX', 'city': 'HOUSTON'}, 'pop': 2095918}
{'_id': {'state': 'PA', 'city': 'PHILADELPHIA'}, 'pop': 1610956}
{'_id': {'state': 'NY', 'city': 'NEW YORK'}, 'pop': 1476790}
{'_id': {'state': 'NY', 'city': 'BRONX'}, 'pop': 1209548}
{'_id': {'state': 'CA', 'city': 'SAN DIEGO'}, 'pop': 1049298}
{'_id': {'state': 'MI', 'city': 'DETROIT'}, 'pop': 963243}
{'_id': {'state': 'TX', 'city': 'DALLAS'}, 'pop': 940191}

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

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

In [ ]:

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

# 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

In [31]:

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)

{'_id': 'DE', 'biggest_city': 'NEWARK', 'biggest_pop': 111674, 'smallest_city': 'BETHEL', 'smallest_pop': 108}
{'_id': 'MS', 'biggest_city': 'JACKSON', 'biggest_pop': 204788, 'smallest_city': 'CHUNKY', 'smallest_pop': 79}
{'_id': 'RI', 'biggest_city': 'CRANSTON', 'biggest_pop': 176404, 'smallest_city': 'CLAYVILLE', 'smallest_pop': 45}
{'_id': 'MO', 'biggest_city': 'SAINT LOUIS', 'biggest_pop': 397802, 'smallest_city': 'BENDAVIS', 'smallest_pop': 44}
{'_id': 'FL', 'biggest_city': 'MIAMI', 'biggest_pop': 825232, 'smallest_city': 'CECIL FIELD NAS', 'smallest_pop': 0}

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

In [33]:

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)

{'_id': 'DE', 'biggest_city': 'NEWARK', 'biggest_pop': 111674, 'smallest_city': 'BETHEL', 'smallest_pop': 108}
{'_id': 'MS', 'biggest_city': 'JACKSON', 'biggest_pop': 204788, 'smallest_city': 'CHUNKY', 'smallest_pop': 79}
{'_id': 'RI', 'biggest_city': 'CRANSTON', 'biggest_pop': 176404, 'smallest_city': 'CLAYVILLE', 'smallest_pop': 45}
{'_id': 'MO', 'biggest_city': 'SAINT LOUIS', 'biggest_pop': 397802, 'smallest_city': 'BENDAVIS', 'smallest_pop': 44}
{'_id': 'FL', 'biggest_city': 'MIAMI', 'biggest_pop': 825232, 'smallest_city': 'CECIL FIELD NAS', 'smallest_pop': 0}