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.시군구||'%')

SQL 면접 문제

window function

계층형 질의

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

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

Enter your comment:
E A O L L