SQL(5)(inner join, outer join, left join, right join, full outer join)

-

INNER JOIN

inner join은 on명령문에 명시된 조인 조건을 충족하지 않는 두 테이블의 행을 제거한다.









1
2
3
4
5
SELECT players.*,
       teams.*
  FROM benn.college_football_players players
  JOIN benn.college_football_teams teams
    ON teams.school_name = players.school_name
cs








1
2
3
4
5
SELECT players.school_name AS players_school_name,
       teams.school_name AS teams_school_name
  FROM benn.college_football_players players
  JOIN benn.college_football_teams teams
    ON teams.school_name = players.school_name
cs









연습문제

"FBS(Division IA Teams)"부서에 있는 학교의 선수 이름, 학교 이름 및 회의를 표시하는 쿼리를 작성

1
2
3
4
5
6
7
SELECT players.player_name,
       players.school_name,
       teams.conference
  FROM benn.college_football_players players
  JOIN benn.college_football_teams teams
    ON teams.school_name = players.school_name
 WHERE teams.division = 'FBS (Division I-A Teams)'
cs










OUTER JOIN

outer join에서는 하나 또는 두 테이블 모두에서 일치하지 않는 행이 봔환 될 수 있다.

몇 가지 유형의 외부 조인이 있다. 자세한 내용은 아래에서 다룰 예정이다.







LEFT JOIN










1
2
3
4
5
6
7
SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
cs








연습문제

tutorial.crunchbase_acquisitions테이블과 tutorial.crunchbase_companies테이블간에 내부조인을 수행하는 쿼리를 작성, 개별 행을 나열하는 대신 각 테이블에서 Null이 아닌 행의 수를 계산

1
2
3
4
5
SELECT COUNT(companies.permalink) AS companies_rowcount,
       COUNT(acquisitions.company_permalink) AS acquisitions_rowcount
  FROM tutorial.crunchbase_companies companies
  JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
cs




위의 쿼리를 LEFT JOIN으로 작성

1
2
3
4
5
SELECT COUNT(companies.permalink) AS companies_rowcount,
       COUNT(acquisitions.company_permalink) AS acquisitions_rowcount
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
cs




주별로 고유한 회사와 인수한 고유회사의 수를 계산. 상태 데이터가 없는 결과를 포함하지 말고 인수한 회사 수를 기준으로 높은 순서에서 낮은 순서로 정렬

1
2
3
4
5
6
7
8
9
SELECT companies.state_code,
       COUNT(DISTINCT companies.permalink) AS unique_companies,
       COUNT(DISTINCT acquisitions.company_permalink) AS unique_companies_acquired
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
 WHERE companies.state_code IS NOT NULL
 GROUP BY companies.state_code
 ORDER BY unique_companies_acquired DESC
cs









RIGHT JOIN








right join은 두 테이블의 이름을 간단히 전환하여 left join과 동일한 결과를 반환하기 때문에 거의 사용되지 않는다. 예는 다음과 같다.

1
2
3
4
5
6
7
SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
cs

1
2
3
4
5
6
7
SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_acquisitions acquisitions
 RIGHT JOIN tutorial.crunchbase_companies companies
    ON companies.permalink = acquisitions.company_permalink
cs








연습문제

총계 및 인수 회사를 주별로 세었지만 left join대신 right join을 사용하여 이전 질의를 다시 작성. 정확히 같은 결과를 반환해야 한다.

1
2
3
4
5
6
7
8
9
SELECT companies.state_code,
       COUNT(DISTINCT companies.permalink) AS unique_companies,
       COUNT(DISTINCT acquisitions.company_permalink) AS acquired_companies
  FROM tutorial.crunchbase_acquisitions acquisitions
 RIGHT JOIN tutorial.crunchbase_companies companies
    ON companies.permalink = acquisitions.company_permalink
 WHERE companies.state_code IS NOT NULL
 GROUP BY companies.state_code
 ORDER BY acquired_companies DESC
cs










JOINS USING WHERE OR ON

ON

join전에 하나 또는 둘의 테이블을 필터링 할 수 있다.

