Window Functions 쿼리 정리를 위한 글
- 윈도우 함수는 현재 행을 기준으로 일련의 행 집합에 대해 연산을 수행
- 집계 함수는 계산 결과를 한 행으로 합치는 특성이 있지만 윈도우 함수는 원래 행을 그대로 유지
- 값과 순위를 매기는 함수가 대표적
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;