SQLShack (한국어)

이 시리즈의 이전 몇 가지 기사에서 보고서 작성 방법에 대한 기초를 마련했습니다. 한 단계 더 나아가 SQL Server PIVOT 테이블 연산자를 사용하는 방법을 살펴 보겠습니다. 간단한 쿼리에서 시작하여 동적 SQL 및 PIVOT으로 천천히 진행합니다. 시작하겠습니다.

데이터 모델 및 일반적인 아이디어

우리가 사용할 데이터 모델은이 시리즈에서 사용했던 것과 동일합니다.

오늘 우리의 임무는 각 도시가 별도의 행에있는 보고서 (SQL Server PIVOT 연산자 사용)를 만드는 것입니다. , 각 도시와 관련된 모든 통화 결과의 수를 계산합니다. 따라서 모든 결과 (사전의 모든 값)는 보고서의 열이됩니다.

“사진은 천 단어의 가치가 있습니다.”이므로 최종 목표도 그림으로 설명하겠습니다.

p>

이를 위해서는 call, call_outcome, customer, city의 4 개 테이블 데이터를 사용해야합니다. 아래 모델을 자세히 살펴보고 이러한 테이블이 어떻게 관련되어 있는지 분석하는 것이 좋습니다.

먼저 테이블의 데이터를 검사하고 각 쿼리를 통해 한 단계 더 가까이 이동합니다. 여기에 사용 된 접근 방식은 SQL 학습 : SQL 쿼리를 사용하여 수동으로 보고서 만들기 문서에 사용 된 접근 방식과 동일합니다.

보고서 카테고리 및 데이터

첫 번째 쿼리 세트는 보고서를 생성하는 데 사용해야하는 4 개의 테이블 모두에 현재 존재하는 데이터를 검사하는 것입니다. 최종 보고서가 올바른 결과를 반환했는지 확인할 수 있도록이를 수행 할 것입니다. 그런 다음 쿼리를 생성합니다. ich는보고 범주 및 보고서 데이터와 SQL Server PIVOT 테이블 쿼리를 반환합니다.

이러한 쿼리는 가능한 한 간단하므로 구문과 관련하여 특별히 언급 할 사항이 없습니다. 반환 된 데이터와 관련하여 다음 사항에 유의해야합니다.

• 도시 테이블에는 6 개의 도시가 포함되어 있으므로 최종 보고서에는 6 개의 행이 있어야합니다.
• call_outcome 테이블에 3 개의 가능한 결과가 있으므로 결과에 대해 3 개의 열이 있어야합니다 (총 4 개 열-> 하나는 도시 이름에 대한 것임)
• 테이블 호출에는 10 개 행 (위 그림에서 8 개만), 따라서 최종 테이블에서 모든 결과의 합계는 10이어야합니다 (각 호출은 정확히 1 개의 결과를 가질 수 있음).

다음 작업 보고 범주를 준비하는 것입니다. 우리는 모든 도시와 가능한 모든 통화 결과의 조합을 원합니다. 이를 위해 CROSS JOIN (Cartesian 제품)을 사용합니다.

모든보고 카테고리가 있으면 해당 쌍에 데이터가 있는지 여부에 관계없이 보고서에 행이 표시됩니다. 이것이 바로 우리가 원하는 것입니다. 해당보고 범주에서 0을보고 해당 범주를 완전히 놓치는 일이 없도록하는 것입니다.

다음으로 할 일은 필요한 데이터가 포함 된 4 개의 테이블을 모두 결합하는 것입니다.

쿼리 자체는 복잡하지 않습니다. 결과적으로 데이터베이스에있는 호출 수와 같은 수인 10 개의 행이 있음을 알 수 있습니다. 또한 DATEDIFF 함수를 사용하여 각 호출 시간을 초 단위로 반환했습니다. 여기에 call.id를 넣을 수도 있지만이 기사에서 언급 한 DATEDIFF 함수를 상기시키고 싶었습니다. 각보고 범주 (도시 & 호출 결과)에 소요되는 SUM / AVG 초가 필요한 경우이 기간을 사용할 수 있습니다.

SQL Server PIVOT TABLE없이보고합니다.

