레드 게이트 Hub

I’ve 보낸 지난 여섯 여행 년의 주위에 우리에게 말하고 데이터베이스 전문가들에 대한 T-SQL 창 함수에서 SQL 토요일과 다른 이벤트입니다. 나는 이러한 기능에 대해 들어 본 사람이 얼마나 적고 심지어 사용하는 사람이 더 적은지에 놀랐다. 의 끝에서 각 프레젠테이션,하나 더 많은 사람들이 와서 말하는 그들고 싶다고 그들에 대해 배운 이러한 기능 년 이전에 그들이 할 수 있기 때문에 도움이되었을 위해 많은 쿼리를 처리합니다.,

이러한 기능은 다른보다 전통적인 방법보다 성능을 향상시키기 위해 추진되었습니다. 나는 부분적으로 동의한다. 그들은 많은 쿼리를 작성하기 쉽게 만들고 때로는 성능을 향상시킵니다.

Windows OS 와 아무 관련이 없습니다.

이러한 함수는 ansi sql2003 표준의 일부이며 SQL Server 의 경우 쿼리를 작성하는 데 사용되는 T-SQL 함수입니다. 그들은 Windows 운영 체제 또는 API 호출과 아무 관련이 없습니다. 오라클과 같은 다른 데이터베이스 시스템도이를 자체 SQL 언어의 일부로 포함 시켰습니다.,

창(또한 창 또는 창)함수는 일련의 행에 대해 계산을 수행합니다. 나는 반환되는 행에서”창을 들여다 보며”계산을 수행 할 마지막 기회가 있다고 생각하고 싶습니다. 창은 행이 더 작은 세트로 분할되고 순서가 지정되는지 여부를 결정하는 OVER 절에 의해 정의됩니다. 실제로 창 함수를 사용하는 경우 항상 오버 절을 사용하게됩니다. OVER 절은 시퀀스 객체에 필요한 구문에 대한 다음 값의 일부이기도하지만,그렇지 않으면 창 함수와 함께 사용됩니다.,

OVER 절에는 옵션 별 파티션이 포함될 수 있습니다. 이렇게하면 행이 더 작은 세트로 나뉩니다. 이것이 GROUP BY 와 동일하다고 생각할 수도 있지만 그렇지 않습니다. 그룹화 할 때 고유 그룹 당 하나의 행이 반환됩니다. PARTITION BY 를 사용할 때 모든 세부 행이 계산과 함께 반환됩니다. 집에 창으로 나뉘는 창이있는 경우 각 창은 창입니다. 창 기능에 대해 생각할 때 전체 결과 집합은 파티션이지만 PARTITION BY 를 사용할 때 각 파티션은 창으로 간주 될 수도 있습니다., PARTITION BY 는 모든 창 기능에 대해 지원 및 선택 사항입니다.

OVER 절에는 ORDER BY 옵션이 포함될 수도 있습니다. 이것은 쿼리의 ORDER BY 절과 독립적입니다. 일부 기능은 ORDER BY 가 필요하며 다른 기능은 지원하지 않습니다. 계산을 적용 할 때 행의 순서가 중요 할 때 ORDER BY 가 필요합니다.

창 함수는 쿼리의 SELECT AND ORDER BY 절에서만 사용될 수 있습니다. 결합,필터링 또는 그룹화 후에 적용됩니다.,

순위 함수

가장 일반적으로 사용되는 창 함수 인 순위 함수는 2005 년부터 사용할 수있었습니다. 그 때 Microsoft 는 row_number,RANK,DENSE_RANK 및 NTILE 을 도입했습니다. ROW_NUMBER 는 파티션 또는 전체 결과 집합에 고유 한 행 번호를 추가하기 위해 매우 자주 사용됩니다. 행을 추가하는 번호,또는 다른 순위 기능은 일반적으로 목표지만,그것은 단계를 따라하는 방법 솔루션입니다.

