이 튜토리얼에서는 다음을 사용하는 방법을 배웁니다. SUM 함수구문과 예제가 포함된 SQL Server(Transact-SQL).

설명

SQL Server(Transact-SQL)에서 SUM 함수표현식의 총 값을 반환합니다.

통사론

SQL Server(Transact-SQL)의 SUM 함수 구문은 다음과 같습니다.

또는 하나 이상의 열로 결과를 그룹화할 때 SUM 함수의 구문은 다음과 같습니다.

매개변수 또는 인수

표현식1 , 표현식2 , ... 표현식_n 은 SUM 함수에 포함되지 않는 표현식으로 SQL 문 끝의 GROUP BY 절에 포함되어야 합니다.
Aggregate_expression은 집계될 열 또는 표현식입니다.
테이블 - 레코드를 가져오려는 테이블입니다. FROM 절에는 테이블이 하나 이상 나열되어 있어야 합니다.
WHERE 조건 - 선택 사항입니다. 이는 선택한 레코드에 대해 충족되어야 하는 조건입니다.

애플리케이션

SUM 함수는 다음 버전의 SQL Server(Transact-SQL)에서 사용할 수 있습니다.
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

필드가 1개인 예

SQL Server(Transact-SQL)에서 SUM 함수를 사용하는 방법을 이해하기 위해 몇 가지 SQL Server SUM 함수 예제를 살펴보겠습니다.

예를 들어 수량이 10보다 큰 모든 제품의 총 수량을 확인할 수 있습니다.

SUM 함수의 이 예에서는 SUM(수량) "총 수량" 표현식에 별칭을 지정했습니다. 결과 세트를 반환할 때 "총 수량"이 필드 이름으로 나타납니다.

DISTINCT 사용 예

SUM 함수에 DISTINCT 연산자를 사용할 수 있습니다. 예를 들어 아래 SQL 문은 급여가 연간 $29,000 미만인 고유한 급여 값을 사용하여 총 급여를 반환합니다.

두 급여가 연간 $24,000인 경우 해당 값 중 하나만 SUM 함수에 사용됩니다.

수식 사용 예

SUM 함수에 포함된 표현식은 단일 필드일 필요는 없습니다. 공식을 사용할 수도 있습니다. 예를 들어 총 수수료를 계산할 수 있습니다.

Transact-SQL

SELECT SUM(sales * 0.03) AS "총 커미션" FROM 주문;

SELECT SUM (판매액 * 0.03 ) AS "총 커미션" 주문에서;

GROUP BY 사용 예

경우에 따라 SUM 함수와 함께 GROUP BY 연산자를 사용해야 합니다.

컴퓨팅

요약 기능

SQL 쿼리 표현식에는 데이터 전처리가 필요한 경우가 많습니다. 이를 위해 특별한 함수와 표현식이 사용됩니다.

특정 쿼리와 일치하는 레코드 수를 확인해야 하는 경우가 많습니다.특정 숫자 열의 값, 최대값, 최소값 및 평균값의 합계는 얼마입니까? 이를 위해 소위 최종(통계, 집계) 함수가 사용됩니다. 요약 함수는 WHERE 절 등으로 지정된 레코드 집합을 처리합니다. SELECT 문 다음에 나오는 열 목록에 이를 포함시키면 결과 테이블에는 데이터베이스 테이블 열뿐만 아니라 이러한 함수로 계산된 값도 포함됩니다. 다음은요약 함수 목록.

• 개수(매개변수 )는 매개변수에 지정된 레코드 수를 반환합니다. 전체 레코드 개수를 알고 싶다면 별표(*) 기호를 파라미터로 지정해야 합니다. 컬럼명을 파라미터로 지정하면 해당 컬럼에 NULL이 아닌 값이 포함된 레코드의 개수를 반환한다. 열에 포함된 값의 수를 확인하려면 열 이름 앞에 DISTINCT 키워드를 붙입니다. 예를 들어:

클라이언트에서 SELECT COUNT(*);

고객으로부터 COUNT(Order_Amount)를 선택합니다.

고객으로부터 COUNT(DISTINCT Order_Amount)를 선택합니다.

다음 쿼리를 실행하려고 하면 오류 메시지가 표시됩니다.

SELECT 지역 , COUNT(*) FROM 클라이언트 ;

• 합계(매개변수 )는 매개변수에 지정된 열 값의 합을 반환합니다. 매개변수는 열 이름을 포함하는 표현식일 수도 있습니다. 예를 들어:

합계 선택 (주문_금액) FROM 고객;

이 SQL 문은 Customers 테이블의 Order_Amount 열에 대해 정의된 모든 값의 합계를 포함하는 1열, 1레코드 테이블을 반환합니다.

원본 테이블에서 Order_Amount 열의 값이 루블로 표시되어 있고 총 금액을 달러로 계산해야 한다고 가정해 보겠습니다. 예를 들어 현재 환율이 27.8인 경우 다음 표현식을 사용하여 필요한 결과를 얻을 수 있습니다.

합계 선택 (Order_amount*27.8) 고객으로부터;

• AVG(매개변수 )는 매개변수에 지정된 열의 모든 값에 대한 산술 평균을 반환합니다. 매개변수는 열 이름을 포함하는 표현식일 수 있습니다. 예를 들어:

고객에서 AVG(Order_Amount)를 선택합니다.

클라이언트에서 AVG(Order_Amount*27.8) 선택

어디 지역<>"North_3west";

• MAX(매개변수 )는 매개변수에 지정된 열의 최대값을 반환합니다. 매개변수는 열 이름을 포함하는 표현식일 수도 있습니다. 예를 들어:

클라이언트에서 MAX(Order_Amount)를 선택하세요.

클라이언트에서 MAX(Order_Amount*27.8)를 선택하세요.

어디 지역<>"North_3west";

• MIN(매개변수 )는 매개변수에 지정된 열의 최소값을 반환합니다. 매개변수는 열 이름을 포함하는 표현식일 수 있습니다. 예를 들어:

고객으로부터 MIN(Order_Amount)을 선택하세요.

SELECT MIN (주문 금액*27 . 8) FROM 클라이언트

어디 지역<>"North_3west";

실제로는 숫자열의 총계, 평균, 최대값, 최소값이 포함된 최종 테이블을 구해야 하는 경우가 많습니다. 이를 위해서는 그룹화(GROUP BY) 및 요약 기능을 사용해야 합니다.

지역 선택, 합계 (주문_금액) FROM 고객

지역별 그룹;

이 쿼리의 결과 테이블에는 지역 이름과 해당 지역의 모든 고객의 총 주문 금액이 포함됩니다(그림 5).

이제 지역별로 모든 요약 데이터를 가져오라는 요청을 고려해 보세요.

지역 선택, SUM(주문_금액), AVG (주문_금액), MAX(주문_금액), MIN(주문_금액)

클라이언트로부터

지역별 그룹;

원본과 결과 테이블은 그림 1에 나와 있습니다. 8. 이 예에서는 북서부 지역만 소스 테이블에 둘 이상의 레코드로 표시됩니다. 따라서 이에 대한 결과 테이블에서는 다양한 요약 함수가 서로 다른 값을 제공합니다.

쌀. 8. 지역별 주문금액 최종표

SELECT 문의 열 목록에 요약 함수를 사용하면 결과 테이블에서 해당 열의 헤더는 Expr1001, Expr1002 등이 됩니다. (또는 SQL 구현에 따라 유사한 것). 그러나 재량에 따라 요약 함수 및 기타 열의 값에 대한 헤더를 설정할 수 있습니다. 이렇게 하려면 SELECT 문의 열 바로 뒤에 다음 형식의 표현식을 지정합니다.

