[유튜브 방송] (즐겁게 배우는 SQL #50) 윈도우 함수 - 윈도우 함수(2)

[유튜브 방송] (즐겁게 배우는 SQL) 기획 소개에서 설명드린 즐겁게 배우는 SQL 50편을 공개해드리겠다. 50편은 몇 가지 윈도우 함수를 소개한다.

2021년 2월 16일자 [즐겁게 배우는 SQL #50] 윈도우 함수 - 윈도우 함수(2) 방송은 다음에서 볼 수 있으며, 전체 방송 플레이리스트는 즐겁게 배우는 SQL에서 확인할 수 있다.

하이라이트를 요약 정리하면 다음과 같다:

• 00:00 윈도우 함수 개괄
• 01:02 (값) LAST VALUE(정해진 윈도우 프레임 내에서 마지막 행의 값을 반환)
• 04:55 (값) LEAD(직후에 물리적으로 떨어진 오프셋 값을 가져오기)
• 08:20 (값) NTH_VALUE(정해진 윈도우 프레임 내에서 N번째 행의 값을 반환)
• 11:34 (순위) NTILE(순서가 있는 결과 집합을 정해진 버킷에 나눠서 넣기)
• 14:51 (순위) PERCENT_RANK(순위를 퍼센트로 보여줌)
• 17:13 (순위) RANK(순위를 보여줌, 중복 가능, 빈 순서 있음)
• 20:51 (순위) ROW_NUMBER(연속적인 정수를 부여, 중복 불가능)

온라인 실습 사이트는 SQL Online IDE에서 진행하면 되고, 원본 학습자료는 SQLite Window Functions를 참고하고, 방송에 사용한 실제 실습 자료는 다음을 참고한다:

• 단계 1: 윈도우 함수 개괄
  1. 윈도우 함수는 현재 행을 기준으로 일련의 행 집합에 대해 연산을 수행한다
  2. 집계 함수는 계산 결과를 한 행으로 합치는 특성이 있는 반면에 윈도우 함수는 원래 행을 그대로 유지하는 특성이 있다
  3. 값과 순위를 매기는 함수가 대표적이다.
• 단계 2: (값) LAST VALUE(정해진 윈도우 프레임 내에서 마지막 행의 값을 반환)
  1. SQL> SELECT Name, printf ( '%.f minutes', Milliseconds / 1000 / 60 ) AS Length, LAST_VALUE ( Name ) OVER ( ORDER BY Milliseconds RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS LongestTrack FROM tracks WHERE AlbumId = 4;
  2. SQL> SELECT AlbumId, Name, printf ( '%.f minutes', Milliseconds / 1000 / 60 ) AS Length, LAST_VALUE ( Name ) OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS ShortestTrack FROM tracks;
• 단계 3: LEAD(직후에 물리적으로 떨어진 오프셋 값을 가져오기)
  1. SQL> CREATE VIEW CustomerInvoices AS SELECT CustomerId, strftime('%Y',InvoiceDate) Year, SUM( total ) Total FROM invoices GROUP BY CustomerId, strftime('%Y',InvoiceDate);
  2. SQL> SELECT * FROM CustomerInvoices ORDER BY CustomerId, Year, Total;
  3. SQL> SELECT CustomerId, Year, Total, LEAD ( Total,1,0 ) OVER ( ORDER BY Year ) NextYearTotal FROM CustomerInvoices WHERE CustomerId = 1;
  4. SQL> SELECT CustomerId, Year, Total, LEAD ( Total,1,0 ) OVER ( PARTITION BY CustomerId ORDER BY Year ) NextYearTotal FROM CustomerInvoices;
• 단계 4: (값) NTH_VALUE(정해진 윈도우 프레임 내에서 N번째 행의 값을 반환)
  1. SQL> SELECT Name, Milliseconds Length, NTH_VALUE ( name,2 ) OVER ( ORDER BY Milliseconds DESC ) SecondLongestTrack FROM tracks;
  2. SQL> SELECT AlbumId, Name, Milliseconds Length, NTH_VALUE ( Name,2 ) OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS SecondLongestTrack FROM tracks;
• 단계 5: (순위) NTILE(순서가 있는 결과 집합을 정해진 버킷에 나눠서 넣기)
  1. SQL> SELECT Name, Milliseconds, NTILE ( 4 ) OVER ( ORDER BY Milliseconds ) LengthBucket FROM tracks WHERE AlbumId = 1;
  2. SQL> SELECT AlbumId, Name, Milliseconds, NTILE ( 3 ) OVER ( PARTITION BY AlbumId ORDER BY Bytes ) SizeBucket FROM tracks;
• 단계 6: (순위) PERCENT_RANK(순위를 퍼센트로 보여줌)
  1. SQL> SELECT Name, Milliseconds, PERCENT_RANK() OVER( ORDER BY Milliseconds ) LengthPercentRank FROM tracks WHERE AlbumId = 1;
  2. SQL> SELECT Name, Milliseconds, printf('%.2f',PERCENT_RANK() OVER( ORDER BY Milliseconds )) LengthPercentRank FROM tracks WHERE AlbumId = 1;
  3. SQL> SELECT AlbumId, Name, Bytes, printf('%.2f',PERCENT_RANK() OVER( PARTITION BY AlbumId ORDER BY Bytes )) SizePercentRank FROM tracks;
• 단계 7: (순위) RANK(순위를 보여줌, 중복 가능, 빈 순서 있음)
  1. SQL> CREATE TABLE RankDemo ( Val TEXT );
  2. SQL> INSERT INTO RankDemo(Val) VALUES('A'),('B'),('C'),('C'),('D'),('D'),('E');
  3. SQL> SELECT * FROM RankDemo;
  4. SQL> SELECT Val, RANK () OVER ( ORDER BY Val ) ValRank FROM RankDemo;
  5. SQL> SELECT Name, Milliseconds, RANK () OVER ( ORDER BY Milliseconds DESC ) LengthRank FROM tracks;
  6. SQL> SELECT Name, Milliseconds, AlbumId, RANK () OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC ) LengthRank FROM tracks;
  7. SQL> SELECT * FROM ( SELECT Name, Milliseconds, AlbumId, RANK () OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC ) LengthRank FROM tracks ) WHERE LengthRank = 2;
  8. SQL>
• 단계 8: (순위) ROW_NUMBER(연속적인 정수를 부여, 중복 불가능)
  1. SQL> SELECT ROW_NUMBER () OVER ( ORDER BY Country ) RowNum, FirstName, LastName, country FROM customers;
  2. SQL> SELECT ROW_NUMBER () OVER ( PARTITION BY Country ORDER BY FirstName ) RowNum, FirstName, LastName, country FROM customers;
  3. SQL> SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY FirstName ) RowNum, FirstName, LastName, Country FROM customers ) WHERE RowNum > 20 AND RowNum <= 30
  4. SQL> CREATE VIEW Sales AS SELECT CustomerId, FirstName, LastName, Country, SUM( total ) Amount FROM invoices INNER JOIN customers USING (CustomerId) GROUP BY CustomerId;
  5. SQL> SELECT Country, FirstName, LastName, Amount FROM ( SELECT Country, FirstName, LastName, Amount, ROW_NUMBER() OVER ( PARTITION BY country ORDER BY Amount DESC ) RowNum FROM Sales ) WHERE RowNum = 1;

EOB