이것은 또 다른 일반적인 과제입니다. 기본 원칙은 다른 속성(순서화 요소)에 의한 순서를 기반으로 한 속성(집계될 요소)의 값을 누적하는 것입니다. 아마도 다른 속성(들)을 기반으로 정의된 행 섹션(파티션 요소). 인생에는 은행 잔고 계산, 재고 재고 또는 현재 판매 수치 추적 등과 같이 누적 합계를 계산하는 많은 예가 있습니다.

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

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

설정 번호 켜기; 사용 TSQL2012; IF OBJECT_ID("dbo.Transactions", "U") IS NOT NULL DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions(actid INT NOT NULL, - tranid 분할 열 INT NOT NULL, - 정렬 열 val 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; 테이블 자르기 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 (UNBOUNDED PRECEDING 및 CURRENT ROW 사이의 tranid 행 ORDER BY actid에 의한 파티션) AS 균형 FROM dbo.Transactions;

이 코드는 간단하고 간단할 뿐만 아니라 빠르게 실행됩니다. 이 쿼리에 대한 계획은 그림에 나와 있습니다.

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

SQL Server 2012 이전에는 중첩 쿼리 또는 조인이 사용되었습니다. 하위 쿼리를 사용할 때 누계는 외부 행과 동일한 활동 값과 외부 행 값보다 작거나 같은 tranid 값을 가진 모든 행을 필터링하여 계산됩니다. 그런 다음 필터링된 행에 집계가 적용됩니다. 관련 쿼리는 다음과 같습니다.

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

SELECT T1.actid, T1.tranid, T1.val, SUM (T2.val) AS balance 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.tranid 이하인 모든 트랜잭션이 있습니다. 읽습니다. 행 집계가 발생하는 지점은 계획에서 약간 다르지만 읽는 행 수는 동일합니다.

스캔 중인 행 수를 이해하려면 데이터 항목 수를 고려해야 합니다. p는 섹션(계정)의 수이고 r은 섹션(트랜잭션)의 행 수입니다. 그런 다음 트랜잭션이 여러 계정에 고르게 분포되어 있다고 가정하면 테이블의 행 수는 대략 p * r과 같습니다. 따라서 상단의 뷰는 p * r 라인에 걸쳐 있습니다. 그러나 우리가 가장 관심을 갖는 것은 Nested Loops iterator에서 일어나는 일입니다.

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

커서 솔루션

커서 기반 솔루션은 정면으로 구현됩니다. 커서는 actid 및 tranid별로 데이터를 정렬하는 쿼리를 기반으로 선언됩니다. 그 후, 커서 레코드의 반복 순회가 수행됩니다. 새 계정이 발견되면 집계를 포함하는 변수가 재설정됩니다. 각 반복에서 새 트랜잭션 금액이 변수에 추가되고 그 후에 행은 현재 트랜잭션에 대한 정보와 누적 합계의 현재 값이 포함된 테이블 변수에 저장됩니다. 반복 전달 후 결과는 테이블 변수에서 반환됩니다. 전체 솔루션의 코드는 다음과 같습니다.

DECLARE @Result AS TABLE (actid INT, tranid INT, val MONEY, 잔액 MONEY); @actid를 INT로, @prvactid를 INT로, @tranid를 INT로, @val을 MONEY로, @balance를 MONEY로 선언합니다. DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transactions ORDER BY actid, tranid; OPEN C FETCH NEXT FROM C INTO @actid, @tranid, @val; 선택 @prvactid = @actid, @balance = 0; WHILE @@ fetch_status = 0 BEGIN IF @actid<>@prvactid SELECT @prvactid = @actid, @balance = 0; SET @balance = @balance + @val; @결과 값에 삽입(@actid, @tranid, @val, @balance); FETCH NEXT FROM C INTO @actid, @tranid, @val; 종료 C; 할당 해제 C; SELECT * FROM @결과;

커서를 사용한 쿼리 계획은 그림과 같습니다.

인덱스의 데이터가 특정 순서로 한 번만 스캔되기 때문에 이 계획은 선형으로 확장됩니다. 또한 커서에서 행을 가져오는 각 작업의 행당 비용은 거의 같습니다. 커서의 한 행을 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(공용 정적 무효 AccountBalances()((SqlConnection conn = new SqlConnection 사용("컨텍스트 연결 = true;")) (SqlCommand comm = new SqlCommand(), comm.Connection = conn, comm.CommandText = @ " "+" SELECT 활동, tranid, val "+" FROM dbo.Transactions "+" ORDER BY 활동, tranid; "; SqlMetaData 열 = 새 SqlMetaData, 열 = 새 SqlMetaData(" 행동 ", SqlDbType.Int), 열 = 새 SqlMetaData("tranid", SqlDbType.Int), 열 = 새 SqlMetaData("val", SqlDbType.Money), 열 = 새 SqlMetaData("균형", SqlDbType.Money), SqlDataqlord 레코드 = 새 열 Pipe.SendResultsStart(레코드) ; conn.Open(), SqlDataReader 판독기 = comm.ExecuteReader(), SqlInt32 prvactid = 0, SqlMoney 잔액 = 0, (reader.Read()) (SqlInt32 actid = reader.GetSqlInt32(0), SqlMoney val = 판독기. GetSqlMoney(2), if (액티비티 == prvactid) (balance + = val;) else (balance = val;) prvactid = actid, rec ord.SetSqlInt32(0, 리더.GetSqlInt32(0)); 기록.SetSqlInt32(1, 리더.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 CREATE PROCEDURE dbo.AccountBalances AS EXTERNAL NAME AccountBalances.StoredProcedures.AccountBalances;

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

EXEC dbo.AccountBalances;

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

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

삭제 절차 dbo.AccountBalances; DROP ASSEMBLY AccountBalance;

중첩된 반복

지금까지 저는 반복적이고 집합적인 솔루션을 보여왔습니다. 다음 솔루션은 반복 및 집합 기반 접근 방식의 하이브리드인 중첩 반복을 기반으로 합니다. 아이디어는 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; CREATE UNIQUE CLUSTERED INDEX 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; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON #Transactions(rownum, actid); DECLARE @rownum AS 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 행동, tranid, val, sumqty FROM #Transactions; DROP TABLE #트랜잭션;

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

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

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

이 메서드는 변수와 함께 UPDATE 문을 사용합니다. UPDATE 문은 열 값을 기반으로 변수에 표현식을 할당할 수 있을 뿐만 아니라 변수가 있는 표현식에 열 값을 할당할 수 있습니다. 솔루션은 속성 actid, tranid, val 및 balance가 있는 Transactions라는 임시 테이블과 키 목록(actid, tranid)이 있는 클러스터형 인덱스를 만드는 것으로 시작합니다. 그런 다음 임시 테이블은 원래 트랜잭션 데이터베이스의 모든 행으로 채워지며 모든 행의 잔액 열에 0.00이 입력됩니다. 그런 다음 임시 테이블과 연결된 변수와 함께 UPDATE 문을 호출하여 누적 합계를 계산하고 계산된 값을 잔액 열에 삽입합니다.

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

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

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

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

CREATE TABLE #트랜잭션(actid INT, tranid INT, val MONEY, 잔액 MONEY); CREATE CLUSTERED INDEX 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를 돈으로 선언합니다. UPDATE #Transactions SET @prevbalance = balance = 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 코드를 작성하고 어셈블리를 데이터베이스에 배포해야 합니다. 그러나 그것을 보면 창 집계를 사용하는 집합 기반 솔루션이 여전히 선호되는 솔루션입니다.

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

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

예시. 개인용 컴퓨터의 최소 및 최대 가격 찾기:

예시. 제조업체 A에서 사용 가능한 컴퓨터 수를 찾으십시오.

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

예시. 제조업체 A에서 사용할 수 있는 다른 모델의 수를 찾으십시오. 쿼리는 제조업체 A에서 생산한 총 모델 수를 결정하는 데 필요한 이전 쿼리와 유사합니다. 여기에서 다른 모델의 수도 찾아야 합니다. PC 테이블(즉, 판매 가능).

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

생산된 PC 모델의 수를 구해야 하는 경우 모든제조업체, 당신은 사용해야합니다 GROUP BY 절구문상 다음 WHERE 절.

GROUP BY 절

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

몇 가지 구체적인 집계 기능을 수행하기 위한 규칙:

• 쿼리의 결과인 경우 수신된 라인이 없습니다(또는 이 그룹에 대해 둘 이상의 행), 집계 함수를 계산하기 위한 초기 데이터가 없습니다. 이 경우 COUNT 함수를 실행한 결과는 0이 되고 다른 모든 함수의 결과는 NULL이 됩니다.
• 논쟁집계 함수 자체적으로 집계 함수를 포함할 수 없음(함수에서 함수). 저것들. 하나의 쿼리에서는 최대 평균값을 얻을 수 없습니다.
• COUNT 함수를 실행한 결과는 정수(정수). 다른 집계 함수는 처리 중인 값의 데이터 유형을 상속합니다.
• SUM 함수 실행 시 사용된 데이터 타입의 최대값을 초과하는 결과가 나왔다면, 오류.

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

HAVING 절

만약에 WHERE 절행 필터링을 위한 술어를 정의한 다음 제안적용된 그룹화 후값으로 그룹을 필터링하는 유사한 술어 정의 집계 함수... 이 절은 다음을 사용하여 얻은 값을 테스트하는 데 필요합니다. 집계 함수에 정의된 레코드 소스의 별도 라인이 아닌 FROM 절에서 그러한 라인의 그룹... 따라서 그러한 수표는 WHERE 절.

이 튜토리얼에서는 사용 방법을 보여줍니다. 합 함수구문 및 예제가 있는 SQL Server(Transact-SQL)에서.

설명

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

통사론

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

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

매개변수 또는 인수

expression1, expression2,…
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

단일 필드 예

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

예를 들어 개수가 10보다 큰 모든 제품의 합계가 어떻게 되는지 알 수 있습니다.

SUM 함수의 이 예에서는 SUM(수량) 표현식을 "Total Quantity"로 설정합니다. 결과 집합을 반환할 때 - "총 수량"이 필드 이름으로 표시됩니다.

DISTINCT 사용 예

SUM 함수에서 DISTINCT 연산자를 사용할 수 있습니다. 예를 들어 다음 SQL 문은 연봉이 $29,000 미만인 경우 고유한 급여 값으로 총 급여를 반환합니다.

두 급여가 연간 $ 24,000라면 SUM 함수에서 이러한 값 중 하나만 사용됩니다.

수식 사용 예

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

Transact-SQL

SELECT SUM (판매 * 0.03) AS "총 수수료" FROM 주문;

GROUP BY 사용 예

경우에 따라 SUM 함수와 함께 GROUP BY 절을 사용해야 합니다.

SQL 11단원. 요약 함수, 계산된 열 및 보기

• AVG() 함수는 열의 평균 값을 반환합니다.


• COUNT() 함수는 열의 행 수를 반환합니다.


• MAX() 함수는 열에서 가장 큰 값을 반환합니다.


• MIN () 함수는 열에서 가장 작은 값을 반환합니다.


• SUM() 함수는 열 값의 합계를 반환합니다.

SELECT MIN(가격), MAX(가격), AVG(가격) FROM 가격;

이제 "Printing House" 공급자가 우리에게 상품을 얼마나 가져왔는지 알고 싶습니다(id = 2). 그러한 요청을 작성하는 것은 그리 쉬운 일이 아닙니다. 그것을 구성하는 방법에 대해 생각해 봅시다.

1. 먼저 배송(수신) 테이블에서 "인쇄소" 공급업체(id = 2)가 수행한 배송의 식별자(id_incoming)를 선택합니다.

2. 이제 테이블 매거진 배달(magazine_incoming)에서 단락 1에서 찾은 배달에서 수행된 상품(id_product) 및 수량(수량)을 선택해야 합니다. 즉, 단락 1의 요청이 중첩됩니다.

3. 이제 검색된 제품의 가격을 결과 테이블에 추가해야 하며 이는 가격 테이블에 저장됩니다. 즉, id_product 열을 기준으로 Magazine_incoming 및 Price 테이블을 연결해야 합니다.

4. 결과 테이블에는 Sum 열이 분명히 부족합니다. 계산된 열... 이러한 열을 생성하는 기능은 MySQL에서 제공됩니다. 이렇게 하려면 쿼리에서 계산된 열의 이름과 계산할 대상을 지정하기만 하면 됩니다. 이 예에서 이러한 열은 summa라고 하며 수량 및 가격 열의 곱을 계산합니다. 새 열 이름은 AS라는 단어로 구분됩니다.

SELECT magazine_incoming.id_product, magazine_incoming.quantity, price.price, magazine_incoming.quantity * price.price AS summa FROM magazine_incoming, 가격 WHERE magazine_incoming.id_product = price.id_product AND id_incoming = (SELECT id_incoming FROM 들어오는 WHERE 2 id_vendor

5. 좋습니다. 우리가 해야 할 일은 요약 열을 요약하고 마침내 "Printing House" 공급업체가 우리에게 상품을 얼마나 가져왔는지 알아내는 것입니다. SUM() 함수를 사용하는 구문은 다음과 같습니다.

SELECT SUM(column_name) FROM table_name;

우리는 열의 이름인 summa를 알고 있지만 쿼리의 결과이기 때문에 테이블 이름이 없습니다. 무엇을 할까요? 이러한 경우 MySQL은 보기를 제공합니다. 보기는 고유한 이름이 할당되고 나중에 사용하기 위해 데이터베이스에 저장할 수 있는 선택 쿼리입니다.

보기를 만드는 구문은 다음과 같습니다.

CREATE VIEW view_name AS 쿼리;

요청을 report_vendor라는 뷰로 저장해 보겠습니다.

CREATE VIEW report_vendor AS SELECT Magazine_incoming.id_product, magazine_incoming.quantity, price.price, magazine_incoming.quantity * price.price AS summa FROM magazine_incoming, 가격 WHERE magazine_incoming.id_product = price.id_product AND id_incoming = (FROMECT id_incomingid_incoming = (FROMECT FROMECT id_incoming) );

6. 이제 최종 SUM() 함수를 사용할 수 있습니다.

SELECT SUM(합계) FROM report_vendor;

따라서 결과를 얻었지만 이를 위해서는 중첩 쿼리, 조인, 계산된 열 및 보기를 사용해야 했습니다. 예, 때로는 아무데도 없이 결과를 얻기 위해 생각해야 합니다. 그러나 우리는 계산된 열과 보기라는 두 가지 매우 중요한 주제를 다루었습니다. 그들에 대해 더 자세히 이야기합시다.

계산된 필드(열)

SELECT column_1_name, column_2_name, column_1_name * column_2_name AS computed_column_name FROM table_name;

두 번째 뉘앙스는 AS 키워드로 계산된 열의 이름을 설정하는 데 사용했습니다. 실제로 이 키워드는 모든 열에 대한 별칭을 설정하는 데 사용됩니다. 이것이 필요한 이유는 무엇입니까? 더 짧고 읽기 쉬운 코드를 위해. 예를 들어 보기는 다음과 같을 수 있습니다.

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity * B.price AS summa FROM magazine_incoming AS A, 가격 AS B WHERE A.id_product = B.id_product AND id_incoming = (SELECT id_incoming FROM 수신 어디에 id_vendor = 2);

이것이 훨씬 더 짧고 명확하다는 데 동의하십시오.

대표

그러나 보기는 테이블이 아닙니다. 즉, 데이터를 저장하지 않고 다른 테이블에서 검색하기만 합니다. 따라서 먼저 테이블의 데이터가 변경되면 프레젠테이션 결과도 변경됩니다. 둘째, 뷰에 질의를 하면 필요한 데이터를 검색하게 되는데, 즉 DBMS의 성능이 저하된다. 그러므로 그들은 학대를 받아서는 안됩니다.

우리는 요약하는 법을 배울 것입니다. 아니오, 이것은 아직 SQL을 학습한 결과가 아니라 데이터베이스 테이블의 열 값에 대한 결과입니다. SQL 집계 함수는 열 값에 대해 작동하여 단일 결과 값을 생성합니다. 가장 일반적으로 사용되는 SQL 집계 함수는 SUM, MIN, MAX, AVG 및 COUNT입니다. 집계 함수를 사용하는 두 가지 경우를 구별할 필요가 있습니다. 첫째, 집계 함수는 자체적으로 사용되며 단일 결과 값을 반환합니다. 둘째, 집계 함수는 SQL GROUP BY 절과 함께 사용됩니다. 즉, 필드(열)별로 그룹화하여 각 그룹의 결과 값을 가져옵니다. 먼저 그룹화하지 않고 집계 함수를 사용하는 경우를 살펴보겠습니다.

SQL SUM 함수

SQL SUM 함수는 데이터베이스 테이블의 열 값의 합계를 반환합니다. 값이 숫자인 열에만 적용할 수 있습니다. 결과 합계를 얻기 위한 SQL 쿼리는 다음과 같이 시작합니다.

합계 선택(COLUMN_NAME) ...

이 표현식 뒤에는 FROM(TABLE_NAME)이 오고 WHERE 절을 사용하여 조건을 지정할 수 있습니다. 또한 열 이름 앞에 DISTINCT를 지정할 수 있으므로 고유한 값만 계산됩니다. 기본적으로 모든 값이 고려됩니다(이를 위해 DISTINCT가 아닌 ALL을 구체적으로 지정할 수 있지만 ALL이라는 단어는 선택 사항임).

MS SQL Server에서 이 단원의 데이터베이스에 대한 쿼리를 실행하고 싶지만 이 DBMS가 컴퓨터에 설치되어 있지 않은 경우 이 링크의 지침을 사용하여 설치할 수 있습니다. .

먼저 회사 데이터베이스인 Company1으로 작업합니다. 이 데이터베이스, 해당 테이블을 만들고 테이블을 데이터로 채우는 스크립트 - 이 링크의 파일 .

예 1.부서 및 직원에 대한 데이터가 포함된 회사 데이터베이스가 있습니다. Staff 테이블에는 모든 항목 외에도 직원 급여에 대한 데이터가 포함된 열이 있습니다. 표에서 선택한 항목은 다음과 같습니다(그림을 확대하려면 마우스 왼쪽 버튼으로 클릭).

모든 급여의 합계를 얻으려면 다음 쿼리를 사용합니다(MS SQL Server에서 - 접두사 USE company1; 사용).

직원에서 SUM(급여) 선택

이 쿼리는 287664.63을 반환합니다.

그리고 지금 . 연습에서 우리는 이미 작업을 복잡하게 만들기 시작하여 실제로 발생하는 작업에 더 가깝게 만듭니다.

SQL MIN 함수

SQL MIN 함수는 값이 숫자인 열에서도 작동하며 열에 있는 모든 값의 최소값을 반환합니다. 이 함수는 SUM 함수와 구문이 동일합니다.

예 3.데이터베이스와 테이블은 예제 1과 동일합니다.

42 번 부서 직원의 최저 임금을 찾아야합니다. 이렇게하려면 다음 쿼리를 작성하십시오 (MS SQL Server - 접두사 USE company1;).

요청은 10505.90 값을 반환합니다.

다시 한번 자조 운동... 이 연습과 다른 연습에서는 Staff 테이블뿐만 아니라 회사 부서에 대한 데이터가 포함된 Org 테이블도 필요합니다.

예 4.조직 테이블이 회사의 부서에 대한 데이터가 포함된 직원 테이블에 추가됩니다. 한 직원이 보스턴에 위치한 부서에서 근무한 최소 년수를 표시하십시오.

SQL MAX 함수

SQL MAX 함수는 유사하게 작동하며 유사한 구문을 가지고 있습니다. 이는 열의 모든 값 중에서 최대값을 결정해야 할 때 사용됩니다.

예 5.

부서 번호 42의 직원의 최대 급여를 찾아야합니다. 이렇게하려면 다음 쿼리를 작성하십시오 (MS SQL Server - 접두사 USE company1;).

요청은 18352.80 값을 반환합니다.

시간이 왔다 자기 해결을 위한 연습.

예 6.직원과 조직이라는 두 개의 테이블로 다시 작업하고 있습니다. 동부 지역에 속한 부서에서 한 직원이 받는 부서 이름과 최대 커미션을 표시하십시오. 사용하다 JOIN(테이블 조인) .

SQL AVG 함수

앞에서 설명한 함수에 대한 위의 구문은 SQL AVG 함수에도 적용됩니다. 이 함수는 열에 있는 모든 값의 평균을 반환합니다.

예 7.데이터베이스와 테이블은 이전 예와 동일합니다.

부서 번호가 42인 직원의 평균 서비스 기간을 찾으려고 한다고 가정합니다. 이렇게 하려면 다음 쿼리를 작성하십시오(MS SQL Server에서 - 접두사 USE company1;).

결과는 6.33의 값이 됩니다.

예 8.우리는 하나의 테이블과 함께 일합니다 - 직원. 경력 4~6년차 직원 평균 급여 빼세요.

SQL 카운트 함수

SQL COUNT 함수는 데이터베이스 테이블의 레코드 수를 반환합니다. 쿼리에서 SELECT COUNT (COLUMN_NAME) ...를 지정하면 결과는 열 값이 NULL(정의되지 않음)인 레코드를 제외한 레코드 수입니다. 별표를 인수로 사용하고 SELECT COUNT(*) ... 쿼리를 시작하면 결과는 테이블의 모든 레코드(행) 수가 됩니다.

예 9.데이터베이스와 테이블은 이전 예와 동일합니다.

커미션을 받는 모든 직원의 수를 알고 싶습니다. Comm 열 값이 NULL이 아닌 직원의 수는 다음 쿼리를 반환합니다(MS SQL Server에서 - 이전 USE company1, 구문 포함).

직원으로부터 SELECT COUNT(통신)

결과는 11입니다.

예 10.데이터베이스와 테이블은 이전 예와 동일합니다.

테이블의 총 레코드 수를 찾아야 하는 경우 별표가 있는 쿼리를 COUNT 함수에 대한 인수로 사용합니다(MS SQL Server에서 - 이전 USE company1; 구문 사용).

직원에서 SELECT COUNT(*)

결과는 17입니다.

다음에 자조 운동하위 쿼리를 사용해야 합니다.

예 11.우리는 하나의 테이블과 함께 일합니다 - 직원. 계획 부서(평원)의 직원 수를 표시합니다.

SQL GROUP BY(그룹화)를 사용하여 함수 집계

이제 SQL GROUP BY 절과 함께 집계 함수를 사용하는 방법을 살펴보겠습니다. SQL GROUP BY 절은 데이터베이스 테이블의 열별로 결과 값을 그룹화하는 데 사용됩니다. 사이트에는 이 연산자에 대한 강의는 별도로 .

우리는 "Ad Portal 1" 데이터베이스로 작업할 것입니다. 이 데이터베이스, 해당 테이블을 만들고 데이터 테이블을 채우는 스크립트는 이 링크의 파일에 있습니다. .

예 12.그래서 분류 포털 데이터베이스가 있습니다. 여기에는 해당 주에 제출된 광고에 대한 데이터가 포함된 광고 테이블이 포함됩니다. 카테고리 열에는 큰 카테고리의 광고(예: 부동산)에 대한 데이터가 포함되고 부품 열에는 카테고리에 포함된 작은 부품에 대한 데이터가 포함됩니다(예: 아파트 및 빌라 부품은 부동산 카테고리의 일부임). Units 열에는 제출된 광고 수에 대한 데이터가 포함되고 Money 열에는 광고 제출에 대해 받은 금액이 포함됩니다.

SQL GROUP BY 문을 사용하여 각 범주에서 광고를 게재하여 얻은 금액을 찾습니다. 다음 쿼리를 작성합니다(MS SQL Server에서 - 이전 구성 USE adportal1;).

카테고리 선택, 카테고리별 ADS GROUP에서 화폐로 SUM(화폐)

예 13.데이터베이스와 테이블은 이전 예제와 동일합니다.

SQL GROUP BY 문을 사용하여 각 카테고리에서 가장 많은 광고가 있는 부분을 찾으십시오. 다음 쿼리를 작성합니다(MS SQL Server에서 - 이전 구성 USE adportal1;).

SELECT Category, Part, MAX(단위) AS Maximum FROM ADS GROUP BY Category

결과는 다음 표가 됩니다.

하나의 테이블에서 전체 및 개별 값을 얻을 수 있습니다. UNION 연산자를 사용하여 쿼리 결과 결합 .

관계형 데이터베이스와 SQL 언어