GROUP BY, HAVING & COUNT()

Grouping과 Counting은 아래와 같은 질문에 도움됩니다

- 해당 가게에서 각 과일마다 몇 개씩 팔았는지

- 동물병원에 얼마나 많은 종들이 보호되고 있는지 알고 싶을 때,

 

이번에 쓸 dataset은 오른쪽 그림입니다.

 

 

COUNT( )

이름을 보고 추측한 뒤 물건의 갯수를 알고 싶을 때, parameter로 column의 이름을 넣으면 됩니다.

해당 column의 entry의 개수를 알려줍니다.

 

'pet' table 속 ID column의 entry 개수를 반환해보면 4개 

COUNT()는 aggregate function 중 하나입니다. 많은 값들을 입력으로 받은 뒤 하나의 값을 출력하죠.

( ex_ SUM(), AVG(), MIN(), MAX() )

위의 사진을 보시면 column 이름이 f0__라는 알 수 없는 이름이 들어갔습니다. 

이름 바꾸는 법은 나중에 다루니 일단 패스.

 

 

GROUP BY

하나 또는 그 이상의 columns를 다룰 수 있습니다.

COUNT()처럼 aggregate function을 적용하면

입력한 column에서 값이 일치하는 row를 모아 하나의 group으로 모아줍니다.

 

예로 'pet' table에 얼마나다 많은 종류의 동물이 포함되어 있는지 알고 보려면,

GROUP BY를 사용하여 Animal column에서 동일한 값을 가진 row를 grouping합니다.

그와 동시에 COUNT()를 사용하면 각 group마다 몇 개의 ID가 있는지 확인할 수 있습니다.

오른쪽 table과 같이, pet table 속 종별 개체 수가 나옵니다

 

 

GROUP BY ... HAVING

Group By에 Having도 함께 쓰면 특정 group을 무시할 수 있습니다.

개체 수가 1마리 이상인 animal만 보여주도록 하기 위해 HAVING 사용

 

 

 

 

 

Aliasing and other improvements(기타 개선 사항)

 

Tip1) Aliasing (별칭)

COUNT(id)의 결과로 반환된 column의 이름은 항상 'f0__'입니다.

원하는 aggregation function으로 특정 지은 이후에

'AS column_name'를 추가하면 해당 column의 이름을 바꿀 수 있습니다.

 

Tip2) Improve Readability

COUNT()의 parameter로 무엇을 넣어야할지 확신이 오지 않는다면

COUNT(1)을 해보세요.

이는 각 group의 row의 개수를 세어줍니다.

대부분 모든 column에 관심을 갖지 않기에 잘 읽힐 수 있도록, 이해할 수 있도록 .head()처럼 해보는 거죠.

이 방법은, 주어진 column name보다 더 적은 수의 data(rows<columns)를 검색합니다.

더 빠르고 data access 할당량(quota)를 적게 사용해주죠.

 

# Improved version of earlier query, now with aliasing & improved readability
query_improved = """
                 SELECT parent, COUNT(1) AS NumPosts
                 FROM `bigquery-public-data.hacker_news.comments`
                 GROUP BY parent
                 HAVING COUNT(1) > 10
                 """

safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
query_job = client.query(query_improved, job_config=safe_config)

# API request - run the query, and convert the results to a pandas DataFrame
improved_df = query_job.to_dataframe()

# Print the first five rows of the DataFrame
improved_df.head()

 

Tip3) Good query vs Bad query

 

SQL은 COUNT()와 같은 Aggregate function을 적용하는 방법이 내장되어 있다며,

 aggregate func(COUNT()) 없이 GROUP BY를 사용하려 한다는 것은 어불성설입니다.

 

마찬가지로 GROUP BY를 사용한다면 반드시,

SELECT 옆에 적은 변수를 GROUP BY 명령문 또는 aggregate func을 함께 써줘야 합니다.

아래 예제와 같이 'parent' 변수는 GROUP BY 명령문에,  'id' 변수는 aggregate func(COUNT())에 사용된 것처럼요.

query_good = """
             SELECT parent, COUNT(id)
             FROM `bigquery-public-data.hacker_news.comments`
             GROUP BY parent
             """

 