1
2
3
4
5
6
7
8
9
SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
   AND acquisitions.company_permalink != '/company/1000memories'
 ORDER BY companies_permalink
cs








WHERE

1
2
3
4
5
6
7
8
9
10
SELECT companies.permalink AS companies_permalink,
       companies.name AS companies_name,
       acquisitions.company_permalink AS acquisitions_permalink,
       acquisitions.acquired_at AS acquired_date
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
 WHERE acquisitions.company_permalink != '/company/1000memories'
    OR acquisitions.company_permalink IS NULL
 ORDER BY companies_permalink
cs








where절의 필터링은 null값도 필터링 할 수 있으므로 null을 포함하도록 추가 행을 추가했다.

연습문제

회사 이름, 상태 및 해당 회사의 고유 투자자 수를 표시하는 쿼리를 작성. 투자자 수를 기준으로 가장 적은 순서로 정렬. 뉴욕주에 있는 회사로만 제한.

1
2
3
4
5
6
7
8
9
SELECT companies.name AS company_name,
       companies.status,
       COUNT(DISTINCT investments.investor_name) AS unqiue_investors
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments investments
    ON companies.permalink = investments.company_permalink
 WHERE companies.state_code = 'NY'
 GROUP BY company_name,companies.status
 ORDER BY unqiue_investors DESC
cs











투자한 회사 수에 따라 투자자를 나열하는 쿼리를 작성. 투자자가 없는 회사의 행을 포함하고 대부분의 회사에서 가장 적은 순서.

1
2
3
4
5
6
7
8
SELECT CASE WHEN investments.investor_name IS NULL THEN 'No Investors'
            ELSE investments.investor_name END AS investor,
       COUNT(DISTINCT companies.permalink) AS companies_invested_in
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments investments
    ON companies.permalink = investments.company_permalink
 GROUP BY investor
 ORDER BY companies_invested_in DESC
cs











FULL OUTER JOIN

두 테이블 모두에서 일치하지 않는 행을 반환한다. 두 테이블 간의 겹침 정도를 이해하기 위해 집계와 함께 일반적으로 사용된다.

1
2
3
4
5
6
7
8
9
SELECT COUNT(CASE WHEN companies.permalink IS NOT NULL AND acquisitions.company_permalink IS NULL
                  THEN companies.permalink ELSE NULL END) AS companies_only,
       COUNT(CASE WHEN companies.permalink IS NOT NULL AND acquisitions.company_permalink IS NOT NULL
                  THEN companies.permalink ELSE NULL END) AS both_tables,
       COUNT(CASE WHEN companies.permalink IS NULL AND acquisitions.company_permalink IS NOT NULL
                  THEN acquisitions.company_permalink ELSE NULL END) AS acquisitions_only
  FROM tutorial.crunchbase_companies companies
  FULL JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink
cs




연습문제

tutorial.crunchbase_companies 및 tutorial.crunchbase_investments_part1을 사용. 위의 예와 같이 일치/ 불일치 하는 행 수를 계산

1
2
3
4
5
6
7
8
9
SELECT COUNT(CASE WHEN companies.permalink IS NOT NULL AND investments.company_permalink IS NULL
                  THEN companies.permalink ELSE NULL END) AS companies_only,
       COUNT(CASE WHEN companies.permalink IS NOT NULL AND investments.company_permalink IS NOT NULL
                  THEN companies.permalink ELSE NULL END) AS both_tables,
       ㅜCOUNT(CASE WHEN companies.permalink IS NULL AND investments.company_permalink IS NOT NULL
                  THEN investments.company_permalink ELSE NULL END) AS investments_only
  FROM tutorial.crunchbase_companies companies
  FULL JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
cs





댓글

댓글 쓰기

이 블로그의 인기 게시물

SQL(9)(performance tuning queries, pivoting data)

