SQL(6)(union, joins with comparison operators, joins on multiple keys, self joins)

-

UNION

데이터를 다른 데이터 세트 위에 쌓을 수 있다. 

1
2
3
4
5
6
7
SELECT *
  FROM tutorial.crunchbase_investments_part1
 
 UNION
 
 SELECT *
   FROM tutorial.crunchbase_investments_part2
cs







union all은 중복행을 없애준다. 하지만 이 예는 중복행이 없으므로 union과 동일한 결과가 생성된다.

1
2
3
4
5
6
7
SELECT *
  FROM tutorial.crunchbase_investments_part1
 
 UNION ALL
 
 SELECT *
   FROM tutorial.crunchbase_investments_part2
cs








데이터 추가에 대한 규칙이 있다.

- 두 테이블 모두 동일한 수의 열을 가져야한다.

- 열은 첫 번째 테이블과 동일한 순서로 동일한 데이터 유형을 가져야한다.

연습문제

위 두 데이터 세트(중복값 포함)를 추가하는 쿼리를 작성. 첫 번째 데이터 세트를 이름이 "T"로 시작하는 회사로만 필터링하고 두 번째 데이터 세트를 "M"으로 시작하는 회사로 필터링

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT company_permalink,
       company_name,
       investor_name
  FROM tutorial.crunchbase_investments_part1
 WHERE company_name ILIKE 'T%'
 
 UNION ALL
 
SELECT company_permalink,
       company_name,
       investor_name
  FROM tutorial.crunchbase_investments_part2
 WHERE company_name ILIKE 'M%'
cs








3개의 열을 표시하는 쿼리 작성(데이터 출처, 회사 상태, 투자자 수)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 'investments_part1' AS dataset_name,
       companies.status,
       COUNT(DISTINCT investments.investor_permalink) AS investors
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
 GROUP BY dataset_name,companies.status
 
 UNION ALL
 
 SELECT 'investments_part2' AS dataset_name,
       companies.status,
       COUNT(DISTINCT investments.investor_permalink) AS investors
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part2 investments
    ON companies.permalink = investments.company_permalink
 GROUP BY dataset_name,companies.status
cs










JOINS WITH COMPARISON OPERATORS

조건문을 on절에 입력할 수 있다.

1
2
3
4
5
6
7
8
9
SELECT companies.permalink,
       companies.name,
       companies.status,
       COUNT(investments.investor_permalink) AS investors
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
   AND investments.funded_year > companies.founded_year + 5
 GROUP BY companies.permalink,companies.name, companies.status
cs








1
2
3
4
5
6
7
8
9
SELECT companies.permalink,
       companies.name,
       companies.status,
       COUNT(investments.investor_permalink) AS investors
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
 WHERE investments.funded_year > companies.founded_year + 5
 GROUP BY companies.permalink,companies.name, companies.status
cs

모든 행을 결합한 다음 필터링하는 대신 조건에 맞는 행만 결합하므로 위와 다른 결과가 생성된다.








JOINS ON MULTIPLE KEYS

여러 외래키에서 테이블을 조인하려는 이유는 정확성, 성능 관련이 있다.

1
2
3
4
5
6
7
8
SELECT companies.permalink,
       companies.name,
       investments.company_name,
       investments.company_permalink
  FROM tutorial.crunchbase_companies companies
  LEFT JOIN tutorial.crunchbase_investments_part1 investments
    ON companies.permalink = investments.company_permalink
   AND companies.name = investments.company_name
cs








SELF JOINS

아래 쿼리는 일본에서 투자한 후 영국으로부터 투자를 받은 회사를 식별하는 쿼리이다.

1
2
3
4
5
6
7
8
9
SELECT DISTINCT japan_investments.company_name,
       japan_investments.company_permalink
  FROM tutorial.crunchbase_investments_part1 japan_investments
  JOIN tutorial.crunchbase_investments_part1 gb_investments
    ON japan_investments.company_name = gb_investments.company_name
   AND gb_investments.investor_country_code = 'GBR'
   AND gb_investments.funded_at > japan_investments.funded_at
 WHERE japan_investments.investor_country_code = 'JPN'
 ORDER BY company_name
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        ...
자세한 내용 보기