데이터베이스 : 5장 Advanced SQL(2)

1. Advanced Aggregation Features

1) Ranking

• rank은 order by와 함께 수행되어야 한다.
• 만약 student_grades(ID, GPA) relation에서 각 학생가 주어진다면
  • 각 학생의 rank를 구한다.
    • select ID, rank() over (order by GPA desc) as s_rank from student_grades
  • order by를 추가적으로 사용하여 rank순으로 정렬할 수 있다,
    • select ID, rank() over (order by GPA desc) as s_rank from student_grades order by s_rank
• Ranking은 gap(격차)를 남긴다..
  • 예를 들어, 두 학생 모두 최상위 GPA가 같다면 두 학생 모두 1위이고 다음 순위는 3위로 지정할 수 있다.
  • dense_rank는격차를 남기지 않고 다음 dense rank를 2로 할 수 있다.
• basic SQL aggregation를 사용하여 ranking을 매길 수 있지만, resultant query는 매우 비효율적이다.
  • select ID, (1 + (select count(*)
                              from student_grades B
                              where B.GPA > A.GPA)) as s_rank
    from student_grades A
    order by s_rank
• Ranking은 데이터의 partition 내에서 순위를 매길 수 있다.
  • 각 학과 내에서 학생의 순위를 구해라
    • select ID, dept_name, rank() over (partition by dept_name order by GPA desc) as dept_rank
      from dept_grades
      order by dept_name, dept_rank
• 단일 select 절에서 여러 rank 절이 존재할 수 있음
• Ranking은 group by 절/집계 이후에 적용된다.
  • 학과별 급여 총합 순위를 구해라
    • select dept_name, sum(salary), rank() over (order by (sum(salary)) desc) as s_rank
      from instructor
      group by dept_name
• 상위 n개의 결과를 구할 수 있다.
  • 각 파티션 내에서 top-n을 허용하므로 많은 데이터베이스에서 지원하는 limit n 절보다 일반적이다.
  • select *
    from ( select ID, dept_name, rank() over (partition by dept_name order by GPA desc) as dept_rank
               from dept_grades
               order by dept_name, dept_rank)
    where dept_rank <= 5

<limit 사용 시와 limit 미사용 시 비교>

• 기타 ranking 함수
  1. percent_rank (within partition, if partitioning is done)
     • ( rank - 1 ) / ( total_rows - 1 ) 값을 통해 퍼센트순위를 구함(0 ~ 1사이의 값)
  2. cume_dist (cumulative distribution : 누적분포)
     • 특정한 칼럼의 값을 기준으로 순위에 따른 누적 분포 비율을 구해주는 함
  3. row_number (non-deterministic in presence of duplicates : 중복이 있는 경우 비결정론적)
• SQL:1999에서는 사용자가 nulls first인지 nulls last로 명시하도록 허락한다.(Null 처리 방법)
  • select ID, rank() over (order by GPA desc nulls last) as s_rank
    from student_grades
• 주어진 상수 n에 대해 함수 ntile(n)의 ranking은 각 파티션의 튜플을 지정된 순서로 가져가서 동일한 수의 튜플을 가진 n개의 bucket으로 나눈다.
  
  • GPA 내림차순으로 학생 ID를 4등분해라
    • select ID, ntile(4) over (order by GPA desc) as quartile
      from student_grades

2) Windowing

• 랜덤 변수를 부드럽게(smooth)하는 데 사용됨
• 예시) moving average : "날짜별 매출액을 지정하여 해당일, 전날, 다음날 매출액의 평균을 계산해라"
  • SQL에서 window specification(구현)
  • sales(date, value) relation이 주어진다.
    • select date, avg(value) over
                           ( order by date rows between 1 preceding and 1 following)
      from sales
      → 첫날의 경우 previous day가 없기 때문에 2일치에 대한 평균으로 계산된다.
• partition 내에서 windowing을 사용할 수 있다.
• 예시) transaction(account_number, date_time, value) relation이 주어지고, 여기서 value는 deposit(예금)에 대해 양수값이고 withdrawal(인출)에 대해 음수값이다.
  • "계좌에서 각 거래 후 각 계좌의 total balance(총 잔액)을 구해라"
    • select account_number, date_time
                  sum(value) over (partition by account_number
                                               order by date_time
                                               rows unbounded preceding)
                   as balance
      from transaction
      order by account_number, date_time

cf. rows unbounded preceding = rows between unbounded preceding and current