[SQL] Window Functions

Window Functions 쿼리 정리를 위한 글

WINDOW 함수

1. 윈도우 함수는 현재 행을 기준으로 일련의 행 집합에 대해 연산을 수행
2. 집계 함수는 계산 결과를 한 행으로 합치는 특성이 있지만 윈도우 함수는 원래 행을 그대로 유지
3. 값과 순위를 매기는 함수가 대표적

CUME_DIST

• 순위
• 누적 분포

    CREATE TABLE CumeDistDemo( Id INTEGER PRIMARY KEY, value INT );
    INSERT INTO CumeDistDemo(value) VALUES(1000),(1200),(1200),(1400),(2000);
    SELECT Id, Value FROM CumeDistDemo;
    SELECT Value, CUME_DIST() OVER ( ORDER BY value ) CumulativeDistribution FROM CumeDistDemo;

DENSE_RANK

• 순서있는 행들의 집합에서 행의 순위 계산, 빈 순서 없음

CREATE TABLE DenseRankDemo ( Val TEXT );
INSERT INTO DenseRankDemo(Val) VALUES('A'),('B'),('C'),('C'),('D'),('D'),('E');
SELECT Val, DENSE_RANK () OVER ( ORDER BY Val ) ValRank FROM DenseRankDemo;
SELECT AlbumId, Name, Milliseconds, DENSE_RANK () OVER ( PARTITION BY AlbumId ORDER BY Milliseconds ) LengthRank FROM tracks;

FIRST_VALUE

• 값
• 정해진 윈도우 프레임 내에서 첫 행의 값을 반환

SELECT Name, printf('%,d',Bytes) Size, FIRST_VALUE(Name) OVER ( ORDER BY Bytes ) AS SmallestTrack FROM tracks WHERE AlbumId = 1;
SELECT AlbumId, Name, printf('%,d',Bytes) Size, FIRST_VALUE(Name) OVER ( PARTITION BY AlbumId ORDER BY Bytes DESC ) AS LargestTrack FROM tracks;

LAG

• 값
• 직전에 물리적으로 떨어진 오프셋 값을 가져오기

SELECT * FROM CustomerInvoices ORDER BY CustomerId, Year, Total;
SELECT CustomerId, Year, Total, LAG ( Total,1,0 ) OVER ( ORDER BY Year ) PreviousYearTotal FROM CustomerInvoices WHERE CustomerId = 4;
SELECT CustomerId, Year, Total, LAG ( Total,1,0 ) OVER ( PARTITION BY CustomerId ORDER BY Year ) PreviousYearTotal FROM CustomerInvoices;

LAST VALUE

• 정해진 윈도우 프레임 내에서 마지막 행의 값을 반환

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;
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;

LEAD

• 직후에 물리적으로 떨어진 오프셋 값을 가져오기

CREATE VIEW CustomerInvoices AS SELECT CustomerId, strftime('%Y',InvoiceDate) Year, SUM( total ) Total FROM invoices GROUP BY CustomerId, strftime('%Y',InvoiceDate);
SELECT * FROM CustomerInvoices ORDER BY CustomerId, Year, Total;
SELECT CustomerId, Year, Total, LEAD ( Total,1,0 ) OVER ( ORDER BY Year ) NextYearTotal FROM CustomerInvoices WHERE CustomerId = 1;
SELECT CustomerId, Year, Total, LEAD ( Total,1,0 ) OVER ( PARTITION BY CustomerId ORDER BY Year ) NextYearTotal FROM CustomerInvoices;

NTH_VALUE

• 정해진 윈도우 프레임 내에서 N번째 행의 값을 반환)

SELECT Name, Milliseconds Length, NTH_VALUE ( name,2 ) OVER ( ORDER BY Milliseconds DESC ) SecondLongestTrack FROM tracks;
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;    

NTILE

• 순서가 있는 결과 집합을 정해진 버킷에 나눠서 넣기

SELECT Name, Milliseconds, NTILE ( 4 ) OVER ( ORDER BY Milliseconds ) LengthBucket FROM tracks WHERE AlbumId = 1;
SELECT AlbumId, Name, Milliseconds, NTILE ( 3 ) OVER ( PARTITION BY AlbumId ORDER BY Bytes ) SizeBucket FROM tracks;

PERCENT_RANK

• 순위를 퍼센트로 보여줌

SELECT Name, Milliseconds, PERCENT_RANK() OVER( ORDER BY Milliseconds ) LengthPercentRank FROM tracks WHERE AlbumId = 1;
SELECT Name, Milliseconds, printf('%.2f',PERCENT_RANK() OVER( ORDER BY Milliseconds )) LengthPercentRank FROM tracks WHERE AlbumId = 1;
SELECT AlbumId, Name, Bytes, printf('%.2f',PERCENT_RANK() OVER( PARTITION BY AlbumId ORDER BY Bytes )) SizePercentRank FROM tracks;

RANK

• 순위를 보여줌 (중복 가능, 빈 순서 있음)

CREATE TABLE RankDemo ( Val TEXT );
INSERT INTO RankDemo(Val) VALUES('A'),('B'),('C'),('C'),('D'),('D'),('E');
SELECT * FROM RankDemo;
SELECT Val, RANK () OVER ( ORDER BY Val ) ValRank FROM RankDemo;
SELECT Name, Milliseconds, RANK () OVER ( ORDER BY Milliseconds DESC ) LengthRank FROM tracks;
SELECT Name, Milliseconds, AlbumId, RANK () OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC ) LengthRank FROM tracks;
SELECT * FROM ( SELECT Name, Milliseconds, AlbumId, RANK () OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC ) LengthRank FROM tracks ) WHERE LengthRank = 2;

ROW_NUMBER

• 연속적인 정수를 부여, 중복 불가능

SELECT ROW_NUMBER () OVER ( ORDER BY Country ) RowNum, FirstName, LastName, country FROM customers;
SELECT ROW_NUMBER () OVER ( PARTITION BY Country ORDER BY FirstName ) RowNum, FirstName, LastName, country FROM customers;
SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY FirstName ) RowNum, FirstName, LastName, Country FROM customers ) WHERE RowNum > 20 AND RowNum <= 30
CREATE VIEW Sales AS SELECT CustomerId, FirstName, LastName, Country, SUM( total ) Amount FROM invoices INNER JOIN customers USING (CustomerId) GROUP BY CustomerId;
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;