이제 카테고리와 데이터를 결합 할 준비가되었습니다. 이전에 언급 한 두 쿼리를 하위 쿼리로 사용하고 LEFT JOIN을 사용하여 조인합니다 (최종 출력에 모든 범주를 표시하기 위해).

18 개의 조합 (6 개의 다른 도시 * 3 개의 다른 통화 결과)이 있으며 여기에는 10 개의 통화가 모두 있습니다 (통화 시간이 < 인 행). > NULL).

이제 SQL Server PIVOT 쿼리를 생성하겠습니다.

SQL Server PIVOT TABLE (정적)

지금까지 우리는 필요한 데이터를 가져 와서 목록으로 가지고 있습니다. 데이터를 Excel로 내보내고 거기에서 변환 할 수 있습니다. 이 작업은 다음 기사에서 실제로 수행합니다.그래도 오늘은 SQL Server PIVOT 연산자를 사용하여이 문제를 해결하려고합니다. 이 섹션에서는 “정적”SQL Server PIVOT에 대해 다룰 것입니다. 사용 된 쿼리와 결과를 먼저 살펴 보겠습니다.

결과가 정확히 우리가 원하는 결과임을 알 수 있습니다. 각 도시는 한 행에 있고 통화 결과에 대한 세 가지 범주는 모두 별도의 열에 있습니다. 특정 도시-결과 쌍에 대한 데이터가 없으면 해당 셀 값 0을 포함합니다.

이제 SQL Server PIVOT 쿼리에 대해 설명하겠습니다. 여기에서 언급해야 할 몇 가지 중요한 사항이 있습니다.

• 쿼리 – report_data, 이전 섹션의 쿼리를 복사하여 붙여 넣었습니다. 유일한 변경 사항은 여기에 적용 할 수 없기 때문에 쿼리의이 부분에 ORDER BY 및 GROUP BY가 없다는 것입니다. 최종 질의 결과가 생성됩니다.)
• 질의의 PIVOT 부분은 두 부분으로 구성됩니다. 첫 번째는 적용 할 집계 함수를 정의합니다. 여기서는 – COUNT ( call_duration). qu의 일부에서 열을 정의하겠습니다. 열로 원하는 모든 값을 문학적으로 나열합니다. 이것은 하드 코딩입니다. 테이블에 새 값을 추가하면 쿼리에 영향을주지 않습니다 (그리고 그래야합니다).

이 쿼리는 작업을 수행하며 변경하지 않으면 완벽하게 작동합니다 (새로 추가 , 삭제, 이름 업데이트) 관련 테이블의 통화 결과. 사전을 변경해야하는 경우 문제가 될 수 있습니다. 예상대로 작동하지 않는 쿼리가 있는지 생각하고 싶지 않습니다. 이 문제를 해결하려면 새로운 개념 인 동적 SQL을 사용해야합니다.

Dynamic SQL Server PIVOT TABLE

코드로 이동하기 전에 동적 SQL이 실제로 무엇인지 간단히 설명하겠습니다. (다음 기사에서 훨씬 더 자세한 검토를 할 것입니다). 가장 간단한 설명은 SQL에서 쿼리를 문자열로 “작성”한 다음 해당 문자열을 SQL Server 저장 프로 시저의 매개 변수로 전달할 수 있다는 것입니다.이 프로시 저는 해당 문자열에 저장된 SQL 문을 실행합니다 (물론 , 구문이 정상일 경우).

일반적으로 사용되지 않는 것처럼 들릴 수 있지만 작업을 훨씬 쉽게 할 수있는 곳이 많이 있습니다. 알 수없는 SQL Server PIVOT 테이블 쿼리 생성 열 수는 정확히 하나의 경우입니다.

이 접근 방식의 아이디어는 다음과 같습니다.

