SQL
함수 (분석)

소개

통사론

1. FIRST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [행 _ 범위 절])
2. LAST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [행 _ 범위 절])
3. LAG (scalar_expression [, offset] [, default]) OVER ([partition_by_clause] order_by_clause)
4. LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
5. PERCENT_RANK () OVER ([partition_by_clause] order_by_clause)
6. CUME_DIST () OVER ([partition_by_clause] order_by_clause)
7. PERCENTILE_DISC (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

FIRST_VALUE 함수를 사용하여 스칼라 식을 사용하여 식별 한 정렬 된 결과 집합의 첫 번째 값을 결정합니다.

SELECT StateProvinceID, Name, TaxRate,
       FIRST_VALUE(StateProvinceID)
        OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

이 예에서 FIRST_VALUE 함수는 세율이 가장 낮은 주 또는 지방의 ID 를 반환하는 데 사용됩니다. OVER 절은 가장 낮은 이자율을 얻기 위해 세율을 명령하는 데 사용됩니다.

LAST_VALUE

LAST_VALUE 함수는 스칼라 표현식을 사용하여 지정하는 정렬 된 결과 세트의 마지막 값을 제공합니다.

SELECT TerritoryID, StartDate, BusinessentityID,
       LAST_VALUE(BusinessentityID) 
        OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

이 예에서는 LAST_VALUE 함수를 사용하여 각 행 집합의 마지막 값을 정렬 된 값으로 반환합니다.

LAG 및 LEAD

LAG 함수는 동일한 결과 집합의 현재 행 앞에있는 행에 대한 데이터를 제공합니다. 예를 들어 SELECT 문에서 현재 행의 값과 이전 행의 값을 비교할 수 있습니다.

스칼라 표현식을 사용하여 비교할 값을 지정합니다. offset 매개 변수는 비교에 사용될 현재 행 앞의 행 수입니다. 행 수를 지정하지 않으면 한 행의 기본값이 사용됩니다.

default 매개 변수는 offset의식이 NULL 값을 가질 때 반환되어야하는 값을 지정합니다. 값을 지정하지 않으면 NULL 값이 리턴됩니다.

LEAD 함수는 행 집합의 현재 행 뒤의 행에 대한 데이터를 제공합니다. 예를 들어 SELECT 문에서 현재 행의 값과 다음 행의 값을 비교할 수 있습니다.

스칼라 표현식을 사용하여 비교해야하는 값을 지정합니다. offset 매개 변수는 비교에 사용될 현재 행 뒤의 행 수입니다.

기본 매개 변수를 사용하여 offset의식이 NULL 값을 가질 때 반환해야하는 값을 지정합니다. 이러한 매개 변수를 지정하지 않으면 한 행의 기본값이 사용되며 NULL 값이 반환됩니다.

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

이 예제에서는 LEAD 및 LAG 함수를 사용하여 BusinessEntityID 열을 기준으로 정렬 된 레코드를 사용하여 위와 아래에 나열된 직원의 날짜와 각 직원의 판매 값을 비교합니다.

PERCENT_RANK 및 CUME_DIST

PERCENT_RANK 함수는 행 집합을 기준으로 한 행의 순위를 계산합니다. 백분율은 현재 행보다 낮은 값을 갖는 그룹의 행 수를 기반으로합니다.

결과 집합의 첫 번째 값의 비율은 항상 0입니다. 집합에서 최상위 또는 최하위 값의 값은 항상 하나입니다.

CUME_DIST 함수는 해당 값보다 작거나 같은 값의 백분율을 판별하여 값 그룹에서 지정된 값의 상대 위치를 계산합니다. 이를 누적 분포라고합니다.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Cumulative Distribution"
FROM Employee;

이 예에서는 ORDER 절을 사용하여 직원의 직책에 따라 SELECT 문에서 검색 한 행을 분할하거나 그룹화하며 각 그룹의 결과는 직원이 사용한 병가 시간 수에 따라 정렬됩니다.

PERCENT_RANK 기능은 각 그룹 내의 항목의 순위를 매 깁니다. 각 항목에 대해 더 낮은 값을 가진 동일한 그룹의 항목 백분율을 리턴합니다.

CUME_DIST 함수는 현재 값보다 작거나 같은 값의 백분율을 반환한다는 점을 제외하면 비슷합니다.

PERCENTILE_DISC 및 PERCENTILE_CONT

PERCENTILE_DISC 함수는 누적 분포가 numeric_literal 매개 변수를 사용하여 제공 한 백분위 수보다 높은 첫 번째 항목의 값을 나열합니다.

값은 WITHIN GROUP 절에 지정된대로 행 집합 또는 파티션별로 그룹화됩니다.

PERCENTILE_CONT 기능은 비슷합니다 PERCENTILE_DISC 기능,하지만 첫 번째 일치하는 항목과 다음 항목의 합계의 평균을 반환합니다.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

0.5 백분위 수와 일치하거나 초과하는 행의 정확한 값을 찾으려면 백분율을 PERCENTILE_DISC 함수의 숫자 리터럴로 전달하십시오. 결과 집합의 Percentile Discreet 열은 누적 분포가 지정된 백분위 수보다 높은 행의 값을 나열합니다.

PERCENTILE_CONT 함수를 사용하여 값의 집합을 계산합니다. 결과의 "Percentile Continuous"열은 결과 값과 그 다음으로 높은 일치 값의 합계의 평균값을 나열합니다.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;