데이터 웨어하우스 쿼리

10분

데이터 웨어하우스의 차원 및 팩트 테이블이 데이터와 함께 로드된 경우 SQL을 사용하여 테이블을 쿼리하고 테이블에 포함된 데이터를 분석할 수 있습니다. Synapse 전용 SQL 풀의 테이블을 쿼리하는 데 사용되는 Transact-SQL 구문은 SQL Server 또는 Azure SQL Database에서 사용되는 SQL과 비슷합니다.

차원 특성별 측정값 집계

데이터 웨어하우스를 사용한 대부분의 데이터 분석에는 차원 테이블의 특성별로 팩트 테이블의 숫자 측정값을 집계하는 작업이 포함됩니다. 별 또는 눈송이 스키마가 구현되는 방식 때문에 이러한 종류의 집계를 수행하는 쿼리는 팩트 테이블을 차원 테이블에 연결하는 JOIN 절과 집계 계층을 정의하는 집계 함수 및 GROUP BY 절의 조합을 사용합니다.

예를 들어 다음 SQL은 가상 데이터 웨어하우스의 FactSales 및 DimDate 테이블을 쿼리하여 연도 및 분기별 판매량을 집계합니다.

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;

이 쿼리의 결과는 다음 표와 유사합니다.

CalendarYear	CalendarQuarter	TotalSales
2020	1	25980.16
2020	2	27453.87
2020	3	28527.15
2020	4	31083.45
2021	1	34562.96
2021	2	36162.27
...	...	...

필요에 따라 집계를 계산하는 데 필요한 만큼의 차원 테이블을 조인할 수 있습니다. 예를 들어 다음 코드는 이전 예제를 확장하여 DimCustomer 테이블에서 고객의 주소 세부 정보를 기준으로 도시별 분기 판매량 합계를 세분화합니다.

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        custs.City,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
JOIN dbo.DimCustomer AS custs ON sales.CustomerKey = custs.CustomerKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter, custs.City
ORDER BY dates.CalendarYear, dates.CalendarQuarter, custs.City;

이번에는 각 도시에 대한 분기별 판매량 합계가 결과에 포함됩니다.

CalendarYear	CalendarQuarter	City	TotalSales
2020	1	암스테르담	5982.53
2020	1	베를린	2826.98
2020	1	시카고	5372.72
...	...	...	..
2020	2	암스테르담	7163.93
2020	2	베를린	8191.12
2020	2	시카고	2428.72
...	...	...	..
2020	3	암스테르담	7261.92
2020	3	베를린	4202.65
2020	3	시카고	2287.87
...	...	...	..
2020	4	암스테르담	8262.73
2020	4	베를린	5373.61
2020	4	시카고	7726.23
...	...	...	..
2021	1	암스테르담	7261.28
2021	1	베를린	3648.28
2021	1	시카고	1027.27
...	...	...	..

눈송이 스키마에 조인

눈송이 스키마를 사용하는 경우 차원이 부분적으로 정규화될 수 있으며 팩트 테이블을 눈송이 차원과 연결하려면 다중 조인이 필요합니다. 예를 들어 데이터 웨어하우스에 제품 범주가 별도의 DimCategory 테이블로 정규화된 DimProduct 차원 테이블이 포함되어 있다고 가정해보겠습니다. 제품 범주에서 판매된 항목을 집계하는 쿼리는 다음 예제와 유사할 수 있습니다.

SELECT  cat.ProductCategory,
        SUM(sales.OrderQuantity) AS ItemsSold
FROM dbo.FactSales AS sales
JOIN dbo.DimProduct AS prod ON sales.ProductKey = prod.ProductKey
JOIN dbo.DimCategory AS cat ON prod.CategoryKey = cat.CategoryKey
GROUP BY cat.ProductCategory
ORDER BY cat.ProductCategory;

이 쿼리의 결과에는 각 제품 범주에 대해 판매된 항목 수가 포함됩니다.

ProductCategory	ItemsSold
Accessories	28271
비트 및 부분	5368
...	...

참고

쿼리에서 DimProduct의 필드가 반환되지 않더라도 FactSales 및 DimProduct에 대한 JOIN 절과 DimProduct 및 DimCategory에 대한 JOIN 절이 모두 필요합니다.

순위 함수 사용

분석 쿼리의 또 다른 일반적인 종류는 차원 특성에 따라 결과를 분할하고 각 파티션 내에서 결과의 순위를 지정하는 것입니다. 예를 들어 판매 수익별로 매년 매장의 순위를 지정할 수 있습니다. 이 목표를 달성하기 위해 Transact-SQL 순위 함수(예: ROW_NUMBER, RANK, DENSE_RANK 및 NTILE)를 사용할 수 있습니다. 이러한 함수를 사용하면 범주에 따라 데이터를 분할할 수 있으며, 각각은 파티션 내 각 행의 상대 위치를 나타내는 특정 값을 반환합니다.