AS 열_제목

AS(as) 키워드는 결과 테이블에서 해당 열의 AS 뒤에 지정된 표제가 있어야 함을 의미합니다. 할당된 제목을 별칭이라고도 합니다. 다음 예(그림 9)에서는 모든 계산된 열에 대한 별칭을 설정합니다.

지역 선택,

합집합 (주문_금액) AS [총 주문금액],

평균 (주문_금액) AS [평균 주문금액],

MAX(Order_Amount) AS 최대,

최소 (주문_금액) AS 최소,

클라이언트로부터

지역별 그룹;

쌀. 9. 컬럼 별칭을 이용한 지역별 주문량 최종 테이블

공백으로 구분된 여러 단어로 구성된 닉네임은 대괄호로 묶입니다.

요약 함수는 SELECT 및 HAVING 절에서 사용할 수 있지만 WHERE 절에서는 사용할 수 없습니다. HAVING 연산자는 WHERE 연산자와 유사하지만 WHERE와는 달리 그룹에서 레코드를 선택합니다.

어느 지역에 둘 이상의 클라이언트가 있는지 확인하려고 한다고 가정해 보겠습니다. 이를 위해 다음 쿼리를 사용할 수 있습니다.

지역 선택, 개수(*)

클라이언트로부터

지역별 그룹 HAVING COUNT(*) > 1;

가치 처리 기능

데이터 작업 시 데이터를 처리해야 하는 경우가 많습니다(원하는 형식으로 변환). 문자열에서 하위 문자열 선택, 선행 및 후행 공백 제거, 숫자 반올림, 제곱근 계산, 현재 시간 확인 등 SQL 다음과 같은 세 가지 유형의 기능이 있습니다.

• 문자열 함수;
• 숫자 함수;
• 날짜-시간 함수.

문자열 함수

문자열 함수는 문자열을 매개변수로 사용하고 이를 처리한 후 문자열 또는 NULL을 반환합니다.

• 하위 문자열 (시작부터 줄)매개변수로 지정된 문자열의 결과 부분 문자열을 반환합니다.선 . 하위 문자열 start 매개변수에 일련 번호가 지정된 문자로 시작하고 length 매개변수에 지정된 길이를 갖습니다. 문자열의 문자는 1부터 시작하여 왼쪽에서 오른쪽으로 번호가 지정됩니다. 여기에서 대괄호는 그 안에 포함된 표현식이 선택 사항임을 나타냅니다. 표현의 경우길이에 대해 사용되지 않은 경우시작 그리고 원래 줄의 끝까지. 매개변수 값시작과 길이 검색된 하위 문자열이 실제로 원래 문자열 내에 있도록 선택해야 합니다. 그렇지 않으면 SUBSTRING 함수는 NULL을 반환합니다.

예를 들어:

SUBSTRING("Dear Masha!" FROM 9 FOR 4)은 "Masha"를 반환합니다.

SUBSTRING("Dear Masha!" FROM 9)은 "Masha!"를 반환합니다.

SUBSTRING("Dear Masha!" FROM 15)은 NULL을 반환합니다.

예를 들어 다음과 같이 SQL 표현식에서 이 함수를 사용할 수 있습니다.

SELECT * 클라이언트에서

WHERE SUBSTRING(Region FROM 1 FOR 5) = "북쪽";

• UPPER(문자열 )는 매개변수에 지정된 문자열의 모든 문자를 대문자로 변환합니다.
• LOWER(문자열 )는 매개변수에 지정된 문자열의 모든 문자를 소문자로 변환합니다.
• TRIM (LEADING | TRAILING | BOTH ["문자"] FROM 문자열 )는 문자열에서 선행(LEADING), 후행(TRAILING) 또는 둘 다(BOTH) 문자를 제거합니다. 기본적으로 제거되는 문자는 공백(" ")이므로 생략 가능합니다. 대부분 이 기능은 공백을 제거하는 데 사용됩니다.

예를 들어:

TRIM (LEADING " " FROM "상트페테르부르크 시")는 "상트페테르부르크 시"를 회전합니다.

TRIM(TRALING " " FROM "상트페테르부르크 시")는 "상트페테르부르크 시"를 반환합니다.

TRIM (BOTH " " FROM " 도시 상트페테르부르크 ")는 "도시 상트페테르부르크"를 반환합니다.

TRIM(BOTH FROM " 상트페테르부르크 시 ")는 "상트페테르부르크 시"를 반환합니다.

TRIM(BOTH "g" FROM "상트페테르부르크 시")는 "상트페테르부르크 시"를 반환합니다.

이러한 함수 중에서 가장 일반적으로 사용되는 함수는 SUBSTRING() 및 TRIM()입니다.

숫자 함수

숫자 함수는 숫자 유형의 데이터를 매개변수로 받아들일 수 있을 뿐만 아니라 항상 숫자 또는 NULL(정의되지 않은 값)을 반환할 수 있습니다.

• 위치 ( targetString IN 문자열) 지정된 문자열에서 대상 문자열이 나타나는지 검색합니다. 검색이 성공하면 첫 번째 문자의 위치 번호를 반환하고 그렇지 않으면 0을 반환합니다. 대상 문자열의 길이가 0인 경우(예: 문자열 " ") 함수는 1을 반환합니다. 매개 변수 중 하나 이상이 NULL인 경우 이면 NULL이 반환됩니다. 줄 문자는 1부터 시작하여 왼쪽에서 오른쪽으로 번호가 매겨집니다.

예를 들어:

POSITION("e" IN "안녕하세요 여러분")은 5를 반환합니다.

POSITION("모두" IN "안녕하세요 여러분")은 8을 반환합니다.

POSITION(" " 안녕하세요 여러분")은 1을 반환합니다.

POSITION("안녕하세요!" IN "안녕하세요 여러분")은 0을 반환합니다.

클라이언트 테이블(그림 1 참조)에서 주소 열에는 도시 이름 외에 우편번호, 거리 이름 및 기타 데이터가 포함됩니다. 특정 도시에 거주하는 고객에 대한 레코드를 선택해야 할 수도 있습니다. 따라서 상트페테르부르크에 거주하는 고객과 관련된 레코드를 선택하려면 다음 SQL 쿼리 표현식을 사용할 수 있습니다.

SELECT * 클라이언트에서

WHERE POSITION (" 상트페테르부르크 " IN 주소 ) > 0;

이 간단한 데이터 검색 요청은 다르게 공식화될 수 있습니다.

SELECT * 클라이언트에서

WHERE 주소 LIKE "%Petersburg%";

• 추출(매개변수 )는 날짜-시간 값 또는 간격에서 요소를 추출합니다. 예를 들어:

추출(날짜 "2005-10-25"의 월) 10을 반환합니다.

• CHARACTER_LENGTH(문자열 )는 문자열의 문자 수를 반환합니다.

예를 들어:

CHARACTER_LENGTH("안녕하세요 여러분")은 11을 반환합니다.

• OCTET_LENGTH(문자열 )은 문자열의 옥텟(바이트) 수를 반환합니다. 라틴 문자나 키릴 문자는 각각 1바이트로 표시되고, 중국어 알파벳 문자는 2바이트로 표시됩니다.
• 카디널리티(매개변수 )는 요소 컬렉션을 매개 변수로 사용하고 컬렉션의 요소 수(기수)를 반환합니다. 예를 들어 컬렉션은 다양한 유형의 요소를 포함하는 배열 또는 다중 집합일 수 있습니다.
• ABS(수 )는 숫자의 절대값을 반환합니다. 예를 들어:

ABS(-123)는 123을 반환합니다.

ABS(2 - 5)는 3을 반환합니다.

• MO D(번호1, 번호2 )는 첫 번째 숫자를 두 번째 숫자로 나눈 정수의 나머지를 반환합니다. 예를 들어:

MOD(5, h)는 2를 반환합니다.

MOD(2, h)는 0을 반환합니다.

• LN(숫자 )는 숫자의 자연 로그를 반환합니다.
• EXP(숫자)는 숫자를 반환합니다. (숫자의 거듭제곱에 대한 자연 로그의 밑수).
• POWER(숫자1, 숫자2 )는 숫자 1을 반환합니다. 2 번 (숫자 1의 숫자 2의 거듭제곱).
• SQRT(숫자 )는 숫자의 제곱근을 반환합니다.
• 바닥(숫자 )는 매개변수에 지정된 값을 초과하지 않는 가장 큰 정수를 반환합니다(내림). 예를 들어:

FLOOR(5.123)은 5.0을 반환합니다.

• CEIL(숫자) 또는 CEILING(숫자) )는 반올림 매개변수로 지정된 값보다 작지 않은 가장 작은 정수를 반환합니다. 예를 들어:

CEIL(5.123)은 6.0을 반환합니다.

• WIDTH_BUCKET (number1, number2, number3, number4)는 0과 number4 + 1 사이 범위의 정수를 반환합니다. number2 및 number3 매개변수는 동일한 간격으로 나누어진 숫자 간격을 지정하며, 그 수는 number4 매개변수에 의해 지정됩니다. 값이 숫자1에 속하는 간격의 수입니다. number1이 지정된 범위를 벗어나면 함수는 0 또는 숫자 4 + 1을 반환합니다. 예:

WIDTH_BUCKET(3.14, 0, 9, 5)는 2를 반환합니다.

날짜-시간 함수

SQL에는 현재 날짜와 시간을 반환하는 세 가지 함수가 있습니다.

• CURRENT_DATE 현재 날짜를 반환합니다(DATE 유형).

예: 2005-06-18.

• CURRENT_TIME(숫자 )는 현재 시간(TIME 유형)을 반환합니다. 정수 매개변수는 초 표현의 정밀도를 지정합니다. 예를 들어, 값 2는 가장 가까운 소수점 이하 2자리까지의 초를 나타냅니다.

12:39:45.27.

• CURRENT_TIMESTAMP(숫자 )는 날짜와 시간을 반환합니다(TIMESTAMP 유형). 예를 들어 2005-06-18 12:39:45.27입니다. 정수 매개변수는 초 표현의 정밀도를 지정합니다.

이러한 함수에서 반환되는 날짜와 시간은 문자 형식이 아닙니다. 이를 문자열로 표현하려면 CAST() 유형 변환 함수를 사용해야 합니다.

날짜-시간 함수는 일반적으로 데이터를 삽입, 업데이트 및 삭제하는 쿼리에 사용됩니다. 예를 들어 판매 정보를 기록할 때 이를 위해 제공되는 열에 현재 날짜와 시간이 입력됩니다. 한 달 또는 분기의 결과를 합산한 후 보고 기간의 판매 데이터를 삭제할 수 있습니다.

계산식

계산 표현식은 상수(숫자, 문자열, 논리), 함수, 필드 이름 및 기타 유형의 데이터를 산술, 문자열, 논리 및 기타 연산자와 연결하여 구성됩니다. 결과적으로 표현식은 연산자를 사용하여 더 복잡한(복합) 표현식으로 결합될 수 있습니다. 괄호는 표현식이 평가되는 순서를 제어하는 ​​데 사용됩니다.

논리 연산자 AND, OR, NOT 및 함수 이전에 논의된 적이 있습니다.

산술 연산자:

• + 추가;
• - 빼기;
• * 곱셈;
• / 분할.

문자열 연산자연결 또는 문자열 연결 연산자(| |)는 하나만 가능합니다. Microsoft Access와 같은 일부 SQL 구현에서는 (| |) 대신 (+) 문자를 사용합니다. 연결 연산자는 두 번째 문자열을 첫 번째 예의 끝에 추가합니다. 다음 표현식은 다음과 같습니다.

"사샤" | | "사랑한다" | | "흔들기"

결과적으로 "Sasha는 Masha를 사랑합니다"라는 문자열을 반환합니다.

표현식을 작성할 때 연산자의 피연산자가 유효한 유형인지 확인해야 합니다. 예를 들어, 123 + "Sasha" 표현식은 산술 더하기 연산자가 문자열 피연산자에 적용되므로 유효하지 않습니다.

계산된 표현식은 SELECT 문 뒤에 나타날 수 있을 뿐만 아니라 WHERE 및 HAVI 문의 조건 표현식에도 나타날 수 있습니다. N.G.

몇 가지 예를 살펴보겠습니다.

Sales 테이블에 ProductType, Quantity 및 Price 열이 포함되어 있다고 가정하고 각 제품 유형에 대한 수익을 알고 싶습니다. 이렇게 하려면 SELECT 문 뒤의 열 목록에 Quantity*Price 표현식을 포함하면 됩니다.

SELECT 제품_유형, 수량, 가격, 수량*가격처럼

총 판매액;

이는 AS(as) 키워드를 사용하여 계산된 데이터 열의 별칭을 지정합니다.

그림에서. 그림 10은 원래 Sales 테이블과 쿼리 결과 테이블을 보여줍니다.

쌀. 10. 제품 유형별 수익 계산에 대한 쿼리 결과

모든 상품 판매로 인한 총 수익을 확인하려면 다음 쿼리를 사용하세요.

합계 선택 (수량*가격) FROM 매출;

다음 쿼리에는 열 목록과 WHERE 절의 조건 모두에 계산된 식이 포함되어 있습니다. 그는 판매 수익이 1000보다 큰 제품을 판매 테이블에서 선택합니다.

제품_유형, 수량*가격 전체를 선택하세요.

판매에서

수량*가격 > 1000;

두 개의 열이 있는 테이블을 가져오려고 한다고 가정해 보겠습니다.

제품 유형 및 가격이 포함된 제품

수익이 포함된 합계입니다.

원래 sales 테이블에서 Product_Type 열은 문자(CHAR 유형)이고 Price 열은 숫자라고 가정하므로, 이들 열의 데이터를 병합(접착)할 때 다음을 사용하여 숫자 유형을 문자 유형으로 변환해야 합니다. CAST() 함수. 이 작업을 수행하는 쿼리는 다음과 같습니다(그림 11).

제품_유형 선택 | | " (가격: " | | CAST(가격 AS CHAR(5)) | | ")" AS 제품, 수량*가격 AS 합계

판매에서;

쌀. 11. 서로 다른 유형의 데이터를 하나의 열에 결합한 쿼리 결과

메모. Microsoft Access에서 유사한 쿼리는 다음과 같습니다.

SELECT 상품_유형 + " (가격: " + C Str (가격) + ")" AS 품목,

수량*가격 AS 합계

판매에서;

CASE 문을 사용한 조건식

