SQL(4)(group by, having, case, distinct, join)

-

GROUP BY

데이터를 그룹으로 분리하여 서로 독립적으로 집계할 수 있다.

1
2
3
4
SELECT year,
       COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year
cs












1
2
3
4
5
SELECT year,
       month,
       COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month
cs

여러 열로 그룹화 할 수 있지만 위와 같이 쉼표로 열 이름을 구분해야한다.









연습문제

매일 거래되는 총 주식 수를 계산하고, 결과를 시간순으로 정렬하십시오.

1
2
3
4
5
6
SELECT year,
       month,
       SUM(volume) AS volume_sum
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month
 ORDER BY year, month
cs










열번호

열 이름을 숫자로 대체할 수 있다. 

1
2
3
4
5
SELECT year,
       month,
       COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
 GROUP BY 1, 2
cs









ORDER BY와 함께 사용

1
2
3
4
5
6
SELECT year,
       month,
       COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month
 ORDER BY month, year
cs









연습문제

연도별로 그룹화 된 Apple 주식의 일일 평균 가격 변동을 계산하는 쿼리

1
2
3
4
5
SELECT year,
       AVG(close - open) AS avg_daily_change
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year
 ORDER BY year
cs












Apple 주식이 매달 달성한 최저 및 최고 가격을 계산하는 쿼리

1
2
3
4
5
6
7
SELECT year,
      month,
      min(low) as low,
      max(high) as high
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month
 ORDER BY year, month
cs








HAVING

where절은 집계 열을 필터링 할 수 없기 때문에 having절을 사용한다.

1
2
3
4
5
6
7
SELECT year,
       month,
       MAX(high) AS month_high
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month
HAVING MAX(high) > 400
 ORDER BY year, month
cs










쿼리 절 순서

절을 작성하는 순서가 중요하다. 지금까지 배운 내용의 순서는 다음과 같다.

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

CASE

1
2
3
4
5
SELECT player_name,
       year,
       CASE WHEN year = 'SR' THEN 'yes'
            ELSE 'no' END AS is_a_senior
  FROM benn.college_football_players
cs

위와 같이 작성하면 'year'가 'SR'이면 'yes', 그렇지 않다면 'no'로 is_a_senior라는 열을 생성한다.










연습문제

플레이어가 캘리포니아 출신인 경우 'yes' 플래그가 지정된 열을 포함하는 쿼리를 작성하고, 해당 플레이어와 함께 결과를 먼저 정렬한다.

1
2
3
4
5
6
SELECT player_name,
       state,
       CASE WHEN state = 'CA' THEN 'yes'
            ELSE NULL END AS from_california
  FROM benn.college_football_players
 ORDER BY player_name
cs










여러 조건문

1
2
3
4
5
6
7
SELECT player_name,
       weight,
       CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 THEN '201-250'
            WHEN weight > 175 THEN '176-200'
            ELSE '175 or under' END AS weight_group
  FROM benn.college_football_players
cs

1
2
3
4
5
6
7
SELECT player_name,
       weight,
       CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 AND weight <= 250 THEN '201-250'
            WHEN weight > 175 AND weight <= 200 THEN '176-200'
            ELSE '175 or under' END AS weight_group
  FROM benn.college_football_players
cs

위의 두 결과는 같지만 겹치치 않게 문장을 만드는 것이 좋다.









연습문제

선수의 이름과 키를 기준으로 네 가지 범주로 분류하는 열을 포함하는 쿼리를 작성

1
2
3
4
5
6
7
SELECT player_name,
       height,
       CASE WHEN height > 74 THEN 'over 74'
            WHEN height > 72 AND height <= 74 THEN '73-74'
            WHEN height > 70 AND height <= 72 THEN '71-72'
            ELSE 'under 70' END AS height_group
  FROM benn.college_football_players
cs










1
2
3
4
SELECT player_name,
       CASE WHEN year = 'FR' AND position = 'WR' THEN 'frosh_wr'
            ELSE NULL END AS sample_case_statement
  FROM benn.college_football_players
cs

다음과 같이 and, or을 사용할 수 있다.











연습문제

모든 열을 선택하는 쿼리를 작성하고 해당 플레이어가 주니어 또는 시니어인 경우 플레이어의 이름을 표시하는 추가 열을 추가합니다.

1
2
3
SELECT *,
       CASE WHEN year IN ('JR', 'SR') THEN player_name ELSE NULL END AS upperclass_player_name
  FROM benn.college_football_players
cs















집계함수와 함께 사용

1
2
3
4
5
6
SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY CASE WHEN year = 'FR' THEN 'FR'
               ELSE 'Not FR' END
cs





1
2
3
4
5
6
7
8
SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY year_group
cs







1
2
3
4
5
6
7
8
9
10
11
12
SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY CASE WHEN year = 'FR' THEN 'FR'
               WHEN year = 'SO' THEN 'SO'
               WHEN year = 'JR' THEN 'JR'
               WHEN year = 'SR' THEN 'SR'
               ELSE 'No Year Data' END
cs







1
2
3
4
5
6
7
SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            *
  FROM benn.college_football_players
cs















