window function(분석)

 

1.   Window Function?

1)   개요

(1)    행과 행간의 관계를 쉽게 정의하기 위해 제공되는 함수

(2)    분석함수 또는 순위함수로도 불림

(3)    기존의 집계함수 또한 window function으로 통합됨

(4)    서브쿼리 사용 가능함

(5)    기본 문법

2)   종류

(1)    CUME_DIST : 항목의 누적 분포를 연산

SELECT  SalesOrderID

  , SalesOrderDetailID

  , ProductID

  , UnitPrice

  , CUME_DIST() OVER(

      PARTITION BY SalesOrderID 

      ORDER BY UnitPrice

    ) AS CDUnitPrice

  , COUNT(UnitPrice) OVER(

      PARTITION BY SalesOrderID

      ORDER BY UnitPrice

    ) AS PartCnt

  , COUNT(UnitPrice) OVER(

      PARTITION BY SalesOrderID

    ) AS TotalCnt

FROM  AdventureWorks2019.Sales.SalesOrderDetail

 

(2)    FIRST_VALUE : 해당 항목으로 정렬된 값의 첫 번째 값을 반환

SELECT  SalesOrderID

  , SalesOrderDetailID

  , ProductID

  , UnitPrice

  , FIRST_VALUE(UnitPrice) OVER(

      PARTITION BY SalesOrderID 

      ORDER BY UnitPrice

    ) AS FVUnitPrice

FROM  AdventureWorks2019.Sales.SalesOrderDetail

 

(3)    LAG : 항목을 정렬 후 해당 행의 이전 행의 값을 반환, 첫 행은 이전 행이 없기 때문에 NULL을 반환 한다.

SELECT  SalesOrderID

  , SalesOrderDetailID

  , ProductID

  , UnitPrice

  , LAG(UnitPrice) OVER(

      PARTITION BY SalesOrderID 

      ORDER BY UnitPrice

    ) AS LAGUnitPrice

FROM  AdventureWorks2019.Sales.SalesOrderDetail

 

(4)    LAST_VALUE : 항목을 정렬 후 해당 항목의 마지막 값을 반환

SELECT  SalesOrderID

  , SalesOrderDetailID

  , ProductID

  , UnitPrice

  , LAST_VALUE(UnitPrice) OVER(

      PARTITION BY SalesOrderID 

      ORDER BY SalesOrderID 

    ) AS LVUnitPrice

FROM  AdventureWorks2019.Sales.SalesOrderDetail

 

(5)    LEAD : 항목의 값을 정렬 후 현재 행의 다음 행의 값을 반환, 마지막 행의 경우 다음 행이 없기 때문에 NULL을 반환한다.

SELECT  SalesOrderID

  , SalesOrderDetailID

  , ProductID

  , UnitPrice

  , LEAD(UnitPrice) OVER(

      PARTITION BY SalesOrderID 

      ORDER BY UnitPrice

    ) AS LEADUnitPrice

FROM  AdventureWorks2019.Sales.SalesOrderDetail

 

(6)    PERCENT_RANK : 해당 항목의 총 개수당 해당 값의 순위에 대한 백분위

SELECT  SalesOrderID

  , SalesOrderDetailID

  , ProductID

  , UnitPrice

  , PERCENT_RANK() OVER(

      PARTITION BY SalesOrderID 

      ORDER BY UnitPrice

    ) AS PRUnitPrice

  , RANK() OVER(

      PARTITION BY SalesOrderID 

      ORDER BY UnitPrice

    )-1 AS DR

  , COUNT(UnitPrice) OVER(

      PARTITION BY SalesOrderID 

    )-1 AS Cnt

FROM  AdventureWorks2019.Sales.SalesOrderDetail

 

2.   출처

1)      https://docs.microsoft.com/ko-kr/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-ver15

2)      https://docs.microsoft.com/ko-kr/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms