SQL
- NULL은 비교할 수 없음
A = { 1,2, NULL } WHERE A < > 2
일 때 A는 1만 나옴 → nvl 함수 쓰자
- A번째 에서 B번만큼 (중간의 몇개만 select)
LIMIT a offset b
- WITH문
WITH ALIAS명_1 AS ( SUB쿼리 ), ALIAS명_2 AS ( SUB쿼리 ) SELECT 컬럼명 FROM ALIAS명~~~
- like 를 in 처럼
SELECT S1.주소, S2.시군구 FROM 고객리스트 S1 LEFT OUTER JOIN (SELECT 시군구 FROM 주소) S2 ON (S1.주소 LIKE '%'||S2.시군구||'%')
window function
window function - Frame phrase
https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=theswice&logNo=221320203566
range와 rows 차이
계층형 질의
https://heodolf.tistory.com/72
조직도 등 (직원id - 상사id) 구조로 되어 있는경우
- Ansi : With 재귀적 방법
- ORACLE : CONNECT BY
Impala
- unixtime 형태의 string → 시간으로
- FROM_UNIXTIME(BIGINT unixtime [, STRING pattern])
from_unixtime(cast(A.time as BIGINT), "yyyy-MM-dd HH:mm:ss")
HIVE
- INSERT INTO vs. INSERT OVERWRITE
- row 더하기 vs. 덮어쓰기
- partition 문과 함께
Examples
SQL z-score 구하기
SQL을 통해서 표준화점수 구하기
$$ z-score = \frac{X_i - Mean(X)}{std(X)} $$
- Key : 평균, 표준편차는 하나의 값이니까, croos join으로 해결 group by 처럼 되면 안됨.
SELECT (T1.values - stats.average) / stats.s AS zvalue FROM [TABLE] T1 cross join (select sttdev(values) as s, avg(values) as average from [TABLE]) stats;
SQL 중앙값 구하기
예시 : Hackerank : weather observation station 20
https://www.hackerrank.com/challenges/weather-observation-station-20/problem
중복을 피해야 하므로 rank가 아닌 row_number 를 쓰는 것 유념
전체 데이터 수가 홀수 일때, 짝수 일 때 나눠서 계산
select round(
avg(
case when (select count(*)+1 from station) % 2 = 1 then -- n+1이 핵심 (데이터 수가 짝수면,)
case when abs(rnk - (select count(*)+1 from station)/2)=1/2 then lat_n else null end --중간 양쪽
else case when rnk = (select count(*)+1 from station)/2 then lat_n else null end -- 홀수면 중간 (n+1)/2
end
),4)
from (
select lat_n, row_number() over(order by lat_n asc) as rnk from station
) T
«SQL 레벨업, 미크, 윤인성 역, 2016, 한빛미디어»
자연수 특성 이용
큰 순서대로(lo), 작은순서대로(hi) 출발해서
데이터 수가 홀수이면 중앙값에서 만나고, 짝수이면 hi = lo +- 1 일 때 만남
select avg(lat_n)
from (select lat_n,
row_number() over (order by lat_n asc) as hi
,row_number() over (order by lat_n desc) as lo
from station ) T
where hi in (lo, lo+1, lo-1)
좀 더 빠르게
row_number()에서의 order by 정렬 한번만 사용하므로(count에 order by가 없음) 성능이 더 좋음
$ 0 \leq 2*r - N \leq 2 \Leftrightarrow \frac{2}{N} \leq r \leq 1+ \frac{2}{N} $
select avg(lat_n)
from (select lat_n, 2 * row_number() over(order by lat_n asc) - count(*) over() as diff
from station ) T
where diff between 0 and 2
Discussion