AS & WITH

이번 튜토리얼에서 사용할 'pets' table

동물의 나이를 포함하고 있음.

 

 

AS

생성할 column의 이름을 바꾸는 데 사용했죠. 이를 aliasing이라고 불렀구요.

Python에서도 aliasing을 하기 위해 as를 씁니다. 대신 pandas나 seaborn을 import 해야하죠.

 

AS를 SQL에서 사용하려면, 선택한 column 직후에 AS를 명시해야합니다.

AS 없는 query

AS를 포함한 query f0__가 'Number'로 변환

 

 

WITH ... AS   =>  CTE

CTE (common table expression)은 AS와 WITH을 함께 쓰는 것을 일컫습니다.

이는 일시적인 table로, 내가 작성한 여러 개의 query들을 읽을 수 있을만큼 덩어리로 구분시켜 분리하는데 도움됩니다.

그 결과를 보고 새롭게 query를 고치기도 하죠.

 

 

예로, pet table을 통해 나이가 많은 동물이 누군지 알고 싶다고 합시다. 

이를 위해, 5살 이상 동물의 정보를 포함한 CTE를 생성합니다.

아직 query가 불완전합니다. 아무런 결과로 보여주지 않죠. 그저 Seniors라고 불리는 CTE를 생성한 것뿐입니다.

아직 query작성을 다 끝내지 못한 상태라서 CTE가 된 거죠!

그래도 CTE를 보면서 남은 query 작성을 어떻게 해야 원하는 결과를 정확하게 가져올 수 있는지 힌트를 얻게 되죠.

또한 CTE를 바탕으로 정보를 추출합니다. group by에 또 group by를 써서 추출하듯이 말이죠.

위의 query 사진은, 아까 만든 CTE를 바탕으로 그곳에서 모든 ID를 반환해줍니다.

이는 물론 CTE 과정 없이 할 수 있지만, 사용하지 않았다면, query가 매우 길어질 텝니다.

 

또한 CTE는 작성한 query 내에서만 존재한다는 점을 명심하세요.

이후 query에서 이전에 만든 CTE를 참조할 수 없습니다.

그러니, CTE는 두 파트로 구분해서 사용해야합니다

1. CTE를 생성하고

2. 그 CTE를 바탕으로 query를 쓸 수 있다는 점 

'

 

 

Ex. How many Bitcoin transactions are made per month?

'transaction' table을 이용하고 CTE를 사용하여 전체 기간에서  Bitcoin 하루 거래량을 보려 합니다.

from google.cloud import bigquery

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

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

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

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

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

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

'block_timestamp' column은 DATETIME format. 거래 시기에 대한 정보가 들어있음.

'

'block_timestamp' column을 DATE() 명령문을 이용하여 DATETIME -> DATE format으로 변환하려 합니다.

그리고 CTE를 사용하여, 각 날짜마다 거래량을 세어보고 이를 이른 날짜부터 정렬해주는 query를 작성할 것입니다.

# Query to select the number of transactions per date, sorted by date
query_with_CTE = """ 

     WITH time AS 
     (
        SELECT DATE(block_timestamp) AS trans_date
        FROM `bigquery-public-data.crypto_bitcoin.transactions`
     )
     SELECT COUNT(1) AS transactions,
            trans_date
     FROM time
     GROUP BY trans_date
     ORDER BY trans_date
     """
# 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_with_CTE, job_config=safe_config)

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

# Print the first five rows
transactions_by_date.head()           

#정렬된 결과를 바탕으로 전체 기간에 대해 날짜별 bitcoin 거래량 plot 그려보기
transactions_by_date.set_index('trans_date').plot()

BigQuery의 CTEs와 Pandas를 함께 써서 간단히 완성한 결과작품

 

---

Exercise

'chicago_taxi_trips' dataset을 보려 합니다. 시카고 도시의 택시 여행에 관련된 dataset입니다.

이를 통해 교통량이 많을 때 얼마나 정체되는지( 교통량이 얼마나 느리게 움직이는지) 봅시다.

from google.cloud import bigquery

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

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

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

 

 

1st Find the data

1-1 get the name of table

-> list() -> client.list_tables(dataset)

# List all the tables in the dataset
tables = list(client.list_tables(dataset))
# Print names of all tables in the dataset (there is only one!)
for table in tables:  
    print(table.table_id)

참고 - TableListItem 객체 속 메소드와 필드 목록

1-2 create table you want

 

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

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

 

2nd Peek at the data

먼저 data의 처음 5개의 줄을 출력하세요.