ORDER BY 는 OVER 절에서 row_number 및이 그룹의 다른 함수를 사용할 때 필요합니다., 이것은 데이터베이스 엔진에 숫자를 적용해야하는 순서를 알려줍니다. ORDER BY 에 사용 된 열 또는 표현식의 값이 고유하지 않은 경우 RANK 및 DENSE_RANK 는 관계를 처리하는 반면 ROW_NUMBER 는 관계를 신경 쓰지 않습니다. NTILE 은 BY 순서에 따라 행을 버킷으로 나누는 데 사용됩니다.

ROW_NUMBER 의 한 가지 이점은 고유하지 않은 행을 고유 한 행으로 바꾸는 기능입니다. 예를 들어 중복 행을 제거하는 데 사용할 수 있습니다.이것이 어떻게 작동하는지 보여 주려면 중복 행이 포함 된 임시 테이블로 시작하십시오., 첫 번째 단계는 테이블을 만들고 채우는 것입니다.

추가하는 그룹 및 분할하여 각각의 열을 다시 시작됩니다 행 숫자를 위해 각각의 고유 한 세트의 행이 있습니다. 행 번호가 1 과 같은 행을 찾아 고유 행을 식별 할 수 있습니다.,

이제 당신이해야 할 모든 당신을 삭제하는 것입니다 모든 행하는 행 숫자보다 큰 하나입니다., 문제는 WHERE 절에 창 함수를 추가 할 수 없다는 것입니다.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., 그런 다음 CTE 에서 바로 행을 삭제할 수 있습니다.나는 이것이 어떻게 작동하는지 잘 모르겠습니다. 여분의 행이 삭제되었으며 고유 한 행 집합이 남아 있습니다.

사이의 차이를 확인하려면 그룹,순,그리고 DENSE_RANK,쿼리를 실행합니다:

주문한 각각의 이상 절짜는 고유하지 않습니다. 이 고객은 2013-10-24 일에 두 번의 주문을했습니다. ROW_NUMBER 는 단지 숫자를 계속 할당하고 중복 된 날짜가 있더라도 다른 것을하지 않았습니다., 두 행에 6 을 할당 한 다음 다음 행에 8 을 가진 ROW_NUMBER 까지 잡은 순위. DENSE_RANK 는 또한 두 행에 6 을 할당했지만 다음 행에 7 을 할당했습니다.

두 가지 차이점을 설명하고 ROW_NUMBER 를 위치 적으로 생각하십시오. 순위는 위치 적이며 논리적입니다. 이 두 행은 논리적으로 동일하게 순위가 매겨 지지만 다음 행은 세트의 위치에 따라 순위가 매겨집니다. DENSE_RANK 는 논리적으로 순위를 매 깁니다. 주문 2013-11-04 는 7 번째 고유 날짜입니다.

이 그룹의 최종 함수를 NTILE 이라고합니다. 행 번호 또는 순위 대신 행에 버킷 번호를 할당합니다., 다음은 예이다:

NTILE 는 매개변수,이 경우 4,이는 수의 물통에서 보고 싶은 결과입니다. 주문 BY 는 판매액의 합계에 적용됩니다. 가장 낮은 25%의 행에는 1 이 할당되고 가장 높은 25%의 행에는 4 가 할당됩니다. 마지막으로 NTILE 의 결과에 1000 을 곱하면 보너스 금액이 생깁니다. 14 를 4 로 균등하게 나눌 수 없으므로 여분의 행은 처음 두 버킷 각각에 들어갑니다.

창 집계

창 집계도 SQL Server2005 와 함께 도입되었습니다., 이들은 몇 가지 까다로운 쿼리를 쉽게 작성하게하지만 종종 오래된 기술보다 더 나쁜 수행합니다. 그들은 당신이 비 집계 쿼리에 좋아하는 집계 함수를 추가 할 수 있습니다. 예를 들어 각 고객에 대한 소계와 함께 모든 고객 주문을 표시하고 싶다고 가정 해보십시오., 을 추가하여 합계하여 이 절은 이를 수행할 수 있습니다 아주 쉽게:

추가하여 파티션,소계에 대해 계산된 각각의 고객입니다. 모든 집계 함수를 사용할 수 있으며 OVER 절에서 ORDER BY 는 지원되지 않습니다.