기존 프로그래밍 언어에는 일부 조건이 참인지 아닌지에 따라 계산 프로세스를 제어할 수 있는 조건부 점프 연산자가 있습니다. SQL에서 이 연산자는 CASE(사건, 상황, 사례). SQL:2003에서 이 연산자는 값을 반환하므로 표현식에 사용할 수 있습니다. 여기에는 두 가지 주요 형태가 있는데, 이 섹션에서 살펴보겠습니다.

값이 포함된 CASE 문

값이 포함된 CASE 문의 구문은 다음과 같습니다.

CASE 확인_값

WHEN 값1 THEN 결과1

WHEN 값2 THEN 결과2

. . .

N의 값이 있을 때 N의 결과

ELSE 결과X

경우에 확인_값값 1과 같음 , CASE 문은 값을 반환합니다.결과1 , THEN 키워드 뒤에 지정됩니다. 그렇지 않으면 check_value가 다음과 비교됩니다.값2 , 동일하면 result2 값이 반환됩니다. 그렇지 않으면 테스트 중인 값이 WHEN 키워드 뒤에 지정된 다음 값과 비교됩니다. test_value가 이러한 값 중 어느 것과도 같지 않으면 값이 반환됩니다.결과 X , ELSE(else) 키워드 뒤에 지정됩니다.

ELSE 키워드는 선택 사항입니다. 누락되어 있고 비교되는 값 중 테스트 중인 값과 동일한 값이 없으면 CASE 문은 NULL을 반환합니다.

Clients 테이블(그림 1 참조)을 기반으로 지역 이름이 코드 번호로 대체된 테이블을 얻고 싶다고 가정해 보겠습니다. 소스 테이블에 서로 다른 영역이 너무 많지 않은 경우 이 문제를 해결하려면 CASE 연산자와 함께 쿼리를 사용하는 것이 편리합니다.

SELECT 이름, 주소,

CASE 지역

"모스크바"가 "77"일 때

"트베리 지역"일 때 "69"

. . .

ELSE 지역

AS 지역코드

클라이언트로부터;

검색 조건이 포함된 CASE 문

CASE 연산자의 두 번째 형태는 테이블에서 특정 조건을 만족하는 레코드를 검색할 때 사용됩니다.

사례

WHEN 조건1 THEN 결과1

WHEN catch2 THEN 결과2

. . .

WHEN 조건 N THEN 결과 N

ELSE 결과X

CASE 문은 WHERE 절로 정의된 집합의 첫 번째 레코드에 대해 조건 1이 참인지 또는 WHERE가 없는 경우 전체 테이블에 대해 true인지 테스트합니다. 그렇다면 CASE는 결과1을 반환합니다. 그렇지 않으면 이 레코드에 대해 조건 2가 확인됩니다. true이면 result2 값이 반환됩니다. 조건 중 어느 것도 true가 아니면 result 값이 반환됩니다.엑스 , ELSE 키워드 뒤에 지정됩니다.

ELSE 키워드는 선택 사항입니다. 누락되고 조건 중 어느 것도 true가 아닌 경우 CASE 문은 NULL을 회전합니다. CASE가 포함된 명령문이 첫 번째 레코드에 대해 실행된 후 다음 레코드로 이동합니다. 이는 전체 레코드 세트가 처리될 때까지 계속됩니다.

책 테이블(제목, 가격)에서 해당 책이 품절된 경우 열이 NULL이라고 가정합니다. 다음 쿼리는 NULL 대신 "품절"을 표시하는 테이블을 반환합니다.

제목 선택,

사례

가격이 NULL인 경우 "품절"

ELSE CAST(가격은 CHAR(8))

AS 가격

책에서;

동일한 열의 모든 값은 동일한 유형이어야 합니다. 따라서 이 쿼리에서는 CAST 유형 변환 함수를 사용하여 Price 열의 숫자 값을 문자 유형으로 캐스팅합니다.

CASE 문의 첫 번째 형식 대신 항상 두 번째 형식을 사용할 수 있습니다.

사례

테스트_값 = 값1일 때 결과1

테스트된_값 = 값2일 때 결과2

. . .

check_value = 값인 경우 N 그러면 결과N

ELSE 결과

NULLIF 및 COALESCE 함수

어떤 경우에는, 특히 데이터 업데이트 요청(UPDATE 연산자)의 경우 번거로운 CASE 연산자 대신 더 간결한 NULLIF()(NULL if) 및 COALESCE()(결합) 함수를 사용하는 것이 편리합니다.

NULLIF 함수( 값1, 값2)는 첫 번째 매개변수의 값이 두 번째 매개변수의 값과 일치하는 경우 NULL을 반환하고, 일치하지 않는 경우 첫 번째 매개변수의 값은 변경되지 않고 반환됩니다. 즉, value1 = value2가 true이면 함수는 NULL을 반환하고, 그렇지 않으면 value1을 반환합니다.

이 함수는 다음 두 가지 형식의 CASE 문과 동일합니다.

• CASE 값1

값2가 NULL일 때

ELSE 값1

• 사례

값1 = 값2일 때 NULL

ELSE 값1

기능 병합( 값1, 값2, ... ,값 N) NULL 또는 NULL일 수 있는 값 목록을 허용합니다. 이 함수는 목록에서 지정된 값을 반환하거나 모든 값이 정의되지 않은 경우 NULL을 반환합니다.

이 함수는 다음 CASE 문과 동일합니다.

사례

값 1이 NULL이 아닌 경우 값 1

값 2가 NULL이 아닌 경우 값 2

. . .

값 N이 NULL이 아닌 경우 값 N

그렇지 않으면 NULL

Books (Title, Price) 테이블에서 해당 도서의 재고가 없는 경우 Price 열이 NULL이라고 가정해 보겠습니다. 다음 쿼리는 대신에 테이블을 반환합니다.없는 "품절"이라는 텍스트가 표시됩니다.

SELECT 이름, COALESCE(CAST(Price AS CHAR(8)),

"품절") AS 가격

책에서;

이것은 또 다른 일반적인 작업입니다. 기본 원칙은 또 다른 속성(파티션 요소)을 기반으로 정의된 행 섹션을 사용하여 다른 속성(순서 요소)에 따른 순서를 기반으로 한 속성(집합 요소)의 값을 누적하는 것입니다. . 은행 계좌 잔액 계산, 창고의 상품 가용성 추적 또는 현재 판매 수치 등 누적 합계를 계산하는 데는 많은 예가 있습니다.

SQL Server 2012 이전에는 누계를 계산하는 데 사용된 집합 기반 솔루션은 리소스 집약적이었습니다. 따라서 사람들은 느리지만 일부 상황에서는 세트 기반 솔루션보다 여전히 빠른 반복 솔루션으로 전환하는 경향이 있었습니다. SQL Server 2012의 창 기능에 대한 확장된 지원을 통해 집합 기반 및 반복 모두에서 이전 T-SQL 기반 솔루션보다 훨씬 더 나은 성능을 발휘하는 간단한 집합 기반 코드를 사용하여 누계를 계산할 수 있습니다. 새로운 솔루션을 보여주고 다음 섹션으로 넘어갈 수 있습니다. 하지만 변경 범위를 제대로 이해하는 데 도움이 되도록 이전 방식을 설명하고 그 성능을 새 접근 방식과 비교해 보겠습니다. 당연히 새로운 접근 방식을 설명하는 첫 번째 부분만 읽고 나머지 부분은 건너뛰어도 됩니다.

다양한 솔루션을 보여주기 위해 계정 잔액을 사용하겠습니다. 다음은 소량의 테스트 데이터로 Transactions 테이블을 생성하고 채우는 코드입니다.