ROW_NUMBER는 파티션 내 행의 서수 위치를 반환합니다. 예를 들어 첫 번째 행의 번호는 1, 두 번째 행은 2입니다.
RANK는 정렬된 결과에서 각 행의 순위가 지정된 위치를 반환합니다. 예를 들어 판매량별로 정렬된 매장 파티션에서 판매량이 가장 높은 매장의 순위는 1입니다. 여러 매장의 판매량이 같으면 동일한 순위가 지정되고 이후 매장에 할당되는 순위는 동점을 포함하여 판매량이 더 높은 매장의 수를 반영합니다.
DENSE_RANK는 RANK와 동일한 방식으로 파티션의 행 순위를 지정하지만, 여러 행의 순위가 같으면 후속 행 순위 위치는 동점을 무시합니다.
NTILE은 행이 속하는 지정된 백분위수 값을 반환합니다. 예를 들어 판매량별로 정렬된 매장 파티션에서 NTILE(4)는 매장의 판매량이 배치되는 사분위수 값을 반환합니다.

예를 들어 다음 쿼리를 참조하십시오.

SELECT  ProductCategory,
        ProductName,
        ListPrice,
        ROW_NUMBER() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS RowNumber,
        RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank,
        DENSE_RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS DenseRank,
        NTILE(4) OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile
FROM dbo.DimProduct
ORDER BY ProductCategory;

쿼리는 범주에 따라 제품을 그룹화로 분할하고 각 범주 파티션 내에서 각 제품의 상대 위치는 정가에 따라 결정됩니다. 이 쿼리의 결과는 다음과 같을 수 있습니다.

ProductCategory	ProductName	ListPrice	RowNumber	순위	DenseRank	Quartile
Accessories	위젯	8.99	1	1	1	1
Accessories	Knicknak	8.49	2	2	2	1
Accessories	Sprocket	5.99	3	3	3	2
Accessories	Doodah	5.99	4	3	3	2
Accessories	Spangle	2.99	5	5	4	3
Accessories	Badabing	0.25	6	6	5	4
비트 및 부분	Flimflam	7.49	1	1	1	1
비트 및 부분	Snickity wotsit	6.99	2	2	2	1
비트 및 부분	Flange	4.25	3	3	3	2
...	...	...	...	...	...	...

참고

샘플 결과는 RANK 및 DENSE_RANK 사이의 차이점을 보여 줍니다. 액세서리 범주에서 Sprocket 및 Doodah 제품은 정가를 같고, 두 제품 모두 3번째로 높은 가격의 제품으로 순위가 지정되었습니다. 다음으로 높은 가격의 제품은 RANK가 5(이보다 더 비싼 제품이 4개 더 있음)이고 DENSE_RANK가 4입니다(더 높은 가격이 3개 있음).

순위 함수에 대한 자세한 내용은 Azure Synapse Analytics 설명서의 순위 함수(Transact-SQL)를 참조하세요.

대략적인 개수 검색

데이터 웨어하우스의 목적은 주로 엔터프라이즈에 대한 분석 데이터 모델 및 보고서를 지원하는 것입니다. 데이터 분석가와 데이터 과학자는 몇 가지 초기 데이터 탐색을 수행하여 데이터의 기본 스케일과 분포를 결정해야 하는 경우가 있습니다.

예를 들어 다음 쿼리는 COUNT 함수를 사용하여 가상 데이터 웨어하우스에서 매년 판매량 수치를 검색합니다.

SELECT dates.CalendarYear AS CalendarYear,
    COUNT(DISTINCT sales.OrderNumber) AS Orders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

이 쿼리의 결과는 다음과 같을 수 있습니다.

CalendarYear	Orders
2019	239870
2020	284741
2021	309272
...	...

데이터 웨어하우스의 데이터 볼륨은 지정된 조건을 충족하는 레코드 수를 계산하는 간단한 쿼리만 실행하는 데도 상당한 시간이 걸릴 수 있음을 의미할 수 있습니다. 대부분의 경우 정확한 개수가 필요하지 않으며 대략적인 추정치로 충분합니다. 그러한 경우에 다음 예와 같이 APPROX_COUNT_DISTINCT 함수를 사용할 수 있습니다.

SELECT dates.CalendarYear AS CalendarYear,
    APPROX_COUNT_DISTINCT(sales.OrderNumber) AS ApproxOrders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

APPROX_COUNT_DISTINCT 함수는 HyperLogLog 알고리즘을 사용하여 대략적인 개수를 검색합니다. 결과는 최대 오류율이 2%이고 확률은 97%이므로 이전과 동일한 가상 데이터를 가진 이 쿼리의 결과는 다음 표와 비슷할 수 있습니다.

CalendarYear	ApproxOrders
2019	235552
2020	290436
2021	304633
...	...

개수는 정확도가 떨어지지만 연간 판매량을 대략적으로 비교하기에는 충분합니다. 많은 양의 데이터를 사용하면 APPROX_COUNT_DISTINCT 함수를 사용하는 쿼리가 더 빠르게 완료되며, 그만큼 낮아지 정확도는 기본 데이터 탐색 중에 허용 가능한 수준일 수 있습니다.

참고

자세한 내용은 APPROX_COUNT_DISTINCT 함수 설명서를 참조하세요.

데이터 웨어하우스 쿼리

차원 특성별 측정값 집계

눈송이 스키마에 조인

순위 함수 사용

대략적인 개수 검색

피드백