Table of Contents

SQL

 A = { 1,2, NULL } WHERE A < > 2

일 때 A는 1만 나옴 → nvl 함수 쓰자 

 LIMIT a offset b

 WITH ALIAS명_1 AS ( SUB쿼리 ), ALIAS명_2 AS ( SUB쿼리 ) SELECT 컬럼명 FROM ALIAS명~~~

 
SELECT S1.주소, S2.시군구 FROM 고객리스트 S1 
LEFT OUTER JOIN (SELECT 시군구 FROM 주소) S2 
ON (S1.주소 LIKE '%'||S2.시군구||'%')

SQL 면접 문제

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) 구조로 되어 있는경우

Impala

 from_unixtime(cast(A.time as BIGINT), "yyyy-MM-dd HH:mm:ss")

HIVE

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;

0 Comments
, , ,

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

0 Comments
,
,