12. Aggregation and window function

Full-Relation Operations

Eliminating Duplicates

• 일반적인 DB 서버들은 성능 향상을 위해 기본적으로 중복을 제거하지 않음

• DISTINCT 키워드를 사용하면 중복 제거 가능

Duplicates in Unions, Intersections, and Differences

• 집합 연산자는 기본적으로 중복이 제거됨

• 중복 제거를 하지 않으려면 ALL 키워드를 사용

Grouping, Aggregation and Having

• Aggregation Operators
  • SUM, AVG, MIN, MAX, COUNT

• 예시

  SELECT MAX(netWorth) FROM MovieExec;
  SELECT AVG(netWorth) FROM MovieExec;
  
  // 테이블의 name의 개수
  SELECT COUNT(name) FROM MovieExec;
  
  // 테이블의 name의 개수(중복 제거)
  SELECT COUNT(DISTINCT name) FROM MovieExec;
  
  // 테이블의 컬럼을 셈
  SELECT COUNT(*) FROM MovieExec;

• filter
  • Syntax - SUM(<expression>) FILTER (WHERE <condition>)
  • PostgreSQL에서는 지원하는데 다른 DB 서버는 지원하지 않을 수 있음
  • 예시

    SELECT COUNT(*) filter (where year >= 1990) FROM Movie;

• Grouping
  • Query: Movie 릴레이션의 각 studio의 모든 영화의 length의 합을 구하라

  // 그룹핑을 하면 그룹화한 속성을 적어주어야 그룹 간 구분이 가능함
  SELECT studioName, SUM(length) FROM Movie GROUP BY studioName;

• Having
  • Query: 적어도 1930년 이전에 만든 영화가 있는 프로듀서들의 film의 length 의 합을 구하라

  SELECT name, SUM(length) FROM MovieExec, Movie
  WHERE producerCNo = certNo GROUP BY name HAVING MIN(year) < 1930;

Window Function

• 행과 행 간의 관계를 정의하기 위해 제공되는 함수

• 순위, 합계, 평균, 행 위치 등을 조작할 수 있음

• GROUP BY 절과 함께 사용할 수 없음

window_function(arg1, arg2, ...) OVER (
	[PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC | DESC]]
    [frame_clause] 
)

Aggregation Function vs Window Function

• Aggregation Function - 그룹당 하나의 값이 결과로 나옴

• Window Function - 그룹 별로 별도로 값이 나올 수 있음

Partition

• OVER 안의 PARTITION BY 절은 PARTITION BY 표현식의 같은 값을 공유하는 행들을 파티션(그룹)으로 나눔

• 각 행에 대해 window function은 현재 행과 같은 파티션에 있는 행들과 계산됨

• PARTITION BY 절이 없으면 모든 행을 포함하는 하나의 파티션이 생김

Window Frame

• sum(), count() 와 같은 window function들은 모든 파티션의 행이 아니라 window frame의 행에서 실행된다.

• 기본적으로 ORDER BY가 제공되면 window frame은 파티션이 시작부터 현재 행까지의 모든 행으로 구성되며 ORDER BY 절에 따라서

• ORDER BY가 없으면 기본 frame은 파티션의 모든 행으로 구성됨

Window Function Example

SELECT title, year,
	row_number() over () from movie;

SELECT title, year,
	row_number() over (order by title, year)
FROM movie
ORDER BY title, year;

SELECT title, year, studioname,
	row_number() over (
    	partition by studioname
    )
FROM movie;

SELECT title, year, studioname,
	row_number() over (
    	partition by studioname
        order by year
    )
FROM movie;

SELECT title, year, studioname,
	COUNT(studioname) over (partition by studioname)
FROM movie;

일반적인 window function

• row_number()
  • 파티션의 현재 행의 번호
  • 반환값: bigint

• rank()
  • 현재 행의 순위. 1, 2, 2, 4 와 같이 매김(순위 중복 o)
  • 반환값: bigint

• dense_rank()
  • 현재 행의 순위. 1, 2, 3, 4 와 같이 매김(순위 중복 x)
  • 반환값: bigint

• percent_rank()
  • 현재 행의 순위를 퍼센트로 나타냄
  • 반환값: double precision

• cume_dist()

• lag(value any_element)

• lead(value any_emelemt)

• first_value(value any)

• last_value(value any)

• nth_value(value any, nth integer)

• SUM, AVG, MIN, MAX, COUNT

 

Uploaded by Notion2Tistory v1.1.0