연습문제

 West Coast(CA, OR, WA), Texas 및 기타 모든지역에 대해 300파운드 이상의 플레이어 수를 계산하는 쿼리

1
2
3
4
5
6
7
SELECT CASE WHEN state IN ('CA', 'OR', 'WA') THEN 'West Coast'
            WHEN state = 'TX' THEN 'Texas'
            ELSE 'Other' END AS arbitrary_regional_designation,
            COUNT(1) AS players
  FROM benn.college_football_players
 WHERE weight >= 300
 GROUP BY arbitrary_regional_designation
cs






캘리포니아의 모든 하위 클래스 선수(FR/SO)의 합산 가중치와 캘리포니아의 모든 상위 선수(JR/SR)의 합산 가중치를 계산하는 쿼리

1
2
3
4
5
6
7
SELECT CASE WHEN year IN ('FR', 'SO') THEN 'underclass'
            WHEN year IN ('JR', 'SR') THEN 'upperclass'
            ELSE NULL END AS class_group,
       SUM(weight) AS combined_player_weight
  FROM benn.college_football_players
 WHERE state = 'CA'
 GROUP BY class_group
cs





집계함수 내에서 사용

이전 예에서는 데이터가 세로로 표시되었지만 경우에 따라 데이터를 가로로 표시할 수 있ㄷ다.

1
2
3
4
5
SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
       COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
       COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr_count,
       COUNT(CASE WHEN year = 'SR' THEN 1 ELSE NULL END) AS sr_count
  FROM benn.college_football_players
cs




연습문제

FR, SO, JR, SR 플레이어를 별도의 열에 표시하고 총 플레이어 수에 대한 다른 열을 사용하여 각주의 플레이어 수를 표시하는 쿼리, 가장 많은 플레이어가 있는 상태가 먼저 오도록 결과를 정렬

1
2
3
4
5
6
7
8
9
SELECT state,
       COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
       COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
       COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr_count,
       COUNT(CASE WHEN year = 'SR' THEN 1 ELSE NULL END) AS sr_count,
       COUNT(1) AS total_players
  FROM benn.college_football_players
 GROUP BY state
 ORDER BY total_players DESC
cs








이름이 A부터 M까지 시작하는 학교의 선수 수와 N~Z로 시작하는 이름을 가진 학교의 수를 표시하는 쿼리

1
2
3
4
5
6
SELECT CASE WHEN school_name < 'n' THEN 'A-M'
            WHEN school_name >= 'n' THEN 'N-Z'
            ELSE NULL END AS school_name_group,
       COUNT(1) AS players
  FROM benn.college_football_players
 GROUP BY school_name_group
cs





DISTINCT

특정 열의 고유한 값만 보고싶을 때 사용

1
2
SELECT DISTINCT month
  FROM tutorial.aapl_historical_stock_price
cs









1
2
SELECT DISTINCT year, month
  FROM tutorial.aapl_historical_stock_price
cs









연습문제

year열의 고유값을 시간순으로 반환하는 쿼리를 작성

1
2
3
SELECT DISTINCT year
  FROM tutorial.aapl_historical_stock_price
 ORDER BY year
cs









집계에서 사용

1
2
SELECT COUNT(DISTINCT month) AS unique_months
  FROM tutorial.aapl_historical_stock_price
cs

집계에서 사용하면 쿼리 속도가 느려질 수 있다.






1
2
3
4
5
SELECT month,
       AVG(volume) AS avg_trade_volume
  FROM tutorial.aapl_historical_stock_price
 GROUP BY month
 ORDER BY avg_trade_volume DESC
cs









연습문제

month의 고유값 수를 계산하는 쿼리

1
2
3
4
5
SELECT year,
       COUNT(DISTINCT month) AS months_count
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year
 ORDER BY year
cs









month열의 고유값 수와 year열의 고유값 수를 별도로 계산하는 쿼리

1
2
3
SELECT COUNT(DISTINCT year) AS years_count,
       COUNT(DISTINCT month) AS months_count
  FROM tutorial.aapl_historical_stock_price
cs





JOIN

여러 테이블의 정보를 쉽게 결합 할 수 있다.

on으로 각 테이블의 동일한 필드에 의해 결합한다.

1
2
3
4
5
6
7
SELECT teams.conference AS conference,
       AVG(players.weight) AS average_weight
  FROM benn.college_football_players players
  JOIN benn.college_football_teams teams
    ON teams.school_name = players.school_name
 GROUP BY teams.conference
 ORDER BY AVG(players.weight) DESC
cs









연습문제

조지아의 모든 선수에 대한 학교 이름, 선수이름, 위치 및 체중을 선택하는 쿼리를 체중순으로 작성

1
2
3
4
5
6
7
SELECT players.school_name,
       players.player_name,
       players.position,
       players.weight
  FROM benn.college_football_players players
 WHERE players.state = 'GA'
 ORDER BY players.weight DESC
cs









join의 종류와 더 자세한 내용은 SQL(5)에서 계속해서 다룰 예정이다.



댓글

댓글 쓰기

이 블로그의 인기 게시물

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        ...
자세한 내용 보기