10주 2일차 TIL 정리

10주 2일차에는 분석목표를 설정하여 SQL문을 작성하는 수업을 주로 진행했다.

 

---

※ PostgreSQL을 Tableau에 연결하기 ※

---

수업 시작 전 PostgreSQL을 태블로 서버에 연결하는 법에 대해 수강생들끼리 알아보았다.

 

1. 태블로의 연결 화면에서 서버에 연결 - 자세히 - PostgreSQL을 선택해준다.

 

2. 드라이버가 없다면 경고창이 뜨는데, 해당 링크를 통해 .jar 파일을 다운받을 수 있다.

 

3. 해당 파일을 받았다면 (윈도우 기준) C:\Program Files\Tableau\Drivers 로 위치를 옮겨준다.

 

4. 태블로를 재시작해 1번과 같이 누르면 다음과 같이 창이 뜬다.

 

5. 여기서 PostgreSQL을 실행하고 있는 편집프로그램으로 들어간다. (예시의 경우 DBeaver)

  여기서 Postgres를 우클릭 후 연결 편집으로 들어가준다.

 

이와 같이 정보가 뜨는데, 이 정보를 보며 4번의 빈칸을 채워넣으면 된다.

 

6. 다음과 같이 채워넣으면 정상적으로 서버에 연결할 수 있다.

 

---

※ 함수 학습 ※

---

1. coalesce(A,B,C)

- 전부 not null이면 A 반환, A가 null이면 B 반환, A와 B가 null이면 C 반환

 

- PostgreSQL에선 ifnull(A,0) 사용하지 않고 coalesce(A,0)사용

 

* postgreSQL에선 nullif가 있다.

select nullif(name, 'no data')

 

→ name컬럼에서 no data라는 항목을 null값으로 처리

 

select coalesce(address,'') || ' ' || coalesce(city,'') || ' ' 
    || coalesce(region,'') || ' ' || coalesce(postal_code,'') || ' ' 
    || coalesce(country,'') as concatted_addr
from northwind.customers c

= (띄어쓰기 없을 경우)

select concat(address, city, region, postal_code, country)
from northwind.customers c

 

2. 날짜 함수

-- 현재의 timestamp과 타임존을 함께 표시
select now();
select current_timestamp;
-- 로컬 timestamp 표시
select localtimestamp; 
-- 현재 date 표시
select current_date;
-- 현재 time과 타임존을 함께 표시
select current_time;
-- 현재 로컬 time 표시
select localtime;

 

- extract, date_part : 현재 데이터에서 특정 하나만 추출(year, month 등)
 

select extract('year' from now())

 

select date_part('year', now())

(integer형 리턴)

 

 

- to_char: char형으로 원하는 날짜형식 출력

select to_char(now(),'yyyy-mm-dd HH24:mm:ss');

 

* 다양한 방법으로 요일 추출하기

select extract ('dow' from cast('2023-04-09' as date));   -- int
select date_part('dow',cast('2023-04-09' as date));       -- int
select to_char(cast('2023-04-09'as date),'day');          -- char

 

3. 자료형 변환

 

- 자료형 변환(표준): cast(변환대상 as 자료형)
- 자료형 변환(postgre): 변환대상::자료형

 

select cast(now() as date);
select now()::date;

select cast(now() as time);
select now()::time;

 

 

---

※ 샘플데이터 ※

---

샘플데이터 - northwind

식품을 수출하는 가상의 회사

 

데이터 파악 단계에서 B2B거래를 하는 회사이며, 공급사로부터 선지급받은 후 배송사를 이용, 고객사로 배송을 하거나 공급사에서 배송사를 통해 직접 고객사로 배송하는 형태로 사업이 운영됨을 알 수 있었다.

 

1. 월별 매출

-- 월별 매출액
select to_char(o.order_date,'yyyy-mm')
       ,sum(od.unit_price * od.quantity * (1-od.discount)) as monthly_sales
from northwind.orders o , northwind.order_details od
where o.order_id = od.order_id
group by 1
order by 1;

2. 월별 매출액, 구매건수, 구매자수

-- 월별 매출액, 주문 수, 구매자 수
select to_char(o.order_date,'yyyy-mm')
       ,sum(od.unit_price * od.quantity * (1-od.discount)) as monthly_sales
       ,count(distinct o.order_id)
       ,count(distinct o.customer_id)
from northwind.orders o , northwind.order_details od
where o.order_id = od.order_id
group by 1
order by 1;

3. 월별 인당 평균 매출액, 건당 평균 구매액

-- 월별 인당 평균 매출액, 건당 평균 구매액
select to_char(o.order_date,'yyyy-mm')
       ,sum(od.unit_price * od.quantity * (1-od.discount)) / count(distinct o.customer_id) as "인당 평균 구매액"
       ,sum(od.unit_price * od.quantity * (1-od.discount)) / count(distinct o.order_id) as "건당 평균 구매액"
from northwind.orders o , northwind.order_details od
where o.order_id = od.order_id
group by 1
order by 1;

 

 

4. 월별 매출 - 주문횟수 그래프, 월별 매출 - 주문고객수 그래프 및 상관관계 히트맵

 

5. 분기별 매출 - 주문횟수 그래프, 분기별 매출 - 주문고객수 그래프 및 상관관계 히트맵

 

6. 결론

월별, 분기별로 파악했을 때 매출은 주문 고객수보다는 주문횟수에 비례해 증가한다.

 

 

 

---

※ python에서 쿼리를 데이터프레임으로 만들기 ※

---

query = """
select to_char(o.order_date,'yyyy-mm')
       ,sum(od.unit_price * od.quantity * (1-od.discount)) as monthly_sales
       ,count(distinct o.order_id) as odr_cnt
       ,count(distinct o.customer_id) as cust_cnt
from northwind.orders o , northwind.order_details od
where o.order_id = od.order_id
group by 1
order by 1;
"""
df = pd.read_sql_query(sql=query, con=postgres_engine)
df.head()

 

 

query = """ 쿼리 이 안에다가 입력 """

 

* 주의사항: 이때 from절에서 테이블을 언급하려면 스키마명을 써줘야 한다.

eg)                      from Book (x)

from Bookstore.Book(o)