SQL 심화 문법 정리

이전 노션 블로그의 SQL Tutorial (2021.01.17)로부터 마이그레이션된 글입니다.

 

Aggreate Functions

aggregate functions(집계 함수)은 항상 사용하게 될 것이니 익숙해져야 한다. arithmetic operators는 한 행의 열간의 연산만 수행하지만, aggregate functions은 행 전체에 걸쳐서 수행할 수 있다.

 

COUNT

Counting all rows

COUNT 는 특정 열의 숫자를 세리는 aggregate function이다.. 아래의 두 SQL 문의 결과는 같다.

SELECT COUNT(*)
	FROM tutorial.aapl_historical_stock_price

SELECT *
	FROM tutorial.aapl_historical_stock_price

Counting individual columns

아래의 코드는 high 열이 is not null 인 모든 행을 세릴 것이다. 각각의 고유한 값들은 신경쓰지 않는다. 때문에 값의 타입에 상관없이 수행가능하다.

SELECT COUNT(high)
	FROM tutorial.aapl_historical_stock_price

결과가 COUNT(*) 보다 적은 것을 확인할 수 있다. high 의 일부만이 null 이기 때문이다. SQL을 사용하면서 자연스럽게 발생하는 null 행을 자주 보게 될것이다.

예를들어, 이메일 주소와 유저가 이메일을 읽은 시간과 날짜의 열이 포함된 유저 테이블이 있다고 가정해보자. 몇몇의 사람들이 이메일을 확인하지 않았다면, 시간과 날짜 필드는 null이 될 것이다.

결과의 열의 헤더가 그냥 "high"로 되어있다. 때문에 AS 를 써서 별도로 명명하는 것이 좋다. 기본적으로 소문자와 공백이 없는 것이 원칙이지만, 필요하다면 " " 을 사용하면 자유롭게 명명 가능하다.

 

SUM

The SQL SUM function

SUM 은 주어진 열들의 값의 합을 구한다. COUNT 와 달리, SUM 은 숫자값을 포함한 열에만 수행할 수 있다. 아래의 쿼리는 volumn 의 합을 구한다.

SELECT SUM(volume)
	FROM tutorial.aapl_historical_stock_price

기억해야하는 것은 : aggregator들은 수직으로만 집계한다. 한 행안에서 계산을 수행하고 싶으면 단순한 arithmetic 을 사용해라.

SUM 은 null을 0으로 처리하기 때문에, null로 인한 문제에 대해선 신경쓸 필요 없다.

 

MIN MAX

MIN MAX 는 특정 열의 값들 중 최소, 최대값을 반환한다.

COUNT 와 같이 숫자가 아닌 열들에도 사용할 수 있다. 열의 값타입에 따라서, MIN 은 최저값, 가장 이른 날짜, 숫자가 아닌 값은 가능한 알파벳적으로 "A"에 가까운 값을 반환하며 MAX 는 그 반대다.

 

AVG

AVG 는 특정 열의 값의 평균을 계산하여 반환한다. 단 2가지 한계가 있다.

1. 숫자인 값을 가진 열에만 사용 가능
2. null을 무시한다. (0으로 처리하는 것이 아니라 무시)

따라서 아래의 SQL문들은 같은 결과를 반환한다.

SELECT AVG(high)
	FROM tutorial.aapl_historical_stock_price
	WHERE high IS NOT NULL

SELECT AVG(high)
	FROM tutorial.aapl_historical_stock_price

null을 0으로 처리하고 싶은 경우, CASE 를 사용하면 된다.

 

 

---

GROUP BY

The SQL GROUP BY clause

COUNT AVG SUM 은 전체 테이블에 걸쳐 수행한다는 공통점이 있다. 하지만 테이블의 일부에서만 수행하길 원한다면? 예를들어, 연도별로 전체 값을 카운트하고 싶을 수 있다.

이런 경우, GROUP BY 가 필요하다. GROUP BY 는 데이터를 그룹으로 분리할 수 있으며, 데이터는 서로 독립적으로 집계한다.

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

여러 개의 열로 그룹화할 수 도 있다. 하지만 열의 이름으로 서로 구별시켜 줘야한다.

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

 

Using GROUP BY with ORDER BY

GROUP BY 절에서 열 이름의 순서는 중요하지 않다. (결과에 영향을 미치지 않는다) 어떻게 집계될지를 컨트롤하고 싶다면 ORDER BY 를 사용해라.

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

 

Using GROUP BY with LIMIT

SQL은 LIMIT 를 수행하기 전에 집계(aggregation)을 수행한다는 것을 알고 있어야합니다. GROUP BY 를 수행한 그룹화한 결과의 행이 LIMIT 양보다 많을경우, SQL은 집계를 모두 계산하여 결과를 산출한 이후, LIMIT 에 따라서 결과창에서 생략하기만 합니다. (계산자체가 생략되지 않습니다.)