data의 상태가 괜찮은지, 어떤 문제가 존재하는지 명확하게 보기 위해서요.

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

일단 여기선, 'trip_seconds' 또는 'trip_miles' column에 0값이 들어있는 게 보이네요.

None 값이 들어간 곳도 종종 보이구요. 해당 column을 사용해야 할 때 조심해야합니다.

 

 

 

3rd Determine when this data is from

만약 data가 오래 전에 기록된 자료만 들어있다면 이를 통한 예측치를 오늘날에 반영하기 힘듭니다.

그러니 매년 여행의 수를 세는 query를 작성해보려 합니다.

'year' column과 'num_trips' column으로 된 결과를 도출해보세요.

rides_per_year_query = """
SELECT EXTRACT(YEAR FROM trip_start_timestamp) AS year, COUNT(1) AS num_trips
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
GROUP BY YEAR
ORDER BY num_trips

"""

# Set up the query
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
rides_per_year_query_job = client.query(rides_per_year_query, job_config=safe_config)

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

# View results

- 'trip_start_timestamp' column은 DATA format으로 있기에 그 중 YEAR format을 추출하기 위해 EXTRACT() 사용.

- FROM `progect-name,dataset_name,table_name`

- 해당 column에서 최종적으로 추출한 값들에 대해 grouping 하므로 YEAR을 적음 (trip_start_timestamp 아님)

 

 

 

4th Dive slightly deeper - (자세하게 살펴볼까요?)

이번에 2017년에 집중해봅시다.

그 해에 월별 택시 승객 수만 출력하는 query를 작성하세요.

위의 query에서 WHERE로 2017년만 추출하고,

year이 아닌 month를 추출하도록 수정하세요.

 

# Your code goes here
rides_per_month_query = """
SELECT EXTRACT(MONTH FROM trip_start_timestamp) AS month, COUNT(1) AS num_trips
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
WHERE 2017 =  EXTRACT(YEAR FROM trip_start_timestamp)
GROUP BY MONTH
ORDER BY num_trips

"""

# Set up the query
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
rides_per_month_query_job = client.query(rides_per_month_query, job_config=safe_config)

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

# View results
print(rides_per_month_result)

 

 

5th Write the sophistical query

매일, 매시각 여행의 수와 평균 속도를 보여주는 query를 CTE를 사용하여 작성하세요.

CTE에는 최종 결과에 사용되는 column만 담겨 있어야합니다. 관련된 여행객 정보만 담아두란 이야기입니다.

 

결과에는 아래 column이 있어야 합니다.

- 'hour_of_day' : 이 column으로 정렬하세요. 'trip_start_timestamp'에서 hour을 추출.

- 'num_trips' : 매일, 매시각 총 여행의 수를 카운팅한 결과

- 'avg_mph' : 그날 그 시각에 출발한 여행들의 mile per hour 단위로 측정된 평균 속도. 계산식은 3600 * SUM(trip_miles) / SUM(trip_seconds). (3600은 초단위를 시단위로 바꿀 때 사용됩니다.)

 

!! 조건

- 2017-01-01부터 2017-07-01 사이 정보만!

- 'trip_seconds' > 0 그리고 'trip_miles' > 0

 

speeds_query = """
               WITH RelevantRides AS
               (
                   SELECT EXTRACT(HOUR FROM trip_start_timestamp) AS hour_of_day, 
                          trip_miles, 
                          trip_seconds
                   FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
                   WHERE trip_start_timestamp > '2017-01-01' AND 
                         trip_start_timestamp < '2017-07-01' AND 
                         trip_seconds > 0 AND 
                         trip_miles > 0
               )
               SELECT hour_of_day, 
                      COUNT(1) AS num_trips, 
                      3600 * SUM(trip_miles) / SUM(trip_seconds) AS avg_mph
               FROM RelevantRides
               GROUP BY hour_of_day
               ORDER BY hour_of_day
               """

# Set up the query (cancel the query if it would use too much of 
# your quota)
safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
speeds_query_job = client.query(speeds_query, job_config=safe_config)

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

# View results
print(speeds_result)

 

 

CTE에서부터 EXTRACT()을 사용하여 'hour_of_day' 생성

WHERE 절에서 AND 사용 시 엔터 구분 무시. 또한 해당 COLUMN 값이 문자열임을 알고 있으면 저렇게 가능.

'2017-01-01'처럼 특정 날짜에 대해서 조건절을 사용하고 싶을 때 '' 문자열 써도 됨.