• 모든 열 이름 (@columns)을 저장할 변수를 선언합니다. ) 및 전체 쿼리를 저장할 변수 (@query)
• SELECT 쿼리를 사용하여 results_text 열에 저장된 모든 값을 찾습니다. 다음 코드는 @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,는 쿼리에 의해 반환 된 모든 이전 컬럼 이름 목록에 컬럼 이름을 추가합니다. 결과적으로 모든 컬럼 이름이 th에 저장됩니다. e 변수 @columns
• @query 변수에는 열이 정의 된 부분을 제외하고 이전 섹션의 전체 쿼리를 저장합니다. 이 부분은 @columns 변수에서 가져옵니다.
• 마지막으로해야 할 일은 전체 쿼리를 SQL Server 시스템 프로 시저 sp_executesql

최종 결과는 이전 섹션과 동일하지만 이번에는 다음을 변경하더라도 쿼리가 작동합니다. result.outcome_text 값. 또한이 쿼리를 쉽게 수정하고 다른 값 (예 : 도시-결과 쌍당 SUM / AVG 통화 시간)을 계산할 수 있습니다.

결론

SQL Server PIVOT 연산자는 다음을 제공합니다. 데이터베이스 계층에서 직접 달성 할 수있는 완전히 새로운 관점. 동적 SQL과 결합하면 더 나아갑니다. 나는 당신이 그것을 가지고 놀기를 강력히 권장합니다 – 직접 시도하는 것보다 더 좋은 학습 방법은 없습니다. 다음 기사에서는 SQL 쿼리 출력을 사용하고 Excel에서 테이블과 그래프를 만드는 방법을 보여줍니다 (SQL Server뿐만 아니라 일반적으로 작동 함).

목차

SQL 학습 : CREATE DATABASE & CREATE TABLE 작업

SQL 학습 : INSERT INTO TABLE

학습 SQL : 기본 키

학습 SQL : 외래 키

학습 SQL : SELECT 문

학습 SQL : INNER JOIN vs LEFT JOIN

Learn SQL : SQL 스크립트

Learn SQL : 관계 유형

Learn SQL : Join 다중 테이블

SQL 학습 : 집계 함수

SQL 학습 : 복잡한 SELECT 쿼리 작성 방법?

SQL 학습 : INFORMATION_SCHEMA 데이터베이스

SQL 학습 : SQL 데이터 유형

SQL 학습 : 이론 설정

SQL 학습 : 사용자 정의 함수

SQL 학습 : 사용자 정의 저장 프로 시저

SQL 학습 : SQL보기

SQL 학습 : SQL 트리거

SQL 학습 : SQL 쿼리 연습

SQL 학습 : SQL 쿼리 예제

SQL 학습 : SQL 쿼리를 사용하여 수동으로 보고서 만들기

SQL 학습 : SQL Server 날짜 및 시간 함수

SQL 학습 : SQL Server 보고서 만들기 날짜 및 시간 함수 사용

SQL 학습 : SQL Server 피벗 테이블

SQL 학습 : Excel로 SQL Server 내보내기

SQL 학습 : SQL Server 루프 소개

SQL 학습 : SQL Server 커서

SQL 학습 : 데이터 삭제 및 업데이트를위한 SQL 모범 사례

SQL 학습 : 명명 규칙

SQL 학습 : SQL 관련 작업

SQL 학습 : SQL Server의 비동 등 조인

SQL 학습 : SQL 주입

• 작성자
• 최근 게시물

Emil은 데이터베이스 전문가입니다. 데이터베이스와 관련된 모든 분야에서 10 년 이상의 경험을 쌓았습니다. 수년 동안 그는 IT 및 금융 업계에서 일했으며 현재 프리랜서로 일하고 있습니다.
그의 과거 및 현재 업무는 데이터베이스 설계 및 코딩에서 데이터베이스에 대한 교육, 컨설팅 및 작성에 이르기까지 다양합니다. 또한 잊지 마세요, BI, 알고리즘 생성, 체스, philately, 개 2 개, 고양이 2 개, 아내 1 명, 아기 1 명 …
LinkedIn에서 찾을 수 있습니다.
Emil Drkusic의 모든 게시물보기

Emil Drkusic의 최신 게시물 (전체보기)

• SQL 학습 : SQL 삽입-2020 년 11 월 2 일
• SQL 학습 : SQL Server의 비동 등 조인-2020 년 9 월 29 일
• SQL 학습 : SQL 관련 작업-2020 년 9 월 1 일