NOCOUNT를 ON으로 설정하세요. TSQL2012를 사용하십시오. IF OBJECT_ID("dbo.Transactions", "U")는 NULL이 아닙니다. DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, -- 분할 열 tranid INT NOT NULL, -- 열 순서 지정 MONEY NOT NULL, -- 측정 CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); GO - 작은 테스트 데이터 세트 INSERT INTO dbo.Transactions(actid, tranid, val) VALUES (1, 1, 4.00), (1, 2, -2.00), (1, 3, 5.00), (1, 4, 2.00), (1, 5, 1.00), (1, 6, 3.00), (1, 7, -4.00), (1, 8, -1.00), (1, 9, -2.00), (1, 10 , -3.00), (2, 1, 2.00), (2, 2, 1.00), (2, 3, 5.00), (2, 4, 1.00), (2, 5, -5.00), (2, 6 , 4.00), (2, 7, 2.00), (2, 8, -4.00), (2, 9, -5.00), (2, 10, 4.00), (3, 1, -3.00), (3, 2, 3.00), (3, 3, -2.00), (3, 4, 1.00), (3, 5, 4.00), (3, 6, -1.00), (3, 7, 5.00), (3, 8, 3.00), (3, 9, 5.00), (3, 10, -3.00);

테이블의 각 행은 계좌의 은행 거래를 나타냅니다. 예금은 val 열에 양수 값을 갖는 거래로 표시되고, 출금은 음수 거래 값으로 표시됩니다. 우리의 임무는 tranid 열로 정렬된 val 행에 거래 금액을 누적하여 각 시점의 계정 잔액을 계산하는 것이며, 이는 각 계정에 대해 별도로 수행되어야 합니다. 원하는 결과는 다음과 같아야 합니다.

두 솔루션을 모두 테스트하려면 더 많은 데이터가 필요합니다. 이는 다음과 같은 쿼리로 수행할 수 있습니다.

DECLARE @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Transactions; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM( NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;

섹션의 섹션(계정) 및 행(거래) 수를 변경하도록 입력을 설정할 수 있습니다.

윈도우 기능을 활용한 세트 기반 솔루션

SUM 창 집계 함수를 사용하는 집합 기반 솔루션부터 시작하겠습니다. 여기에서 창의 정의는 매우 명확합니다. 창을 actid별로 섹션화하고, tranid별로 정렬하고, 필터를 사용하여 맨 아래(UNBOUNDED PRECEDING)부터 현재까지 프레임의 줄을 선택해야 합니다. 해당 요청은 다음과 같습니다.

SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Balance FROM dbo.Transactions;

이 코드는 간단하고 간단할 뿐만 아니라 속도도 빠릅니다. 이 쿼리에 대한 계획은 그림에 나와 있습니다.

테이블에는 POC 요구 사항을 충족하고 창 기능에서 사용할 수 있는 클러스터형 인덱스가 있습니다. 특히, 인덱스 키 목록은 분할 요소(actid)와 순서 지정 요소(tranid)를 기반으로 하며, 인덱스에는 적용 범위를 제공하기 위해 쿼리(val)의 다른 모든 열도 포함됩니다. 계획에는 정렬된 스캔, 내부 요구에 대한 라인 번호 계산, 창 집계가 포함됩니다. POC 인덱스가 있으므로 최적화 프로그램은 계획에 정렬 연산자를 추가할 필요가 없습니다. 이것은 매우 효과적인 계획입니다. 게다가 선형적으로 확장됩니다. 나중에 성능 비교 결과를 보여주면 이 방법이 이전 솔루션과 비교하여 얼마나 더 효과적인지 확인할 수 있습니다.

SQL Server 2012 이전에는 하위 쿼리나 조인이 사용되었습니다. 하위 쿼리를 사용하는 경우 외부 행과 동일한 actid 값과 외부 행의 값보다 작거나 같은 tranid 값을 가진 모든 행을 필터링하여 누계를 계산합니다. 그런 다음 필터링된 행에 집계가 적용됩니다. 해당 요청은 다음과 같습니다.

연결을 사용하여 유사한 접근 방식을 구현할 수 있습니다. 조인의 ON 절에 있는 하위 쿼리의 WHERE 절과 동일한 조건자가 사용됩니다. 이 경우 T1으로 지정된 인스턴스에 있는 동일한 계정 A의 N번째 트랜잭션에 대해 T2 인스턴스에서 N개의 일치 항목을 찾을 수 있으며 트랜잭션 번호는 1부터 N까지입니다. 일치 결과 T1의 행은 다음과 같습니다. 반복되므로 현재 트랜잭션에 대한 정보를 가져오고 T2의 val 속성에 집계를 적용하여 누계를 계산하려면 T1의 모든 요소에 걸쳐 행을 그룹화해야 합니다. 완료된 요청은 다음과 같습니다.

SELECT T1.actid, T1.tranid, T1.val, SUM(T2.val) AS 잔액 FROM dbo.Transactions AS T1 JOIN dbo.Transactions AS T2 ON T2.actid = T1.actid AND T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

아래 그림은 두 솔루션에 대한 계획을 보여줍니다.

두 경우 모두 클러스터형 인덱스의 전체 검색이 인스턴스 T1에서 수행됩니다. 그런 다음 계획의 각 행에 대해 인덱스 끝 페이지의 현재 계정 섹션 시작 부분의 인덱스에서 검색 작업이 있으며, 이는 T2.tranid가 T1보다 작거나 같은 모든 트랜잭션을 읽습니다. 천진한. 행 집계가 발생하는 지점은 계획에 따라 약간 다르지만 읽는 행 수는 동일합니다.

조회되는 행 수를 이해하려면 데이터 요소 수를 고려해야 합니다. p를 섹션(계정) 수, r을 섹션(트랜잭션)의 행 수로 설정합니다. 그런 다음 트랜잭션이 계정 전체에 균등하게 분배된다고 가정하면 테이블의 행 수는 대략 p*r과 같습니다. 따라서 위의 스캔은 p*r 행을 다룹니다. 그러나 우리가 가장 관심을 갖는 것은 Nested Loops 반복자에서 일어나는 일입니다.

각 섹션에서 계획은 1 + 2 + ... + r 행 읽기를 제공하며 총계는 (r + r*2) / 2입니다. 계획에서 처리되는 총 행 수는 p*r + p*입니다. (r + r2) / 2. 이는 섹션 크기가 증가함에 따라 계획의 작업 수가 제곱으로 증가한다는 것을 의미합니다. 즉, 섹션 크기를 f배로 늘리면 작업량이 약 f 2배로 증가합니다. 이것은 나쁘다. 예를 들어, 100줄은 10,000줄에 해당하고, 1000줄은 백만 줄에 해당합니다. 간단히 말해서, 2차 함수가 매우 빠르게 증가하기 때문에 섹션 크기가 상당히 크면 쿼리 실행이 크게 느려집니다. 이러한 솔루션은 섹션당 수십 줄에서 만족스럽게 작동하지만 그 이상은 작동하지 않습니다.

커서 솔루션

커서 기반 솔루션이 정면으로 구현됩니다. 커서는 actid 및 tranid를 기준으로 데이터를 정렬하는 쿼리를 기반으로 선언됩니다. 그런 다음 커서 레코드를 통한 반복 전달이 수행됩니다. 새 계정이 감지되면 집계가 포함된 변수가 재설정됩니다. 각 반복에서 새 거래 금액이 변수에 추가된 후 현재 거래에 대한 정보와 누계의 현재 값이 포함된 행이 테이블 변수에 저장됩니다. 반복 전달 후 테이블 변수의 결과가 반환됩니다. 완성된 솔루션의 코드는 다음과 같습니다.

DECLARE @Result AS TABLE(actid INT, tranid INT, val MONEY, Balance MONEY); @actid AS INT, @prvactid AS INT, @tranid AS INT, @val AS MONEY, @balance AS MONEY를 선언하세요. DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transactions ORDER BY actid, tranid; OPEN C FETCH 다음 FROM C INTO @actid, @tranid, @val; SELECT @prvactid = @actid, @balance = 0; WHILE @@fetch_status = 0 @actid인 경우 시작<>@prvactid SELECT @prvactid = @actid, @balance = 0; SET @balance = @balance + @val; @Result VALUES(@actid, @tranid, @val, @balance)에 삽입합니다. C에서 @actid, @tranid, @val로 다음 항목을 가져옵니다. 끝 닫기 C; C를 할당 해제합니다. SELECT * FROM @Result;

커서를 사용한 쿼리 계획은 그림에 나와 있습니다.

이 계획은 인덱스의 데이터가 특정 순서로 한 번만 스캔되므로 선형적으로 확장됩니다. 또한 커서에서 행을 검색하는 각 작업의 행당 비용은 대략 동일합니다. 하나의 커서 라인을 처리하여 생성된 로드를 g와 같게 하면 이 솔루션의 비용은 p*r + p*r*g로 추정할 수 있습니다(기억하시겠지만 p는 섹션 수이고 r은 섹션의 행 수). 따라서 섹션당 행 수를 f배로 늘리면 시스템의 부하는 p*r*f + p*r*f*g가 됩니다. 즉, 선형적으로 증가하게 됩니다. 행당 처리 비용은 높지만 확장의 선형 특성으로 인해 특정 파티션 크기에서 이 솔루션은 이러한 솔루션의 2차 확장으로 인해 중첩 쿼리 및 조인 기반 솔루션보다 더 나은 확장성을 나타냅니다. 제가 수행한 성능 측정에 따르면 커서 솔루션이 더 빠른 부분은 섹션당 수백 행입니다.

커서 기반 솔루션이 제공하는 성능 이점에도 불구하고 관계형이 아니기 때문에 일반적으로 피해야 합니다.

CLR 기반 솔루션

다음을 기반으로 한 가능한 솔루션 중 하나 CLR(공용 언어 런타임)본질적으로 커서를 사용하는 솔루션 형태입니다. 차이점은 다음 행을 가져오고 반복하는 데 많은 리소스를 낭비하는 T-SQL 커서를 사용하는 대신 훨씬 빠른 .NET SQLDataReader 및 .NET 반복을 사용한다는 것입니다. 이 옵션을 더 빠르게 만드는 CLR 기능 중 하나는 결과 행이 임시 테이블에 필요하지 않다는 것입니다. 결과는 호출 프로세스로 직접 전송됩니다. CLR 기반 솔루션의 논리는 커서 및 T-SQL 솔루션의 논리와 유사합니다. 다음은 해결 저장 프로시저를 정의하는 C# 코드입니다.

시스템 사용; System.Data 사용; System.Data.SqlClient 사용; System.Data.SqlTypes 사용; Microsoft.SqlServer.Server 사용; 공개 부분 클래스 StoredProcedures ( 공개 정적 void AccountBalances() ( (SqlConnection conn = new SqlConnection("context 연결=true;") 사용) ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" " + "SELECT actid, tranid, val " + "FROM dbo.Transactions " + "ORDER BY actid, tranid;"; SqlMetaData 열 = 새 SqlMetaData; 열 = 새 SqlMetaData("actid" , SqlDbType.Int); 열 = 새 SqlMetaData("tranid" , SqlDbType.Int); columns = new SqlMetaData("val", SqlDbType.Money); columns = new SqlMetaData("balance", SqlDataRecord(columns); Pipe.SendResultsStart(record); SqlDataReader reader = comm .ExecuteReader(); SqlInt32 prvactid = 0; while (reader.Read()) ; SqlMoney val = reader.GetSqlMoney(2); if (actid == prvactid) ( 잔액 = val; ) prvactid = actid; 레코드.SetSqlInt32(0, reader.GetSqlInt32(0)); Record.SetSqlInt32(1, reader.GetSqlInt32(1)); Record.SetSqlMoney(2, val); Record.SetSqlMoney(3, 잔액); SqlContext.Pipe.SendResultsRow(레코드); ) SqlContext.Pipe.SendResultsEnd(); ) ) )