올바른 집계 결과를 위해서는 이렇게 동작해야 합니다. 계산되는 열의 수까지 LIMIT 된다면, 틀린 결과가 나올 것입니다.

 

 

---

HAVING

The SQL HAVING clause

종종 GROUP BY 으로는 충분하지 않은 dataset를 만날것이다. month로 집계된 데이터를 예로 들어보자. dataset에는 수많은 month가 있을텐데, 그 중 AAPL 주식이 $400/share 인 경우만 찾고 싶다면 어떻게 해야할까? WHERE 절은 집계된 열을 필터링하는데 사용되지 않기 때문에 여기선 사용할 수 없다.

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` 은 집계된 쿼리를 필터링하기위한 "깔끔한" 방법이다. 하지만, 보통은 subquery를 이용한다.

 

Query clause order

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

 

---

CASE

The SQL CASE statement

CASE 는 if/then 로직을 처리한다. WHEN 과 THEN 으로 이러진 최소 한 개의 짝으로 구성되고, 모든 CASE 문의 마지막엔 END 가 와야한다. ELSE 는 선택 사항이며, WHEN/THEN 에 해당하지 않는 것들에게 값을 줄 수 있다.

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

 

 

Adding multiple conditions to a CASE statement

CASE 의 WHEN/THEN 은 위에서부터 실행되므로 아래와 같이 조건을 나열한다면 범위에 따른 조건을 적용시킬 수 있다.

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

조건을 겹치지않고 명확히 범위를 제시할 수 도 있다. 이때는 AND OR 를 사용하면 된다.

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

 

A quick review of CASE basics

1. CASE 는 언제나 SELECT 절안에 들어간다.
2. CASE 는 WHEN THEN END 로 이루어지며 선택적인 ELSE 도 있다.
3. WHEN 과 THEN 사이에서 WHERE 같은 어떤 조건 연산자로든 다양한 조건문을 만들 수 있다. AND OR 를 사용해서 여러 조건문을 묶을 수도 있다.
4. WHEN 으로 여러 조건문을 포함시킬 수 있고, ELSE 로 명시되지 않은 조건들을 처리할 수 있다.

 

Using CASE with aggregate functions

CASE 는 aggregate functions 과 함께 사용함으로서 더 복잡하고 유용하게 쓸 수 있다. 예를들어, 특정 조건을 채운 행만을 원한다면, COUNT 는 null을 무시하기 때문에 CASE 를 사용해서 조건을 평가하고 값이 결과에 따른 null 혹은 non-null 값을 도출한다.

 

 

---

DISTINCT

Using SQL DISTINCT from viewing unique values

특정 열안에 있는 유니크한 값만을 보기위해서 SELECT DISTINCT 문법을 쓰면 된다.

SELECT DISTINCT year, month
	FROM tutorial.aapl_historical_stock_price

DISTINCT 는 새로운 data set을 파악할때 쓰인다. 각 열의 유니크한 값들을 살펴보고 어떻게 데이터를 필터링하고 그룹화할건지 접근한다.

 

Using DISTINCT in aggregations

집계할때도 함께 사용할 수 있는데, 대부분 COUNT 와 함께 쓸 것이다.

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

이외에는 aggregation에서 그다지 사용될 일이 없습니다. SUM, AVG, MAX, MIN 등등은 유니크한 값들을 필요로 하지 않습니다.

 

DISTINCT performance

DISTINCT 을 사용하면 (특히 집계에서) 쿼리가 상당히 느려질 수 있습니다. 이에 대해서 뒤에서 자세히 다룰 것 입니다.

 

 

---

Joins

JOIN

지금까지는 한 번에 한 테이블에 대한 수행 했다. 하지만 SQL의 진정한 강점은 한번에 여러 개의 테이블로부터 데이터 처리를 수행하는 것 이다. 지난 수업들까지 사용한 테이블은 관계형 DB에서의 스키마의 일부다. 관계형 DB는 테이블끼리 서로 관계되어 있으며, 정보가 여러 테이블들과 쉽게 합쳐지기 위한 공통된 식별자가 있다.

 

joins가 무엇이가 뭐가 유용한지 이해하기 위해서는 트위터를 떠올려 보자

트위터는 많은 데이터를 저장해야한다. 트위터의 하나의 큰 테이블안의 각 행은 하나의 트위터를 나타낼 것이다. 각 트위터의 본문을 담은 열, 작성된 시간을 담은 열, 작성한 사람을 담은 열 등등이 행안에 있을 것이다. 여기서 트윗을 한 사람을 찾아내는 것은 조금 어렵습니다. 트위터가 테이블에 모든 데이터를 다음과 같이 저장한다고 가정해봅시다.

 

 

당신이 트윗할때마다, 트위터는 DB에 당신과 당신의 트윗에 대한 정보와 함께 새로운 행을 생성합니다.

하지만 여기서 문제가 발생합니다. 당신의 자기소개를 변경할때, 트위터는 테이블에 있는 당신의 트윗 하나하나마다 정보를 변경해줘야 하게 됩니다. 이런 구조 때문에, 트위터는 두가지 테이블로 구성되어야 합니다. 첫 번째 테이블 - 유저 테이블 - 프로필 정보를 가지고 있고 행마다 유저가 저장된다. 두 번재 테이블 - 트윗 테이블 - 트유ㅏㅅ 정보와 트위터를 작성한 사람의 username을 포함한다. 유저 테이블의 username에 트위터 테이블의 username을 matching/joining 함으로서, 트위터는 모든 트윗에 프로필 정보를 연결할 수 있다.

The anatomy of a join

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

 

Aliases in SQL

joins를 수행할 때, 테이블의 이름에 aliases를 붙이는 것이 가장 쉬운 방법이다. 소문자로 구성하며 공백대신 언더바를 쓴다. benn.college_football_players - players

 

JOIN and ON

ON 은 JOIN 다음에 오며, FROM 에 나온 테이블과 JOIN 에 나온 테이블의 관계를 설명합니다. 위의 SQL을 풀어 설명하면 다음과 같다.

players 테이블의 행 중에서 school_name 필드와 teams 테이블 행의 school_name 필드가 같은 경우 두 행을 서로 연결한다.

위와 같이 join으로 새로운테이블을 생성했다면, aggregate functions을 사용할 수 있게 된다.

다음과 같이 실행하면 두 테이블의 모든 열을 하나의 테이블로 출력한다. 만약 하나의 테이블에서만 출력하고 싶다면 players.* 를 SELECT 하면 된다.

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

 

 

 

INNER JOIN

INNER JOIN

players 테이블과 teams 테이블을 join 해보았는데, 만약 데이터가 깨끗하지 않다면? 만약 teams 테이블에 똑같은 이름의 학교가 여러개라면? 만약 teams 테이블에 없는 학교가 players 테이블에 있다면?

 

조인된 테이블 중 하나 또는 두 테이블 모두에 상대 테이블과 일치하지 않는 행을 가진 경우가 많습니다. 이런 경우, inner join 인지 outer join 인지에 따라 달라집니다.

inner joins은 JOIN 혹은 INNER JOIN 이라고 적으며, 모든 테이블에서 ON 의 조건을 만족하지 않는 행들을 제거합니다.

Joining tables with identical column names

두 테이블을 연결할 때, 양쪽 태이블에 똑같은 이름의 열을 가지고 있을 수 있다. 아래와 같은 경우, school_name 이라고 불리는 열을 양쪽다 가지고 있다.

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

두 열이 각자 다른 값을 가지고 있더라도 결과는 하나의 열만 지원한다. 이러한 경우를 방지하기 위해 열에 각각 이름을 지어주면 된다.

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

 

Outer Joins

LEFT TABLE = FROM TABLE / RIGHT TABLE = JOIN TABLE

• LEFT JOIN 은 왼쪽 테이블 반환 (매칭되지 않은 왼쪽 테이블 + 매칭된 테이블)
• RIGHT JOIN 은 오른쪽 테이블반환 (매칭되지 않은 오른쪽 테이블 + 매칭된 테이블)
• FULL OUTER JOIN 은 양쪽 테이블 반환 (..)

 

---

UNION

JOIN 은 두 개의 dataset을 나란하게 섞었지만, UNION 은 하나의 dateset을 다른 하나의 위에 (stack)쌓는다. UNION 은 별도로 두 개의 SELECT 문의 사용이 가능하며, 두 개의 결과는 하나의 테이블에 한 번에 출력된다. 쿼리의 첫 부분의 결과가 먼저 출력되고, 그 이후에 두번째 쿼리의 결과가 출력된다.

SELECT *
  FROM tutorial.crunchbase_investments_part1

 UNION

 SELECT *
   FROM tutorial.crunchbase_investments_part2

UNION 는 오직 고유한(distinct) 값만 결과로 만든다. 즉, 첫 번째 문의 결과가 테이블에 추가(append)되고나서 두 번째 문의 결과가 추가할때 겹치는 행은 추가되지 않는다. 고유하지 않고 모든 문의 결과를 추가하고 싶은 경우, UNION ALL 을 사용해야 한다.

SELECT *
  FROM tutorial.crunchbase_investments_part1

 UNION ALL

 SELECT *
   FROM tutorial.crunchbase_investments_part2

SQL은 데이터 추가(append)에 있어서 까다로운 룰이 있다

1. 양쪽 테이블은 같은 수의 열을 가지고 있어야만 한다.
2. 양쪽 테이블은 같은 순서로 같은 데이터 타입을 가지고 있어야 한다.

열의 이름은 같아야만 하는 것은 아니지만, UNION 을 사용할 때는 대게 같은 것이다. 위의 예제처럼 형식은 같지만 분리된 dataset을 연결할 때 주로 사용하기 때문이다.