아래의 코드 

author 변수가 aggregate func 또는, GROUP BY에 사용되지 않았기에 작동되지 않는 query문 입니다.

이 코드는 다음과 같은 에러메세지가 나와요,

"SELECT list expression references column (column's name) which is neither grouped nor aggregated at"

query_bad = """
            SELECT author, parent, COUNT(id)
            FROM `bigquery-public-data.hacker_news.comments`
            GROUP BY parent
            """

 

 

 

---

Ex. Which Hacker News comments generated the most discussion?

이제 real dataset을 다뤄봅시다. Hacker News dataset에는 Hacker News SNS에 있는 이야기와 댓글이 들어있습니다.

 

"comment" table을 이용해볼게요.

from google.cloud import bigquery

# Create a "Client" object
client = bigquery.Client()

# Construct a reference to the "hacker_news" dataset
dataset_ref = client.dataset("hacker_news", project="bigquery-public-data")

# API request - fetch the dataset
dataset = client.get_dataset(dataset_ref)

# Construct a reference to the "comments" table
table_ref = dataset_ref.table("comments")

# API request - fetch the table
table = client.get_table(table_ref)

# Preview the first five lines of the "comments" table
client.list_rows(table, max_results=5).to_dataframe()

 

dataset 설명

- 'parent' column : 대댓글(답변/응답) 유무 여부

-  'id' column : 각 comment를 가리키는 고유한 ID

 

 

자 이제 대댓글(답변)이 가장 많은 댓글, 즉 인기 있는 댓글을 보기 위해 아래와 같이 코드를 작성해보려 합니다.

'parent' column에서 id가 10보다 큰 row만, 즉 적어도 10개 이상 달린 댓글들만  grouping 해봅시다.

# Query to select comments that received more than 10 replies
query_popular = """
                SELECT parent, COUNT(id)
                FROM `bigquery-public-data.hacker_news.comments`
                GROUP BY parent
                HAVING COUNT(id) > 10
                """

query 작성이 완료되면 DataFrame에 결과를 저장하세요.

# Set up the query (cancel the query if it would use too much of 
# your quota, with the limit set to 10 GB)
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
query_job = client.query(query_popular, job_config=safe_config)

# API request - run the query, and convert the results to a pandas DataFrame
popular_comments = query_job.to_dataframe()

# Print the first five rows of the DataFrame
popular_comments.head()

오른쪽 그림을 보면 'popular_comments' dataframe은 10개 이상의 답변을 받은 comment의 id만을, 그리고 그 id가 받은 댓글의 개수를 함께 보여줍니다. 예로, ID 6855145 댓글은 39개 대댓글이 달렸네요.

 

 

적어도 10000개보다 많은 대댓글을 받은

댓글 작성자가 몇 개의 댓글을 썼는지 확인하고 싶다면 아래와 같은 코드를 작성하면 됩니다.

prolific_commenters_query = """
SELECT author, COUNT(id) AS NumPosts
FROM `bigquery-public-data.hacker_news.comments`
GROUP BY author
HAVING COUNT(id) > 10000
""" # 두 COUNT() 모두 COUNT(1)로 해도 무방함

# Set up the query (cancel the query if it would use too much of 
# your quota, with the limit set to 1 GB)
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
query_job = client.query(prolific_commenters_query, job_config=safe_config)

# API request - run the query, and return a pandas DataFrame
prolific_commenters = query_job.to_dataframe()

# View top few rows of results
print(prolific_commenters.head())

 

삭제된 댓글의 개수를 알아봅시다. 'deleted' column에서 True 값을 가진 comment를 보세요.

!! WHERE deleted = True

NO need GROUP BY !!

# Query to determine how many posts were deleted
deleted_posts_query = """
                      SELECT COUNT(1) AS num_deleted_posts
                      FROM `bigquery-public-data.hacker_news.comments`
                      WHERE deleted = True
                      """

# Set up the query
query_job = client.query(deleted_posts_query)

# API request - run the query, and return a pandas DataFrame
deleted_posts = query_job.to_dataframe()

# View results
print(deleted_posts)

num_deleted_posts = deleted_posts.iloc[0,0]
#227736