SQL Server에서 이 저장 프로시저를 실행하려면 먼저 이 코드를 기반으로 AccountBalances라는 어셈블리를 빌드하고 이를 TSQL2012 데이터베이스에 배포해야 합니다. SQL Server에서 어셈블리를 배포하는 데 익숙하지 않은 경우 저장 프로시저 문서의 저장 프로시저 및 CLR 섹션을 읽어보는 것이 좋습니다.

어셈블리 이름을 AccountBalances로 지정하고 어셈블리 파일 경로가 "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll"인 경우 어셈블리를 데이터베이스에 로드하고 다음 코드를 사용하여 저장 프로시저를 등록할 수 있습니다.

"C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll"에서 어셈블리 AccountBalances를 생성합니다. GO 외부 이름으로 dbo.AccountBalances 프로시저 만들기 AccountBalances.StoredProcedures.AccountBalances;

어셈블리를 배포하고 프로시저를 등록한 후 다음 코드를 사용하여 실행할 수 있습니다.

EXEC dbo.AccountBalances;

앞서 말했듯이 SQLDataReader는 커서의 또 다른 형태이지만 이 버전은 T-SQL에서 기존 커서를 사용하는 것보다 행 읽기 오버헤드가 훨씬 적습니다. 또한 T-SQL보다 .NET에서 반복이 훨씬 빠릅니다. 따라서 CLR 기반 솔루션도 선형적으로 확장됩니다. 테스트 결과, 섹션의 행 수가 15개를 초과하면 이 솔루션의 성능이 하위 쿼리 및 조인을 사용하는 솔루션의 성능보다 더 높은 것으로 나타났습니다.

완료되면 다음 정리 코드를 실행해야 합니다.

삭제 절차 dbo.AccountBalances; 조립품 삭제 AccountBalances;

중첩된 반복

지금까지 반복적이고 집합 기반 솔루션을 보여주었습니다. 다음 솔루션은 반복 및 집합 기반 접근 방식이 혼합된 중첩 반복을 기반으로 합니다. 아이디어는 먼저 ROW_NUMBER 함수를 사용하여 계산되는 rownum이라는 새 속성과 함께 원본 테이블(이 경우 은행 계좌)의 행을 임시 테이블에 복사하는 것입니다. 행 번호는 actid로 분할되고 tranid로 정렬되므로 각 은행 계좌의 첫 번째 거래에는 번호 1이 할당되고 두 번째 거래에는 번호 2가 할당되는 식입니다. 그런 다음 키 목록(rownum, actid)이 포함된 클러스터형 인덱스가 임시 테이블에 생성됩니다. 그런 다음 재귀적 CTE 표현식 또는 특별히 제작된 루프를 사용하여 모든 계정에서 반복당 하나의 행을 처리합니다. 그런 다음 현재 행과 관련된 값과 이전 행과 관련된 값을 더하여 누계를 계산합니다. 다음은 재귀적 CTE를 사용하여 이 논리를 구현한 것입니다.

