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
          WHERE descript = 'WARRANT ARREST'
        ) sub
  WHERE sub.resolution = 'NONE'
cs








하위 쿼리를 사용하여 여러 단계로 집계

1
2
3
4
5
6
7
8
9
10
11
12
SELECT LEFT(sub.date, 2) AS cleaned_month,
       sub.day_of_week,
       AVG(sub.incidents) AS average_incidents
  FROM (
        SELECT day_of_week,
               date,
               COUNT(incidnt_num) AS incidents
          FROM tutorial.sf_crime_incidents_2014_01
         GROUP BY cleaned_month,sub.day_of_week
       ) sub
 GROUP BY cleaned_month,sub.day_of_week
 ORDER BY cleaned_month,sub.day_of_week
cs











일반적으로 내부 쿼리를 먼저 작성하고 결과가 이해 될 때까지 수정 한 다음 외부 쿼리로 이동하는 것이 가장 쉽다.

연습문제

각 범주에 대한 월 평균 사건 수를 표시하는 쿼리

1
2
3
4
5
6
7
8
9
10
SELECT sub.category,
       AVG(sub.incidents) AS avg_incidents_per_month
  FROM (
        SELECT EXTRACT('month' FROM cleaned_date) AS month,
               category,
               COUNT(1) AS incidents
          FROM tutorial.sf_crime_incidents_cleandate
         GROUP BY sub.category,avg_incidents_per_month
       ) sub
 GROUP BY sub.category
cs











조건부 논리의 하위 쿼리

1
2
3
4
5
SELECT *
  FROM tutorial.sf_crime_incidents_2014_01
 WHERE Date = (SELECT MIN(date)
                 FROM tutorial.sf_crime_incidents_2014_01
              )
cs








위 쿼리는 하위 쿼리의 결과가 하나의 셀이기 때문에 작동한다.

in 내부 쿼리에 여러 결과가 포함된 경우 작동하는 조건부 논리 유형은 다음과 같다.

1
2
3
4
5
6
7
SELECT *
  FROM tutorial.sf_crime_incidents_2014_01
 WHERE Date IN (SELECT date
                 FROM tutorial.sf_crime_incidents_2014_01
                ORDER BY date
                LIMIT 5
              )
cs








하위 쿼리 결합

조인에서 쿼리를 필터링 할 수 있다. where절에서 필터링하는 것이 아니라 외부 쿼리와 동일한 테이블을 조회하는 하위 쿼리를 조인하는 것이 일반적이다. 다음 쿼리는 이전 예제와 동일한 결과를 생성한다.

1
2
3
4
5
6
7
8
SELECT *
  FROM tutorial.sf_crime_incidents_2014_01 incidents
  JOIN ( SELECT date
           FROM tutorial.sf_crime_incidents_2014_01
          ORDER BY date
          LIMIT 5
       ) sub
    ON incidents.date = sub.date
cs








1
2
3
4
5
6
7
8
9
10
SELECT incidents.*,
       sub.incidents AS incidents_that_day
  FROM tutorial.sf_crime_incidents_2014_01 incidents
  JOIN ( SELECT date,
          COUNT(incidnt_num) AS incidents
           FROM tutorial.sf_crime_incidents_2014_01
          GROUP BY 1
       ) sub
    ON incidents.date = sub.date
 ORDER BY sub.incidents DESC, time
cs

위의 쿼리는 지정된 날짜에 보고 된 사고 수에 따라 모든 결과의 순위를 지정한다. 내부 쿼리에서 매일 총 인시던트 수를 집계한 다음 해당 값을 사용하여 외부 쿼리를 정렬하여 이를 수행한다.








연습문제

보고된 사고가 가장 적은 세 가지 범주의 모든 행을 표시하는 쿼리

1
2
3
4
5
6
7
8
9
10
11
12
SELECT incidents.*,
       sub.count AS total_incidents_in_category
  FROM tutorial.sf_crime_incidents_2014_01 incidents
  JOIN (
        SELECT category,
               COUNT(*) AS count
          FROM tutorial.sf_crime_incidents_2014_01
         GROUP BY 1
         ORDER BY 2
         LIMIT 3
       ) sub
    ON sub.category = incidents.category
cs








하위 쿼리는 쿼리 성능을 향상시키는데 매우 유용할 수 있다. 

