BOAZ SQL 스터디(2)-intermediate SQL(1)

업데이트: October 26, 2019

SQL Aggregate Functions

Apple stock prices dataset를 사용할 것이다.

Aggregate functions in SQL

COUNT
SUM
MIN / MAX
AVG

row를 기준으로 수행하는 산술연산은 이전 절에서 다뤘다. agg func은 col의 전체 row를 기준으로 수행한다.

COUNT

Counting all rows

COUNT(1)은 COUNT(*)과 같다.

Counting individual columns

SELECT COUNT(high)
  FROM tutorial.aapl_historical_stock_price

high column이 not null인 경우만 count한다.

Counting non-numerical columns

SELECT COUNT(date)
  FROM tutorial.aapl_historical_stock_price

COUNT는 단순 non-null 값만 세기 떄문에 구별된(distinct) 값을 세지 않는다.

DISTINCT로 구별된 값을 가져올 수 있음

결과 컬럼명을 다른 이름으로 바꾸고 싶으면 AS문을 사용하면 된다.

공백 넣고 싶으면 "쓰면 된다.

SQL에서 "는 이렇게만 쓰인다. 반면 '는 여러 방법에서 쓰임

SUM

SELECT SUM(volume)
  FROM tutorial.aapl_historical_stock_price

agg func은 수직으로만 적용한다. row로 산술연산 하지 않는다

SUM에서 null 값은 0으로 처리한다.

MIN / MAX

non-numerical column에서 column type에 따라 연산이 다르다.

MIN은 가장 작은 수, 가장 빠른 날짜, 사전식으로 가장 빠른 문자. MAX는 반대이다.

AVG

오직 숫자형 컬럼에만 사용될 수 있다.

NULL을 완벽하게 무시한다.

NULL값을 0으로 생각해서 연산하고 싶으면 추가 구문을 작성해야 한다.

GROUP BY

지금까지의 연산들은 전체 테이블을 기준으로 수행했다. 테이블의 일부분에 수행해야 한다면 GROUP BY써야 한다.

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

여러 컬럼을 GROUP BY 할 수도 있다.

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

컬럼 이름 대신 숫자로도 접근할 수 있다. 모든 SQL에 적용되는 것은 아님!!

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

aggregation은 LIMIT 실행 전에 수행된다. LIMIT문과 함께 쓸 때 초과된 row를 포함해서 수행된다.

HAVING

WHERE문은 aggregate 할 컬럼에 대해서는 조건문을 수행할 수 없다. HAVING 문으로 할 수 있다.

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, months

subquery를 통해서도 같은 결과를 얻을 수 있다.

Query clause order

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

DISTINCT

SELECT DISTINCT month
  FROM tutorial.aapl_historical_stock_price

여러 컬럼을 포함하면 유니크한 쌍을 전부 보여준다.

SELECT DISTINCT year, month
  FROM tutorial.aapl_historical_stock_price

Using DISTICT in aggregations

유니크한 month의 수를 셀 수 있다.

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

DISTINCT는 SELECT문 말고 agg func 안에 들어간다. SUM과 AVG도 사용 가능하다.하지만 MAX / MIN은 DISTINCT를 쓸 필요가 없다.

DISTINCT performance

특히 agg에서 DISTINCT를 사용하면 쿼리 속도가 꽤 느려질 수 있습니다.

CASE

SQL의 if / then 구문이다.

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

조건문을 체크한다
TRUE이면 THEN 문을 실행
FALSE이면 null 값을 남긴다.
AS로 컬럼명을 지정한다.

multiple conditions to a CASE statement

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

WHERE문과 같이 AND와 OR로 조건문을 수행할 수 있다.

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

review

CASE문은 SELECT안에 있어야 한다.
CASE는 WHEN, THEN, END를 꼭 포함해야 하고,,ELSE는 선택적으로 포함할 수 있다.

CASE with aggregate functions

CASE는 agg func와 함께 쓰면 더 유용하다.

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

그냥 WHERE절 써도 비슷한 결과를 얻을 수 있다.

SELECT COUNT(1) AS fr_count
  FROM benn.college_football_players
 WHERE year = 'FR'

그러나, WHERE는 하나의 조건을 통해 나온 결과만 카운트 가능하다. 여러 조건을 통해 나온 여러 쌍을 카운트하려면

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 1

group by로 전체 CASE구문을 써도 되고, 숫자도 되고, AS문으로 만든 alias도 된다.

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

전체 CASE구문을 반복해서 쓰려면, GROUP BY 절에서 AS문을 제거해야 한다.

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

CASE문과 aggregateion func를 섞어 쓰는 건 까다롭다. 처음에는 CASE문만 먼저 작성하고 실행시켜 보자.

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

이후에 *을 aggregation과 GROUP BY 문으로 바꾸자.

남궁찬