창 집계 개선 사항 2012 년

을 시작으로 2012 년,추가할 수 있습니다 주문에 의해 이상 절을 창의 집계를 생산하는 실행하는 합계와 이동평균,대한 예입니다. 동시에 마이크로 소프트는 프레이밍의 개념을 도입했다. 로 파티션을 추가하는 것은 창을 창으로 나누는 것과 같습니다., 프레이밍을 추가하는 것은 스테인드 글라스 창을 만드는 것과 같습니다. 각 행에는 표현식이 적용될 개별 창이 있습니다.

이 향상으로 프레이밍 구문을 추가하지 않아도 실행중인 합계를 만들 수 있습니다., Here is an example that returns a running total by customer:

기본 프레임이 사용되는 경우 프레임을 지정하지 않은 범위 사이에 무제한 이전 및 현재의 행이 있습니다. 불행히도,이 프레임을 대신 지정하는 경우처럼 잘 수행되지 않습니다:제한되지 않은 앞 행과 현재 행 사이의 행. 차이점은 단어 행입니다. 범위는이 시간에 부분적으로 만 구현되며 행이 위치 인 동안 기간 동안 작업하기위한 것입니다., 프레임 사이 행 무제한 이전 및 현재의 행 의미 있는 창문으로 구성의 첫 번째 행에는 파티션의 모든 행하는 현재의 행이 있습니다. 각 계산은 다른 행 집합에 대해 수행됩니다. 예를 들어,행 4 에 대한 계산을 수행 할 때 행 1~4 가 사용됩니다.

행 5 에 대한 계산을 수행 할 때 행은 1~5 입니다. 한 행에서 다음 행으로 이동하면 창이 커집니다.,

사용할 수 있습니다 또한 구문을 행 사 N 선행 및 현재의 행 사 현재 행 N 다음과 같습니다. 예를 들어 3 개월 이동 평균을 계산하는 데 유용 할 수 있습니다. 다음 그림은 앞의 2 행과 현재 행 사이의 행을 나타냅니다.

5 가 현재 행일 때 창이 움직이며 크기가 변경되지 않습니다.,

여기에는 용어의 목록을 알아야 할 때 쓰는 프레이밍 옵션:

나는 이 구문에 혼란스럽지만 사용하여 SQL 프롬프트를 쓰는 프레이밍 옵션을 더 쉽습니다!

오프셋 기능

도 포함되어 있으로 SQL Server2012 릴리스의 네 가지 기능할 수 있도록 하는 값을 포함에 다른 행하지 않고 자기입니다. Microsoft 는 이러한’분석 함수’를 호출하지만이 주제에 대해 제시 할 때 항상’오프셋 함수’로 참조합니다., 함수 중 두 가지를 사용하면 현재 행 이전(지연)또는 이후(리드)행에서 열 또는 표현식을 가져올 수 있습니다. 다른 두 가지 기능을 할 수 있는 값을 반환하는 첫 번째 행에서의 파티션(FIRST_VALUE)또는 파티션의 마지막 행(LAST_VALUE). FIRST_VALUE 및 LAST_VALUE 도 프레이밍이 필요하므로 이러한 함수를 사용할 때 프레임을 포함해야합니다. 네 가지 기능 모두 OVER 절의 ORDER BY 옵션을 요구합니다. 데이터베이스 엔진이 반환 할 값이 포함 된 행을 알아 내기 위해 행의 순서를 알아야하기 때문에 의미가 있습니다.,

어떤 사람들은 좋아하는 밴드를 가지고 있습니다. 좋아하는 기능인 지연이 있습니다. 사용하기 쉽습니다(프레임 없음!)및 위대한 수행합니다., Here is an example:

지연 및 리 필요한 인수–열거하고 싶은 표현을 반환합니다. 기본적으로 LAG 는 이전 행의 값을 반환하고 LEAD 는 다음 행의 값을 반환합니다. 오프셋 매개 변수의 값을 제공하여 수정할 수 있습니다.이 값은 기본적으로 1 입니다. 파티션의 첫 번째 행이 NULL 을 반환한다는 것을 알 수 있습니다. 널을 재정의하려는 경우 기본값을 제공합니다., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE 및 LAST_VALUE 사용할 수 있는 값을 찾으려면서 행하거나 마지막의 행 파티션입니다. 성능상의 이유뿐만 아니라 기본 프레임이 LAST_VALUE 에서 기대하는대로 작동하지 않기 때문에 프레임을 지정해야합니다. 기본 프레임,제한되지 않은 선행 행과 현재 행 사이의 범위는 현재 행까지만 올라갑니다. 파티션의 마지막 행은 포함되지 않습니다., 예상 결과를 얻으려면 LAST_VALUE 를 사용할 때 현재 행과 UNBOUNDED FOLLOWING 사이에 행을 지정해야합니다., 여기에 예를 사용하여 FIRST_VALUE:

통계 기능

Microsoft 그룹은 이러한 네 가지 기능–PERCENT_RANK,CUME_DIST,PERCENTILE_DISC,PERCENTILE_CONT–따라서 오프셋 기능을 부르는 모든 여덟 분석적 기능입니다. 나는 이것들을 오프셋 함수와 구별하기를 좋아하기 때문에,나는 이것들을 통계라고 부른다.

PERCENT_RANK 및 CUME_DIST 는 파티션을 통해 각 행에 대한 순위를 제공합니다. 그들은 약간 다릅니다. PERCENT_RANK 는 현재 행보다 순위가 낮은 행의 백분율을 반환합니다., “내 점수는 점수의 90%보다 높습니다.”CUME_DIST 또는 누적 분포는 정확한 순위를 반환합니다. “내 점수는 점수의 90%에 있습니다.”다음은 매달 세인트 루이스의 평균 고온을 사용하는 예입니다. 계급은 화씨 온도에 의해 결정되었음을 주목하십시오.

순위가 결정되지 않는 상대적으로 값을,하지만 위치의 행이 있습니다. 3 월과 11 월이 같은 평균 고온을 가지고있어서 같은 순위를 매겼다는 것을 알 수 있습니다.

PERCENT_RANK 및 CUME_DIST 를 계산하는 방법이 궁금 할 수 있습니다., 여기에는 수식은:

PERCENTILE_DISC 및 PERCENTILE_CONT 반대로 작용 방법입니다. 퍼센트 순위가 주어지면 그 순위에서 값을 찾으십시오., 그들은 다른 에서는 PERCENTILE_DISC 를 반환하는 값에 존재를 설정하는 동안 PERCENTILE_CONT 을 계산한 정확한 값이 없는 경우에 값을 설정 정확하게 떨어지는 순위입니다. PERCENTILE_CONT 를 사용하여 0.5 를 퍼센트 순위로 공급하여 중앙값을 계산할 수 있습니다. 예를 들어,세인트루이스에서 어떤 온도가 50%로 평가됩니까?

PERCENTILE_CONT 함수는 평균 값을 가장 가까운 중간,67 69,및 평균니다. PERCENTILE_DISC 는 정확한 값인 67 을 반환합니다., 또한이 두 함수에는 OVER 절 대신 ORDER BY 가 포함 된 GROUP 내의 다른 함수에서 볼 수없는 추가 절이 있음을 알 수 있습니다.

요약

이 문서는 T-SQL 창 함수에 대한 매우 빠른 개요입니다. Sql Server2005,순위 함수 및 창 집계와 함께 두 가지 유형의 함수가 릴리스되었습니다. 2012 에서는 프레이밍 및 분석 기능을 사용하여 창 집계를 강화했습니다. 분석 함수를 오프셋 함수와 통계 함수의 두 그룹으로 분리하는 것을 좋아합니다., 창 기능을 사용하면 많은 쿼리를 쉽게 작성할 수 있으며 이것이 주요 이점이라고 생각합니다. 어떤 경우에는 쿼리도 더 잘 수행되지만 다른 날에 대한 토론입니다.

이 기사가 이러한 환상적인 기능에 대해 더 많이 배우도록 영감을 주었기를 바랍니다!피>