1
2
3
4
SELECT COUNT(*)
  FROM tutorial.crunchbase_acquisitions acquisitions
  FULL JOIN tutorial.crunchbase_investments investments
    ON acquisitions.acquired_month = investments.funded_month
cs

7,414 * 83,893

두 테이블을 개별적으로 집계한 다음 이를 결합하여 훨씬 더 작은 데이터 세트에 걸쳐 카운틀를 수행하면 이 문제를 더 효율적으로 해결할 수 있다. 다음과 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT COALESCE(acquisitions.month, investments.month) AS month,
       acquisitions.companies_acquired,
       investments.companies_rec_investment
  FROM (
        SELECT acquired_month AS month,
               COUNT(DISTINCT company_permalink) AS companies_acquired
          FROM tutorial.crunchbase_acquisitions
         GROUP BY 1
       ) acquisitions
 
  FULL JOIN (
        SELECT funded_month AS month,
               COUNT(DISTINCT company_permalink) AS companies_rec_investment
          FROM tutorial.crunchbase_investments
         GROUP BY 1
       )investments
 
    ON acquisitions.month = investments.month
 ORDER BY 1 DESC
cs









연습문제

2012년 1분기부터 분기별로 설립되고 인수된 회사 수를 계산하는 쿼리를 작성. 두 개의 개별 쿼리에서 집계를 만든 다음 조인.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    SELECT COALESCE(companies.quarter, acquisitions.quarter) AS quarter,
           companies.companies_founded,
           acquisitions.companies_acquired
      FROM (
            SELECT founded_quarter AS quarter,
                   COUNT(permalink) AS companies_founded
              FROM tutorial.crunchbase_companies
             WHERE founded_year >= 2012
             GROUP BY 1
           ) companies
      
      LEFT JOIN (
            SELECT acquired_quarter AS quarter,
                   COUNT(DISTINCT company_permalink) AS companies_acquired
              FROM tutorial.crunchbase_acquisitions
             WHERE acquired_year >= 2012
             GROUP BY 1
           ) acquisitions
        
        ON companies.quarter = acquisitions.quarter
     ORDER BY 1
cs








하위쿼리 및 UNION

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







1
2
3
4
5
6
7
8
9
10
SELECT COUNT(*) AS total_rows
  FROM (
        SELECT *
          FROM tutorial.crunchbase_investments_part1
 
         UNION ALL
 
        SELECT *
          FROM tutorial.crunchbase_investments_part2
       ) sub
cs







연습문제

위와 결합된 데이터 세트에서 투자자가 수행한 총 투자수를 기준으로 순위를 매기는 쿼리

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT investor_name,
       COUNT(*) AS investments
  FROM (
        SELECT *
          FROM tutorial.crunchbase_investments_part1
         
         UNION ALL
        
         SELECT *
           FROM tutorial.crunchbase_investments_part2
       ) sub
 GROUP BY 1
 ORDER BY 2 DESC
cs









아직 운영중인 회사만 제외하고 이전 문제와 동일한 작업을 수행하는 쿼리를 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT investments.investor_name,
       COUNT(investments.*) AS investments
  FROM tutorial.crunchbase_companies companies
  JOIN (
        SELECT *
          FROM tutorial.crunchbase_investments_part1
         
         UNION ALL
        
         SELECT *
           FROM tutorial.crunchbase_investments_part2
       ) investments
    ON investments.company_permalink = companies.permalink
 WHERE companies.status = 'operating'
 GROUP BY 1
 ORDER BY 2 DESC
cs









WINDOW FUNCTIONS

1
2
3
SELECT duration_seconds,
       SUM(duration_seconds) OVER (ORDER BY start_time) AS running_total
  FROM tutorial.dc_bikeshare_q1_2012
cs









1
2
3
4
5
6
7
SELECT start_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_total
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
cs








연습문제

각 라이드의 지속 시간을 각 start_terminal에서 라이더가 누적한 총 시간의 백분율로 표시하는 쿼리

1
2
3
4
5
6
7
SELECT start_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER (PARTITION BY start_terminal) AS start_terminal_sum,
       (duration_seconds/SUM(duration_seconds) OVER (PARTITION BY start_terminal))*100 AS pct_of_total_time
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
 ORDER BY 1, 4 DESC
cs








