기본
주석처리
-- : 한 줄
/* */ : 여러 줄SELECT, GROUP BY, ORDER BY, JOIN, HAVING, LIMIT 짬뽕
select count(*) as count, avg(rental_rate) as average, rating
from film
group by rating
having sum(rental_rate) > 5
order by average desc
limit 5
- * 하나만 Select한 상태에서 group by, order by를 쓰면 버그가 난다.
- Ordering을 할 때 NULL값들의 순서를 바꾸고 싶다면 = NULLS FIRST , NULLS LAST
- Order By 안에 집계함수가 사용될 수 있다. ORDER BY COUNT(SUBMISSIONS.HACKER_ID) DESC
Distinct
특정 컬럼에 distinct를 붙일 수 있다.
SELECT COUNT(DISTINCT NAME) FROM ANINAML
만약 집계함수 안이 아닌 바깥 컬럼에 Distinct를 붙이면 SELECT에 명시된 전체 컬럼의 값을 distinct시킨다
Group By
JOIN
INNER JOIN : 테이블끼리 매칭이 되는 애들만 ROW로 보여준다
LEFT OUTER JOIN : 우선 왼쪽(from 기준) 테이블의 ROW를 전부 가져온 후, 매칭되는 애들은 연결시켜주고 나머지는 NONE으로 표시한다.
select *
from customer_table C
INNER JOIN order_table O
ON C.id = O.customer_id
WHERE O.id IS NULL
- JOIN하는 테이블들 간의 관계를 잘 파악할 것
- INNER JOIN과 LEFT JOIN을 언제 사용할지 확인할 것 (대부분 LEFT가 INNER보단 안전)
Having
Group By에 의해 생성된 결과를 제한할 때 사용한다. 보통 그룹함수 COUNT, SUM, AVG, MAX, MIN 등과 함께 사용된다.
Group By 1 은 Select의 첫번째 컬럼을 기준으로 정렬하겠다는 뜻. Order By 1도 동일하다고 보면 됨.
SubQuery
[종류]
- 단일행 서브쿼리
- select player_name from player where team_id = ( select team_id from player where player_name = "yonglae" )
- 다중행 서브쿼리
- select player_name from player where team_id in ( select team_id from player where player_name = "yonglae" )
- 다중컬럼 서브쿼리
- select player_name from player where (team_id,height) in ( select team_id, min(height) from player group by team_id )
FROM 절에서도 서브쿼리 사용이 가능함 (from에 복수 테이블 넣으면 cross join이 됨)
select t.team_name, p.player_name, p.height
from player t, (
select team_id, player_name, back_no
from player
where position = 'MF') p
)
where p.team_id = t.team_id
WITH AS
임시테이블을 만들어 이름을 부여하는 구문. SubQuery를 재사용할 떄가 많을 때 사용함.
with eee as (
select job, sum(sal) as total from emp group by job
)
select job, total from eee where total > (select avg(total) from eee)
UNION
두 개의 SQL 결과를 합치는 연산임
- 기본적으로 중복을 제거해줌 (UNION ALL 중복 허용)
- 컬럼의 개수와 순서가 모든 쿼리에서 동일해야 함
- 데이터 형식이 같아야함 (이름은 달라도 된다는 이야기)
- 그러나 ORDER BY를 양쪽에서 할 수는 없다. 한쪽에서만 해야됨.
select name from classa
Union
select name from classb
COUNT
- 1은 *와 같다
- 정답 : 7, 6, 4
IF
if(조건, valIfTrue, valIfFalse)
select if(name = 'grab','O','X') from ...
[참고] Postgresql은 select if 문이 없음
CASE WHEN
case 컬럼
when 조건값 then 값
...
else 값
end;
case name
when 'grab' then 'right'
when 'larry' then 'unright'
else 'idontknow'
end;
NULL
IS NULL & IS NOT NULL & ISNULL 이 있음
is null은 조건문에서 null인지를 확인할 때 사용됨
isnull 은 첫번째 인자가 null이면 두번째 인자를 return한다.
nullif 는 첫번째, 두번째 값이 같다면 null을, 같지 않다면 첫번째 값을 return함
coalesce 는 복수의 인자들을 입력했을 때, 순차적으로 NULL이 아닌 인자 값을 return함.
mysql 은 isnull 대신 ifnull을 사용함
TYPE CAST & Converison
컬럼은 여러 타입이 존재함. 보통 (필드이름::새타입) 혹은 case(필드이름 as 새타입) 을 사용함.
select amount::float from bank
CTAS
보통 Create Table → Insert Values를 한다면
CTAS 를 사용하면 수월하게 테이블을 생성해서 값까지 사용할 수 있다.
CREATE TABLE 스키마.channel AS
SELECT DISTINCT channel FROM ...
CREATE TABLE LIKE
이미 있는 테이블의 스키마를 가지고 새로 테이블을 만들고 싶다면?
CREATE TABLE 테이블 LIKE 복사할_테이블
Create Temp Table ~
Temp Table은 Session내에서만 사용되는 가상 테이블이라고 보면 됨.
Session과 Conenction 차이
Connection으로 외부 프로세스와 데이터베이스 인스턴스가 연결됨
그리고 인스턴스 내부에서 Session이 생성됨.
집계(Aggregate) 함수
GROUP_CONCAT
그루핑된 문자열들 중 NULL이 아닌 값들을 Concatenation한다. PIVOT에서 그루핑된 레코드 중에서 해당하는 문자열을 구할 때 GROUP_CONCAT 사용하면 해당 값만 가져올 수 있다.
윈도우 함수
윈도우 함수는 **행과 행 간의 관계**를 정의하며 일반적으로 GROUP BY와 함께 사용할 수 없다. (서브 쿼리로 처리해야 함) 윈도우 함수를 사용하면 행의 순서는 바뀔 수 있다.
- PARTITION 구문 : GROUP BY와 비슷하게 파티션 영역을 나눈다.
- ORDER BY : 항목에 대해 정렬한다
- Windowing : 윈도우 함수가 적용될 ROW의 적용 구간을 정한다.
탐색함수 : LEAD, LAG, FIRST_VALUE, LAST_VALUE
번호 지정 함수 : RANK, DENSE_RANK, ROW_NUMBER ...
집계 분석 함수 : 기존 집계 함수들, AVG, COUNT, ...
보통 ROWS BETWEEN A AND B 구조로 파티션 영역을 나눌 때 사용한다. 이때 A와 B에는 UNBOUNDED PRECEDING(맨 처음 행), CURRENT ROW, UNBOUNDED FOLLOWING (맨 마지막 행)이 대표적으로 들어간다.
[주의]
- Window Function은 Group By 와 함께 사용할 수 없다.
RANK, ROW_NUMBER
Rank와 ROW_NUMBER는 유사지만 RANK는 공동 순위를 매겨줌 (빅쿼리는 ROW_NUMBER없음)
참고 : https://velog.io/@yewon-july/Window-Function
https://medium.com/humanscape-tech/sql의-window-function-a9e5a58212e2
https://dataschool.com/how-to-teach-people-sql/how-window-functions-work/
LAG, LEAD
참고 : https://devjhs.tistory.com/180
LAG : 명시된 값을 기준으로 이전 로우의 값 반환
LEAD : 명시된 값을 기준으로 이후 로우의 값 반환
[예시] https://www.mysqltutorial.org/mysql-window-functions/mysql-first_value-function/
PIVOT
정규화된 테이블들을 크로스 집계해서 비정규화시키는 기술. OLAP 데이터 분석을 할 때 굉장히 자주 사용되는 기술임. 보통 디멘션(차원)과 측정 값으로 나뉜다. 일반적인 RDB의 값들이 1차원이라면 PIVOT을 거친 테이블은 다차원 데이터를 만든다고 보면 된다.
측정값의 경우 집계함수를 넣어서 해당되는 여러 ROW들을 집계하는 편이다.
문자, 숫자 처리 함수
https://hayden-archive.tistory.com/113
문자열
SUBSTRING (문자열, 시작위치, 개수) : 시작위치 만큼 해서 개수까지 가능
LEFT(문자열, 개수) : 문자열 중 왼쪽 개수 출력
RIGHT(문자열, 개수) : 문자열 중 오른쪽 개수 출력
숫자
일반적으로 반올림, 버림하는 아래 함수들은 자릿수를 -로 하면 정수 범위의 자릿수를 처리할 수 있다. https://devjhs.tistory.com/87
ROUND : 반올림 (자릿수 설정 가능)
TRUNCATE : 내림 (자릿수 설정 가능)
CEILING : 소숫점 올림(자릿수 설정 불가능)
FLOOR : 소숫점 버림 (자릿수 설정 불가능)
[참고] Postgresql에서 ROUND 함수를 쓸 때 함수의 첫번째 인자에 numeric이 들어가야 한다. float으로 형변환하면 오류남. ROUND(D_QTY::numeric/P_QTY,3)
MYSQL 헷갈리는 부분 정리
- NULL CHECK를 위해 IFNULL 이 사용된다. COALESCE도 사용됨
- 조건문으로 CASE WHEN , IF(조건, 참, 거짓) 모두 사용된다.
- 타입 캐스팅을 위해 CAST(a AS 타입)
- ORDER BY에서 CASE 조건을 넣어줄 때 string을 넣으면 안된다. (CASE WHEN 조건1 THEN 필드명 END) ASC 이런식으로 처리해줘야 함
https://extbrain.tistory.com/66
http://www.joshi.co.kr/index.php?mid=board_iuyq53&document_srl=306261