[SQL] SQL 중급

지난번 글에 이어서 Mode의 SQL 튜토리얼을 읽고 정리하고 실습해보고 있습니다. 오늘은 Count를 포함한 기초 산술연산 방법, 조건, 그리고 join에 대해서 다뤄보려고 합니다. 잘못 이해했거나 오류가 있는 부분은 알려주시면 정말 감사하겠습니다!

Mode에서 로그인 후 새 report를 만드셔서 따라하실 수 있습니다. 링크 아래에 달아두겠습니다.

https://app.mode.com/

Count

SELECT COUNT(*)
  FROM tutorial.aapl_historical_stock_price

특정 열의 행의 수를 계산합니다. *을 입력하면 전체 행의 수를 출력합니다. high를 입력하는 경우에는 null이 아닌 행의 수를 계산합니다( *보다 항상 작아요! )

열을 셀 수도, 숫자가 아닌 데이터도 갯수를 세는 것이기 때문에 가능합니다.

SELECT COUNT(low) AS low
  FROM tutorial.aapl_historical_stock_price

--

SELECT COUNT(date) AS count_of_date  -- non-numerical
  FROM tutorial.aapl_historical_stock_price

SUM, MIN/MAX

null을 0 취급합니다. 열의 합계를 출력할 수 있습니다.

SELECT SUM(volume)
  FROM tutorial.aapl_historical_stock_price

해당 열에서의 최소값과 최대값을 출력합니다.

SELECT MIN(volume) AS min_volume,
       MAX(volume) AS max_volume
  FROM tutorial.aapl_historical_stock_price

AVG

SELECT AVG(high)
  FROM tutorial.aapl_historical_stock_price

열의 평균을 계산하며, 숫자에만 가능합니다.

GROUP BY, HAVING

SELECT year,
       month,
       COUNT(*) AS count
  FROM tutorial.aapl_historical_stock_price
 GROUP BY year, month

설정한 값들을 기준으로 그룹으로 묶어줍니다. 예제의 경우에는 year과 month가 같은 항목들을 한 그룹으로 묶고 count를 출력해주는 것이 됩니다.

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

HAVING은 WHERE의 그룹화 버전이라고 생각하시면 됩니다. 그룹화된 항목들에 조건 필터링을 걸어줄 수 있어요.

Query clause order

쿼리 순서는 SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY 로 이루어져 있습니다.

CASE

SELECT player_name,
       year,
       CASE WHEN year = 'SR' THEN 'yes'
            ELSE NULL END AS is_a_senior
  FROM benn.college_football_players

일반 프로그래밍의 switch - case문과 동일합니다. WHEN = if이며, 조건문을 만족하면 'yes'를, 만족하지 않으면 NULL을 새로 추가된 is_a_senior 열에 추가하는 동작을 수행해요.

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

여러 조건을 추가하는 것도 가능합니다.

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

COUNT와 함께 쓰는 경우가 많은데, 위 예제를 뜯어보면,

SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            *
  FROM benn.college_football_players

여기까지 실행했을 때는 아래와 같은 결과가 나오는 것을 확인 할 수 있어요.

제일 앞 라인이 year_group이므로 count도 제일 앞 라인을 기준으로 하도록 되어있습니다(COUNT(1) AS count)

그리고 count 결과를 그룹화해서 출력해야하므로 GROUP BY 를 CASE문으로 나눠서 실행하면

이런 결과를 볼 수 있습니다. 이 예제는 새로 count한 행에 새 이름을 붙여주기 위해 위와 같은 작업을 거쳤는데, 간단하게는 아래와 같이 쓸 수도 있어요.

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 year_group

반대로 COUNT 안에 CASE를 넣을 수도 있습니다.

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

DISTINCT

고유값을 출력합니다.

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

위 예제처럼 쓰면, month의 고유한 값의 개수를 세서 unique_months에 추가하도록 되어있다. 달은 총 12개 있으므로 12가 추가되는 것을 볼 수 있습니다.

SELECT month,
       AVG(volume) AS avg_trade_volume
  FROM tutorial.aapl_historical_stock_price
 GROUP BY month
 ORDER BY 2 DESC

위 코드에서는 달과 거래량의 평균을 기준으로, month로 그룹화하고 거래량 평균으로 정렬했어요. 거래량 평균은 달을 그룹화했으므로 각 월에 해당하는 모든 거래량의 평균이라고 할 수 있습니다.

SQL join - 관계형 데이터베이스

Visual JOIN

Visual JOIN

많은 데이터가 존재할 때 데이터를 하나 변경하는데 아주 많은 비용이 발생하는 것은 어렵지 않게 예상할 수 있습니다. 따라서 결합(join)을 통해 특정 부분에 일치시켜서 연결해줌으로써 비용을 줄일 수가 있는데, 이 수많은 join들에 대해서 간단히 살펴봅시다.

SELECT *
  FROM benn.college_football_players players
  JOIN benn.college_football_teams teams
    ON teams.school_name = players.school_name

players 데이터베이스의 school_name와 teams 데이터베이스의 school_name 가 같은 부분을 players에 teams를 이식하는 것이 우리의 첫번째 목표입니다.

• teams 쿼리

• players 쿼리

• 결과

school name이 같은 곳에 teams의 정보를 뒤에 붙여서 join 해줬습니다.

Inner Join

교집합입니다. 기본적으로 일반적인 join을 썼을 때 inner join을 뜻하게 됩니다.

SELECT players.*,
       teams.*
  FROM benn.college_football_players players
  JOIN benn.college_football_teams teams
    ON teams.school_name = players.school_name

players와 teams의 목록 중 school name이 같은 것을 추려 players에 teams를 join해주고, 그것을 전체 scv chart에 출력해주는 코드입니다.

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)'

이 경우에는 위와 같은 조건에 teams의 division 조건이 FBS인 것만 뽑아냈어요.

SELECT A.ID,
             A.ENAME
             A.KNAME
FROM A INNER JOIN B
ON A.ID = B.ID

위와 같이 작성한 경우, A와 B의 ID가 같은 것만 추려서 SELECT됩니다. 나머지 A는 당연히 SELECT되지 않습니다.

Outer Join

합집합입니다. 전부 다 모아서 보여주는 방식입니다.

Left Join

공통적인 부분과 left 에 있는 table 1의 부분을 포함합니다. 

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
  JOIN tutorial.crunchbase_acquisitions acquisitions
    ON companies.permalink = acquisitions.company_permalink

위와 같이 실행하면 아래와 같이 280 North가 두 번 나타납니다. 이 경우 table 2에 table 1과 같은 항목이 있고 그대로 join했기 때문이에요.

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

이렇게 실행하면 null 값을 포함해서 들어갑니다.

Right Join

left join과 반대로 table 1과 2의 공통부분과 table 2 전체가 포함됩니다. 

 

---

아직 SQL에 대해서는 공부가 모자라서 아무래도 직접 데이터베이스를 만들어보거나 실전에서 사용하면서 더 늘면 수정하고 추가하도록 하겠습니다. mode는 처음 알고 써봤는데 문법 연습하기는 꽤 좋은 것 같아요! 그럼 다음 포스팅에서 뵙겠습니다 :)