SELECT actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; 고유한 클러스터형 인덱스 생성 idx_rownum_actid ON #Transactions(rownum, actid); WITH C AS (SELECT 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val FROM C AS PRV JOIN #Transactions AS CUR ON CUR.rownum = PRV.rownum + 1 AND CUR.actid = PRV.actid) SELECT actid, tranid, val, sumqty FROM C OPTION (MAXRECURSION 0); DROP TABLE #거래;

그리고 이것은 명시적 루프를 사용한 구현입니다.

SELECT ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST(val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; 고유한 클러스터형 인덱스 생성 idx_rownum_actid ON #Transactions(rownum, actid); @rownum을 INT로 선언하세요. SET @rownum = 1; WHILE 1 = 1 BEGIN SET @rownum = @rownum + 1; UPDATE CUR SET sumqty = PRV.sumqty + CUR.val FROM #Transactions AS CUR JOIN #Transactions AS PRV ON CUR.rownum = @rownum AND PRV.rownum = @rownum - 1 AND CUR.actid = PRV.actid; IF @@rowcount = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #거래;

이 솔루션은 파티션당 행 수가 적고 파티션 수가 많을 때 우수한 성능을 제공합니다. 그런 다음 반복 횟수가 적고 대부분의 작업은 한 행 번호와 연관된 행을 이전 행 번호와 연관된 행과 연결하는 솔루션의 집합 기반 부분에 의해 수행됩니다.

변수를 사용한 여러 줄 업데이트

지금까지 표시된 누적 합계 계산 방법은 올바른 결과를 제공하는 것을 보장합니다. 이 섹션에 설명된 기술은 문서화된 시스템 동작이 아닌 관찰된 시스템 동작을 기반으로 하고 상대성 원리에도 모순되기 때문에 논란의 여지가 있습니다. 높은 매력은 빠른 작업 속도 때문입니다.

이 방법은 변수와 함께 UPDATE 문을 사용합니다. UPDATE 문은 열의 값을 기준으로 변수에 표현식을 할당할 수 있고, 변수가 있는 표현식에 열의 값을 할당할 수도 있습니다. 솔루션은 actid, tranid, val 및 Balance 속성을 갖는 Transactions라는 임시 테이블과 키 목록(actid, tranid)이 있는 클러스터형 인덱스를 생성하는 것으로 시작됩니다. 그런 다음 임시 테이블은 원본 Transactions 데이터베이스의 모든 행으로 채워지고 모든 행의 잔액 열에는 값 0.00이 입력됩니다. 그런 다음 임시 테이블과 연관된 변수와 함께 UPDATE 문을 호출하여 누계를 계산하고 계산된 값을 잔액 열에 삽입합니다.

@prevaccount 및 @prevbalance 변수가 사용되며 잔액 열의 값은 다음 식을 사용하여 계산됩니다.

SET @prevbalance = 잔액 = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END

CASE 표현식은 현재 계정 ID와 이전 계정 ID가 동일한지 확인하고, 동일하다면 잔액 열의 이전 값과 현재 값의 합계를 반환합니다. 계정 ID가 다른 경우 현재 거래 금액이 반환됩니다. 다음으로 CASE 표현식의 결과가 잔액 열에 삽입되고 @prevbalance 변수에 할당됩니다. 별도의 표현식에서 ©prevaccount 변수에는 현재 계정의 ID가 할당됩니다.

UPDATE 문 이후에 솔루션은 임시 테이블의 행을 표시하고 마지막 행을 삭제합니다. 완성된 솔루션의 코드는 다음과 같습니다.

CREATE TABLE #트랜잭션(actid INT, tranid INT, val MONEY, Balance MONEY); 클러스터형 인덱스 생성 idx_actid_tranid ON #Transactions(actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, Balance) SELECT actid, tranid, val, 0.00 FROM dbo.Transactions ORDER BY actid, tranid; @prevaccount를 INT로, @prevbalance를 MONEY로 선언하세요. UPDATE #트랜잭션 SET @prevbalance = 밸런스 = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) OPTION (MAXDOP 1); SELECT * FROM #거래; DROP TABLE #거래;

이 솔루션의 개요는 다음 그림에 나와 있습니다. 첫 번째 부분은 INSERT 문으로 표시되고, 두 번째 부분은 UPDATE, 세 번째 부분은 SELECT 문으로 표시됩니다.

이 솔루션은 UPDATE 실행 최적화가 항상 클러스터형 인덱스의 순서화된 스캔을 수행한다고 가정하고 동시성 등 이를 방해할 수 있는 상황을 방지하기 위한 여러 힌트를 제공합니다. 문제는 최적화 프로그램이 항상 클러스터형 인덱스의 순서로 검색된다는 공식적인 보장이 없다는 것입니다. 정의에 따라 해당 동작을 보장할 수 있는 논리 요소가 코드에 없으면 코드가 논리적으로 올바른지 확인하기 위해 물리적 계산에 의존할 수 없습니다. 이 코드에는 이 동작을 보장할 수 있는 논리적 기능이 없습니다. 당연히 이 방법을 사용할지 여부는 전적으로 귀하의 양심에 달려 있습니다. 수천 번 확인하고 "모든 것이 제대로 작동하는 것 같다"고 하더라도 그것을 사용하는 것은 무책임하다고 생각합니다.

다행히 SQL Server 2012에서는 이러한 선택이 사실상 불필요해졌습니다. 윈도우 집계 함수를 사용하는 매우 효율적인 솔루션이 있으면 다른 솔루션을 생각할 필요가 없습니다.

성능 측정

다양한 기술의 성능을 측정하고 비교했습니다. 결과는 아래 그림과 같습니다.

하위 쿼리/조인 방법은 다른 방법보다 속도가 너무 느려서 다른 척도를 사용해야 했기 때문에 결과를 두 개의 그래프로 나눴습니다. 어떤 경우든 대부분의 솔루션은 작업 부하와 파티션 크기 사이의 선형 관계를 보여주고 하위 쿼리 또는 조인 솔루션만 2차 관계를 보여줍니다. 또한 윈도우 집계 함수를 기반으로 하는 새로운 솔루션이 얼마나 더 효율적인지 확인하는 것도 분명합니다. 변수를 사용하는 UPDATE 솔루션도 매우 빠르지만 이미 설명한 이유로 사용을 권장하지 않습니다. CLR 솔루션도 매우 빠르지만 모든 .NET 코드를 작성하고 어셈블리를 데이터베이스에 배포해야 합니다. 어떻게 보더라도 창호유닛을 활용한 키트 기반 솔루션이 가장 바람직하다.

SQL 언어의 SUM 함수는 단순함에도 불구하고 데이터베이스 작업 시 자주 사용됩니다. 도움을 받으면 보조 DBMS 도구의 도움을 받지 않고도 중간 또는 최종 결과를 얻는 것이 편리합니다.

함수 구문

대부분의 SQL 언어에서 합계 구문은 동일합니다. 즉, 필드 이름이나 일부 산술 연산만 합계가 필요한 인수로 사용됩니다.

예외적인 경우에는 특정 값을 숫자나 변수로 전송할 수 있지만 이러한 "구성표"는 많은 값을 전달하지 않기 때문에 실제로 사용되지 않습니다. 다음은 SQL의 함수 구문입니다.

sum(a) - 여기서는 숫자 값이나 표현식이 매개변수로 사용됩니다.

매개변수 앞에 키워드(예: 각각 고유한 값만 사용하거나 모든 값을 사용하는 DISTINCT 또는 ALL)를 설정할 수 있다는 점은 주목할 가치가 있습니다.

SQL에서 SUM을 사용하는 예

기능이 어떻게 작동하는지 완전히 이해하려면 몇 가지 예를 고려해 볼 가치가 있습니다. SQL에서 SUM은 반환 결과와 중간 값(예: 조건 테스트)으로 모두 사용될 수 있습니다.

첫 번째 경우에는 구매 횟수가 복수일 수 있다는 점을 고려하여 각 제품의 판매량을 반환해야 하는 옵션을 고려합니다. 결과를 얻으려면 다음 쿼리를 실행하면 충분합니다.

SELECT 제품, sum(PurchaseAmount) FROM Sales GroupBy;

이 명령에 대한 응답은 각 제품의 총 구매 금액이 포함된 고유한 제품 목록입니다.

두 번째 예에서는 판매량이 특정 값(예: 100)을 초과한 제품 목록을 가져와야 합니다. 이 작업에 대한 결과는 여러 가지 방법으로 얻을 수 있으며 그 중 가장 최적의 방법은 하나의 요청을 실행하는 것입니다.

SELECT Product FROM (SELECT Product, sum(구매 금액) as Amount FROM Sales) WHERE Sum > 100.

특정 공급업체가 생산한 PC 모델 수를 어떻게 알 수 있나요? 동일한 기술적 특성을 가진 컴퓨터의 평균 가격을 결정하는 방법은 무엇입니까? 일부 통계 정보와 관련된 이러한 질문과 기타 많은 질문은 다음을 사용하여 답변할 수 있습니다. 최종(집계) 함수. 표준은 다음과 같은 집계 함수를 제공합니다.

이러한 함수는 모두 단일 값을 반환합니다. 동시에, 기능 개수, 최소그리고 최대모든 데이터 유형에 적용 가능하지만 합집합그리고 평균숫자 필드에만 사용됩니다. 기능의 차이 세다(*)그리고 세다(<имя поля>) 두 번째는 계산할 때 NULL 값을 고려하지 않는다는 것입니다.

예. 개인용 컴퓨터의 최소 및 최대 가격을 찾으십시오.

예. 제조업체 A에서 생산할 수 있는 컴퓨터 수를 찾으십시오.

예. 제조업체 A가 생산한 다양한 모델의 수에 관심이 있는 경우 쿼리는 다음과 같이 공식화될 수 있습니다(제품 테이블에 각 모델이 한 번 기록된다는 사실을 사용).

예. 제조업체 A가 생산한 사용 가능한 다양한 모델의 수를 찾으십시오. 쿼리는 제조업체 A가 생산한 총 모델 수를 확인해야 했던 이전 쿼리와 유사합니다. 여기서는 다음에서 다양한 모델의 수도 찾아야 합니다. PC 테이블(즉, 판매 가능한 테이블)

통계 지표를 얻을 때 고유한 값만 사용되도록 하려면 집계 함수의 인수사용될 수 있다 DISTINCT 매개변수. 또 다른 매개변수 전체기본값이며 열의 모든 반환 값이 계산된다고 가정합니다. 운영자,

생산된 PC 모델 수를 알아야 하는 경우 모든 사람제조업체에 따라 사용해야 합니다. GROUP BY 절, 구문상 다음과 같습니다. WHERE 절.

GROUP BY 절

GROUP BY 절다음에 적용할 수 있는 출력 라인 그룹을 정의하는 데 사용됩니다. 집계 함수(COUNT, MIN, MAX, AVG 및 SUM). 이 절이 누락되고 집계 함수가 사용되는 경우에는 이름이 언급된 모든 열이 선택하다, 에 포함되어야 합니다. 집계 함수, 이러한 함수는 쿼리 조건자를 충족하는 전체 행 집합에 적용됩니다. 그렇지 않으면 SELECT 목록의 모든 열이 포함되지집계 함수를 지정해야 합니다. GROUP BY 절에서. 결과적으로, 모든 출력 쿼리 행은 해당 열의 동일한 값 조합을 특징으로 하는 그룹으로 나뉩니다. 이후에는 각 그룹에 집계 함수가 적용됩니다. GROUP BY의 경우 모든 NULL 값은 동일하게 취급됩니다. NULL 값이 포함된 필드를 기준으로 그룹화하면 이러한 모든 행이 하나의 그룹에 속하게 됩니다.
만약에 GROUP BY 절이 있는 경우, SELECT 절에서 집계 함수 없음, 그러면 쿼리는 단순히 각 그룹에서 하나의 행을 반환합니다. 이 기능은 DISTINCT 키워드와 함께 결과 집합에서 중복 행을 제거하는 데 사용할 수 있습니다.
간단한 예를 살펴보겠습니다.

SELECT 모델, COUNT(모델) AS Qty_model, AVG(가격) AS Avg_price PC에서 모델별로 그룹화;

이 요청에서는 각 PC 모델에 대해 모델 수와 평균 비용이 결정됩니다. 동일한 모델 값을 가진 모든 행이 그룹을 형성하고 SELECT의 출력은 각 그룹에 대한 값의 개수와 평균 가격 값을 계산합니다. 쿼리 결과는 다음 표와 같습니다.

모델	수량_모델	평균 가격
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

SELECT에 날짜 열이 있는 경우 각 특정 날짜에 대해 이러한 표시기를 계산할 수 있습니다. 이렇게 하려면 날짜를 그룹화 열로 추가해야 하며, 그런 다음 각 값 조합(모델-날짜)에 대해 집계 함수가 계산됩니다.

몇 가지 구체적인 내용이 있습니다 집계 함수 수행 규칙:

• 요청에 따른 결과인 경우 수신된 행이 없습니다.(또는 특정 그룹에 대해 둘 이상의 행), 집계 함수를 계산하기 위한 소스 데이터가 없습니다. 이 경우 COUNT 함수의 결과는 0이 되고, 다른 모든 함수의 결과는 NULL이 됩니다.
• 논쟁집계 함수 그 자체로는 집계 함수를 포함할 수 없습니다.(함수에서 함수). 저것들. 예를 들어 한 쿼리에서는 최대 평균값을 얻는 것이 불가능합니다.
• COUNT 함수를 실행한 결과는 다음과 같습니다. 정수(정수). 다른 집계 함수는 처리하는 값의 데이터 유형을 상속합니다.
• SUM 함수가 사용된 데이터 유형의 최대값보다 큰 결과를 생성하는 경우 오류.

따라서 요청에 포함되지 않은 경우 GROUP BY 절, 저것 집계 함수포함 된 SELECT 절, 모든 결과 쿼리 행에 대해 실행됩니다. 요청에 다음이 포함된 경우 GROUP BY 절, 지정된 열 또는 열 그룹의 값이 동일한 각 행 집합 GROUP BY 절, 그룹을 구성하고 집계 함수각 그룹별로 개별적으로 수행됩니다.

제안을 받는 중

만약에 WHERE 절행 필터링을 위한 조건자를 정의한 다음 제안을 받음적용됩니다 그룹화 후값별로 그룹을 필터링하는 유사한 조건자를 정의합니다. 집계 함수. 이 절은 다음을 사용하여 얻은 값을 검증하는 데 필요합니다. 집계 함수다음에 정의된 레코드 원본의 개별 행이 아닌 FROM 절, 그리고 에서 그러한 라인의 그룹. 따라서 그러한 수표는 다음에 포함될 수 없습니다. WHERE 절.