뉴욕시 택시 및 리무진 위원회는 월별 노란색 택시 운행과 기타 렌트차량에 대한 데이터를 발표합니다. 2016년 6월 1일 하루 동안의 노란 택시 운행 기록 데이터로 날짜와 시간 관련 쿼리를 작성해보겠습니다.

데이터 불러오기

• CREATE TABLE 테이블 만들기
• COPY 데이터 불러오기
• CREATE INDEX 인덱스 생성해서 조회시간 단축하기

이제 이건 좀 외웁시다!

CREATE TABLE nyc_yellow_taxi_trips (
    trip_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    vendor_id text NOT NULL,
    tpep_pickup_datetime timestamptz NOT NULL,
    tpep_dropoff_datetime timestamptz NOT NULL,
    passenger_count integer NOT NULL,
    trip_distance numeric(8,2) NOT NULL,
    pickup_longitude numeric(18,15) NOT NULL,
    pickup_latitude numeric(18,15) NOT NULL,
    rate_code_id text NOT NULL,
    store_and_fwd_flag text NOT NULL,
    dropoff_longitude numeric(18,15) NOT NULL,
    dropoff_latitude numeric(18,15) NOT NULL,
    payment_type text NOT NULL,
    fare_amount numeric(9,2) NOT NULL,
    extra numeric(9,2) NOT NULL,
    mta_tax numeric(5,2) NOT NULL,
    tip_amount numeric(9,2) NOT NULL,
    tolls_amount numeric(9,2) NOT NULL,
    improvement_surcharge numeric(9,2) NOT NULL,
    total_amount numeric(9,2) NOT NULL
);

COPY nyc_yellow_taxi_trips (
    vendor_id,
    tpep_pickup_datetime,
    tpep_dropoff_datetime,
    passenger_count,
    trip_distance,
    pickup_longitude,
    pickup_latitude,
    rate_code_id,
    store_and_fwd_flag,
    dropoff_longitude,
    dropoff_latitude,
    payment_type,
    fare_amount,
    extra,
    mta_tax,
    tip_amount,
    tolls_amount,
    improvement_surcharge,
    total_amount
   )
FROM '/Users/beckhyejin/Desktop/data_research/practical-sql-main/Chapter_12/nyc_yellow_taxi_trips.csv'
WITH (FORMAT CSV, HEADER);

CREATE INDEX tpep_pickup_idx
ON nyc_yellow_taxi_trips (tpep_pickup_datetime);

데이터 확인하기

SELECT count(*) FROM nyc_yellow_taxi_trips; -- 총 368774개의 rows

SELECT * FROM nyc_yellow_taxi_trips LIMIT 5;

타임존 설정

SET TIME ZONE 명령을 통해 현재 세션의 시간대를 설정할 수 있습니다.

SHOW timezone; -- 현재 타임존 확인 

SET TIME ZONE 'US/Pacific';

'미국 뉴욕시' 기준으로 시간세팅을 해줘야합니다.

데이터 확인하기

5월 31일의 데이터만 있는게 맞는지 확인해보겠습니다.

SELECT 
	min(tpep_pickup_datetime),
	max(tpep_pickup_datetime)
FROM nyc_yellow_taxi_trips

2016년 5월 31일 21시부터 6월 1일 ~21시까지 입니다.

하루 중 가장 바쁜 시간대

시간 추출 date_part('hour')

tpep_pickup_datetime 택시 손님 승차 시간대를 기록한 컬럼입니다.

• date_part('hour', 컬럼명) 으로 시간대만 추출
• count(*) 으로 그걸 세주기
• GROUP BY 컬럼명으로 시간대별 묶어주기

오후 15~19시에 대체적으로 택시를 많이 탑니다.

CSV파일 내보내기 COPY, TO, WITH

COPY
	(SELECT 
		date_part('hour', tpep_pickup_datetime) as trip_hour, 
		count(*) as cnt_hour
	FROM nyc_yellow_taxi_trips 
	GROUP BY trip_hour 
	ORDER BY cnt_hour DESC) 
TO '/Users/beckhyejin/Desktop/data_research/practical-sql-main/Chapter_12/hourly_pickups.csv'
WITH (FORMAT CSV, HEADER); 

엑셀로 시각화