13 Sat

데이터 분석을 위한 고급 SQL

윈도우 함수

• GROUP BY와 비슷한 역할을 한다.

• 차이점은, GROUP BY 하고자 한 컬럼만 보여주는 것이 아니라 원래 데이터에 추가하는 방식으로 동작하는 점.

• 함수 (컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

  • PARTITION BY 는 GROUP BY 와 쓰임이 동일

집계 함수

• MAX(컬럼) OVER (PARTITION BY 컬럼)

  • 파디션 별 레코드에 대한 최댓값을 구한다

• SUM(컬럼) OVER (ORDER BY 컬럼)

  • 모든 레코드에 대한 누적합을 구할 때 주로 사용한다

  • 윈도우 함수는 MySQL 신버전에서는 작동하지만 구버전에서는 지원하지 않으므로 윈도우 함수를 사용하지 않을 때에도 누적합(그 외의 기능도)을 구할 수 있어야 한다.

• SUM(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)

  • 파디션 별 레코드에 대한 누적합을 구한다

순위 정하기

• 순위를 정하는 함수들은 인자를 받지 않는다.

• ROW_NUMBER()

  • ROW_NUMBER() OVER (ORDER BY val) AS 'row_number'

  • val 에 따른 순위를 정해준다.

  • 공동 순위가 존재하지 않으며, 먼저 존재하는 레코드가 더 순위가 높다.

• RANK()

  • 공동 순위가 존재한다(비어있는 순위가 존재)

  • list = [100, 100, 80, 80, 60] 과 같은 데이터에 대해서 ``rank = [1, 1, 3, 3, 5]와 같은 rank를 반환한다.

• DENSE_RANK()

  • 공동 순위가 존재한다(비어있는 순위가 없음)

  • list = [100, 100, 80, 80, 60] 과 같은 데이터에 대해서 rank = [1, 1, 2, 2, 3]와 같은 rank를 반환한다.

데이터 위치 바꾸기

• LAG()

  • 데이터를 미는 함수

  • LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'

  • 이 때 밀리는 데이터의 처음 값은 NULL 값을 가지게 된다.

• LEAD()

  • 데이터를 당기는 함수

  • LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag'

  • 이 때 당겨지는 데이터의 마지막 값은 마지막 데이터는 NULL 값을 가지게 된다.

• LAG/LEAD (Column, Count, Default)

  • Count : 횟수

  • Default : NULL 대신 첨부될 값

윈도우 함수를 이용하지 않고 SQL로 누적합 구하기

• JOIN

select e1.id, e1.name, e1.kg, e1.line, sum(e2.kg) as Cumsum
from elevator e1
    inner join elevator as e2 on e1.id = e2.id
    and e1.line >= e2.line 
GROUP BY 1, 2, 3, 4

• SUBQUERY

select e1.id, e1.name, e1.kg, e1.line,
    (select sum(e2.kg)
    from elevator e2
    where e1.id = e2.id
    and e1.line >= e2.line as cumsum
from elevator e1