-
이미지
PERFORMANCE TUNING QUERIES 쿼리를 개선할 수 있는 개선방법을 식별하는 방법 먼저 수행해야하는 계산 수와 쿼리 런타임에 영향을 주는 몇가지 사항 - 테이블 크기 : 쿼리가 수백만개의 행이 있는 하나 이상의 테이블에 도달하면 성능에 영향을 미칠 수 있다. - 조인 : 쿼리가 결과 집합의 행 수를 크게 늘리는 방식으로 두 테이블을 조인하면 쿼리가 느려질 수 있다. - 집계 : 결과를 생성하기 위해 여러 행을 결합하려면 단순히 해당 행을 검색하는 것보다 더 많은 계산이 필요하다. - 쿼리를 실행하는 다른 사용자 : 데이터베이스에서 동시에 실행되는 쿼리가 많을수록 데이터베이스가 주어진 시간에 더 많은 처리해야하며 모든것이 느리게 실행된다. - 데이터베이스 소프트웨어 및 최적화 : 제어 할 수 없는 부분이지만 사용중인 시스템을 알고있다면 범위 내에서 작업하면 쿼리를 보다 효율적으로 만들 수 있다. 테이블 크기 줄이기 1 2 3 4 SELECT   *    FROM  benn.sample_event_table   WHERE  event_date  > =   '2014-03-01'    AND event_date  <    '2014-04-01' cs 1 2 3 SELECT  COUNT( * )    FROM  benn.sample_event_table   LIMIT   100 cs 1 2 3 4 5 6 SELECT  COUNT( * )    FROM  (      SELECT   *        FROM  benn.sample_event_table       LIMIT ...
자세한 내용 보기

데이터 크롤링(3주차)

-
이미지
 데이터를 시각화하고 분석을 진행함에 앞서 필요한 데이터들을 수집가능한지 어떤 문제점이 있는지 알아보기 위해 테스트를 진행해 보았다. 먼저 네이버 금융에 들어가 IT서비스 분야를 선택하면 다음과 같은 화면이 나타난다. 이 곳에서 필요한 데이터를 수집해 보기로 했다. 일단 종목명부터 수집해 보았다. import requests from pprint import pprint from bs4 import BeautifulSoup 먼저 웹 크롤링을 하기 위해 필요한 패키지들을 불러왔다. html = requests.get('https://finance.naver.com/sise/sise_group_detail.nhn?type=upjong&no=154') # 웹 페이지 요청 soup = BeautifulSoup(html.text, 'html.parser') # 다루기 좋게 파싱작업진행 data2 = soup.find_all('div', {'class':'name_area'}) # 회사 이름들을 추출한다. len(data2) #66 66개의 모든 종목명을 추출했다. name=[] for i in range(len(data2)):     name.append(soup.find_all('div', {'class':'name_area'})[i].get_text()) print(name) 리스트로 만들어 좀 더 보기 쉽게 만들었다. 결과는 다음과 같다. name1=[] for i in range(len(name)):     name1.append(name[i].replace('*', '').replace(' ','')) print(name1) 종목명에 특수문자와 공백을 제거했다. 결과는 다음과 같다. 이제 종목명 옆에 있는 다른 값들을 추출해 볼것이다. 이 곳에서 두가지의 문제점을 발견한다. 1. na...
자세한 내용 보기

SQL(8)(writing subqueries, window functions)

-
이미지
WRITING SUBQUERIES 하위쿼리는 쿼리내의 여러 위치에서 사용할 수 있지만 from문으로 시작하는 것이 가장 쉽다. 1 2 3 4 5 6 7 SELECT  sub. *    FROM  (          SELECT   *            FROM  tutorial.sf_crime_incidents_2014_01           WHERE  day_of_week  =   'Friday'        ) sub   WHERE  sub.resolution  =   'NONE' cs 1 2 3 SELECT   *    FROM  tutorial.sf_crime_incidents_2014_01   WHERE  day_of_week  =   'Friday' cs 연습문제 모든 Warrant Arrests를 선택하는 쿼리를 작성하고 해결되지 않은 사건만 표시하는 외부쿼리로 래핑 1 2 3 4 5 6 7 SELECT  sub. *    FROM  (          SELECT   *            FROM  tutorial.sf_crime_incidents_2014_01        ...
자세한 내용 보기