SUM, COUNT, AVG

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT start_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_total,
       COUNT(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_count,
       AVG(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_avg
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
cs








연습문제

자전거 타기 지속 시간의 누적합계를 표시하지만 위 처럼 그룹화하고 지속시간이 내림차순으로 정렬하는 쿼리

1
2
3
4
5
6
7
SELECT end_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER
         (PARTITION BY end_terminal ORDER BY duration_seconds DESC)
         AS running_total
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
cs








ROW_NUMBER()

주어진 행의 번호를 표시, 시작은 1

1
2
3
4
5
6
7
SELECT start_terminal,
       start_time,
       duration_seconds,
       ROW_NUMBER() OVER (ORDER BY start_time)
                    AS row_number
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
cs









partition by절을 사용하면 각 파티션별로 카운트를 할 수 있다.

1
2
3
4
5
6
7
8
SELECT start_terminal,
       start_time,
       duration_seconds,
       ROW_NUMBER() OVER (PARTITION BY start_terminal
                          ORDER BY start_time)
                    AS row_number
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
cs









RANK(), DENSE_RANK()

rank는 동일 순위가 부여되고 순위를 건너 뛰지만 dense_rank는 순위를 건너 뛰지 않는다.

1
2
3
4
5
6
7
SELECT start_terminal,
       duration_seconds,
       RANK() OVER (PARTITION BY start_terminal
                    ORDER BY start_time)
              AS rank
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
cs









연습문제

각 출발 터미널에서 가장 긴 라이드 5개를 터미널별로 정렬하고 각 터미널 내에서 가장 긴 라이드에서 최단 라이드까지 표시하는 쿼리. 2012년 1월 8일 이전에 발생한 라읻드로 제한.

1
2
3
4
5
6
7
8
9
10
SELECT *
  FROM (
        SELECT start_terminal,
               start_time,
               duration_seconds AS trip_time,
               RANK() OVER (PARTITION BY start_terminal ORDER BY duration_seconds DESC) AS rank
          FROM tutorial.dc_bikeshare_q1_2012
         WHERE start_time < '2012-01-08'
               ) sub
 WHERE sub.rank <= 5
cs







NTILE

주어진 행이 속하는 백분위 수를 식별 할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT start_terminal,
       duration_seconds,
       NTILE(4) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
          AS quartile,
       NTILE(5) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
         AS quintile,
       NTILE(100) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
         AS percentile
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
cs








위 쿼리의 결과를 보면 percentile열이 예상 한대로 정확하게 계산되지 않는다. 100개 이상의 레코드가 있기 때문이다.

연습문제

여행 기간과 해당 기간이 속하는 백분위 수만 표시하는 쿼리

1
2
3
4
5
6
SELECT duration_seconds,
       NTILE(100) OVER (ORDER BY duration_seconds)
         AS percentile
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
 ORDER BY 1 DESC
cs









LAG, LEAD

가져올 열과 가져오기를 수행할 행수를 입력하기만 하면된다.

행간의 차이를 계산할 때 유용하다.

1
2
3
4
5
6
7
8
SELECT start_terminal,
       duration_seconds,
       duration_seconds -LAG(duration_seconds, 1) OVER
         (PARTITION BY start_terminal ORDER BY duration_seconds)
         AS difference
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
 ORDER BY start_terminal, duration_seconds
cs









가져올 이전 행이 없는 경우 null이 입력된다. 외부 쿼리로 null을 제거할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
  FROM (
    SELECT start_terminal,
           duration_seconds,
           duration_seconds -LAG(duration_seconds, 1) OVER
             (PARTITION BY start_terminal ORDER BY duration_seconds)
             AS difference
      FROM tutorial.dc_bikeshare_q1_2012
     WHERE start_time < '2012-01-08'
     ORDER BY start_terminal, duration_seconds
       ) sub
 WHERE sub.difference IS NOT NULL
cs









다음과 같이 명칭을 작성할 수 있다.

1
2
3
4
5
6
7
8
9
10
SELECT start_terminal,
       duration_seconds,
       NTILE(4) OVER ntile_window AS quartile,
       NTILE(5) OVER ntile_window AS quintile,
       NTILE(100) OVER ntile_window AS percentile
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'
WINDOW ntile_window AS
         (PARTITION BY start_terminal ORDER BY duration_seconds)
 ORDER BY start_terminal, duration_seconds
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...
자세한 내용 보기